0

If I do an UPDATE to a row within a transaction, each time xmin increments. I thought that xmin always represents the current tx id. But it seems to be storing current xid + cmin instead.

But when figuring out whether the row is visible or not, then the current tx must somehow know which transaction this xmin comes from. I thought that the Snapshot contains only transaction IDs and their statuses, but apparently it also must somehow map the xid+cmin to the owning transaction? Why doesn't it store the xid instead?

Here's the script:

start transaction isolation level read committed;
update a set name='bb' where id=1 returning txid_current(), xmin;

I repeat the last line multiple times and get the same xid, but different xmin on every time. Isolation level doesn't seem to matter.

Environment: tried Postgres 15 and 17, client: IntelliJ IDEA, both are on MacOS. version() output:

PostgreSQL 15.8 (Postgres.app) on aarch64-apple-darwin21.6.0, compiled by Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit
Stanislav Bashkyrtsev
  • 587
  • 3
  • 9

1 Answers1

1

The culprit is IntelliJ IDEA - it silently inserts Savepoints before each statement* when Transaction mode is Manual.

When I try the same in psql, the result is what you'd expect - the xmin doesn't increment. But if I then put a Savepoint before each UPDATE statement, then the behavior becomes identical.

So, to disable this behavior I stay in Auto mode in IntelliJ. I also raised a ticket for the IntelliJ team.

* I can't prove it though because I couldn't find a simple way to get a list/count of Savepoints/Subtransactions in PG.

Stanislav Bashkyrtsev
  • 587
  • 3
  • 9