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(dot)ignatov(at)postgrespro(dot)ru <mailto: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 <mailto: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
> <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).