Обсуждение: Detecting if current transaction is modifying the database
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.
Hi! Make trigger function
On Fri, Aug 5, 2016 at 10:25 PM +0300, "Christian Ohler" <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.)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.
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.ignatov@postgrespro.ru> wrote:
Hi! Make trigger functionOn Fri, Aug 5, 2016 at 10:25 PM +0300, "Christian Ohler" <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.)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.
On 08/05/2016 01:48 PM, Christian Ohler wrote:
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).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.ignatov@postgrespro.ru> wrote:Hi! Make trigger functionOn Fri, Aug 5, 2016 at 10:25 PM +0300, "Christian Ohler" <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.)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.
On Fri, Aug 5, 2016 at 12:55 PM, Rob Sargent <robjsargent@gmail.com> wrote: > > What sort of interface are you looking for. Where/When would you grab the information? Do what with it? Log triggersare 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?
Christian Ohler <ohler@shift.com> writes: > 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. > 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
On 08/05/2016 02:15 PM, Christian Ohler wrote: > On Fri, Aug 5, 2016 at 12:55 PM, Rob Sargent <robjsargent@gmail.com> wrote: >> What sort of interface are you looking for. Where/When would you grab the information? Do what with it? Log triggersare 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?
On Fri, Aug 5, 2016 at 1:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Christian Ohler <ohler@shift.com> writes: >> 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. >> 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.
On Fri, Aug 5, 2016 at 1:26 PM, Rob Sargent <robjsargent@gmail.com> wrote: > On 08/05/2016 02:15 PM, Christian Ohler wrote: >> >> 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.
On Fri, Aug 5, 2016 at 4:24 PM, Christian Ohler <ohler@shift.com> wrote: Your check for a exclusive self-lock on transactionid should work. It may be possible to find a way to do it that is less expensive, so I would definitely encapsulate that in a function; but off-hand I'm not thinking of a better way. You might be tempted to use the txid_current() function, but note that it assigns a transaction ID if there is not yet one assigned. That has prevented that function from being useful to me in every case I've considered it so far; I wish we had a function that told the current transaction ID and just returned NULL if none has yet been assigned. I'm not sure what the best name would be for such a function when we already have a function called txid_current() which does something different from that. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Aug 8, 2016 at 8:23 AM, Kevin Grittner <kgrittn@gmail.com> wrote: > Your check for a exclusive self-lock on transactionid should work. > It may be possible to find a way to do it that is less expensive, > so I would definitely encapsulate that in a function; but off-hand > I'm not thinking of a better way. Great, thanks for confirming this. I agree that txid_current() isn't usable for this, but even a "soft" version of it (that doesn't assign an ID) still wouldn't quite be what I'm looking for; Tom's idea of checking for WAL records sounds more like the "proper" solution, in that it more directly checks whether COMMIT would do "real" work, and thus would presumably have fewer false positives. (But that's a guess. AFAICT, creating a temp table also produces WAL records, so perhaps checking for them is no better than checking for a transaction ID after all.)
Christian Ohler <ohler@shift.com> writes: > ... (But that's a guess. AFAICT, creating a temp table > also produces WAL records, so perhaps checking for them is no better > than checking for a transaction ID after all.) Well, creating a temp table makes entries in the system catalogs, which requires both an XID and WAL entries. The same goes for most database alterations, really. There are very limited cases where you can produce WAL without assigning an XID or vice versa, but I'm not sure it's worth your while to distinguish. regards, tom lane