Обсуждение: Index corruption
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
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 > > ************
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
> 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
> 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
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
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
> 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
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';
}
> 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
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
> > 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
> 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
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
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
> 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
> 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
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) #
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) #
> -----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
> 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
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) #