Re: Suggestion; "WITH VACUUM" option

Tom Lane wrote:

>Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
>
>>How hard would it be to add a "WITH (VACUUM)" option to UPDATE and DELETE
>>queries? This option would cause the regular vacuum activity -- purging the
>>dead tuple and its index references -- to be done immediately, as part of the
>>statement, instead of being deferred.
>>
>>
>
>
>
>>Easy? Hard? Insane? What do you think?
>>
>>
>
>Impossible. You can't vacuum a tuple until the last open transaction
>that can see it is gone. It is therefore *impossible* for a transaction
>to vacuum away its own detritus; until the transaction commits, you
>can't even start to wonder whether other open transactions see it or
>not.
>
>Vacuuming has to be done later, and that being the case, I don't see any
>real advantage to altering the "background vacuum" design we have.
>
>
This does raise an interresting question, and I understand that it is
*impossible* to do with PostgreSQL as it currently exists, however, let
me just toss this out there:

Suppose you do this:

update largetable set foo=bar;

Lets also assume that "largetable" has tens of millions of rows. I have
databases like this, and I sometimes do operations like this. I have
found it more efficient to break up the update into a series of:

update largetable set foo=bar where somefield < a;
vacuum
update largetable set foo=bar where somefield < b;
vacuum
update largetable set foo=bar where somefield < c;
vacuum
update largetable set foo=bar where not foo = bar;
vacuum

On some of my databases a statement which updates all the rows is
unworkable in PostgreSQL, on Oracle, however, there is no poblem.

For my use, it is a pain in the neck to deal with, but not unworkable.
For some other users, it may be a bigger problem.

>
>

>
>