Re: [HACKERS] COPY with hints, rebirth

2012-03-03 Thread Simon Riggs
On Fri, Mar 2, 2012 at 8:58 PM, Noah Misch n...@leadboat.com wrote: On Fri, Mar 02, 2012 at 08:46:45AM +, Simon Riggs wrote: On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It's still broken: [BEGIN;TRUNCATE;SAVEPOINT;COPY;ROLLBACK TO] So

Re: [HACKERS] COPY with hints, rebirth

2012-03-03 Thread Simon Riggs
On Sat, Mar 3, 2012 at 1:14 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Mar 2, 2012 at 8:58 PM, Noah Misch n...@leadboat.com wrote: On Fri, Mar 02, 2012 at 08:46:45AM +, Simon Riggs wrote: On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com

Re: [HACKERS] COPY with hints, rebirth

2012-03-03 Thread Simon Riggs
On Fri, Mar 2, 2012 at 11:15 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: I like Marti's idea. At present, making his idea work could easily mean checksums sink, so not sure whether to attempt to make that work in detail. For my part,

Re: [HACKERS] COPY with hints, rebirth

2012-03-03 Thread Kevin Grittner
Simon Riggs wrote: Kevin Grittner wrote: Simon Riggs wrote: I like Marti's idea. At present, making his idea work could easily mean checksums sink For my part, improving bulk load performance and TRUNCATE transactional semantics would trump checksums It doesn't look like it needs

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Simon Riggs
On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 01.03.2012 18:40, Simon Riggs wrote: On Sun, Feb 26, 2012 at 7:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com  wrote: On 24.02.2012 22:55, Simon Riggs wrote: What exactly does

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Noah Misch
On Fri, Mar 02, 2012 at 08:46:45AM +, Simon Riggs wrote: On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It's still broken: [BEGIN;TRUNCATE;SAVEPOINT;COPY;ROLLBACK TO] So this approach isn't the one... The COPY FREEZE patch provides a

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Kevin Grittner
Noah Misch n...@leadboat.com wrote: Incidentally, I contend that we should write frozen tuples to new/truncated tables unconditionally. +1 The current behavior of making old snapshots see the table as empty violates atomicity at least as badly as letting those snapshots see the

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Simon Riggs
On Fri, Mar 2, 2012 at 8:58 PM, Noah Misch n...@leadboat.com wrote: On Fri, Mar 02, 2012 at 08:46:45AM +, Simon Riggs wrote: On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It's still broken: [BEGIN;TRUNCATE;SAVEPOINT;COPY;ROLLBACK TO] So

Re: [HACKERS] COPY with hints, rebirth

2012-03-02 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: I like Marti's idea. At present, making his idea work could easily mean checksums sink, so not sure whether to attempt to make that work in detail. For my part, improving bulk load performance and TRUNCATE transactional semantics would trump

Re: [HACKERS] COPY with hints, rebirth

2012-03-01 Thread Simon Riggs
On Sun, Feb 26, 2012 at 7:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 24.02.2012 22:55, Simon Riggs wrote: A long time ago, in a galaxy far away, we discussed ways to speed up data loads/COPY. http://archives.postgresql.org/pgsql-hackers/2007-01/msg00470.php In

Re: [HACKERS] COPY with hints, rebirth

2012-03-01 Thread Heikki Linnakangas
On 01.03.2012 18:40, Simon Riggs wrote: On Sun, Feb 26, 2012 at 7:16 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 24.02.2012 22:55, Simon Riggs wrote: What exactly does it do? Previously, we optimised COPY when it was loading data into a newly created table or a

Re: [HACKERS] COPY with hints, rebirth

2012-02-29 Thread Simon Riggs
On Sat, Feb 25, 2012 at 6:58 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sat, Feb 25, 2012 at 6:24 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: This patch extends that and actually sets the tuple header flag as HEAP_XMIN_COMMITTED during

Re: [HACKERS] COPY with hints, rebirth

2012-02-29 Thread Christopher Browne
On Wed, Feb 29, 2012 at 11:14 AM, Simon Riggs si...@2ndquadrant.com wrote: But it is very effective at avoiding 4 out of the 5 writes you mention. For the common case, would we not want to have (1) [WAL] and (2) [Writing the pre-frozen tuple]? If we only write the tuple (2), and don't capture

Re: [HACKERS] COPY with hints, rebirth

2012-02-29 Thread Simon Riggs
On Wed, Feb 29, 2012 at 6:14 PM, Christopher Browne cbbro...@gmail.com wrote: On Wed, Feb 29, 2012 at 11:14 AM, Simon Riggs si...@2ndquadrant.com wrote: But it is very effective at avoiding 4 out of the 5 writes you mention. For the common case, would we not want to have (1) [WAL] and (2)

Re: [HACKERS] COPY with hints, rebirth

2012-02-26 Thread Heikki Linnakangas
On 24.02.2012 22:55, Simon Riggs wrote: A long time ago, in a galaxy far away, we discussed ways to speed up data loads/COPY. http://archives.postgresql.org/pgsql-hackers/2007-01/msg00470.php In particular, the idea that we could mark tuples as committed while we are still loading them, to

Re: [HACKERS] COPY with hints, rebirth

2012-02-25 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: This patch extends that and actually sets the tuple header flag as HEAP_XMIN_COMMITTED during the load. Fantastic! So, without bulk-load conditions, a long-lived tuple in PostgreSQL is written to disk at least five times[1]: (1) The WAL record for

Re: [HACKERS] COPY with hints, rebirth

2012-02-25 Thread Simon Riggs
On Sat, Feb 25, 2012 at 6:24 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: This patch extends that and actually sets the tuple header flag as HEAP_XMIN_COMMITTED during the load. Fantastic! So, without bulk-load conditions, a long-lived

[HACKERS] COPY with hints, rebirth

2012-02-24 Thread Simon Riggs
A long time ago, in a galaxy far away, we discussed ways to speed up data loads/COPY. http://archives.postgresql.org/pgsql-hackers/2007-01/msg00470.php In particular, the idea that we could mark tuples as committed while we are still loading them, to avoid negative behaviour for the first reader.