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/
Show quoted text
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
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 +1100The 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 pointingstill
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/
Import Notes
Resolved by subject fallback
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)
SallyFrom: 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 +1100The 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 pointingstill
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/
Show quoted text
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