Duplicate key after a vacuum - Mailing list pgsql-admin

From Gaetano Mendola
Subject Duplicate key after a vacuum
Date
Msg-id b8ovo3$b3i$1@news.hub.org
Whole thread Raw
List 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@WCPR&O67+$]6O_5!;;K*K>U_D^KS^S4 8#_%7['19J
M[ZU+I@^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@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@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.^@7!1S8
MXZ1-@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@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!#@RWR-@AM.>:;PXH
MN5;M0.^2G^F:H/HHJL$F>9]7=7;4F %'MG3F0@E=: ,<GMXE=V2Q8 TVRMNT
M@LL"BBK@)AFKQ8C,-PB46',32+8)!+U';JAB01KLCX_I(:M.Z1:N"#@T:X>\
ME@46;Y!Q>'J#W+/%PC78(E=/Z:[X@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@?5"Y0DLU/'V.^)>A@\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@#NP1PE4KL@DI6<N*=+4LZY#GX`[LWY6LVP
MA;70.+2EPVU*J-5A2WO:Z_B:``<HL(&W[7J$@!EP9.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@.>)F!HF#'3[7-!.SVFFZ8),\\@]SBJG4X?4NA"KX##FSK1
M2"9@VT5 6W*BE%"K=&GC5S"N6)#ZR/6*^:$H#]R1:N>TB11JE7K3F%6-U(1)
MEYV@E11JDZX0\:?]J&(!4C[UTL+*'*F:SA[[U+/Z>FZ@WMBG>B[3IX9?PJ>&
M=GUJ2/C4YW'%@J1\:IF=BI(]5K^$5PWM>M70V*MZ+M.KAE_"JX9VO6I$>-7G
M<<6"I/WJ4W9(>=Q'7\*M1G;=ZFP;$1RJ@NE6+?41D4*MTD6ZU698L1 IK[I/
M/Q=G<"Q @'X)I[JX?8B -W:J@0L.!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^@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@VUBJZC1N(:J1'PU1!:-50`EN\[+?Q K*2%
M,!I1-V@AYUS51)&51E778#92Q%MC5'E:1?8J*&N*=7N?&_@K*>(9C2O=PDT`
M^[.+!] E7DD7WT@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@Y*^.2T%.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@6S7[L! XHE AFU(6!&J\=TRI-1E9B#@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@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@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@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=@S'%D^%AR8IE)?)BDL&\2?^D=*02\Q*?(S6;0UY*"L(!I
M`3MEI*10JQ9 RD@'1+$@L3K20U95#7-,YC77T ]NEX I?F_R/HD4\KJ3$)X?
M\$A:6D]*P)O6D\8R`*DZ`OI+1)K:5M+UZ,):2;OAQ<+#@LQFD<Z.M?9)'0(6
MO;193'/ZZU"_N)V4@$=BRR%3+$PLI.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)[@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;]@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@4;VZ"@&\"K.ES3!L+%MNN-\^B_)%^9@8M,>MI)?/2
M)E*JU1E@^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_@CD6"FI"^<`!B]FWZ^#MP)*\/0+`6WI/)T2:I4NY#Q]/+98J-AI^BF'S2X$
MG.;)NGGB@Y6(7WR,CL-CMS'ETP87`@L[/W]N`M5X:!P3AHS8+C,PC[$IL599
M1X+&GB@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'[@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*@.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@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@?^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)[.OO@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-@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@]QN$]Y=D^Y:1EG4#D+#4^T@MQH"BRN'UFX+
M!M4//GS(M\ @(2H"1ID^8I%8)N8FH01#DURVC@*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@G3+XL-)*8"+5*%Q:.S@\TE@@L/%6?N]6I
MQ# Z1<[!I.%]`R9B;1IDT+\'S\%V-_J3& M.!S&O^H?V)72F(6!,BE7+)N99
M'4JL54-@_220,A8T%HL.%G*N'6 <&H3SAHC<^9MC/(_'E2X4E3.FT!Z&X?!(
M*#JD:X*)GD7&6!@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@BU.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!@VMV[X@Q/-^?]1\RFK)%238W
M1J S!@Z/O4<&2&,A8RTHVW-9-A=RSER/0H/K*[:>X;+^@5#I#Y^>#>/!BX:Q
MY!$U?E5SYEH#<8,%]-5:^6GZ0*B*S'M=A(SC01U=`-)TE$A8YP2#)]UJ)+$^
M$2E@R1:T]+CJ:?2V/&X@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,.!8RF@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=*!@9$L2"Q@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$@2/JD5/X-#PID_@!*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`% ````@`8)B>+DPR:N'-%P``?NX```8`````````
F`0`@`+:!`````&8R+FQO9U!+!08``````0`!`#0```#Q%P``````
`
end


pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: LC_CTYPE
Next
From: Jodi Kanter
Date:
Subject: Re: LC_CTYPE