[GENERAL] Detecting if current transaction is modifying the database

[Christian Ohler]

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

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.

[Alex Ignatov]

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" <***@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.)

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.

[Christian Ohler]

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.

Post by Alex Ignatov
Hi! Make trigger function
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
Hi,

Post by Christian Ohler
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/

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.

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.

[Rob Sargent]

Post by Christian Ohler
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
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"
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.

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).

[Christian Ohler]

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).

I'm looking for a statement (or sequence of statements) that, when run
within a transaction, tells the client if any writes are happening in
that transaction – basically an interface similar to my proposed
solution. I have some database wrapper code on the client that passes
along arbitrary statements to Postgres, and I'd like for that wrapper
code to be able to determine whether the current transaction is a
write (for various purposes including logging). It would be nice not
to make the client-side wrapper code dependent on instrumentation of
the database schema itself.

What can you tell me about my proposed solution? Does it do what I
describe I want from it? Are there limitations I should be aware of?

[Tom Lane]

Post by Christian Ohler
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).

You could check to see if the current transaction has had an XID assigned,
or if it's emitted any WAL records. There are already tests for those
sorts of conditions in various places, though I do not think they're
exposed at the SQL level.

Post by Christian Ohler
Another thing I should have mentioned is that I don't consider incrementing
a sequence to be a modification.

Things might not work the way you want on that...

regards, tom lane

[Rob Sargent]

Post by Christian Ohler

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).

I'm looking for a statement (or sequence of statements) that, when run
within a transaction, tells the client if any writes are happening in
that transaction – basically an interface similar to my proposed
solution. I have some database wrapper code on the client that passes
along arbitrary statements to Postgres, and I'd like for that wrapper
code to be able to determine whether the current transaction is a
write (for various purposes including logging). It would be nice not
to make the client-side wrapper code dependent on instrumentation of
the database schema itself.
What can you tell me about my proposed solution? Does it do what I
describe I want from it? Are there limitations I should be aware of?

At what point do you intend to inform the client that the db will be
(significantly) altered? You're planned call is within the transaction
and presumably late in the sequence (so the locks have been created).
Not sure if your client can see any results until after the transaction
has been largely executed. Does that matter?

[Christian Ohler]

Post by Tom Lane

Post by Christian Ohler
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).

You could check to see if the current transaction has had an XID assigned,
or if it's emitted any WAL records. There are already tests for those
sorts of conditions in various places, though I do not think they're
exposed at the SQL level.

Yes, checking if the current transaction has an XID assigned was the
idea behind my proposed solution above:

SELECT count(*) FROM pg_locks WHERE pid=pg_backend_pid() AND
locktype='transactionid' AND mode='ExclusiveLock' AND granted;

(See my original email for why I think this checks whether an XID is
assigned.) It works in my superficial tests. Does it look like I'm
checking for the right conditions?

Checking for WAL records is an interesting idea, thanks – it seems
like that would be an even more direct test than whether an XID has
been assigned. I looked at pg_current_xlog_insert_location(), but
that seems to be global, not limited to the current transaction, so
doesn't seem usable for this.

Post by Tom Lane

Post by Christian Ohler
Another thing I should have mentioned is that I don't consider incrementing
a sequence to be a modification.

Things might not work the way you want on that...

It's fine if they result in false positives (but don't seem to for my
above method).

Thanks,
Christian.

[Christian Ohler]

Post by Rob Sargent

Post by Christian Ohler
I'm looking for a statement (or sequence of statements) that, when run
within a transaction, tells the client if any writes are happening in
that transaction – basically an interface similar to my proposed
solution. I have some database wrapper code on the client that passes
along arbitrary statements to Postgres, and I'd like for that wrapper
code to be able to determine whether the current transaction is a
write (for various purposes including logging). It would be nice not
to make the client-side wrapper code dependent on instrumentation of
the database schema itself.
What can you tell me about my proposed solution? Does it do what I
describe I want from it? Are there limitations I should be aware of?

At what point do you intend to inform the client that the db will be
(significantly) altered? You're planned call is within the transaction and
presumably late in the sequence (so the locks have been created). Not sure
if your client can see any results until after the transaction has been
largely executed. Does that matter?

If I'm understanding you correctly, it does not matter – I'm looking
for a method to determine whether the current transaction includes any
writes _so far_.

Thanks,
Christian.