7 
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.

Improve this question
5
  • Just select from the table you just inserted into from within the same transaction.
    – user1822
    Dec 7, 2015 at 21:49
  • @a_horse_with_no_name yes, that's what I suggested, but they wanted to be able to see the records that had not changed, unless you know of a way to show both the original table and include some sort of modified field. I don't know how else to accomplish this without the use of a temporary table / log, but thought postgresql writer might have something I'm not aware of (possibly using OIDs), since it must be able to rollback a transaction. I was hopeful when I thought Postgres might track simple insert/update, but I doubt it would be as easy to track updates.
    – vol7ron
    Dec 7, 2015 at 21:51
  • Sounds like a job for an audit table.
    – Joishi Bodio
    Dec 7, 2015 at 22:08
  • 1
    You want to capture your changed data ?
    – Luan Huynh
    Dec 8, 2015 at 1:46
  • @Innnh yes within a transaction, but would also like to show non-affected rows. Using what Erwin gave, it looks like I can yield the results with a union or CASE statement.
    – vol7ron
    Dec 8, 2015 at 15:33

1 Answer 1

Reset to default
10

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:

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:

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:

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

Related:

Improve this answer
3
  • 1
    I know two things: 1) I trust Tom Lane 2) I envy your Postgres prowess - this worked beautifully. I'm curious how deleted rows would be treated, but fortunately, I don't have to worry about them :)
    – vol7ron
    Dec 8, 2015 at 15:34
  • @vol7ron: Rows deleted in the same transaction are always invisible either way, so not included in the result. But there is a limitation: does not work with subtransactions. Consider the update.
    – Erwin Brandstetter
    Dec 9, 2015 at 4:27
  • Yeah, I imagine there might be a number of edge cases. I don't think it's a common procedures, but I think it's interesting that kind of data isn't available this day in age (knowing there's a lot of other outstanding updates that may have higher priorities). I'm thinking for debugging purposes, it would be a nice feature; or at least just to cease paranoia for ACID-compliance.
    – vol7ron
    Dec 9, 2015 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.