

Hi Hackers

I have noticed an issue with deferrable triggers not always being
deferrable.  Eg:

  alter table subsession
     add constraint fk_subsession_session foreign key (session_id)
        references session (session_id)
        deferrable;

  alter table subsession2
     add constraint fk_subsession2_session foreign key (session_id)
        references session (session_id)
        on delete restrict on update restrict
        deferrable;

select tgconstrname, tgtype, tgenabled, tgdeferrable, tginitdeferred
from pg_trigger where tgisconstraint;

      tgconstrname      | tgtype | tgenabled | tgdeferrable |
tginitdeferred 
------------------------+--------+-----------+--------------+----
 fk_subsession_session  |     21 | t         | t            | f
 fk_subsession_session  |      9 | t         | t            | f
 fk_subsession_session  |     17 | t         | t            | f
 fk_subsession2_session |     21 | t         | t            | f
 fk_subsession2_session |      9 | t         | f            | f
 fk_subsession2_session |     17 | t         | f            | f

So it would seem that if I include the clauses:

        on delete restrict on update restrict

Then the 'deferrable' which follows is only applied to creates and
not to updates or deletes.

Since 'restrict' is the default, the clauses aren't adding any value
and can be omitted.  In my case, the SQL is generated for me by
PowerDesigner.  My workaround is to tweak the PowerDesigner output 
definition to not include this line.

I have seen this behaviour in both 7.2 and 7.3.  Is it a bug?  Or
am I misunderstanding something?

Regards
Grant


07 Nov 2003 11:20:56 +1300
Grant McLean <grant@catalyst.net.nz>
Deferrable triggers

On Thu, 7 Nov 2003, Grant McLean wrote:

> So it would seem that if I include the clauses:
>
>         on delete restrict on update restrict
>
> Then the 'deferrable' which follows is only applied to creates and
> not to updates or deletes.
>
> Since 'restrict' is the default, the clauses aren't adding any value
> and can be omitted.  In my case, the SQL is generated for me by
> PowerDesigner.  My workaround is to tweak the PowerDesigner output
> definition to not include this line.
>
> I have seen this behaviour in both 7.2 and 7.3.  Is it a bug?  Or
> am I misunderstanding something?

Restrict is not the default, there is a difference between restrict and no
action. In fact I believe the main point of restrict (which IIRC was added
for sql99) is to allow you to have a deferred constraint that can do
immediate checking of validity on pk changes.

Thu, 6 Nov 2003 14:31:37 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Deferrable triggers
On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote:
> 
> On Thu, 7 Nov 2003, Grant McLean wrote:
> 
> > So it would seem that if I include the clauses:
> >
> >         on delete restrict on update restrict
> >
> > Then the 'deferrable' which follows is only applied to creates and
> > not to updates or deletes.
> >
> > Since 'restrict' is the default, the clauses aren't adding any value
> > and can be omitted.  In my case, the SQL is generated for me by
> > PowerDesigner.  My workaround is to tweak the PowerDesigner output
> > definition to not include this line.
> >
> > I have seen this behaviour in both 7.2 and 7.3.  Is it a bug?  Or
> > am I misunderstanding something?
> 
> Restrict is not the default, there is a difference between restrict and no
> action. In fact I believe the main point of restrict (which IIRC was added
> for sql99) is to allow you to have a deferred constraint that can do
> immediate checking of validity on pk changes.

I was basing my reasoning on the CREATE TABLE documentation which says:

  NO ACTION

    Produce an error indicating that the deletion or update would create
    a foreign key constraint violation. This is the default action. 

  RESTRICT

    Same as NO ACTION. 

So as you pointed out, RESTRICT is not the default, but according to the
docs NO ACTION is the default and RESTRICT is the same as NO ACTION.
Is the difference between the two documented anywhere?

Regards
Grant



07 Nov 2003 12:28:09 +1300
Grant McLean <grant@catalyst.net.nz>
Re: Deferrable triggers
On Thu, 7 Nov 2003, Grant McLean wrote:

> On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote:
> >
> > On Thu, 7 Nov 2003, Grant McLean wrote:
> >
> > > So it would seem that if I include the clauses:
> > >
> > >         on delete restrict on update restrict
> > >
> > > Then the 'deferrable' which follows is only applied to creates and
> > > not to updates or deletes.
> > >
> > > Since 'restrict' is the default, the clauses aren't adding any value
> > > and can be omitted.  In my case, the SQL is generated for me by
> > > PowerDesigner.  My workaround is to tweak the PowerDesigner output
> > > definition to not include this line.
> > >
> > > I have seen this behaviour in both 7.2 and 7.3.  Is it a bug?  Or
> > > am I misunderstanding something?
> >
> > Restrict is not the default, there is a difference between restrict and no
> > action. In fact I believe the main point of restrict (which IIRC was added
> > for sql99) is to allow you to have a deferred constraint that can do
> > immediate checking of validity on pk changes.
>
> I was basing my reasoning on the CREATE TABLE documentation which says:
>
>   NO ACTION
>
>     Produce an error indicating that the deletion or update would create
>     a foreign key constraint violation. This is the default action.
>
>   RESTRICT
>
>     Same as NO ACTION.
>
> So as you pointed out, RESTRICT is not the default, but according to the
> docs NO ACTION is the default and RESTRICT is the same as NO ACTION.
> Is the difference between the two documented anywhere?

Hmm, I don't think so actually.  I'm surprised that we hadn't had that
mistake pointed out before. The restrict entry should mention the
fact that it's non-deferring.

To -hackers: Is it still safe to send small documentation patches for 7.4
at this point?

Thu, 6 Nov 2003 15:41:57 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Deferrable triggers
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> To -hackers: Is it still safe to send small documentation patches for 7.4
> at this point?

Of course.  Docs patches are fair game up till release (although I think
Peter wants us to minimize edits to the reference pages, because
regenerating the man pages is a bit of a PITA).

			regards, tom lane

Thu, 06 Nov 2003 20:01:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Deferrable triggers 