No long-lived transaction, still can't delete tuples - Mailing list pgsql-general

Hi again informative friends.  I have a new minor problem with my
database running postgresql 7.2.  I vacuum all the tables hourly, to
keep the peak number of tuples low.  I do this so that my toast
tables don't get beyond the amount of free space that the toast
system can track (whereupon it grows out of control).  This usually
works fine.

Today I have noticed that I can't vacuum a table, because all the of
the deleted tuples are marked as "Keep" -- they are still visible to
some transaction.  I do have a system that keeps a connection open
to postgres all the time, so I suspected that.  But, this systems
main loop looks like this:

while(1) {
    $dbh->commit();

    ...


So I believe the transaction is not long-lived.  Also, I checked
with tethereal to make absolutely certain that the commit was
happening:

->    0  5163 6f6d 6d69 7400                       Qcommit.
<-    0  4343 4f4d 4d49 5400 5a                    CCOMMIT.Z
->    0  5162 6567 696e 00                         Qbegin.
<-    0  4342 4547 494e 005a                       CBEGIN.Z

This makes me completely sure that my transactions are NOT staying
open.  However, I still can't vacuum the silly table:

rupert=# vacuum verbose resp_body;
NOTICE:  --Relation resp_body--
NOTICE:  Pages 322: Changed 0, Empty 0; Tup 11287: Vac 0, Keep 8013,
UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  --Relation pg_toast_129372505--
NOTICE:  Pages 9070: Changed 0, Empty 0; Tup 37772: Vac 0, Keep
27768, UnUsed 0.
        Total CPU 0.00s/0.03u sec elapsed 0.02 sec.
NOTICE:  Analyzing resp_body
VACUUM

I shut down this long-running system, then I was able to vacuum the
table:

rupert=# vacuum verbose resp_body;
NOTICE:  --Relation resp_body--
NOTICE:  Index resp_body_resp_idx: Pages 34; Tuples 3286: Deleted
8013.
        CPU 0.01s/0.06u sec elapsed 3.77 sec.
NOTICE:  Removed 8013 tuples in 301 pages.
        CPU 0.01s/0.09u sec elapsed 0.34 sec.
NOTICE:  Pages 322: Changed 4, Empty 0; Tup 3286: Vac 8013, Keep 0,
UnUsed 0.
        Total CPU 0.02s/0.16u sec elapsed 4.11 sec.
NOTICE:  --Relation pg_toast_129372505--
NOTICE:  Index pg_toast_129372505_idx: Pages 146; Tuples 10054:
Deleted 27768.
        CPU 0.02s/0.23u sec elapsed 0.26 sec.
NOTICE:  Removed 27768 tuples in 6716 pages.
        CPU 0.39s/1.64u sec elapsed 4.15 sec.
NOTICE:  Pages 9082: Changed 14, Empty 0; Tup 10054: Vac 27768, Keep
0, UnUsed 0.
        Total CPU 0.41s/1.91u sec elapsed 4.44 sec.
NOTICE:  Analyzing resp_body
VACUUM

I really need some help here.  My understanding is that you cannot
vacuum any tuple which was deleted in a transaction number greater
than the oldest open transaction number.  This is a simple and
elegant system, and it seems like it would always work.  But as I
said above I am totally certain that I have only one connection
open continuously and in that connection I am constantly committing
and starting a new transaction.

Please help me reconcile this problem.  Perhaps there is a way I can
query the transaction numbers and live connections at runtime?

-jwb