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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
