Hi Dave:
On Fri, Jun 26, 2015 at 2:59 AM, Dave Johansen <davejohansen@gmail.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.
Francisco Olarte.