From: | Christian Ohler <ohler(at)shift(dot)com> |
---|---|
To: | Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Detecting if current transaction is modifying the database |
Date: | 2016-08-05 19:48:59 |
Message-ID: | CAOsiKEK+txdZzfirYxVP575pvhXRbuNMvcshGyCLo6aC1nqSGA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)ignatov(at)postgrespro(dot)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" <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 | Rob Sargent | 2016-08-05 19:55:21 | Re: Detecting if current transaction is modifying the database |
Previous Message | John R Pierce | 2016-08-05 19:43:43 | Re: Streaming Replica Master-Salve Config. |