X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 89C069FA30D for ; Sat, 24 Jun 2006 00:29:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 70408-02 for ; Sat, 24 Jun 2006 00:29:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 5E16D9F9AD3 for ; Sat, 24 Jun 2006 00:29:35 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so1321828ugf for ; Fri, 23 Jun 2006 20:29:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=DTgQ90qqIi/LnBz5+AEIag08bAJ7lrOF1YP0DJBZC27UgweTBI/RdHdDDo3TBtXWBzI0fu25FYCtzKk522yG1FcdoatM+OycyIKwrXhFICYdU55vDuUX79DSLXcfbzZ+Dg3ug8FhlhzpJS2LAyubQVj0S3F2A+6gehYIPJTwwMQ= Received: by 10.78.136.7 with SMTP id j7mr1503995hud; Fri, 23 Jun 2006 20:29:33 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Fri, 23 Jun 2006 20:29:33 -0700 (PDT) Message-ID: Date: Fri, 23 Jun 2006 22:29:33 -0500 From: "Aaron Bono" To: "Tom Lane" Subject: Re: Fwd: Start up question about triggers Cc: "Andrew Sullivan" , pgsql-sql@postgresql.org In-Reply-To: <17077.1151084888@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_33439_15004662.1151119773842" References: <006901c696c1$d2af82d0$6400a8c0@Dell4500> <200606231548.k5NFmhSW028183@auth-smtp.hol.gr> <20060623173504.GC12422@phlogiston.dyndns.org> <17077.1151084888@sss.pgh.pa.us> X-Google-Sender-Auth: 32f10df978ca1b48 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/263 X-Sequence-Number: 25499 ------=_Part_33439_15004662.1151119773842 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline This is why I was searching for good meta data. Here is a thought. If your trigger has the OLD and NEW, is there a way to get a list of fields from OLD and NEW? If TG_RELNAME is the name of the table, could you just ask PostgreSQL what the columns are in that table, iterate through those columns, get the values for each of these columns out of OLD and NEW and save the old/new values? What I really cannot find is a way to _dynamically_ in the trigger ask what COLUMNS are in OLD and NEW. If we had: - table affected (TG_RELNAME?) - columns that are in the table - old values for each of these columns - new values for each of these columns Then you could store this information into two tables: modify_table modify_table_id modify_dt table_name modify_value modify_value_id modify_table_id old_value new_value I wish I had more experience with stored procedures - I know what I would try to do, just not if it is possible or how to implement it. Tom makes a very good point that having the actual query is not going to help in a general sense. If someone does an insert or update which fires a trigger that does further updates and inserts or even changes values on the fly, the inserts and updates you record will NOT reveal exactly what is going on. Keeping the values from OLD and NEW at the very end would be much more useful. -Aaron Bono On 6/23/06, Tom Lane wrote: > > Andrew Sullivan writes: > > On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote: > >> Then there exist a TG_QUERY parameter that we could use to get the > actual > >> query ran by a user, so if I ran the imaginary query > > > Which "actual query"? By the time the trigger fires, the query might > > already have been rewritten, I think. No? I _think_ that even > > BEFORE triggers happen after the rewriter stage is called, but > > someone who has more clue will be able to correct me if I'm wrong. > > Even if you could get hold of the user query text, it'd be a serious > mistake to imagine that it tells you everything you need to know about > the update. Aside from rule rewrites, previous BEFORE triggers could > have changed fields that are mentioned nowhere in the query. The only > safe way to determine what's going on is to compare the OLD and NEW > row values. > > regards, tom lane > > ------=_Part_33439_15004662.1151119773842 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline This is why I was searching for good meta data.

Here is a thought.  If your trigger has the OLD and NEW, is there a way to get a list of fields from OLD and NEW?  If TG_RELNAME is the name of the table, could you just ask PostgreSQL what the columns are in that table, iterate through those columns, get the values for each of these columns out of OLD and NEW and save the old/new values?

What I really cannot find is a way to _dynamically_ in the trigger ask what COLUMNS are in OLD and NEW.  If we had:
  • table affected (TG_RELNAME?)
  • columns that are in the table
  • old values for each of these columns
  • new values for each of these columns
Then you could store this information into two tables:

modify_table
   modify_table_id
   modify_dt
   table_name

modify_value
   modify_value_id
   modify_table_id
   old_value
   new_value

I wish I had more experience with stored procedures - I know what I would try to do, just not if it is possible or how to implement it.

Tom makes a very good point that having the actual query is not going to help in a general sense.  If someone does an insert or update which fires a trigger that does further updates and inserts or even changes values on the fly, the inserts and updates you record will NOT reveal exactly what is going on.  Keeping the values from OLD and NEW at the very end would be much more useful.

-Aaron Bono

On 6/23/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Fri, Jun 23, 2006 at 06:48:49PM +0300, Forums @ Existanze wrote:
>> Then there exist a TG_QUERY parameter that we could use to get the actual
>> query ran by a user, so if I ran the imaginary query

> Which "actual query"?  By the time the trigger fires, the query might
> already have been rewritten, I think.  No?  I _think_ that even
> BEFORE triggers happen after the rewriter stage is called, but
> someone who has more clue will be able to correct me if I'm wrong.

Even if you could get hold of the user query text, it'd be a serious
mistake to imagine that it tells you everything you need to know about
the update.  Aside from rule rewrites, previous BEFORE triggers could
have changed fields that are mentioned nowhere in the query.  The only
safe way to determine what's going on is to compare the OLD and NEW
row values.

                        regards, tom lane

------=_Part_33439_15004662.1151119773842--