Re: reindexing, pg_class (and other system tables) - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: reindexing, pg_class (and other system tables)
Date
Msg-id 20040225233112.GB24038@svana.org
Whole thread Raw
In response to Re: reindexing, pg_class (and other system tables)  ("Sally Sally" <dedeb17@hotmail.com>)
List pgsql-general
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

Attachment

pgsql-general by date:

Previous
From: "Sally Sally"
Date:
Subject: Re: reindexing, pg_class (and other system tables)
Next
From: Michael Chaney
Date:
Subject: Re: Moving from MySQL to PGSQL....some questions