Re: Vacuum not deleting tuples when lockless

=?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= <fmartin91(at)gmail(dot)com> writes:
> We are experiencing some `vacuum` issues with a given table (potentially more). When a manual vacuum runs on the given table it seems that the `vacuum` process is not doing the expected cleanup.

> DETAIL:  113257 dead row versions cannot be removed yet.

Locks don't really have anything to do with that: what does matter is
how old is the oldest open transaction, because that determines the
"event horizon" that dead row versions have to fall below before they
can be removed. That oldest transaction might not be holding any locks
at the moment, but it doesn't matter, because in principle it could ask
to read this table later --- and it should see the table's contents as
of its snapshot.

Serializable transactions are worse than repeatable-read transactions
for this purpose, because the former will keep a snapshot as of their
start time.

As Jerry mentioned, replication slots can also act like open transactions
for this purpose, though I don't recall how much of that behavior is
present in 9.2.x.

regards, tom lane