Duplicate key after a vacuum

From: "Gaetano Mendola" <mendola(at)bigfoot(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Duplicate key after a vacuum
Date: 2003-04-30 17:05:08
Message-ID: b8ovo3$b3i$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi, I already cross posted this to bug and admin but I find the message
neather in admin and in bugs, anyway I repost it:

This problem is the second time that happen, the first time
Tom Lane suggest me that may be an impromper db shut down
did this, but this time I'm sure that is not the scene.

Here is the query:

empdb=# select oid,id_user_traffic, id_user from user_traffic where id_user
= 4338;
oid | id_user_traffic | id_user
---------+-----------------+---------
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
1432439 | 2884 | 4338
1432446 | 2885 | 4338
1432455 | 2886 | 4338
1432987 | 6780 | 4338
1433032 | 6781 | 4338
1434031 | 5976 | 4338
1435875 | 6782 | 4338
1439351 | 11109 | 4338
1437999 | 11110 | 4338
1438117 | 11111 | 4338
1438117 | 11111 | 4338
(21 rows)

empdb=# select oid,id_user_traffic,id_user from user_traffic where
id_user_traffic = 11111;
oid | id_user_traffic | id_user
---------+-----------------+---------
1438117 | 11111 | 4338
(1 row)

I think that this select return one row just because Postgres know
that id_user_traffic is a primary key!

This time I know that this happen just after a vacuum, you'll find attached
the log about the
process that did the vacuum.

PS:
empdb=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

begin 666 f2.zip
M4$L#!!0````(`&"8GBY,,FKAS1<``'[N```&````9C(N;&]GQ5W?D]LVDGZ_
MOX)OFU2=)P3X>U)Y<!S?ENNRZUQBIVIW*ZOB2/0,-Y*H)2G'WK_^0&I(D6RP
M&RT2Y9?$'H^^5G\--!J-;N#EJ70\UW&C>Q'<NZY3[4KIG(JJ?BRSZA_"#\/?
M[AWU_\2-I?N;\^/;/]\[SK8X'K-MG1='I\RV6?XQV]T[3^I#W_UC7VS3_6__
M]9(!*S2PZ;E^*LK\/PWPN<K*[W8/Z>Z0'YU=6J</:95]EQU.NP>6'/F;\\/K
M[]\WDKXY5^4W#_GQF^97#VE59Z6S?<KWN^XC59V6=7Y\=/[(ZR?G*Y8<[RKG
MGV^ZWV$A^$.$%Q^%)]Q(LA""$<*)]=EP^%D^S='UXU^S/AA?/_CFF-<_W<)<
M<L7XI3'ANS(]5FD[IEX5AT-ZW''@O'[$__N<E9_OG8?L,3]^ZU397HU3YS&K
MN^&8';?%3HV7K[[^5(at)WCPR&O67+$]6O_5!;;K*K>U_D^KS^S4 8#_%7['19J
M[ZU+I(at)^4_+^&519&L I1X<I$1=TPV9W+M &Y=]P[UY6)4)_+MBRL>%W2D\D(
M_N7UN\:Y%I=!ZKQ[Z_RI./[IVU]?OGK__B^.\^OKG[]_^\MKY^5?7_[XM[^_
M=CBR?'<-X_AB7>/X<E5"?6\5)=5<>//7_WFK0%Z\^#G;MX/&.3UNMLJ?[(O'
M._7'759MR_S4_,N+%RSPX(7HX7]*'[/*$?+>>?64'A^SG>/^M_/Z<*H_.^ZW
MSKOSR1%>XMX[OZ;;YE_^-\M.S?_?']]7ZG?%'5.P(ON?;]X52(at)GGU4_OVUE0
M?=/\]]Q,!$=I>FIPU4_"Y@<\^'"&M+I0*_A=]X>-"'T1,BF+`&7N/&.S=+D\
M?2(&72Z?KKC7Z.4QW7_^3Q/.S ZQ";) D1-Z]#Z6Q?D$C(#"!BX<M_-&D'-&
M\*8L44(7&@&'%X9C5H:"R9:T,V0IH5;9\L(at)AVXXK%J:!JSTI1\YD'_K8)$1\
M;*Q YWQLZ/-(TGE9H;>!"/@V,/6R4GT1'F>6G"PEU.J(I9UL,[A8D ;>M<S^
M*/,ZX_$?0O_J18B#E>$Z+C:<=[$"#%B^"4)3%ZO"`I?)&/2QZC.SC$7".(ZB
MQ)J/V81/&.UEG\<7"]7 S]:?3]PQJXEE$:<1-.99Q02<4#;BFR D3=!PQ8*,
M:/[3NB[SAS/;<<3 "&$R;P3?12:"QUOLE&CCQ>X6.R2D'7K..+B12QMCNT^K
MBF>(2$ /'L\;(@SG[2 2R6)*B39VXC>LHY$D#='R-<&4**9'&R$_/F7*TT$[
MX,B^G4B&$KHPDL'A`]("'5DLV+EP<H2[RSXQ+0!C260BR& M&RR.)G%X.IIL
MN6)A&H23Q2DKT[HH>3:(-?MU#_%&OK>.$>+%6W8<7I!&Z/ABP4H3(dot)^(at)7!1S8
MXZ1-(at)MDE@6D$SZX1? ,CZ%8$#T4-:!ND!T _CAERZ/>-V:>$+F0?AX](]M,#
M"S V(;Z "4,<->%0+V+S18 2:Y/\Q#4(at)OYCFP'#(N2WR"%.;!<-Q-?MCQ/7,
MIL!X_">+DY X/+T]OG#%`C78'>\5;6=%(M,&FAWR#?X'[ 8HH59-0&^/.[)8
ML 9;Y'U:/F;%P[^R;<VT`]PDK[(GH(1:M0.]/1[PQ4!.7(,-<OJH/O>8:K(5
M.#3<)",3(ES'$HU4BY9(7'I_W-/%PC78(]=E_OB8P<T!#(at)RWR-@AM.>:;PXH
MN5;M0.^2G^F:H/HHJL$F>9]7=7;4F %'MG3F0(at)E=: ,<GMXE=V2Q8 TVRMNT
M(at)LL"BBK@)AFKQ8C,-PB46',32+8)!+U';JAB01KLCX_I(:M.Z1:N"#(at)T:X>\
ME(at)46;Y!Q>'J#W+/%PC78(E=/Z:[X(at)VD#N$U&YD$2&Q_C4%*M&F$NH(35'?#D
M$4>V%$U20JVR14>3EY'%`94F)RW%\6-65KI".AP;1I*8[Q8K>0ZY.)3$X0V.
M6GK"6, &L>0N.V7''=,,,)0,$/\=>6XR9P(at)?5"Y0DLU/'V.^)>A(at)\L(7"]0@
MEFS.-'?9!Z898"B)U$((;[9 CSD;[ :3<BXS":I'O-AG$@;3D_9]N%R<G$3A
M/8/DY&5PL5 -\I/*)U5UF>9'9(at)#NP1PE4KL@DI6<N*=+4LZY#GX`[LWY6LVP
MA;70.+2EPVU*J-5A2WO:Z_B:``<HL(&W[7J$(at)!EP9(dot)AND45O-GL"RFTHH0O-
M@,.;.EL92B9;EGPM)=0F6S[M:[NAQ8(U<+95K7Y:U3DX#Q)8RT'B0U\;N&(0
M)431M#+#%?YDKR>2( X'A4JN%T=WS&\!S!*$K5F"L5D"?=1&P!N[7M^-F?S9
M<;VDT&6CF(#7)Q3.#_M\>Z>&6PXV900>3"2HR Q)YR3Q;#*!290NF>#K2^ \
MW9).P&L/W0<\3<#0/+6O][07M'.E-G> =1P/^E<O0(K'_62^J0RP3DF&K'OZ
MX2EUQ= X?*!UL(at)(dot)>)F!HF#'3[7-!.SVFFZ8),\\(at)]SBJG4X?4NA"KX##FSK1
M2"9(at)VT5 6W*BE%"K=&GC5S"N6)#ZR/6*^:$H#]R1:N>TB11JE7K3F%6-U(1)
MEYV(at)E11JDZX0\:?]J&(!4C[UTL+*'*F:SA[[U+/Z>FZ(at)WMBG>B[3IX9?PJ>&
M=GUJ2/C4YW'%(at)J1\:IF=BI(]5K^$5PWM>M70V*MZ+M.KAE_"JX9VO6I$>-7G
M<<6"I/WJ4W9(>=Q'7\*M1G;=ZFP;$1RJ(at)NE6+?41D4*MTD6ZU698L1 IK[I/
M/Q=G<"Q @'X)I[JX?8B -W:J(at)0L(dot)!0CH+^%4([M.-2:<ZF58L1 QG[HOBM_/
MITV=/NRY&8!84V>/=%H)7\RV[D_KO$G!R\K("'AMJ;V&KC&HC%!0?:G]<THG
MW319G39QOLD^(at)883&:/0PUK[-TTGGJ,P-OFNN>3A8[[+RONN3B0,6EOLFS\+
MW_7OG1^R?59G\$2&%-E:8,0]2#=J1S\!'&IUV>:;-MG8*^*'*RD2VE(DTBM2
MG(]U<^/9<[92K&60R$@/CZ]'K-6CJ-JK`'H]_+7TB(WT$'P]$KT]TG+7*Q%$
M*RF1:)2 GHEOC,35*M&<BYVK3(at)VUBJZC1N(:J1'PU1!:-50`EN\[+?Q K*2%
M,!I1-V(at)AYUS51)&51E778#92Q%MC5'E:1?8J*&N*=7N?&_(at)K*>(9C2O=PDT`
M^[.+!] E7DD7WT(at)7_OJ1Z!?UHGS(5="S.=7'?L+[,EE)&5NK>J)?U9NC_-^S
MLKJN(FO->5NK>J)?U4_Y=1%QUS*&V8JNN["2`-:OZ(=TNTEW._7K5WM$:\UX
MLU7]!A^L7]4'NFR.17E(>V_LQ2L%*HENC5\>< G7A6O\='O0A/;M#W[/^DA2
M^-X:BK7R313C>F<%#%=]H%@;Y _5DOXJTZF5;DFM81CP<W8H/C;?S:DOWSD_
M.L(Y-:IP06TL*PH8-ATE4(at)Y*^(dot)2T%(dot)I">K-M][M2*!G)>% *)5QP:Q/Y)6:*
M)T0\U3'479]%P,]UM4\33K$;@<LB"6C8UKY&PHD4NBSA1,!KV]KUB8DQKH?4
ML"A<?1O2!;@ZGYJS(at)6S7[L! XHE AFU(6!&J\=TRI-1E9B#(at)M7U(6K8XL$+?
MB73!/:1'Q1ZLNB(@6:WL*[$O..U'NO0``2\-?484P'H*`AJZW#5\!BG4YF#5
M]WF.!Q4+#RW'S ^:N_ (0-;]1\*X*XB4:I5UK!:S)8F%AA5C5MF_S]EQ"X\$
MO #%A/%XOONTZ=':I'2Z_5T9I$]7>;*/\$3SDL$UPI/^-*X(at)I0^C)FTYIKA+
M=,3[*+"$,3E4JRP>R_1PS3!(WUPO4OQ +^\RH/RQ7LE=K/-Z/KKT2QB2ZW0:
M!N1Q&#+4HJ0/U!*>+J$E9\R%`^M"<O7MAD&YU$;E).ZM43D!K+D*0"!US,_$
M-RY+J=6%Y6XD(at)MYQQ4$X;4HGOP/P77[4JAA-II!_%]TPUI EHQ]T+$!LS=B5
M!S4]RX_Y-MN4^>-3K?X&SN()?+B$K+%NDT+A"C+7H'?#0$-6$"UE+'!L06G0
M;S6$G5-^4NBRI1R'UW>L:JAB(at)0K"`-OB6)?IMJXZ0_,,H6E:M6\(;<LJ)Z-+
MP".'_5K*6.#8H?\(G6D(.VD(4JC5&8&D(494L4"Q'$1]?7)KTSJ_\\/LVU:$
M&#OWHI!"K=H#R4=(at)S'%D^%AR8IE)?)BDL&\2?^D=*02\Q*?(S6;0UY*"L(!I
M`3MEI*10JQ9 RD@'1+$(at)L3K20U95#7-,YC77T ]NEX I?F_R/HD4\KJ3$)X?
M\$A:6D]*P)O6D\8R`*DZ`OI+1)K:5M+UZ,):2;OAQ<+#(at)LQFD<Z.M?9)'0(6
MO;193'/ZZU"_N)V4(at)$=BRR%3+$PLI(dot)P**C8?SL=V=68Z#LW[<?;'O_;EN!6-
M@,25D"\6,A9<=HCM?8=,*\!H$KW@;?9>,5XZ+N#$D_S\S^P#<N"$Q0U!PPH.
MK7E"SOZP1=Z/6V/8#AI,P; =C2T6J/Z4JYL,:7VN-FULG]_B/4+6A:9B+3O,
MGW2MD1^8?4 .#MO(8_+U);PMZPFY&^A"O2T88"QHM)[(at)V9,S+< J)/"-+V$C
MI5HU@:FC#9LZ219?T9=PM)%=1QLACK8;52P\S,=V#3E]P,'DG^5AC0^U2:%6
M^4?.A0!=+&#L?&B;](at)G=YG23:0;-94^(&>1*NY5H:74!`8^<#4WH8L%BIT+9
M`;QK1:#!'3H2&Z]'O=T].M;SJ2AB06';\Z8-HDFD'[,;G(^FW3,:7*(GIGOT
M6+B3%2 (HD'9:#2]]9(at)4;VZ"@&\"K.ES3!L+%MNN-\^B_)%^9(at)8M,>MI)?/2
M)E*JU1E(at)^KY[V/1?\_BRTU-/"K5*E_:QD?&H8N'I8T9XVO.45W51,M?+!(:-
M$?(NJA B7LEO)[K84<YL#GVV&08OV*%G9,^LL:"Q,+)9CL_/#2!,[Y%H0DCD
MGLIF.S$Y&[BZ<-ZQ0&(WCDR0.'+$%PL4BR%;Q&-1<X]G$DWXZ"/57IZ(D]F;
M$YCS01="SEU;><-\0$+(*UL3Q 1%Q*+'='?(CWES/[::7= (.+#FZM!Y$R2S
M_(at)CD6"FI"^<`!B]FWZ^#MP)*\/0+`6WI/)T2:I4NY#Q]/+98J-AI^BF'S2X$
MG.;)NGGB(at)Y6(7WR,CL-CMS'ETP87`(at)L[/W]N`M5X:!P3AHS8+C,PC[$IL599
M1X+&GB(at)6(!8U/I;%^51MVK]53[K4+(JM>:$.2\VN-.Y9#]3Q+:!_H&Z&+Q8P
M%BU>D)GT\QZG6XO^Q2$B#F]\EA/X$9,O2V<YE%"K="%G.9<QQ4+#CF_R8YV5
M'],]=Y1JSF^P7(@7SA[AB)#'S>+#<AP>*;[LN>(`SCQ(UZ?9FS8?0'[(at)HI"P
M>:@#:GN'.D<V[![RHD&S5^C*:_-0Z$TC:5)Z:X!GZB\/:<@Q]>+.UR4"`XD"
MPVM]FEZO7K7G4\S3/KUJY5ZO^!%!\\0`HA4E?*B5/Z.5KB>>`(9W_(RTZJW5
M]WG%YH:B) ]5$CJ59MY("+#^12'A;3\CE4;W_;B^N3J45*(at)(dot)F/)!S%<'7OAS
MM<Y@"B6K:1(8&4:7!2" A_F-KOU.?;MA^UVH;;\C<8=?F+/0$< PW/>PBJN6
M]F8944H]+R0BB))^+?&]F"L?+";B,O<G]V'X^J>!"'CS"U(at)CD!4@H.T4S))"
MEX4].#S6FM7-2!8>=B)7[0O8_$/@P7.XP6"=%G>+:);U:7\H*=8JZ\CY6TO2
M`$SZ]RZZYJ!]5_\J'L:,-W !"@==<P/21#G-5QM=PZ1^O??/RB?X,KQZZ-$P
M-Y$Z<'<7?R G7=_RS(at)?^H %&KJ)5P/ VMF;A;%52T4U9-]>]]$NG"FCD2"//
M132B! \U:IN+/7>J$;S2K %&HUL/7LO6:P1O'&CN+QIK)!"-*,%#C0*=1N(N
M!L&-`L8>@%+ \)*VH4;-L)M7)YI7AY0*#"0GW=_>70+"3QI8?R]$-^0&MY=>
M59'QS6J8W;\*G!0)[(dot)OO(at)5 [,ENJ^&9WL&I502ZY5L!P]]:J, PUX<A*$#TH
M<<.1U1[!2A!L^KJI3P#K[GKP1#((-F4,@TT37(,X'V[M:6"860N$A]S UJXB
MSPDVI5AW#.L&XEI-$R3<[P#6\DC-=G$73!88H;R7;H4A\)&SV&:43;#091!]
MJ;%L+HZH:K">$Y"LRX$"L^2FB53S`,K3<8[#(V>O'4TL/.SD=2:K3X/"_0+2
MY2778G[1AH&$#XQ/70,I>7P%%DY=381:I0LY==6>?M"(V(EK4_NAOV:!QF4=
MO<JUZ%]T]DK#(V>O(ZY8H-(at)A[*!XF,D_Z[%QLXL&381:I1\YA!T0Q8+$CF&O
M]Y> T@,26--7AY2*!<%*$V!99QT-CQS"CMEBH6(GL,-:P-;(<]=:T&)8Y[*>
MV1O3)E(7G';1\$BXB%''DD%V(at)]QN$]Y=D^Y:1EG4#D+#4^T(at)MQH"BRN'UFX+
M!M4//GS(M\ @(2H"1ID^8I%8)N8FH01#DURVC(at)*8!!RLD/#8>XMSS+'PL4QU
M"SH?2.' ,&4=8R^%24_,%EP*P>.,D;86VEF"PR-IZS%C+%0L?]TA;HI35K;_
MHN\FI*78:"0QD;K0;>'PIHTD41(at)G3+XL-)*8"+5*%Q:(dot)S(at)\TE@@L/%6?N]6I
MQ# Z1<[!I.%]`R9B;1IDT+\'S\%V-_J3& M.!S&O^H?V)72F(6!,BE7+)N99
M'4JL54-(at)_220,A8T%HL.%G*N'6 <&H3SAHC<^9MC/(_'E2X4E3.FT!Z&X?!(
M*#JD:X*)GD7&6!(at)Z[Y%P3$W<B4P$;]8CP70^)7?A3$#A$R3LG'-'." 99[;O
M`/'(3V",&0QB3)E,H_XPBN;X'U]X9R)Z8=B/PQL_]>VZ,*3!H2W<T6(at)BU(dot)IP
M11*<U\'%0L2RF^?N?1PF\YH6$P_A'ANN'I-_74CI<PZB<'CC<K/FI(%'FH5R
M,Q.A%H>K=!'OVH^M"2#6A"E=S+M>;S'0CUD<67,#!-+'K8;L;-MJ`&Q 23:W
M@;8\!8?'+VH<,C:!Q:J#Y,Q#9%-7U+V#"8R!(at)VMV[X@Q/-^?]1\RFK)%238W
M1J S!(at)Z/O4<&2&,A8RTHVW-9-A=RSER/0H/K*[:>X;+^(at)5#I#Y^>#>/!BX:Q
MY!$U?E5SYEH#<8,%]-5:^6GZ0*B*S'M=A(SC01U=`-)TE$A8YP2#)]UJ)+$^
M$2E(at)R1:T]+CJ:?2V/&X(at)4K:!@:3NP$1B&R(Y>H&M*WJ*1P7VB;;FB80EC1#J
M*TX)8+AP^,E@:R1"D*UI>6_<5=R7V'NN&UY]EA<DP!K4EX!N*VEU#,8Z1G?R
M!AVQ-02,(dot)!8RF(at)W>IU6UV64?TO.^GGHL`I>5_S6\2LM$ZK)PBH!'5H\15Q-0
M-$9#>Q>[-0DD"SS\B\+>Q42YP?D,9N(E,IX-J,([IO")$<2=:'>L,AP;(;GS
M=0F;`/7F6/MB3]<0,+AW44^*MB_VB)M=KFB<S (2&I98)0GBFA(93DY"@OCJ
MED(Y[L(R$6]^/@57"A(>*;::\L;"12^YT.>124Q6N97AD:V!T"7NB(37IWHU
MFV'/"YET67B?S$2H5;J03*\VU:X`!0I(5AN<"A443NM825BTCG7*O>G1DX'4
MA>2C\%CCVY J%B:9ZM56>)"P,*#T_ `)7GS?G1P[-:O4M6533=3Q=68F7V%)
M310)3Q46:.H[2$PLD'QNDF).!,U+9,,L9CS-0HA)`"."ZTORL<>;#LL>)*/A
M32L)XJ:5FL>:C4H"`Z%6Z4(J";JQ-<'#>CBEAY4--&\MG<IBFU5PO**HOH4;
MS$V$+F0>AT=*!(at)9$L2"Q(at)H%+5EH!PTT/B6OA+703H5;I1PH%1E2Q0.G+R^M:
M">+RSRM578G_Q3$C#D_>6WYABH6)-C]=O)D"WI[+O/[,- $K=)S-V3--L#AR
M1.&Q-\:F;+%PL>CQ4#WJVTI(5-938[,U,CP++'MJC(9'PL4!41-(K/58FKTT
M5APWJ>:TA 2W<%^:B="%-L#A35X:Z_EB(6/YQ^Z1C*8D7^T(/C$-P7H")YY=
M$(at)2/JD5/X-#PID_(at)!*X73&[")<%MO#9F(M0F85A/U'1\L7"Q.+('9O+/*C<5
MLRMH$/(H6AQ%XO &S^!,0Q@<4$60/[S^_OV?%>*KXG#(ZW?74LGF!^EQQ\)3
MWNW'MPW:[GRI!K]W0M>[<Y-07JX(8*%%UV_7[$\VV:>\_LK]FH417S&JIT-V
MN DDN8)T'_]_4$L!`A0`% ````(at)`8)B>+DPR:N'-%P``?NX```8`````````
F`0`(at)`+:!`````&8R+FQO9U!+!08``````0`!`#0```#Q%P``````
`
end

Browse pgsql-admin by date

  From Date Subject
Next Message Jodi Kanter 2003-04-30 17:46:55 Re: LC_CTYPE
Previous Message Tom Lane 2003-04-30 14:56:34 Re: LC_CTYPE