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

Something weird is going on with some of mai emails - I tried to send this from hannu(at)tm(dot)ee,
then from hannu(at)skype(dot)net and it does not appear in list. Another answer sent after this
came through in less than 5 min.

Now I removed Tom Lane from To: and moved pgsql-hackers(at)postgresql(dot)org from CC: to To: field

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 I came up with is adding an extra bool to PGPROC (inVacuum) which
is set if the current command is Vacuum in its own transaction and
modifying GetOldestXmin() tos skip backends where inVacuum==true. I also
added this skipping logic to where PGPROC-xmin is set.

The resultin code passes 'make check' and also seems to do the right
thing, wrt its purpose. I tested it by generating a 2M row table
'bightable' and then running 'vacuum verbose analyse bigtable;' on it
(runtime ~25 sec).

At the same time I repeatedly ran in another connection
'update smalltable set data=data+1;vacuum verbose smalltable;'
which was able to free all 16 old rows all the time during both vacuum
and analyse phases.

The only surprise was that it did not work during plain 'analyse
bigtable;'. It seems that plain analyse does not set use_own_xacts and
thus cant't make use of this optimisation.

Please comment on the attached patch (against ver 8.0.2), especially
what could be broken (WAL, some trx isolation level, ... ?) and how to
test if it is.

Also, does this have any chance to get accepted in 8.1 ?

--
Hannu Krosing <hannu(at)skype(dot)net>