From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Owen Jacobson <ojacobson@osl.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: After Trigger assignment to NEW
Date: Sat, 25 Feb 2006 06:40:44 -0800 (PST)
Message-ID: <20060225062938.W75421@megazone.bigpanda.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0602250917250.24445-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0602250917250.24445-100000@matrix.gatewaynet.com>
On Sat, 25 Feb 2006, Achilleus Mantzios wrote:
> O Owen Jacobson Ýãñáøå óôéò Feb 24, 2006 :
>
> > Achilleus Mantzios wrote:
> >
> > > O Tom Lane Ýãñáøå óôéò Feb 24, 2006 :
> > >
> > > > By definition, an AFTER trigger is too late to change what was
> > > > stored. Use a BEFORE trigger.
> > >
> > > Too late if someone wants to store it.
> > > I wanna store the intented original values, thats why i use
> > > AFTER trigger.
> > > But i would like to alter what a final AFTER trigger would see.
> > >
> > > I'll elabarote a little.
> > >
> > > An update happens.
> > > The row is stored.
> > > An after trigger is fired that alters some NEW columns
> > > (nullifies them), aiming for a subsequent trigger
> > > to see the altered results .
> > >
> > > It should be something like a pointer to a HeapTuple, (right?),
> > > so that would be feasible i suppose.
> > >
> > > I would not even make a post if it was something that trivial.
> > >
> > > I hope you get my point.
> >
> > Your real problem is that the "subsequent" trigger has behaviour you
> > don't like. That's what you should be fixing. If dbmirror has no way
> > to exclude specific tables from mirroring, take it up with them as a
> > feature request, or patch dbmirror to work how you want it to.
> >
> > AFTER triggers *must* receive the row that was actually
> > inserted/updated/deleted. If they could receive a "modified" row that
> > didn't reflect what was actually in the database, all sorts of useful
> > trigger-based logging and replication patterns wouldn't work, and
> > there's really no other way to implement them. See also Tom Lane's
> > other message for further implications of being able to modify the
> > rows seen by AFTER triggers.
> >
>
> As i have explained my dbmirror is FK null values gnostic(=aware) already
> as we speak.
[...]
> So nullifying a value just before the dbmirror trigger would do exactly
> the right thing (for me)
Yes it does what you want for this very specific case. But, would it do
what you want if someone put a trigger before it that changed the values
to some non-NULL thing? That seems likely to break your mirroring.
> > I'd also be hesitant to write triggers that have to execute in a specific order.
>
> Meaning that would hurt portability?
> Most people need features rathen than the relief to know they can migrate
> to another database (which they probably never will)
In this case, you're giving up the "feature" that users can write
constraints, logging or mirroring after triggers that are guaranteed to
get the data that was actually inserted in order to get the feature that a
trigger can affect the data to the next trigger. This seems like a general
loss in functionality for a larger fraction of users than those who gain.
> Back to AFTER trigger changing values issue,
> i think things are not so dramatic if
> FK triggers could just be fired first.
Actually, I think we technically fire the checks too early as it is, so I
don't see enshrining that or making it earlier is a good idea.
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: sszabo@megazone.bigpanda.com, achill@matrix.gatewaynet.com, ojacobson@osl.com
Subject: Re: After Trigger assignment to NEW
In-Reply-To: <20060225062938.W75421@megazone.bigpanda.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