Обсуждение: [GENERAL] xmin increasing within a transaction block?
Hi all, I suspect this has a trivial explaination, but this is what I'm experiencing: > CREATE TABLE foo( i int ); > BEGIN; * > INSERT INTO foo(i) VALUES( 1 ); * > INSERT INTO foo(i) VALUES( 2 ); * > SELECT xmin, cmin, xmax, cmax, i FROM foo;xmin | cmin | xmax | cmax | i ------+------+------+------+---2466 | 0 | 0 | 0 | 12467 | 1 | 0 | 1 | 2 (2 rows) * > SELECT txid_current();txid_current -------------- 2465 (1 row) Why is xmin greater than the current transaction id (and most notably not "fixed")? What am I missing here? I'm running 9.6.5. > select version(); version ------------------------------------------------------------------------------------------------------------------------------------------------------PostgreSQL 9.6.5on amd64-portbld-freebsd11.0, compiled by FreeBSD clang version 3.8.0 (tags/RELEASE_380/final 262564) (based on LLVM 3.8.0), 64-bit (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax, cmax, i FROM foo; > xmin | cmin | xmax | cmax | i > ------+------+------+------+--- > 2466 | 0 | 0 | 0 | 1 > 2467 | 1 | 0 | 1 | 2 > (2 rows) With this example both rows show the same xmin to me, which is what I'd expect. > Why is xmin greater than the current transaction id (and most notably > not "fixed")? Something is using subtransactions there. My first guess would be that there are triggers with EXCEPTION blocks, but your example doesn't show any. Or maybe you have event triggers. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi, On 2017-11-06 10:36:06 +0100, Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax, cmax, i FROM foo; > xmin | cmin | xmax | cmax | i > ------+------+------+------+--- > 2466 | 0 | 0 | 0 | 1 > 2467 | 1 | 0 | 1 | 2 > (2 rows) > > * > SELECT txid_current(); > txid_current > -------------- > 2465 > (1 row) > > > Why is xmin greater than the current transaction id (and most notably > not "fixed")? > What am I missing here? > I'm running 9.6.5. That doesn't look like plain postgres behaviour to me. Any chance you're using a pooler in statement mode in front of postgres? Greetings, Andres Freund -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Mon, Nov 6, 2017 at 1:53 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > Something is using subtransactions there. My first guess would be that > there are triggers with EXCEPTION blocks, but your example doesn't show > any. Or maybe you have event triggers. Thanks, but I don't see any event trigger: > \dy List of event triggersName | Event | Owner | Enabled | Procedure | Tags ------+-------+-------+---------+-----------+------ (0 rows) Please note that I tested it on a freshly created database obtaining the same behavior. I did have in the past event trigger, but I have dropped long time before this behavior. Any other idea? Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Mon, Nov 6, 2017 at 2:29 PM, Andres Freund <andres@anarazel.de> wrote: > That doesn't look like plain postgres behaviour to me. Any chance you're > using a pooler in statement mode in front of postgres? None I'm aware of, since the machine is using postgresql locally and I'm connecting to it using the port 5432. I did have in the past enabled wal_level to logical but now it is set to minimal and I don't have any replication slot (in the case it could do something related to this behvior): # SELECT * FROM pg_replication_slots; (0 rows) Any idea? Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Luca Ferrari wrote: > Any other idea? None here. Maybe try attaching a debugger, setting a breakpoint on AssignTransactionId, and grab backtraces when it is hit. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Luca Ferrari wrote:
>> Why is xmin greater than the current transaction id (and most notably
>> not "fixed")?
> Something is using subtransactions there. My first guess would be that
> there are triggers with EXCEPTION blocks, but your example doesn't show
> any. Or maybe you have event triggers.
I can reproduce the example if I "\set ON_ERROR_ROLLBACK on" in psql.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Nov 6, 2017 at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: >> Luca Ferrari wrote: >>> Why is xmin greater than the current transaction id (and most notably >>> not "fixed")? > >> Something is using subtransactions there. My first guess would be that >> there are triggers with EXCEPTION blocks, but your example doesn't show >> any. Or maybe you have event triggers. > > I can reproduce the example if I "\set ON_ERROR_ROLLBACK on" in psql. > Shame on me, I did forgot to have enabled that in my ~/.psqlrc file (and did not hit an error within the transaction block to see it was aborting). And in fact, the manual page for psql reports that ON_ERROR_ROLLBACK: 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. Sorry for the noise. Thanks, Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general