Thread: Index corruption

Index corruption

From
Adriaan Joubert
Date:
Hi,
   Something in my pg_proc table got corrupted and when trying to
vacuum it, vacuum would create thousands of index files in the database
directory. It would just go into an endless loop and all it seems to do
is create files. Anybody else seen this before? I've tried everything
and am now in the process of dumping all the data out table by table and
rebuilding the database from scratch. Any ideas what I can do to avoid
this or any idea how this could have happened? I'm running 6.5.2 on
Digital Unix 4.0F.

Cheers,

Adriaan



Re: [HACKERS] Index corruption

From
Ed Loehr
Date:
Were other queries occurring during your vacuum?  There have been reports
in these mailing lists that concurrent queries cause vacuum failures...

Cheers,
Ed Loehr

Adriaan Joubert wrote:

> Hi,
>
>     Something in my pg_proc table got corrupted and when trying to
> vacuum it, vacuum would create thousands of index files in the database
> directory. It would just go into an endless loop and all it seems to do
> is create files. Anybody else seen this before? I've tried everything
> and am now in the process of dumping all the data out table by table and
> rebuilding the database from scratch. Any ideas what I can do to avoid
> this or any idea how this could have happened? I'm running 6.5.2 on
> Digital Unix 4.0F.
>
> Cheers,
>
> Adriaan
>
> ************



Re: [HACKERS] Index corruption

From
Tom Lane
Date:
Adriaan Joubert <a.joubert@albourne.com> writes:
>     Something in my pg_proc table got corrupted and when trying to
> vacuum it, vacuum would create thousands of index files in the database
> directory. It would just go into an endless loop and all it seems to do
> is create files. Anybody else seen this before?

No, that's a new one AFAIK.  I don't suppose you saved the state of your
DB before rebuilding it?  I'd like to try to reproduce the problem...
        regards, tom lane


Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
> No, that's a new one AFAIK.  I don't suppose you saved the state of your
> DB before rebuilding it?  I'd like to try to reproduce the problem...

No, sorry. I got increasing desperate as this was a production system and I
was under a bit of pressure to get it back up. A day earlier I had had a
complaint about the number of tuples in the index being incorrect. At the
third attempt I managed to run vacuum over it without the backend crashing
and the it seemed to behave well. Next morning I ran vacuum again and then I
ended up with the endless file-creation loop. Oh yes, to get it to vacuum
I had to delete all my functions (pg_proc) and then reload them. I know that
all my procedures are small enough not to break the 8K limit, as I used to
have trouble with that. I tried the same trick, i.e. dropping and reloading
my functions, but no luck. As most of what they do is to enforce referential
integrity, Jan's foreign key stuff may solve a large part of the problem!

I had the system logging with debug level 3 and there was nothing in the
logs. Did anything get fixed in this area between 6.5.2 and 6.5.3? I.e.
should I upgrade? I'd rather not just at the moment.

Merry Christmas!

Adriaan





Re: [HACKERS] Index corruption

From
Bruce Momjian
Date:
> I had the system logging with debug level 3 and there was nothing in the
> logs. Did anything get fixed in this area between 6.5.2 and 6.5.3? I.e.
> should I upgrade? I'd rather not just at the moment.

No:Release 6.5.3This is basically a cleanup release for 6.5.2. We have added a newpgaccessthat was missing in 6.5.2,
andinstalled an NT-specific fix.Migration to v6.5.3A dump/restore is not required for those running 6.5.*.Detailed
ChangeListUpdated version of pgaccess 0.98NT-specific patch
 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
Adriaan Joubert wrote:

>     Something in my pg_proc table got corrupted and when trying to
> vacuum it, vacuum would create thousands of index files in the database
> directory. It would just go into an endless loop and all it seems to do
> is create files. Anybody else seen this before? I've tried everything
> and am now in the process of dumping all the data out table by table and
> rebuilding the database from scratch. Any ideas what I can do to avoid
> this or any idea how this could have happened? I'm running 6.5.2 on
> Digital Unix 4.0F.

OK, I've got the same problem again. I upgraded to 6.5.3 just in case there
was something different, and
it makes no difference. Thousands of pg_proc_prosrc_index..<id> files and
vacuum just seems to be in and endless loop. I really need to get this
pinned down. Any ideas where I could start looking?

