Thread: reindexing, pg_class (and other system tables)
Very strange things are happening when I am reindexing a table. I did this with postmaster running as a standalone. The index for the table prior to reindexing is on a separate disk and I created a sym link (due to lack of space). When the reindexing is done I see new files created under /data/base/dbnumber/ but when I look at pg_class table it is pointing still to the old index because the oid I get is the older one. What does this mean? I assumed reindexing a non-sys table with postmaster running as a standalone would also update the relevant tables. Do I have to do a separate process to update the pg_class and other tables that need to be aware of the reindexing? Sally _________________________________________________________________ Stay informed on Election 2004 and the race to Super Tuesday. http://special.msn.com/msn/election2004.armx
The filename on disk is stored in relfilenode, not in oid. On Wed, Feb 25, 2004 at 10:17:47PM +0000, Sally Sally wrote: > Very strange things are happening when I am reindexing a table. I did this > with postmaster running as a standalone. The index for the table prior to > reindexing is on a separate disk and I created a sym link (due to lack of > space). When the reindexing is done I see new files created under > /data/base/dbnumber/ but when I look at pg_class table it is pointing still > to the old index because the oid I get is the older one. What does this > mean? I assumed reindexing a non-sys table with postmaster running as a > standalone would also update the relevant tables. Do I have to do a > separate process to update the pg_class and other tables that need to be > aware of the reindexing? > Sally > > _________________________________________________________________ > Stay informed on Election 2004 and the race to Super Tuesday. > http://special.msn.com/msn/election2004.armx > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the Catholic church can survive the printing press, science fiction > will certainly weather the advent of bookwarez. > http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow
Attachment
wait a minute, I thought it was under pg_class that you can look up the actual number because so far for all the others it has been the same (the oid field under pg_class) Sally >From: Martijn van Oosterhout <kleptog@svana.org> >Reply-To: Martijn van Oosterhout <kleptog@svana.org> >To: Sally Sally <dedeb17@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] reindexing, pg_class (and other system tables) >Date: Thu, 26 Feb 2004 10:01:06 +1100 > >The filename on disk is stored in relfilenode, not in oid. > >On Wed, Feb 25, 2004 at 10:17:47PM +0000, Sally Sally wrote: > > Very strange things are happening when I am reindexing a table. I did >this > > with postmaster running as a standalone. The index for the table prior >to > > reindexing is on a separate disk and I created a sym link (due to lack >of > > space). When the reindexing is done I see new files created under > > /data/base/dbnumber/ but when I look at pg_class table it is pointing >still > > to the old index because the oid I get is the older one. What does this > > mean? I assumed reindexing a non-sys table with postmaster running as a > > standalone would also update the relevant tables. Do I have to do a > > separate process to update the pg_class and other tables that need to be > > aware of the reindexing? > > Sally > > > > _________________________________________________________________ > > Stay informed on Election 2004 and the race to Super Tuesday. > > http://special.msn.com/msn/election2004.armx > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >-- >Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > If the Catholic church can survive the printing press, science fiction > > will certainly weather the advent of bookwarez. > > http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow ><< attach3 >> _________________________________________________________________ Watch high-quality video with fast playback at MSN Video. Free! http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
The first time the table is created, it uses the oid because it's known to be unique. When it's rebuilding an index or anything that requires the recreation of the table, it needs to create a new file with a new filename (still need to be able to rollback on error remember). Since the oid of the table cannot change, the filename is stored in relfilenode. Hope this makes it clearer. On Wed, Feb 25, 2004 at 11:20:17PM +0000, Sally Sally wrote: > wait a minute, I thought it was under pg_class that you can look up the > actual number because so far for all the others it has been the same (the > oid field under pg_class) > Sally > > > >From: Martijn van Oosterhout <kleptog@svana.org> > >Reply-To: Martijn van Oosterhout <kleptog@svana.org> > >To: Sally Sally <dedeb17@hotmail.com> > >CC: pgsql-general@postgresql.org > >Subject: Re: [GENERAL] reindexing, pg_class (and other system tables) > >Date: Thu, 26 Feb 2004 10:01:06 +1100 > > > >The filename on disk is stored in relfilenode, not in oid. > > > >On Wed, Feb 25, 2004 at 10:17:47PM +0000, Sally Sally wrote: > >> Very strange things are happening when I am reindexing a table. I did > >this > >> with postmaster running as a standalone. The index for the table prior > >to > >> reindexing is on a separate disk and I created a sym link (due to lack > >of > >> space). When the reindexing is done I see new files created under > >> /data/base/dbnumber/ but when I look at pg_class table it is pointing > >still > >> to the old index because the oid I get is the older one. What does this > >> mean? I assumed reindexing a non-sys table with postmaster running as a > >> standalone would also update the relevant tables. Do I have to do a > >> separate process to update the pg_class and other tables that need to be > >> aware of the reindexing? > >> Sally > >> > >> _________________________________________________________________ > >> Stay informed on Election 2004 and the race to Super Tuesday. > >> http://special.msn.com/msn/election2004.armx > >> > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > >-- > >Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > >> If the Catholic church can survive the printing press, science fiction > >> will certainly weather the advent of bookwarez. > >> http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow > ><< attach3 >> > > _________________________________________________________________ > Watch high-quality video with fast playback at MSN Video. Free! > http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the Catholic church can survive the printing press, science fiction > will certainly weather the advent of bookwarez. > http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow