Re: [HACKERS] pg_upgrade bug found!

2011-04-09 Thread Bruce Momjian
bricklen wrote: On Fri, Apr 8, 2011 at 10:01 PM, bricklen brick...@gmail.com wrote: Update on the status of the steps we took, which were: - test on a hot standby by bringing it live, running the script, determing the missing clog files, copying them into the live (hot standby) pg_clog

Re: [HACKERS] pg_upgrade bug found!

2011-04-09 Thread Bruce Momjian
Alvaro Herrera wrote: Why is it important to have the original pg_clog files around? Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum and thus removed if they were leftover from aborted

Re: [HACKERS] pg_upgrade bug found!

2011-04-09 Thread Bruce Momjian
Bruce Momjian wrote: Alvaro Herrera wrote: Why is it important to have the original pg_clog files around? Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum and thus removed if they were

Re: [HACKERS] pg_upgrade bug found!

2011-04-09 Thread Aidan Van Dyk
On Sat, Apr 9, 2011 at 7:03 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Why is it important to have the original pg_clog files around?  Since the transactions in question are below the freeze horizon, surely the tuples that involve those

Re: [HACKERS] pg_upgrade bug found!

2011-04-09 Thread Noah Misch
On Sat, Apr 09, 2011 at 09:05:42AM -0400, Aidan Van Dyk wrote: On Sat, Apr 9, 2011 at 7:03 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Why is it important to have the original pg_clog files around? ?Since the transactions in question are below

Re: [HACKERS] pg_upgrade bug found!

2011-04-09 Thread Bruce Momjian
Aidan Van Dyk wrote: On Sat, Apr 9, 2011 at 7:03 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Why is it important to have the original pg_clog files around? ?Since the transactions in question are below the freeze horizon, surely the tuples

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Noah Misch
On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote: Noah Misch wrote: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of pg_class.relfrozenxid twiddling and SET

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Noah Misch wrote: On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote: Noah Misch wrote: 1) The pg_class.relfrozenxid that the TOAST table should have received (true relfrozenxid) is still covered by available clog files. Fixable with some combination of

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote: One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. I don't understand. Can you elaborate? Regards,

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0; True; it just performs more

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote: One concern I have is that existing heap tables are protecting clog files, but once those are frozen, the system might remove clog files not realizing it has to freeze the heap tables too. I don't understand. Can

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Jeff Davis wrote: On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE) n-freeze_min_age = n-freeze_table_age = 0;

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Noah Misch
On Fri, Apr 08, 2011 at 10:05:01AM -0700, Jeff Davis wrote: On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote: Right, VACUUM FREEZE. I now see I don't need to set vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has: if (n-options VACOPT_FREEZE)

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: New version; I made some other small adjustments: -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8 -- servers that was upgraded by pg_upgrade and pg_migrator. -- Run the script using psql for every database in the cluster -- except

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: OK, now that I have committed the fixes to git, I think it is time to consider how we are going to handle this fix for people who have already used pg_upgrade, or are using it in currently released versions. I am thinking an announce list email with this query would be

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
Bruce, * Bruce Momjian (br...@momjian.us) wrote: OK, here is a draft email announcement: Couple suggestions (also on IRC): --- A bug has been discovered in all released versions of pg_upgrade and (formerly) pg_migrator.

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Stephen Frost wrote: -- Start of PGP signed section. Bruce, * Bruce Momjian (br...@momjian.us) wrote: OK, here is a draft email announcement: Couple suggestions (also on IRC): Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in pg_dump, and hence a new pg_upgrade binary will not work; you

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never move the relfrozenxid backward; and (b) there is no way to

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian (br...@momjian.us) wrote: Yes, I like your version better; I did adjust the wording of the last sentence to mention it is really the release, not the new pg_upgrade, which fixes the problem because the fixes are in

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: I am worried if I mention pg_dump that people will think pg_dump is broken, when in fact it is only the --binary-upgrade mode of pg_dump that is broken. I adjusted the wording of the last paragraph slighly to be clearer, but hopefully not confuse. We don't actually

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Noah Misch
On Fri, Apr 08, 2011 at 12:16:50PM -0700, Jeff Davis wrote: On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote: 1. Make relfrozenxid go backward to the right value. There is currently no mechanism to do this without compiling C code into the server, because (a) VACUUM FREEZE will never

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Josh Berkus
-- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic period and watch the database load.

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this script during a low traffic

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance. Users should execute this

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Bruce Momjian wrote: Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: -- It will not lock any tables but will generate I/O. add: IMPORTANT: Depending on the size and configuration of your database, this script may generate a lot of I/O and degrade database performance.

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Jeff Davis
On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in the migrated databases. You might want to clarify that the fix may be

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 4:00 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote: A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4. These releases will remove the need for the above script by correctly updating all TOAST tables in

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 4:51 PM, bricklen brick...@gmail.com wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be running the suggested script shortly, but can anyone tell me in how bad of shape my db is

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
Hi Stephen, On Fri, Apr 8, 2011 at 6:57 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I've been noticing in my logs for the past few days the message you note in the wiki. It seems to occur during a vacuum around 7:30am every day. I will be

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL: Could not open file pg_clog/04BE: No such file or directory. Great! And there's no file in pg_clog which matches that

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the error message DETAIL:  Could not open file pg_clog/04BE: No such

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 7:20 PM, bricklen brick...@gmail.com wrote: On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: bricklen, * bricklen (brick...@gmail.com) wrote: I looked deeper into our backup archives, and it appears that I do have the clog file reference in the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will hopefully prevent autovacuum from 'butting in' and causing issues

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. One recommendation is to also increase autovacuum_freeze_max_age to 5 (500m), which will

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats.  One recommendation is to also

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Stephen Frost
bricklen, * bricklen (brick...@gmail.com) wrote: Thanks guys, I really appreciate your help. For the vacuum freeze, you say database-wide, should I run vacuumdb -a -v -F ? Will freezing the other tables in the cluster help (not sure how that works with template0/1 databases?) Yes, using the

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Bruce Momjian
bricklen wrote: On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Stephen Frost wrote: -- Start of PGP signed section. bricklen, * bricklen (brick...@gmail.com) wrote: Now, is this safe to run against my production database? Yes, with a few caveats. ?One

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread Alvaro Herrera
Why is it important to have the original pg_clog files around? Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum and thus removed if they were leftover from aborted transactions or deleted, no? So

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 9:28 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Why is it important to have the original pg_clog files around?  Since the transactions in question are below the freeze horizon, surely the tuples that involve those transaction have all been visited by vacuum