In the logs I only get this
NOTICE:  --Relation pg_proc--
NOTICE:  Pages 30: Changed 0, Reapped 5, Empty 0, New 0; Tup 1074: Vac 25,
Keep/VTL 0/0, Crash 0, UnUsed 2, MinLen 145, MaxLen 3013; Re-using:
Free/Avail. Space 30108/30108; EndEmpty/Avail. Pages 0/5. Elapsed 0/0 sec.

so the relation pg_proc did not even change!

I'd really appreciate any help on this one -- I'm getting an awful lot of
stick for this.

Adriaan




Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
Further to my problem: I've run the vacuum in a backend under the debugger and
at least figured out in which
loop the zillions of files are created. It is in md.c. In the stack trace
below, you can see that blockno has got a totally stupid value and in the loop
in lines 1049-1063 in md.c (v 6.5.3. sources) it opens zillions of files. If
anybody knows where this comes from I'd appreciate it. In the meantime I'll
try to dig a bit further.

Cheers,

Adriaan


>0  0x12012d8d4 in _mdfd_getseg(reln=0x1402488c0, blkno=745239393) "md.c":1051

#1  0x12012c7a8 in mdread(reln=0x1402488c0, blocknum=745239393,
buffer=0x14012ec70="\334") "md.c":414
#2  0x12012ddfc in smgrread(which=0, reln=0x1402488c0, blocknum=745239393,
buffer=0x14012ec70="\334") "smgr.c":226
#3  0x12011b724 in ReadBufferWithBufferLock(reln=0x1402488c0,
blockNum=745239393, bufferLockHeld='\000') "bufmgr.c":302
#4  0x12011b4c0 in ReadBuffer(reln=0x1402488c0, blockNum=745239393)
"bufmgr.c":180
#5  0x120063ce8 in _bt_getbuf(rel=0x1402488c0, blkno=745239393, access=1)
"nbtpage.c":304
#6  0x120069950 in _bt_step(scan=0x14025ca68, bufP=0x11fffb6c8,
dir=ForwardScanDirection) "nbtsearch.c":1131
#7  0x12006867c in _bt_next(scan=0x14025ca68, dir=ForwardScanDirection)
"nbtsearch.c":706
#8  0x120065140 in btgettuple(scan=0x14025ca68, dir=ForwardScanDirection)
"nbtree.c":390
#9  0x12017f238 in fmgr_c(finfo=0x11fffb780, values=0x11fffb7a8,
isNull=0x11fffb778="") "fmgr.c":135
#10 0x12017f81c in fmgr(procedureId=330) "fmgr.c":336
#11 0x120057fa0 in index_getnext(scan=0x14025ca68,
direction=ForwardScanDirection) "indexam.c":316
#12 0x120091714 in vc_vaconeind(vpl=0x11fffba38, indrel=0x1402488c0,
num_tuples=1074, keep_tuples=0) "vacuum.c":2015
#13 0x12008d874 in vc_vacone(relid=1255, analyze='\000', va_cols=0x0)
"vacuum.c":532
#14 0x12008cb80 in vc_vacuum(VacRelP=0x11fffbae8, analyze='\000', va_cols=0x0)
"vacuum.c":267
#15 0x12008c974 in vacuum(vacrel=0x14025b060="", verbose='\001',
analyze='\000', va_spec=0x0) "vacuum.c":150
#16 0x120133b08 in ProcessUtility(parsetree=0x14025b080, dest=Debug)
"utility.c":638
#17 0x120130060 in pg_exec_query_dest(query_string=0x11fffbcc0="vacuum verbose
pg_proc;\n", dest=Debug, aclOverride='\000') "postgres.c":727
#18 0x12012fea8 in pg_exec_query(query_string=0x11fffbcc0="vacuum verbose
pg_proc;\n") "postgres.c":656
#19 0x120131980 in PostgresMain(argc=2, argv=0x11ffffd08, real_argc=2,
real_argv=0x11ffffd08) "postgres.c":1647
#20 0x1200be424 in main(argc=2, argv=0x11ffffd08) "main.c":103
#21 0x12003fb28 in __start(0xb3f, 0x0, 0x2, 0x0, 0x0, 0x1402cc040) in postgres





