On 8 August 2012 20:34, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Aug 7, 2012 at 4:52 PM, Simon Riggs <si...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
