Undead record haunts my database, need exorcism

From: Kevin Sterner <sterner(at)hep(dot)upenn(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Undead record haunts my database, need exorcism
Date: 2002-05-21 20:33:03
Message-ID: 200205212033.QAA14066@higgs.hep.upenn.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Ron Johnson 2002-05-21 20:40:00 Better way to bulk-load millions of CSV records into postgres?
Previous Message Ron Johnson 2002-05-21 20:20:13 Large tables being split at 1GB boundary