Skip site navigation (1) Skip section navigation (2)

Re: reindexing, pg_class (and other system tables)

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Sally Sally <dedeb17(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: reindexing, pg_class (and other system tables)
Date: 2004-02-25 23:31:12
Message-ID: 20040225233112.GB24038@svana.org (view raw or flat)
Thread:
Lists: 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(at)svana(dot)org>
> >Reply-To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
> >To: Sally Sally <dedeb17(at)hotmail(dot)com>
> >CC: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> >
> >--
> >Martijn van Oosterhout   <kleptog(at)svana(dot)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(at)postgresql(dot)org so that your
>      message can get through to the mailing list cleanly

-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)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

In response to

pgsql-general by date

Next:From: Michael ChaneyDate: 2004-02-26 00:09:23
Subject: Re: Moving from MySQL to PGSQL....some questions
Previous:From: Sally SallyDate: 2004-02-25 23:20:17
Subject: Re: reindexing, pg_class (and other system tables)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group