Re: COPY with no WAL, in certain circumstances - Mailing list pgsql-patches
From | Simon Riggs |
---|---|
Subject | Re: COPY with no WAL, in certain circumstances |
Date | |
Msg-id | 1168119366.3951.24.camel@silverbirch.site Whole thread Raw |
In response to | Re: COPY with no WAL, in certain circumstances (Bruce Momjian <bruce@momjian.us>) |
Responses |
Re: COPY with no WAL, in certain circumstances
(Bruce Momjian <bruce@momjian.us>)
Re: COPY with no WAL, in certain circumstances (Robert Treat <xzilla@users.sourceforge.net>) Re: COPY with no WAL, in certain circumstances (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-patches |
On Sat, 2007-01-06 at 15:24 -0500, Bruce Momjian wrote: > Joshua D. Drake wrote: > > > > > > BEGIN; > > > > CREATE TABLE foo... > > > > INSERT INTO foo VALUES ('1'); > > > > COPY foo... > > > > > > > > COMMIT; > > > > > > On ABORT, the entire table disappears, as well as the INSERT, so I don't > > > see any problem. I assume the INSERT is WAL logged. > > > > No I don't see any problems, I am just trying to understand the > > boundaries. E.g., is there some weird limitation where if I have any > > values in the table before the copy (like the example above) that copy > > will go through WAL. > > > > Or in other words, does this patch mean that all COPY execution that is > > within a transaction will ignore WAL? > > Yes, because it is possible to do in all cases. Very happy to add documentation where Tom suggested. Reason for no documentation was that CREATE INDEX and CREATE TABLE AS SELECT already use this optimisation, but to my knowledge neither was/is documented on those command pages. The rule is: if the relfilenode for a table is new in this transaction (and therefore the whole things will be dropped at end-of-transaction) then *all* COPY commands are able to avoid writing WAL safely, if: - PITR is not enabled - there is no active portal (which could have been opened on an earlier commandid and could therefore see data prior to the switch to the new relfilenode). In those cases, *not* using WAL causes no problems at all, so sleep well without it. So all of these work as shown BEGIN; COPY foo... --uses WAL TRUNCATE foo... COPY foo.. --no WAL COPY foo.. --no WAL COMMIT; BEGIN; CREATE TABLE foo... INSERT INTO foo --uses WAL COPY foo.. --no WAL INSERT INTO foo --uses WAL COPY foo.. --no WAL INSERT INTO foo --uses WAL COPY foo... --no WAL COMMIT; BEGIN; CREATE TABLE foo... AS SELECT --no WAL INSERT INTO foo --uses WAL COPY foo.. --no WAL COMMIT; BEGIN; DECLARE CURSOR cursor CREATE TABLE foo... COPY foo.. --uses WAL because active portal COPY foo.. --uses WAL because active portal CLOSE cursor COPY foo.. --no WAL COPY foo.. --no WAL COMMIT; psql --single-transaction -f mydb.pgdump Come to think of it, I should be able to use pg_current_xlog_insert_location() to come up with a test case. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
pgsql-patches by date: