How to view tuples changed in a PostgreSQL transaction?

BEGIN;

   INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c');

   -- SELECT * FROM pg_stat_activity WHERE ...
   -- shows records with a flag if they have been added/deleted/updated

END;

Is there a way to view the uncommitted inserts/deletes of a transaction for a given table in PostgreSQL 9.x? This is purely for visual confirmation.

While my initial thought is no (just perform a SQL-Select of the table), certain users wanted to visualize differences within the transaction. I could see how this might be helpful if having a lot of savepoints and transactions. Thus, it was worth asking to the community at large.

You can inspect the system column xmin to identify newly inserted row versions. That includes new row versions written by UPDATE. Deleted rows that were deleted in the same transaction are always invisible either way, so not included in the result.
Postgres 13 or newer

The function pg_current_xact_id returns the current transaction ID as xid8, a dedicated 8-byte data type representing unique transaction IDs.

For storage and historical reasons, xmin in the tuple header only stores xid, a 4-byte data type sliced down from the full transaction ID that wraps around every ~ 4 billion transactions. (There is a whole machinery in place to avoid actual wraparounds in the DB.) The manual:

Unlike xid values, xid8 values increase strictly monotonically and cannot be reused in the lifetime of a database cluster.

There is also pg_current_xact_id_if_assigned(), which does not burn a transaction ID if non has been assigned, yet (if the transaction has been read-only so far). That's better for the purpose, as there cannot be any rows from a read-only transaction anyway.

Either way, to make it work, we have to add an explicit cast:

SELECT * FROM foo_table
WHERE  xmin = pg_current_xact_id_if_assigned()::xid;

Here is the thread discussing the patch to add xid8:

• https://www.postgresql.org/message-id/flat/2B1364F4-8522-4357-9051-19118B852DA4%40enterprisedb.com#2af551897dc5084068582e4516f510b4

Older versions

There is the similar function txid_current(), which returns bigint. It is deprecated now, but still supported as of pg 16. It builds on xid, but quoting the manual for pg 12:

... extended with an "epoch" counter so it will not wrap around during the life of an installation.

There is a thread on pgsql-hackers with Tom Lane discussing it:

• https://www.postgresql.org/message-id/28621.1321226208@sss.pgh.pa.us

I derived the safe WHERE condition from it:

SELECT * FROM foo_table
WHERE  xmin = (txid_current() % (2^32)::bigint)::text::xid;

Disclaimer

However, this does not work with subtransactions started with SAVEPOINT (or some other way, plpython can use subtransactions, too). Those spawn separate xids and there is currently (as of pg 12) no way to get the full list of xids belonging to the top transaction returned by txid_current(). In my search for a solution I found this closely related thread on pgsql-hackers:

• Re: rows modified in current transaction

I don't currently see a reliable solution working around that limitation.

Related:

• Can I select data inserted in the same uncommited transaction?

StackExchange Database Administrators Q#123145, answer score: 10