Re: Detecting if current transaction is modifying the database

Hi! Make trigger function 

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

On Fri, Aug 5, 2016 at 10:25 PM +0300, "Christian Ohler" <ohler@shift.com> wrote:

Hi,

I'm trying to find a way to have Postgres tell me if the current transaction would modify database if I committed it now.  I can live with a conservative approximation (sometimes – ideally, rarely – get a "yes" even though nothing would be modified, but never get a "no" even though there are pending modifications).  It's acceptable (probably even desirable) if a no-op write operation like "UPDATE foo SET bar = 1 WHERE bar = 1" is considered a modification.

(The use case is an audit log mechanism vaguely similar to pgMemento.)

This sentence from https://www.postgresql.org/docs/9.5/static/view-pg-locks.html :

> If a permanent ID is assigned to the transaction (which normally happens

> only if the transaction changes the state of the database), it also holds

> an exclusive lock on its permanent transaction ID until it ends.

makes me think that I can perhaps do it as follows:

SELECT count(*) FROM pg_locks WHERE pid=pg_backend_pid() AND locktype='transactionid' AND mode='ExclusiveLock' AND granted;

Is that right?  "Permanent transaction ID" refers to the XID, correct?  Are there other, better ways?  Are there ways to avoid false positives due to temp tables?

Thanks in advance,

Christian.