Re: [HACKERS] Index corruption

From
Bruce Momjian
Date:
> Further to my problem: I've run the vacuum in a backend under the debugger and
> at least figured out in which
> loop the zillions of files are created. It is in md.c. In the stack trace
> below, you can see that blockno has got a totally stupid value and in the loop
> in lines 1049-1063 in md.c (v 6.5.3. sources) it opens zillions of files. If
> anybody knows where this comes from I'd appreciate it. In the meantime I'll
> try to dig a bit further.

Were you the person I told to use pg_upgrade and re-initdb your
database? If not, I would recommend that as the fix.  You may need to
re-enable pg_upgrade by editing the script.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
Hi,

I've dug around a bit more and things seem to be going wrong in
access/nbtree/nbtsearch.c. I've printed out everything that seemed even remotely
relevant and I'm hoping that somebody can tell me what is going on here. Is this
just a corruption of the index?  I know dropping and re-creating a system index
is not allowed, but is there some way that I could disable that check and
recreate the index?

Regards,

Adriaan

in _bt_step at line 1131

(ladebug) p *opaque
struct BTPageOpaqueData { btpo_prev = 1949202277; btpo_next = 745239393; btpo_parent = 779576686; btpo_flags = 25705;
}
(ladebug) p page
0x1400fac70="\024"

(ladebug) p *rel
struct RelationData { rd_fd = 14; rd_nblocks = 0; rd_refcnt = 1; rd_myxactonly = '\000'; rd_isnailed = '\000';
rd_isnoname= '\000'; rd_nonameunlinked = '\000'; rd_am = 0x14024a000; rd_rel = 0x1402486d0; rd_id = 17030; lockInfo =
0x14024e430="\206B";rd_att = 0x1401d3df0; rd_rules = 0x0; rd_istrat = 0x14024a410; rd_support = 0x140249fe0; trigdesc =
0x0;
}

(ladebug) p *(rel->rd_am)
struct FormData_pg_am { amname = union nameData {   data = "btree";   alignmentDummy = 1701999714; }; amowner = 2001;
amkind= 'o'; amstrategies = 5; amsupport = 1; amgettuple = 330; aminsert = 331; amdelete = 332; amgetattr = 0;
amsetlock= 0; amsettid = 0; amfreetuple = 0; ambeginscan = 333; amrescan = 334; amendscan = 335; ammarkpos = 336;
amrestrpos= 337; amopen = 0; amclose = 0; ambuild = 338; amcreate = 0; amdestroy = 0;
 
}

(ladebug) p *(rel->rd_rel)
struct FormData_pg_class { relname = union nameData {   data = "pg_proc_prosrc_index";   alignmentDummy = 1885300592;
};reltype = 0; relowner = 2001; relam = 403; relpages = 22; reltuples = 1073; relhasindex = '\000'; relisshared =
'\000';relkind = 'i'; relnatts = 1; relchecks = 0; reltriggers = 0; relukeys = 0; relfkeys = 0; relrefs = 0; relhaspkey
='\000'; relhasrules = '\000'; relacl = [0] = 0;
 
}


(ladebug) p *(rel->rd_att->attrs[0])   [ Only one attribute ]
struct FormData_pg_attribute { attrelid = 17030; attname = union nameData {   data = "prosrc";   alignmentDummy =
1936683632;}; atttypid = 25; attdisbursion = 0; attlen = -1; attnum = 1; attnelems = 0; attcacheoff = -1; atttypmod =
-1;attbyval = '\000'; attisset = '\000'; attalign = 'i'; attnotnull = '\000'; atthasdef = '\000';
 
}




Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
> Were you the person I told to use pg_upgrade and re-initdb your
> database? If not, I would recommend that as the fix.  You may need to
> re-enable pg_upgrade by editing the script.

Nope, but I've tried this now and it fails miserably for me. I had to edit the file
created by pg_dump quote heavily as I have user-defined types and they required
some initialisation before they could be used in the definition of indexes. Anyway,
I managed to work around that, and pg_upgrade claimed that everything had finished
successfully. But, although the datafiles were in the right place and the right
size all the tables were empty. So some system tables were evidently not
initialised correctly. I tried this both with 6.5.2 and 6.5.3, but no luck either
way.

