From: Aaron Bono <postgresql@aranya.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: Start up question about triggers
Date: Fri, 23 Jun 2006 22:29:33 -0500
Message-ID: <bf05e51c0606232029j2f468ceapfbb583278be41504@mail.gmail.com> (raw)
In-Reply-To: <17077.1151084888@sss.pgh.pa.us>
References: <006901c696c1$d2af82d0$6400a8c0@Dell4500>
<200606231548.k5NFmhSW028183@auth-smtp.hol.gr>
<20060623173504.GC12422@phlogiston.dyndns.org>
<17077.1151084888@sss.pgh.pa.us>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, tgl@sss.pgh.pa.us, ajs@crankycanuck.ca
Subject: Re: Fwd: Start up question about triggers
In-Reply-To: <bf05e51c0606232029j2f468ceapfbb583278be41504@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox