Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] xmin increasing within a transaction block?

[GENERAL] xmin increasing within a transaction block?

From
Luca Ferrari
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Alvaro Herrera
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Andres Freund
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Luca Ferrari
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Luca Ferrari
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Alvaro Herrera
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Tom Lane
Date:
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

Re: [GENERAL] xmin increasing within a transaction block?

From
Luca Ferrari
Date:
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