Any other suggestions would be very welcome.

Regards,

Adriaan



Re: [HACKERS] Index corruption

From
Tom Lane
Date:
pg_proc_prosrc_index is the problem, eh?  I'll bet a nickel that you're
seeing still another manifestation of btree's problems with oversized
index entries.  (See recent thread 'Error "vacuum pg_proc"'.)

Check to see if you have any functions whose definitions exceed 2700
bytes, eg withselect proname from pg_proc where length(prosrc) > 2700;
If so, you need to rewrite them to be smaller, perhaps by breaking
them into multiple functions.

7.0 should fix this problem, but it's a real hazard in 6.5.
        regards, tom lane


Re: [HACKERS] Index corruption

From
Bruce Momjian
Date:
> > Were you the person I told to use pg_upgrade and re-initdb
> your > database? If not, I would recommend that as the fix.
> You may need to > re-enable pg_upgrade by editing the script.
> 
> Nope, but I've tried this now and it fails miserably for me. I
> had to edit the file created by pg_dump quote heavily as I have
> user-defined types and they required some initialisation before
> they could be used in the definition of indexes. Anyway, I
> managed to work around that, and pg_upgrade claimed that everything
> had finished successfully. But, although the datafiles were in
> the right place and the right size all the tables were empty.
> So some system tables were evidently not initialised correctly.
> I tried this both with 6.5.2 and 6.5.3, but no luck either way.

All I can say is someone did this recently for a system index problem
and it worked.

-- Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Index corruption

From
Bruce Momjian
Date:
> pg_proc_prosrc_index is the problem, eh?  I'll bet a nickel that you're
> seeing still another manifestation of btree's problems with oversized
> index entries.  (See recent thread 'Error "vacuum pg_proc"'.)
> 
> Check to see if you have any functions whose definitions exceed 2700
> bytes, eg with
>     select proname from pg_proc where length(prosrc) > 2700;
> If so, you need to rewrite them to be smaller, perhaps by breaking
> them into multiple functions.
> 
> 7.0 should fix this problem, but it's a real hazard in 6.5.

Wow, do we need that 7.0 release!


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
Tom Lane wrote:

> pg_proc_prosrc_index is the problem, eh?  I'll bet a nickel that you're
> seeing still another manifestation of btree's problems with oversized
> index entries.  (See recent thread 'Error "vacuum pg_proc"'.)
>
> Check to see if you have any functions whose definitions exceed 2700
> bytes, eg with
>         select proname from pg_proc where length(prosrc) > 2700;
> If so, you need to rewrite them to be smaller, perhaps by breaking
> them into multiple functions.

Yep, I've got two of those. I saw the message about  lengths in indexes,
but howcome this is relevant for procedures? I thought it would only be an
index on name and a pointer into pg_proc? Just asking because I want to
understand how this works.

I'll rewrite them and see whether that fixes it. Thanks a lot for the
help!

Adriaan



Re: [HACKERS] Index corruption

From
Tom Lane
Date:
Adriaan Joubert <a.joubert@albourne.com> writes:
>> Check to see if you have any functions whose definitions exceed 2700
>> bytes, eg with
>> select proname from pg_proc where length(prosrc) > 2700;
>> If so, you need to rewrite them to be smaller, perhaps by breaking
>> them into multiple functions.

> Yep, I've got two of those.

Bingo ...

> I saw the message about  lengths in indexes,
> but howcome this is relevant for procedures?

