Re: Inserting heap tuples in bulk in COPY - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Inserting heap tuples in bulk in COPY
Date
Msg-id CA+U5nMJUp-=H+J47SOdtPBAgXD5kSKaj-2k+PEOc7Nb3U5SmhA@mail.gmail.com
Whole thread Raw
In response to Re: Inserting heap tuples in bulk in COPY  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
On 8 August 2012 20:34, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Aug 7, 2012 at 4:52 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> Incidentally, we can also optimise repeated inserts within a normal
>> transaction using this method, by implementing deferred unique
>> constraints. At present we say that unique constraints aren't
>> deferrable, but there's no reason they can't be, if we allow buffering
>> in the index. (Implementing a deferred constraint that won't fail if
>> we do UPDATE foo SET pk = pk+1 requires a buffer the size of foo,
>> which is probably a bad plan, plus you'd need to sort the inputs, so
>> that particular nut is another issue altogether, AFAICS).
>
> We've had deferrable unique constraints since 9.0, courtesy of Dean Rasheed.

Yeh, but IIRC there was some issue I can't seem to find detail on
about it not working quite right in production. Not sure now.


>> I think we need to implement buffering both to end of statement or end
>> of transaction, not just one or the other.
>
> Another (not necessarily better) idea is to use a buffer that's part
> of the index, like the GIN fastupdate stuff, so that there's no
> particular constraint on when the buffer has to be flushed, but
> competing index scans may be slower until it is.

I think that works very well for non-unique indexes, though it does
increase WAL traffic since you need to do a double hop into the index.
Its not possible for unique constraints/pk indexes since they need to
fail the transaction in case of duplicates.

The buffer I was imagining would be a private buffer within a
transaction, so wouldn't increase WAL.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services


pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: Inserting heap tuples in bulk in COPY
Next
From: Robert Haas
Date:
Subject: Re: -Wformat-zero-length