Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and DELETE?

On Thu, Dec 22, 2005 at 02:31:33PM +0000, Simon Riggs wrote:
> Having just optimized COPY to avoid writing WAL during the transaction
> in which a table was first created, it seems worth considering whether
> this should occur for INSERT, UPDATE and DELETE also.
>
> It is fairly common to do data transformation using INSERT SELECTs and
> UPDATEs. This is usually done with temporary tables however. (DELETE
> would most efficiently be handled as an additional NOT clause on the
> insert, so it is uncommonly used in this circumstance.)
>
> However, CREATE TABLE AS SELECT (CTAS) does not allow inheritance, so a
> new permanent partition has to be created using CREATE TABLE, followed
> by an INSERT SELECT or COPY.
>
> Is that sufficient reason to optimise INSERT SELECT and UPDATE also? Or
> should I not bother? Or should I try to teach CTAS to use inheritance
> (which sounds harder and has a few gotchas).
>
> Currently, CTAS optimization requires a heap_sync during ExecEndPlan. It
> would be easy enough to extend this so that it also works for INSERT,
> UPDATE and DELETE.

Well, both UPDATE and DELETE seem like pretty odd use cases to me;
typically I'd do any needed data manipulation during the INSERT SELECT.
But it doesn't make sense to me to set this up for INSERT and ignore
UPDATE and DELETE.

I do think this needs to be something that is made either completely
transparent or must be specifically enabled. As described, I believe
this would break PITR, so users should have to specifically request that
behavior (and they should probably get a WARNING message, too).

Though, what's interesting is that theoretically it should be possible
to do this and still protect PITR, by logging the statements (but not
the actual data) to WAL. This isn't very practical with WAL (though it
might be worth looking at storing a compressed version of what's being
fed in to COPY), but in this case if we end up in a recovery situation
the data that the insert is pulling from should exist in the database in
the same state, so it should be possible to re-create the table. There's
still an issue of how to handle the pages from the new table that will
end up in WAL on subsiquent transactions, since presumably they might be
identical, but someone might be able to come up with a clever solution
for that. In the meantime, breaking WAL recovery needs to be something
that users must specifically request, via something like UPDATE NOWAL.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461