Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-09 Thread Bruce Momjian
Patch withdrawn by author. --- Simon Riggs wrote: http://archives.postgresql.org/pgsql-hackers/2006-10/msg01172.php As discussed on -hackers, its possible to avoid writing any WAL at all for COPY in these

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-07 Thread Simon Riggs
On Sat, 2007-01-06 at 21:32 -0500, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: On Saturday 06 January 2007 16:36, Simon Riggs wrote: snip BEGIN; CREATE TABLE foo... INSERT INTO foo--uses WAL COPY foo.. --no WAL INSERT INTO foo--uses WAL COPY foo.. --no WAL

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-07 Thread Simon Riggs
On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: 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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-07 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: Uh ... what in the world has an active portal got to do with it? I think you've confused snapshot considerations with crash recovery. The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-07 Thread Simon Riggs
On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: Uh ... what in the world has an active portal got to do with it? I think you've confused snapshot considerations with crash recovery. The patch

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Bruce Momjian
FYI, I am going need to add documentation in the COPY manual page or no one will know about this performance enhancement. --- Simon Riggs wrote: http://archives.postgresql.org/pgsql-hackers/2006-10/msg01172.php As

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Joshua D. Drake
On Sat, 2007-01-06 at 11:05 -0500, Bruce Momjian wrote: FYI, I am going need to add documentation in the COPY manual page or no one will know about this performance enhancement. I have some questions: As discussed on -hackers, its possible to avoid writing any WAL at all for COPY in these

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Bruce Momjian
Joshua D. Drake wrote: On Sat, 2007-01-06 at 11:05 -0500, Bruce Momjian wrote: FYI, I am going need to add documentation in the COPY manual page or no one will know about this performance enhancement. I have some questions: As discussed on -hackers, its possible to avoid writing any

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Joshua D. Drake
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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: FYI, I am going need to add documentation in the COPY manual page or no one will know about this performance enhancement. I don't think it belongs in COPY. What would make more sense is another item under the populating a database performance tips,

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Euler Taveira de Oliveira
Simon Riggs wrote: As discussed on -hackers, its possible to avoid writing any WAL at all for COPY in these circumstances: Cool. The enclosed patch implements this, as discussed. There is no user interface to enable/disable, just as with CTAS and CREATE INDEX; no docs, just code comments.

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Joshua D. Drake
On Sat, 2007-01-06 at 16:41 -0200, Euler Taveira de Oliveira wrote: Simon Riggs wrote: As discussed on -hackers, its possible to avoid writing any WAL at all for COPY in these circumstances: Cool. The enclosed patch implements this, as discussed. There is no user interface to

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Tom Lane
Euler Taveira de Oliveira [EMAIL PROTECTED] writes: Simon Riggs wrote: The enclosed patch implements this, as discussed. There is no user interface to enable/disable, just as with CTAS and CREATE INDEX; no docs, just code comments. IMHO, this deserves an GUC parameter (use_wal_in_copy?).

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Bruce Momjian
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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Simon Riggs
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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Bruce Momjian
Simon Riggs wrote: 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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Euler Taveira de Oliveira
Joshua D. Drake wrote: IMHO, this deserves an GUC parameter (use_wal_in_copy?). Because a lot of people use COPY because it's faster than INSERT but expects that it will be in WAL. The default would be use_wal_in_copy = true. That I don't think makes sense. A copy is an all or nothing

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Robert Treat
On Saturday 06 January 2007 16:40, Bruce Momjian wrote: Simon Riggs wrote: 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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Robert Treat
On Saturday 06 January 2007 16:36, Simon Riggs wrote: 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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: 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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: Simon Riggs wrote: 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. I wasn't aware those used the optimization. Seems they

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Tom Lane
Robert Treat [EMAIL PROTECTED] writes: On Saturday 06 January 2007 16:36, Simon Riggs wrote: snip 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

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Joshua D. Drake
Is there some technical reason that the INSERT statements need to use WAL in these scenarios? First, there's enough other overhead to an INSERT that you'd not save much percentagewise. Second, not using WAL doesn't come for free: the cost is having to fsync the whole table

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Tom Lane
Joshua D. Drake [EMAIL PROTECTED] writes: cost is having to fsync the whole table afterwards. So it really only makes sense for commands that one can expect are writing pretty much all of the table. I could easily see it being a net loss for individual INSERTs. What about multi value

Re: [PATCHES] COPY with no WAL, in certain circumstances

2007-01-06 Thread Joshua D. Drake
On Sat, 2007-01-06 at 22:09 -0500, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: cost is having to fsync the whole table afterwards. So it really only makes sense for commands that one can expect are writing pretty much all of the table. I could easily see it being a net loss