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.
    – a_horse_with_no_name
    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
9

You can inspect the internal xmin to identify newly inserted row versions (note, that includes results from UPDATE):

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

How so?

The function txid_current() returns the current transaction ID. However, while the internal transaction ID type in xmin is 32 bits wide and wraps around every 4 billion transactions, txid_current() returns a bigint quantity, per documentation:

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

Addressing comment: Deleted rows that were deleted in the same transaction are always invisible either way, so not included in the result.

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 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, privacy policy and cookie policy

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