Re: How to make lazy VACUUM of one table run in several

On T, 2005-04-26 at 17:54 -0400, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > Could I avoid having a transaction at all?
>
> Not really; too much of the database access infrastructure is tied to
> transaction stuff ... even facilities as basic as memory management.
>
> > As VACUUM is not "transactional" in the sense that it does not change
> > anything visible to users ever, can't be undone by rollback, etc... ,
> > could it be possible to create enough "transaction-like" environment for
> > it to really run outside of transactions. Perhaps just advancing
> > oldestXmin at certain intervals ?
>
> I wonder whether you could avoid advertising the VACUUM's XID in PGPROC.
> Not sure that this can work, but it would be a lot simpler than stopping
> and starting transactions ...

What dreadful things will happed if I just set the xid and xmin of
current transaction (proc->xid, ->xmin) to some value below
FirstNormalTransactionId, so that TransactionIdIsNormal(xid) will make
GetOldestXmin ignore my transaction ?

Will this for example confuse WAL or cause something else equally grim
to happen ?

An alternative would be to have some special "ignore-me" flag in PGPROC.

A more general solution to the problem "VACUUM does not clean dead
tuples fast enough due to an old transaction" problem is keeping the
OldestXmin for each table separately as a list of table OIDs in each
PGPROC.

This would be automatically extandable to long COPY, or in fact any
single SQL statement running in its implicit transaction by examining
the query plan and reserving all tables touched by the query and its
dependencies.
And this could be done as a user-level command for explicit transactions
(LOCK TABLE my_table, othertable, yetanothertable FOR/FROM VACUUM) which
would tell postgres that the current query will need these (and only
these) tables. If LOCK FOR VACUUM is done inside a transaction, then
touching any other table should be an error.

--
Hannu Krosing <hannu(at)tm(dot)ee>