Re: Inserting from multiple processes?

On Sat, Jun 27, 2015 at 11:00 AM, Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> Hi Dave:
>
> On Fri, Jun 26, 2015 at 2:59 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
>
> > It appears that calling "SELECT insert_test_no_dup('2015-01-01', 1, 1)"
> > cause the XID to increment? I'm not sure if it's only when the exception
> > happens or all the time, but if there some way to prevent the increment
> of
> > XID because it's causing problems with our system:
> >
> http://www.postgresql.org/message-id/CAAcYxUer3MA=enXvnOwe0oSAA8ComvxCF6OrHp-vUppr56twFg@mail.gmail.com
>
> I, personally, would expect an START TRANSACTION to burn an XID, they
> are serial, and they need to be allocated to have transaction
> ordering, like the thing which happens with the sequences. I assume
> the server can have some optimizations ( like delaying XID adquisition
> to the first appropiate statement, which I think depends on your
> isolation level ), but I would never expect it to not allocate it
> before an insert, it needs it to be sent to the table, in case it
> succeeds, and has to acquire it beforehand, in case someone needs to
> acquire another xid between the time it starts inserting and the time
> it succeeds or fail. Some internals expert may shed some light, but
> after reading your link it seems your problem is just you do too many
> transactions without a vacuum ( also reading your pointed threas it
> sees you do vacuum fulls, which seems unneeded ) and expecting
> postgres has some kind of magic to avoid burning the xids.

The issue is that the following uses 5 XIDs when I would only expect it to
us 1:
BEGIN;
SELECT insert_test_no_dup('2015-01-01', 1, 1);
SELECT insert_test_no_dup('2015-01-02', 2, 2);
SELECT insert_test_no_dup('2015-01-01', 1, 1);
SELECT insert_test_no_dup('2015-01-02', 2, 2);
SELECT insert_test_no_dup('2015-01-01', 1, 1);
SELECT insert_test_no_dup('2015-01-02', 2, 2);
END;

It appears that the unique violation that is caught and ignored increments
the XID even though I didn't expect that to happen. I agree that our
software was burning XIDs needlessly and Postgres handled this situation as
best as it could. It also sounds like Postgres 9.5 adds features to support
this sort of use more efficiently, but the XID incrementing on the unique
violation seems like it could/should be fixed, if it hasn't been already.