On Wed, Oct 8, 2014 at 4:30 PM, Marti Raudsepp <marti@juffo.org> wrote:
> On Thu, Oct 9, 2014 at 1:51 AM, Peter Geoghegan <pg@heroku.com> wrote:
>> On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>>> Although the last go-around does suggest that there is at least one
>>> point of difference on the semantics. You seem to want to fire the
>>> BEFORE INSERT triggers before determining whether this will be an
>>> INSERT or an UPDATE. That seems like a bad idea to me
>
> Indeed, the current behavior breaks even the canonical "keep track of
> how many posts are in a thread" trigger example because INSERT
> triggers are fired for an effective row UPDATE. I can't see how that's
> acceptable.
DB2 had the foresight to add the following restrictions to BEFORE ROW
triggers - they cannot:
"""
Contain any INSERT, DELETE, or UPDATE operations, nor invoke any
routine defined with MODIFIES SQL DATA, if it is not a compound SQL.
Contain any DELETE or UPDATE operations on the trigger subject table,
nor invoke any routine containing such operations, if it is a compound
SQL.
Reference a materialized query table defined with REFRESH IMMEDIATE
Reference a generated column other than the identity column in the NEW
transition variable.
"""
To get a sense of how doing fancy things in before triggers leads to
trouble, considering the hardening Kevin added in commit 6868ed74. In
short, use an AFTER trigger for this kind of thing, and all of these
issues go away.
>> Well, it isn't that I'm doing it because I think that it is a great
>> idea, with everything to recommend it. It's more like I don't see any
>> practical alternative.
>
> I proposed an alternative that avoids this surprise and might allow
> some other benefits. Can you please look into that?
I looked at that. You're talking about making the case where before
row triggers clearly are appropriate (when you just want to change the
tuple being inserted) fail, in order to make an arguably inappropriate
case for before row triggers work (when you don't change the tuple to
be inserted, but you do want to do some other thing). That seems
backwards. My proposed behavior seems far less surprising.
--
Peter Geoghegan
