Thread: Undead record haunts my database, need exorcism

Gentle Wizards,

My PostgreSQL 6.5.3 database is having a serious problem.

I have a backup utility that writes backup sets to tape.  When a set is
written, it is stored as an entry in a table, "sets".  While the tape drive
is in use, an entry is placed in another table, "dblock".  This utility
(and other utilities) check dblock before attempting to use the tape drive.
There is only ever one record in dblock (or no records).

Sometimes a process will crash, for whatever reason, and leave an errant
record in "dblock".  I would delete the record, and the backups would
proceed.

Lately, however, the entries in "sets" and "dblock" have gotten badly out
of joint.  New records disappear, in "sets", while new records in "dblock"
revert to old, deleted ones that can't be re-deleted.  Here's an example:

     backups=> select * from dblock;
           date|time    |  pid|host      |drive
     ----------+--------+-----+----------+---------
     05-16-2002|20:31:54|35892|mytapehost|/dev/rmt0
     (1 row)

(There's an old record in dblock, so I clear it:)

     backups=> delete from dblock;
     DELETE 1

     backups=> select * from dblock;
           date|time    |  pid|host      |drive
     ----------+--------+-----+----------+---------
     (0 rows)

(So far, so good.  Some time later, another process starts:)

     backups=> select * from dblock;
           date|time    |  pid|host      |drive
     ----------+--------+-----+----------+---------
     05-18-2002|10:51:41|28488|mytapehost|/dev/rmt0
     (1 row)

     backups=> select * from sets where date>='05-18-2002';
     volid  |seqno|      date|host  |filesys|      size|level
     -------+-----+----------+------+-------+----------+-----
     SP0013 |  328|05-18-2002|myhost|/foo1  |2147483647|    0
     SP0013 |  329|05-18-2002|myhost|/foo2  |  15433728|    0
     SP0013 |  330|05-18-2002|myhost|/foo3  | 358612992|    0
     SP0013 |  331|05-18-2002|myhost|/foo4  | 165347328|    0
     (4 rows)

(Excellent.  But then, a while later, the sets are gone!)

     backups=> select * from sets where date>='05-18-2002';
     volid|seqno|date|host|filesys|size|level
     -----+-----+----+----+-------+----+-----
     (0 rows)

(Now here's dblock.  The ghost of the old record is back, but I can't
delete it.  I can't even drop the table:)

     backups=> select * from dblock;
           date|time    |  pid|host      |drive
     ----------+--------+-----+----------+---------
     05-16-2002|20:31:54|35892|mytapehost|/dev/rmt0
     (1 row)

     backups=> delete from dblock;
     DELETE 0

     backups=> drop table dblock;
     ERROR:  DeleteTypeTuple: dblock type nonexistent


Any insight or suggestions would be appreciated.

Thanks,

Kevin

Kevin Sterner <sterner@hep.upenn.edu> writes:
> My PostgreSQL 6.5.3 database is having a serious problem.

6.5.3?  You are past due for an update ...

> Lately, however, the entries in "sets" and "dblock" have gotten badly out
> of joint.  New records disappear, in "sets", while new records in "dblock"
> revert to old, deleted ones that can't be re-deleted.

Hmm.  How large is your pg_log file?  I suspect that you have suffered
transaction ID wraparound.  If the pg_log file is 1Gb in size (enough
to store commit bits for 4 billion transactions) that would confirm the
suspicion.

Whether that's the problem or not, 6.5.3 is enough features and bugfixes
ago that I would wholeheartedly recommend an immediate update to 7.2.1.

            regards, tom lane

Kevin Sterner <sterner@hep.upenn.edu> writes:
>> Hmm.  How large is your pg_log file?  I suspect that you have suffered
>> transaction ID wraparound.

> The pg_log file is only 4 Mbytes.

Scratch that theory, then.  I'm not sure why you'd be seeing this odd
behavior.

>> Whether that's the problem or not, 6.5.3 is enough features and bugfixes
>> ago that I would wholeheartedly recommend an immediate update to 7.2.1.

> Does that version still support AIX?

Sure.

            regards, tom lane

> 6.5.3?  You are past due for an update ...

Concur.  I have some inertia to overcome on that, however.  Meanwhile,
I need to get the old jalopy back on the road.

> Hmm.  How large is your pg_log file?  I suspect that you have suffered
> transaction ID wraparound.  If the pg_log file is 1Gb in size (enough
> to store commit bits for 4 billion transactions) that would confirm the
> suspicion.

The pg_log file is only 4 Mbytes.  The database is not very active; fewer
than 100 records per day get stored.  Is such a wraparound still possible?
If so, how would I detect it and/or fix it?

> Whether that's the problem or not, 6.5.3 is enough features and bugfixes
> ago that I would wholeheartedly recommend an immediate update to 7.2.1.

Does that version still support AIX?

Thanks,

Kevin