Re: Detecting if current transaction is modifying the database

Rob Sargent <robjsargent@xxxxxxxxx> · Fri, 5 Aug 2016 13:55:21 -0600



    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.ignatov@xxxxxxxxxxxxxx>
          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@xxxxxxxxx>
                    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.
                          

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