Re: Triggers on columns

On Thu, 3 Sep 2009, Robert Haas wrote:

> On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
>> On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote:
>>> On Sep 3, 2009, at 7:44 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>>> The SQL standard specifies that a trigger is fired if the column is
>>>> mentioned in the UPDATE statement, independent of whether the value is
>>>> actually changed through the update.
>>>
>>> That is thorougly bizarre, IMO.
>>
>> Well, if you find that bizarre, consider the existing behavior: Why
>> should an ON UPDATE row trigger fire when none of the values of the
>> row's columns actually change?  I think if you read
>>
>> TRIGGER ON UPDATE
>>
>> as
>>
>> TRIGER ON UPDATE OF <all columns>
>>
>> then it makes some sense.
>
> Not to me. I use triggers to maintain database invariants, such as:
>
> CREATE TABLE foo (id serial, name varchar, number_of_bars integer not
> null default 0, primary key (id));
> CREATE TABLE bar (id serial, foo_id integer not null references foo (id));
>
> By setting up INSERT, UPDATE, and DELETE triggers on bar, I can
> maintain the invariant that number_of_bars for each foo is in fact the
> number of bars where foo_id is the id of that foo. However, in order
> to suppress unnecessary updates to the foo table, I have to have the
> update trigger check whether OLD.foo_id = NEW.foo_id before it does
> anything.
>
> If TRIGGER ON UPDATE OF foo_id means whether the value actually
> changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id
> means whether the column was present in the update list, then it
> doesn't. Perhaps there are some use cases where we can be certain
> that we only care about whether the value was in the update list, and
> not whether it was changed, but off the top of my head it seems like
> 0% of mine would fall into that category.
>
> It also seems to me logically inconsistent that we would expose this
> information via the CREATE TRIGGER interface but not to the trigger
> function itself. From within the function, you can compare NEW and
> OLD, but you get no visibility into which columns were actually
> updated. And apparently now from within CREATE TRIGGER we'll have
> just the opposite. Blech...
>
> By the way, I completely agree that it would be useful to have a way
> to suppress triggers from firing when no columns were actually
> modified. But I also wouldn't argue that should be the only available
> behavior. Sometimes it's useful to schedule a no-op update explicitly
> for the purpose of firing triggers.

A simple use case would be to update a timestamp column with CURRENT_TIMESTAMP
as instance.

>
> ...Robert

--
Guillaume (ioguix) de Rorthais
>From pgsql-hackers-owner(at)postgresql(dot)org Thu Sep 3 14:15:44 2009
Received: from localhost (unknown [200.46.208.211])
by mail.postgresql.org (Postfix) with ESMTP id 097A5634E22
for <pgsql-hackers-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Thu, 3 Sep 2009 14:15:44 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
with ESMTP id 61422-03
for <pgsql-hackers-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Thu, 3 Sep 2009 17:15:29 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from fetter.org (start.fetter.org [66.92.188.65])
by mail.postgresql.org (Postfix) with ESMTP id 98BBA633CA1
for <pgsql-hackers(at)postgreSQL(dot)org>; Thu, 3 Sep 2009 14:15:23 -0300 (ADT)
Received: by fetter.org (Postfix, from userid 500)
id 9123BFBCBF6; Thu, 3 Sep 2009 10:15:22 -0700 (PDT)
Date: Thu, 3 Sep 2009 10:15:22 -0700
From: David Fetter <david(at)fetter(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: gcc versus division-by-zero traps
Message-ID: <20090903171522(dot)GT8410(at)fetter(dot)org>
References: <14006(dot)1251987857(at)sss(dot)pgh(dot)pa(dot)us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <14006(dot)1251987857(at)sss(dot)pgh(dot)pa(dot)us>
User-Agent: Mutt/1.5.19 (2009-01-05)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.579 tagged_above=-10 required=5 tests=AWL=1.020,
BAYES_00=-2.599
X-Spam-Level:
X-Archive-Number: 200909/198
X-Sequence-Number: 145004

On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote:
> We have seen several previous reports of regression test failures
> due to division by zero causing SIGFPE, even though the code should
> never reach the division command:
>
> http://archives.postgresql.org/pgsql-bugs/2006-11/msg00180.php
> http://archives.postgresql.org/pgsql-bugs/2007-11/msg00032.php
> http://archives.postgresql.org/pgsql-bugs/2008-05/msg00148.php
> http://archives.postgresql.org/pgsql-general/2009-05/msg00774.php
>
> It's always been on non-mainstream architectures so it was hard to
> investigate. But I have finally been able to reproduce this:
> https://bugzilla.redhat.com/show_bug.cgi?id=520916
>
> While s390x is still not quite mainstream, at least I can get
> access to one ;-).

Do you also have access to z/OS with Unix System Services? IBM's
compiler, c89, is amazingly strict, and should help us flush out bugs. :)

> What turns out to be the case is that
> "simple" test cases like
> if (y == 0)
> single_function_call(...);
> z = x / y;
> do not show the problem; you need something pretty complex in the
> if-command. Like, say, an ereport() construct. So that's why the gcc
> boys haven't already had visits from mobs of villagers about this.
>
> I hope that the bug will get fixed in due course, but even if they
> respond pretty quickly it will be years before the problem disappears
> from every copy of gcc in the field. So I'm thinking that it would
> behoove us to install a workaround, now that we've characterized the
> problem sufficiently. What I am thinking is that in the three
> functions known to exhibit the bug (int24div, int28div, int48div)
> we should do something like this:
>
>
> if (arg2 == 0)
> + {
> ereport(ERROR,
> (errcode(ERRCODE_DIVISION_BY_ZERO),
> errmsg("division by zero")));
> + /* ensure compiler realizes we don't reach the division */
> + PG_RETURN_NULL();
> + }
> /* No overflow is possible */
> PG_RETURN_INT64((int64) arg1 / arg2);
>
> Thoughts?

How big would this change be? How would people know to use that
construct everywhere it's appropriate?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate