From: | Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru> |
---|---|
To: | Christian Ohler <ohler(at)shift(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Detecting if current transaction is modifying the database |
Date: | 2016-08-05 19:35:24 |
Message-ID: | 19518802B8BEC076.8086516C-662E-4D10-BE82-DB216597001E@mail.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)shift(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-08-05 19:43:43 | Re: Streaming Replica Master-Salve Config. |
Previous Message | Christian Ohler | 2016-08-05 19:21:53 | Detecting if current transaction is modifying the database |