CODEWITHSUNDEEP
sqlpostgresqltransactionscommit
Caroline Kwerts
Caroline Kwerts

Reputation: 225

PostgreSQL: find out if a row has been created by the current transaction

Is it possible to find out if a row in a table has been created by the current transaction (and therefore is not yet visible for other transactions, because the current transaction is still active)?

My use case: I am adding event logging to the database. This is done in plpgsql triggers. A row in the event table looks like this: (event id:serial, event action:text, count:integer:default 1).

Now, the reasoning behind my question: If a certain row has been created by this transaction (most likely in another trigger), I could increment the count instead of creating a new row in the event table.

Upvotes: 0

Views: 1917

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246788

You could just look for logging entries like this:

SELECT ...
FROM tablename
WHERE xmin = current_txid() % (2^32)::bigint;

That will find all rows added or modified in the current transaction.

The downside is that this will force a sequential scan of the whole table, and you cannot avoid that since you cannot have an index on a system column.

So you could add an extra column xid to your table that is filled with txid_current()::bigint whenever a row is inserted or updated. Such a column can be indexed and efficiently used in a search:

SELECT ...
FROM tablename
WHERE xid = current_txid();

Upvotes: 4

Ben
Ben

Reputation: 35613

You might consider something like this:

create table ConnectionCurrentAction (
     connectionID int primary key,
     currentActionID uuid
)

then at the beginning of the transaction:

delete ConnectionCurrentAction where connectionID = pg_backend_pid()
insert ConnectionCurrentAction(connectionID, currentActionID) 
select pg_backend_pid(), uuid_generate_v4()

You can wrap this in a proc called say, audit_action_begin

Note: You may instead choose to enforce the requirement that an "action" be created explicitly by removing the delete here.

At the end of a transaction, do audit_action_end:

delete ConnectionCurrentAction where connectionID = pg_backend_pid()

Whenever you want to know the current transaction:

(select currentActionID from ConnectionCurrentAction where connectionID - pg_backend_pid()(

You can wrap that in a function audit_action_current()

You can then put the currentActionID into your log which will enable you to identify whether a row was created in the current action or not. This will also allow you to identify where rows in different audit tables were created in the current logical action.

If you don't want to use a uuid a sequence would do just as well here. I like uuids.

Upvotes: 1

Related Questions