From: | mlw <pgsql(at)mohawksoft(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | josh(at)agliodbs(dot)com, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Suggestion; "WITH VACUUM" option |
Date: | 2002-12-17 13:02:34 |
Message-ID: | 3DFF206A.4010708@mohawksoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | mlw | 2002-12-17 16:49:47 | Re: Password security question |
Previous Message | Lee Kindness | 2002-12-17 12:23:20 | Re: PQnotifies() in 7.3 broken? |