xmin increasing within a transaction block?

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 triggers
Name | 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