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

2007-01-10 Thread Simon Riggs
On Tue, 2007-01-09 at 16:31 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: ...continuing this discussion about setting HEAP_XMIN_COMMITTED... BTW, a sufficient counterexample for that kluge is that neither SPI or SQL-function execution use a separate portal for invoked

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

2007-01-10 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: I agree we could get this to Just Work by altering HeapTupleSatisfies...() functions so that their first test is if (TransactionIdIsCurrentTransactionId(xvac)) rather then if (!(tuple-t_infomask HEAP_XMIN_COMMITTED)) Huh? That doesn't make

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

2007-01-10 Thread Simon Riggs
On Wed, 2007-01-10 at 10:37 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I agree we could get this to Just Work by altering HeapTupleSatisfies...() functions so that their first test is if (TransactionIdIsCurrentTransactionId(xvac)) Oh? Sorry, I meant xmin not xvac at

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

2007-01-10 Thread Jim C. Nasby
On Sat, Jan 06, 2007 at 09:20:53PM -0500, Tom Lane wrote: 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

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

2007-01-09 Thread Simon Riggs
On Sun, 2007-01-07 at 11:29 -0500, Tom Lane wrote: I wrote: ... The active-portal kluge that you've just mentioned is nothing but a kluge, proving that you thought of some cases where it would fail. But I doubt you thought of everything. New patch submitted to -patches on different

Re: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances

2007-01-07 Thread Martijn van Oosterhout
On Sun, Jan 07, 2007 at 11:46:29AM +, Simon Riggs wrote: On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as well. I think you just talked yourself out of getting this

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

2007-01-07 Thread Simon Riggs
On Sun, 2007-01-07 at 12:59 +0100, Martijn van Oosterhout wrote: On Sun, Jan 07, 2007 at 11:46:29AM +, Simon Riggs wrote: On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as

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

2007-01-07 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: I think you just talked yourself out of getting this patch applied. Maybe; what would be your explanation? The main reason is that you were guilty of false advertising. This patch was described as being

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

2007-01-07 Thread Tom Lane
I wrote: ... The active-portal kluge that you've just mentioned is nothing but a kluge, proving that you thought of some cases where it would fail. But I doubt you thought of everything. BTW, a sufficient counterexample for that kluge is that neither SPI or SQL-function execution use a

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

2007-01-07 Thread Simon Riggs
On Sun, 2007-01-07 at 11:14 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: I think you just talked yourself out of getting this patch applied. Maybe; what would be your explanation? The main reason is that you were guilty

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

2007-01-07 Thread Simon Riggs
On Sun, 2007-01-07 at 11:29 -0500, Tom Lane wrote: I wrote: ... The active-portal kluge that you've just mentioned is nothing but a kluge, proving that you thought of some cases where it would fail. But I doubt you thought of everything. BTW, a sufficient counterexample for that kluge

Re: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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: [HACKERS] [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