Re: Suggestion; "WITH VACUUM" option

Tom Lane wrote:

>Josh Berkus <josh@agliodbs.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.



>  
>

>  
>