Re: [HACKERS] pg_upgrade bug found!

2011-04-08 Thread bricklen
On Fri, Apr 8, 2011 at 10:01 PM, bricklen brick...@gmail.com wrote: Update on the status of the steps we took, which were: - test on a hot standby by bringing it live, running the script, determing the missing clog files, copying them into the live (hot standby) pg_clog dir Now, on the

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Josh Berkus
On 4/7/11 9:16 AM, Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. BTW, just for the release notes, RhodiumToad == Andrew Gierth. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com --

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Jeff Davis
On Thu, 2011-04-07 at 12:16 -0400, Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Noah Misch
On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Jeff Davis wrote: I have added a personal regression test to show which pg_class.relfrozenxid values are not preserved, and with this patch the only ones not preserved are toast tables used by system tables, which are not copied from the old cluster (FirstNormalObjectId = 16384). I am

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Robert Haas
On Thu, Apr 7, 2011 at 3:46 PM, Bruce Momjian br...@momjian.us wrote: Jeff Davis wrote: I have added a personal regression test to show which pg_class.relfrozenxid values are not preserved, and with this patch the only ones not preserved are toast tables used by system tables, which are

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: ISTM we need to force a minor release once we are sure this has been corrected. We had also probably put out an announcement warning people that have already used pg_upgrade of possible data corruption. I'm not sure exactly what the language around

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Jeff Davis
On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote: OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the relfrozenxid (pretty complicated

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote: OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote: OK, so the only other idea I have is to write some pretty complicated query function that does a sequential scan of each toast table and pulls the earliest xmin/xmax from the tables and use that to set the

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Robert Haas wrote: Well, that won't work, because VACUUM can't be executed in a transaction block or function. Good point. The only bright part of this is that missing clog will throw an error so we are not returning incorrect data, and hopefully people will report problems to us

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Bruce Momjian wrote: Robert Haas wrote: Well, that won't work, because VACUUM can't be executed in a transaction block or function. Good point. The only bright part of this is that missing clog will throw an error so we are not returning incorrect data, and hopefully people

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Jeff Davis
On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote: I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably. PL/pgSQL can't see dead rows, so that would not be correct.

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote: I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably. PL/pgSQL can't see dead rows, so

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Bruce Momjian wrote: Jeff Davis wrote: On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote: I want to avoid anything that requires a compile because they are hard for many sites to install so TransactionIdPrecedes() is out. We will need to do this in PL/pgSQL probably.

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Kevin Grittner
Bruce Momjian br...@momjian.us wrote: all we need to do is set those hint bits before the clog gets remove, so maybe just a SELECT * would do the trick! Does that mean that those experiencing the problem are failing to do the vacuumdb run which is recommended in the pg_upgrade instructions?

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: all we need to do is set those hint bits before the clog gets remove, so maybe just a SELECT * would do the trick! Does that mean that those experiencing the problem are failing to do the vacuumdb run which is recommended in

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Jeff Davis
On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the existing value could be

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid backward. If it was never preserved to begin with, I assume that the

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Robert Haas
On Thu, Apr 7, 2011 at 5:52 PM, Bruce Momjian br...@momjian.us wrote: Jeff Davis wrote: On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems?  Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never set the relfrozenxid

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Robert Haas wrote: On Thu, Apr 7, 2011 at 5:52 PM, Bruce Momjian br...@momjian.us wrote: Jeff Davis wrote: On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote: Any idea how to correct existing systems? ?Would VACUUM FREEZE of just the toast tables work? VACUUM FREEZE will never

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Bruce Momjian wrote: Yes, it will be reasonable. That means that VACUUM FREEZE of the toast table, if there are no concurrent transactions, will freeze all of the tuples; and the newFrozenXid should always be seen as newer than the existing (and wrong) relfrozenxid. Then, it will

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Jeff Davis
On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote: So I think we have four possible approaches to correct databases: 1) SELECT * to set the hint bits 2) VACUUM to set the hint bits 3) VACUUM FREEZE to remove the old xids 4) some complicated function I don't

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Jeff Davis wrote: On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote: So I think we have four possible approaches to correct databases: 1) SELECT * to set the hint bits 2) VACUUM to set the hint bits 3) VACUUM FREEZE to remove the old xids 4) some complicated

Re: [HACKERS] pg_upgrade bug found!

2011-04-07 Thread Bruce Momjian
Noah Misch wrote: On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote: Bruce Momjian wrote: OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not

[HACKERS] pg_upgrade bug found!

2011-04-05 Thread Bruce Momjian
OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row