Adam Dear <adear@xxxxxxxx> 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
