On 08/05/2016 01:48 PM, Christian Ohler wrote:
Thanks, fair point. I should have mentioned that I know about
triggers but was hoping to find a less invasive mechanism (IIUC, I'd
have to install a trigger on every table) – it seems to me that
Postgres should just be able to tell me whether COMMIT will do
anything, it obviously has to track that somehow (or some
approximation of it).
Another thing I should have mentioned is that I don't consider
incrementing a sequence to be a modification.
On Fri, Aug 5, 2016 at 12:35 PM, Alex Ignatov
<a.igna...@postgrespro.ru <mailto:a.igna...@postgrespro.ru>> wrote:
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"
<oh...@shift.com <mailto:oh...@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
<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.
What sort of interface are you looking for. Where/When would you grab
the information? Do what with it? Log triggers are the typical pattern
here (with packages just for that sort of thing).
