From: | Christian Ohler <ohler(at)shift(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Detecting if current transaction is modifying the database |
Date: | 2016-08-05 19:21:53 |
Message-ID: | CAOsiKEL-q49K9vsYwxvcG6reo-KNG98twnahtVaks_CynKDG8A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Ignatov | 2016-08-05 19:35:24 | Re: Detecting if current transaction is modifying the database |
Previous Message | Alex Ignatov | 2016-08-05 16:59:01 | Re: fun fact about temp tables |