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

On Sun, Apr 24, 2005 at 12:02:37PM +0300, Hannu Krosing wrote:

> to check for some time/page_cnt limit after each heap page (near the
> start of main loop in lazy_scan_heap() ), and if it is reached then
> stop, clean up indexes, and return the blkno of next page needing to
> be vacuumed, and replacing the call to lazy_vacuum_rel(onerel,
> vacstmt); in vacuum.c with the following loop.
>
> next_page_to_vacuum = 0;
> while (next_page_to_vacuum < RelationGetNumberOfBlocks(onerel)) {
> StartTransactionCommand();
> ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
>
> next_page_to_vacuum = lazy_vacuum_rel(onerel, vacstmt);
>
> CommitTransactionCommand();
> }
>
> Must some locks also be released an reaquired inside this loop, or is
> there something else I should keep in mind when trying to do this ?

There is "session lock" on the table. You must release that.

However, after releasing and reacquiring that lock, all you know about
the table must be rechecked. In particular the table can be dropped :-)
or truncated, or vacuumed by some other process; etc. So you'd need to
start the vacuum "from scratch." (I guess you'd skip the first N
pages.)

One thing to keep in mind is whether the Xmin calculations are right
after such a thing ... e.g. if you truncated the clog with the wrong
parameters, you could lose data.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem." (Tom Lane)