From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Inserting heap tuples in bulk in COPY |
Date: | 2012-08-08 19:54:23 |
Message-ID: | CA+U5nMJUp-=H+J47SOdtPBAgXD5kSKaj-2k+PEOc7Nb3U5SmhA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 8 August 2012 20:34, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Aug 7, 2012 at 4:52 PM, Simon Riggs <simon(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2012-08-08 20:23:04 | Re: -Wformat-zero-length |
Previous Message | Robert Haas | 2012-08-08 19:34:07 | Re: Inserting heap tuples in bulk in COPY |