xmin and ON_ERROR_ROLLBACKToday I was querying a table on a database and tried to see if I remember right on MVCC, i.e., Multi Version Concurrency Control. However I did noted what I thought it was a strange behavior: within a
BEGIN TRANSACTIONblock the
xminwas always increasing. That puzzled me, so I decided to reproduce it on a short example and asked for some explaination on the
-generalmailing list. First try was that there was something like a trigger (or an event trigger) using some sort of sub-transaction, but I did not have any. However this made me think about the trial of
logical decodingI tried a few weeks ago on the very same cluster, so I asked if that was the problem. But the answer was really easier and the behavior was due to the configuration of
psql: I did not remember I had set on startup the
ON_ERROR_ROLLBACKoption. Despite its name, the option does the opposite: a transaction is already forced to rollback once a command issued an error. The
psqldocumentation reveals the problem:
in other words, having it to
ON_ERROR_ROLLBACK When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. When set to interactive, such errors are only ignored in interactive sessions, and not when reading script files. When unset or set to off, a statement in a transaction block that generates an error aborts the entire transaction. The error rollback mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails.
psqlto issue a
SAVEPOINTbefore any command in the transaction block, therefore making the transaction itself made up by several sub-transactions, and that was the reason why
xminwas increasing. PostgreSQL is, of course, always right!