In 6.5 (and before), there's an index on the prosrc field of pg_proc,
ie, the definition of the procedure.  There's not any real good reason
to have such an index, so we've removed it for 7.0 ... but in 6.5 it's
there and it creates problems if you have long procedure definitions :-(
        regards, tom lane


Re: [HACKERS] Index corruption

From
wieck@debis.com (Jan Wieck)
Date:
> OK, I've got the same problem again. I upgraded to 6.5.3 just in case there
> was something different, and
> it makes no difference. Thousands of pg_proc_prosrc_index..<id> files and
> vacuum just seems to be in and endless loop. I really need to get this
> pinned down. Any ideas where I could start looking?

    I  made  the  patch below against 6.5.2 (you aren't the first
    who had trouble with it). Hopefully it will  apply  on  6.5.3
    too,  haven't  checked.   It removes the prosrc index. Due to
    the catalog changes you need  to  dump  (BEFORE),  reinstall,
    initdb and reload.

    Should  we  eventually put it somewhere on the FTP server and
    have a pointer in the FAQ?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #


begin 644 remove_prosrc_idx_6.5.2.diff.gz
M'XL("!^I1S@"`W)E;6]V95]P<F]S<F-?:61X7S8N-2XR+F1I9F8`U5A[;YM6
M%/^;?HK35)K`PPT8OZM.BF+<><N2S,ZV5E5E$;A.4&UP`6_QHGSWG7,N$+#!
M:;6IVZS(IN>>QSWO'_7\Q0*:;@1QY+X,(__F^-IQ/XK`.W:=Q%F&-\=^X(D[
M/[AYZ1+/@>-GC4;C<]0HX\B'D7`!+#!;0\,<6@:8@\'@6;/9?,+&CJS91G$I
MVRA_^#)M2^\8P/\$4$Z2)/*O-XDXWZPFI%%':DZ<BJ7O,?GA%=+=6R=2`*!Q
M[JS$?'TS7T>A.\>+^*Z(WZ.&7=H'>`WWET@0WB82))7:>`Y*3KY(31`U)\XB
M]X#=9+L6NW:+-+9[A82"20`B7!STQUTZ<;RKN$1DS:=$*:BF%&%8VP/`I^9_
M+*S_BPA6%:K5,W1KT,M*E<+J>R)V49YK?QZN11"I^W'0Z&9*LEDO!?*>RG[A
MD[%(W-LK.E!OA;/&/#B)'P8ZL&(=XH]BJX.%"IJ`?XJTXR[#6*C,DI]$(ME$
M`;`1HCU(.OY]CXK9!#[OE?/,=0(U,POE.R3B+H$&Y1FY-92^)T/9N?2=3"FD
MY$>Q':%C?./WY@>FYY:5_%H%=CM(HNTD\!/?6?I_"O4;%C4^Z,3#'\RC>NTG
ML=G5P+@SR@=4O)C.:Q%I8):/IN(F=U2#\?S*?GME_USBN0S](!'1&Y'@K3<K
M-?/R,=*',IM%C[G_1F)-K@PNI(K$X@DULM4=Z%:O+5O9>W(3I*U9LPCRT\-[
M(&?[\C50)VH:0V-0OP5,R]1-JRN;BW1+0B^?8`HTCOG77X#Z/$XB=[7FM.6]
MIL,;^]R>3DYG]M7YR4^VIC'__;-OX86_\,0"\&`V'T_>VB,^45`<7>`Z?\WE
M[@>JI,F652BW>#033N3>SK;QJ>/>"IG6R^G%;'JJ2[:LKBK**C6@Z35N]W6S
M;17='B"AX+:R7D1"%#6E5Z,XY!TVB7_%+O)4O&_JME(8"<WODKF'^:'?T/=>
M443$,A;XBWP"TZ;:T^G%5(>C91A^1)\7801Y<.%Z"W&XB5P!@1!>#`O_#M0?
MG$`[TJ0NG*0+MOI`W_1UH%(WB;^,L5XPE,?Q-N:'G7*M9JFNV6K>BL*UJ@OW
M\^2[PY957[V6U=&MCIFC&"5<+&*1A`MU'$8K&HRT=B+Q1^0G6*?B]_FGC;/4
M9/6<_W)VQD\J3<;Q)G`U(-H#3ZS'O9H-$:YU13EN@*Q!:@P:;>D4O)=S[B0H
M[&E9/>D$-'9_^4=:J[XXZ=`A[0S)MS<+F5SPH'+5/+"?]Q=K$3E)&/'6+;D%
MZ-7IV<G('E^]N[2SEC?UM)-Y'E*LVT;=//0#=[GQQ#XLO>6LUQ^7JZN>KZ*R
MNN7*^GS9WM`Z@(Q;IM[*X08T8!:N!.`<\VEIAD',79JB&,#UC9ER,DC'$A2]
M%RP@((4^Q)`A'T6Q$*'M,!1Q'3'L:2BB,D5I[3.4T%45`]T!*3F+R9&3WJ9S
M[]_QMO5UO*V<('V]W<YRG6FH`.AP5/2;G^ALSC5V5)#=P]W`HNQP>%`@QZV/
M`O@5$*`.G.@Q)"3>W!?/^AU*XM@7^R:+\)U0V5$6\,H+EM!_B1V_^'I[(CQ@
M=BW(C!5,\%3!^%NR^JK2TV_I_7Z6'ES"`A=KOGSW8WT(5B,/K]:)QYN\7EGN
MZP%M&?P`^>HCX;J<I(A$6D`)BZ7-!CY2"&-&K?5FO_S5H-*-8FZ?"@<EL2X<
MI:P?4B3]3U+V:F7EU[RGM6%OI<JH1OJFWN_6UXAIM'33Z!?>#T<V[K*I/9^<
MC^RWZE[WI5.,B+")<4'`=4)8#QF`F,)UK+$?U7IJFK).:\H.+(.J>:%S?7"I
MY"2DD+*8KM#/[M"LOT/>V0<,(P_7S*-+55Z56S]51\0OC,[^3*C3E3+E,='2
M3)N&@:GL/(4Q)'3,06,98>P>5N.+7:X*=-&O1A=/27:&^/94BRTZ`[W;V]TW
M;Z87].*$\Z1EE<FS=[/)",GM`GEJ_S:=7-E2HM4I+%>)2TE-MT!]Q'5XT"L<
MG)V<7Y!VI=67E8$@L+GSX0VO*/C>MW$38)?I-7F([T$!`H.5[&%Z/1$>(P4'
IF99+2$*@_V/(WMU4C>-(WG<SK/%/>U_RLUOI9^\K^/D76W26Z346````
`
end

Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
>     I  made  the  patch below against 6.5.2 (you aren't the first
>     who had trouble with it). Hopefully it will  apply  on  6.5.3
>     too,  haven't  checked.   It removes the prosrc index. Due to
>     the catalog changes you need  to  dump  (BEFORE),  reinstall,
>     initdb and reload.

Great! Thanks a lot, I'll rebuild it all and get it back up. A real life-saver -- I was
getting quite a bit of flak and Oracle was coming up in conversations ;-(. Now I can at
least show them that Postgres response to problems beats anything other db's can offer ;-)

>     Should  we  eventually put it somewhere on the FTP server and
>     have a pointer in the FAQ?

Yep, I think so. It is not obvious that this should be a problem (I certainly didn't
expect an index on the procedure source) and it causes severe problems.

Thanks a lot, Jan!

Adriaan



Re: [HACKERS] Index corruption

From
wieck@debis.com (Jan Wieck)
Date:
Tom Lane wrote:

> pg_proc_prosrc_index is the problem, eh?  I'll bet a nickel that you're
> seeing still another manifestation of btree's problems with oversized
> index entries.  (See recent thread 'Error "vacuum pg_proc"'.)
>
> [...]
>
> 7.0 should fix this problem, but it's a real hazard in 6.5.

    I  already  posted  a patch that removes pg_proc_prosrc_index
    from 6.5.2.  This one is definitely not fixable  by  anything
    else (except changing all functions to <2700).

    Anyway,  I still think that a new implementation of reindexdb
    would be good. Some of the system indices can cause big,  big
    trouble,  if  they  get corrupted. If you would ever be faced
    with a corrupted pg_class_... index, you  won't  be  able  to
    dump  any  more,  because the backend will fail to startup at
    all.

    I analyzed the problem of recreating system  catalog  indices
    some weeks ago, and ISTM that during bootstrap operation, ALL
    tuples are visible.

    I hacked in a "drop index" for the bootstrap parser, and on a
    freshly  created  DB some hand-made BKI script ran smooth and
    recreated  all  the  indices  well.  But  it  failed  on  the
    regression  DB,  bacause it bombed out with duplicate errors.
    First I was a little puzzled  about  it,  because  I  allways
    thought  that  only  vacuum removes index tuples. So it could
    only be the main tuples visibility  that  prevents  from  dup
    errors between vacuum times.

    Thus,  IMHO  there  should  be  another  command added to the
    bootstrap parser.  This would recreate ALL  existing  indices
    (system  and user ones), but tell the visibility code somehow
    to ignore deleted tuples. I don't have the time to do it now,
    so at least I'd like to have a TODO item for it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [HACKERS] Index corruption

From
wieck@debis.com (Jan Wieck)
Date:
Tom Lane wrote:

> Adriaan Joubert <a.joubert@albourne.com> writes:
>
> > I saw the message about  lengths in indexes,
> > but howcome this is relevant for procedures?
>
> In 6.5 (and before), there's an index on the prosrc field of pg_proc,
> ie, the definition of the procedure.  There's not any real good reason
> to have such an index, so we've removed it for 7.0 ... but in 6.5 it's
> there and it creates problems if you have long procedure definitions :-(

    The usage of it is only #ifdef'd out!

    It's  a  very old standing FEATURE, that doesn't work anyhow.
    It has to do with tuple set's, and as far as I read the  code
    in  question,  the  (no  longer  supported either) nested dot
    notation looked for a 'sql' language function returning a set
    of tuples and created that on the fly.  Therefore, it checked
    by the required functions source text if it exists.

    IIRC the #ifdef is somewhat like SETS_FIXED.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

RE: [HACKERS] Index corruption

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Jan Wieck
> 
>     Anyway,  I still think that a new implementation of reindexdb
>     would be good. Some of the system indices can cause big,  big
>     trouble,  if  they  get corrupted. If you would ever be faced
>     with a corrupted pg_class_... index, you  won't  be  able  to
>     dump  any  more,  because the backend will fail to startup at
>     all.
> 
>     I analyzed the problem of recreating system  catalog  indices
>     some weeks ago, and ISTM that during bootstrap operation, ALL
>     tuples are visible.
> 
>     I hacked in a "drop index" for the bootstrap parser, and on a
>     freshly  created  DB some hand-made BKI script ran smooth and
>     recreated  all  the  indices  well.  But  it  failed  on  the
>     regression  DB,  bacause it bombed out with duplicate errors.
>     First I was a little puzzled  about  it,  because  I  allways
>     thought  that  only  vacuum removes index tuples. So it could
>     only be the main tuples visibility  that  prevents  from  dup
>     errors between vacuum times.
> 
>     Thus,  IMHO  there  should  be  another  command added to the
>     bootstrap parser.  This would recreate ALL  existing  indices
>     (system  and user ones), but tell the visibility code somehow
>     to ignore deleted tuples. I don't have the time to do it now,
>     so at least I'd like to have a TODO item for it.
>

I may be able to implement reindex command unless you have
the time to do it.

Different from your suggestion,I would implement it in a non-bootstrap
standalone postgres with the new option by which PostgreSQL ignores
system indexes.   

Comments ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp


Re: [HACKERS] Index corruption

From
Adriaan Joubert
Date:
> I may be able to implement reindex command unless you have
> the time to do it.
>
> Different from your suggestion,I would implement it in a non-bootstrap
> standalone postgres with the new option by which PostgreSQL ignores
> system indexes.
>
> Comments ?

That would be really useful. BTW, I've been running the patched database
fora little while now and all my problems seem to have gone away. Thanks
a lot to all of you!

Adriaan



Re: [HACKERS] Index corruption

From
Jan Wieck
Date:
Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Jan Wieck
>
> >     Thus,  IMHO  there  should  be  another  command added to the
> >     bootstrap parser.  This would recreate ALL  existing  indices
> >     (system  and user ones), but tell the visibility code somehow
> >     to ignore deleted tuples. I don't have the time to do it now,
> >     so at least I'd like to have a TODO item for it.
> >
>
> I may be able to implement reindex command unless you have
> the time to do it.
>
> Different from your suggestion,I would implement it in a non-bootstrap
> standalone postgres with the new option by which PostgreSQL ignores
> system indexes.
    Sounds good to me.



Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #