Re: Fix disabled triggers with deferred constraints

On 7 Aug 2002, Neil Conway wrote:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> > > Elliot Lee <sopwith(at)redhat(dot)com> writes:
> > > I remember looking at this issue and not doing anything because I
> > > couldn't decide whether the test for enabled status should occur when
> > > the trigger is queued or when it is executed --- or, perhaps, both?
> > > Is there anything in the standard about it?
>
> [...]
>
> > I think we're still waiting for someone to figure out what the behavior
> > should be per spec.
>
> I took a brief look at SQL99, but I couldn't find anything regarding
> this issue (AFAICS it doesn't mention "disabled triggers" at all). But
> given my prior track record for divining information from the
> standards, perhaps someone should double-check :-)
>
> I did notice some behavior which we don't implement AFAIK:
>
> If the constraint mode is /deferred/, then the constraint is
> effectively checked when the constraint mode is changed to
> /immediate/ either explicitely by execution of a <set
> constraints mode statement>, or implicitely at the end of the
> current SQL-transaction.
>
> (SQL99, Section 4.17.1, paragraph 3)
>
> We don't recheck any outstanding deferred constraints when the
> constraint mode is explicitly switched back to IMMEDIATE, as the
> standard says we should.

(have been out since last wed, responding now)
Seems to work for me in some cases if I understand correctly.

sszabo=# create table iia(a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'iia_pkey'
for table 'iia'
CREATE TABLE
sszabo=# create table iib(a int constraint foo1 references iia(a)
deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE
sszabo=# begin;
BEGIN
sszabo=# insert into iib values (1);
INSERT 9678819 1
sszabo=# end;
ERROR: foo1 referential integrity violation - key referenced from iib not
found in iia
sszabo=# begin;
BEGIN
sszabo=# insert into iib values (1);
INSERT 9678820 1
sszabo=# set constraints all immediate;
ERROR: foo1 referential integrity violation - key referenced from iib not
found in iia