Re: pg_dump after transaction id wraparound failure

On Mon, May 15, 2006 at 03:04:47PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > Vacuum will make anything that disappeared by wraparound in the last
> > billion transactions reappear, so a databasewide vacuum should solve
> > all your problems, no need to dump...
>
> Oh, I had forgotten about that. Did we establish that 7.4.* works that
> way? I think so, but obviously I've been programming too many hours in
> a row :-(

Well, I demonstrated it for 8.1beta here:

http://archives.postgresql.org/pgsql-general/2005-11/msg00097.php

And I just demonstrated it on a test 7.4.7 database here (Debian
7.4.7-6 to be precise).

test=# select ctid, xmin, xmax, cmin, cmax, * from test;
ctid | xmin | xmax | cmin | cmax | x
-------+------------+------+------+------+---
(0,1) | 541 | 0 | 0 | 0 | 1
(0,2) | 2147484153 | 0 | 0 | 0 | 2
(0,3) | 2147484183 | 0 | 0 | 0 | 3
(3 rows)

### Row is still there...

test=# select ctid, xmin, xmax, cmin, cmax, * from test;
ctid | xmin | xmax | cmin | cmax | x
-------+------------+------+------+------+---
(0,2) | 2147484153 | 0 | 0 | 0 | 2
(0,3) | 2147484183 | 0 | 0 | 0 | 3
(2 rows)

### OMG, row is gone

test=# vacuum test;
VACUUM
test=# select ctid, xmin, xmax, cmin, cmax, * from test;
ctid | xmin | xmax | cmin | cmax | x
-------+------------+------+------+------+---
(0,1) | 2 | 0 | 0 | 0 | 1
(0,2) | 2147484153 | 0 | 0 | 0 | 2
(0,3) | 2147484183 | 0 | 0 | 0 | 3
(3 rows)

### Yay row is back
test=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 row)

Have a ncie day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.