Re: [GENERAL] Unable to dump database using pg_dump

Adam Dear <adear@usnx.net> writes:
> madisoncounty=# select ctid,xmin,xmax,cmin,cmax,usename from pg_shadow;
> ctid | xmin | xmax | cmin | cmax | usename
> -------+------+------+------+------+---------------
> (0,1) | 1 | 596 | 596 | 1 | postgres
> (0,2) | 2 | 1 | 1 | 0 | postgres
> (0,5) | 2 | 0 | 0 | 0 | madisoncounty
> (3 rows)

Hm, I thought you deleted the (0,2) tuple ... did you restore a physical
backup or something?

Anyway, as far as I can see the way that you got into this state must
have been

1. The (0,1) tuple must have been the one originally inserted by initdb;
there's no other way it could have xmin=1.

2. Shortly after initdb (at transaction 596 to be exact) this tuple was
updated --- probably by a password-assignment operation --- creating the
tuple at (0,2), which must originally have had xmin = 596, cmin = 1 (the
xmax overlays cmin in 7.4, so we can assume that column value is bogus).

3. Much time passes, and pg_shadow never gets vacuumed so the dead
tuple at (0,1) is never cleaned up. Eventually the XID counter passes 2
billion + 596, and suddenly transaction 596 appears to be in the future,
so the tuple at (0,1) starts to be seen by SELECTs again.

4. At this point you ran VACUUM FREEZE, which replaced the xmins of the
second and third tuples with 2 (FrozenTransactionId) ... but 7.4 does
not think it could ever need to freeze xmax, and at this point VACUUM
wouldn't touch the (0,1) tuple anyway because it considers the tuple as
RECENTLY_DEAD.

So VACUUM won't help you, at least not for another 2 billion
transactions. And the DELETE doesn't work either because it correctly
perceives (0,1) as an updated tuple that's been superseded by (0,2),
which doesn't meet the WHERE clause so DELETE doesn't touch it.
You could delete (0,2) but that leaves you with no working postgres user
(since the system's SnapshotNow rules consider (0,1) as dead), and if
you create another one you're back to having 2 entries in pg_shadow.
Nasty :-(

I can't think of any way out of this using plain 7.4 SQL operations.
You could maybe hack a special case into VACUUM to make it nuke the
dead tuple, but what's probably going to be easier is to manipulate the
data on disk. Are you comfortable enough with editing binary data
to find the "596" and replace it with "2"? It'd be somewhere near
the end of the first (and probably only) block of pg_shadow, and a
few bytes before one of the occurrences of the string "postgres".
BTW, pg_shadow is $PGDATA/global/1260.

(If you try this, do the editing while the postmaster is stopped,
else you might have problems with it buffering the old data.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general