Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes: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:
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.
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.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
