Re: COPY with no WAL, in certain circumstances

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