On Sat, Jun 27, 2015 at 11:00 AM, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
Hi Dave:
On Fri, Jun 26, 2015 at 2:59 AM, Dave Johansen <davejohansen@xxxxxxxxx> 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@xxxxxxxxxxxxxx
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);
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.
