Thread: Bug in Rule+Foreing key constrain?
I have found a strange behaviour that I don't know if is a bug or not.
I have three tables:
* abilitazione with a primary key of (comuneid, cassonettoid, chiaveid)
* cassonetto with a primary key of (comuneid, cassonettoid)
* chiave with a primary key of (comuneid, chiaveid)
and two foreign key from abilitazioni to cassonetto and chiave tables
respectivly. Here is the picture (I hope it will be clear)
cassonetto abilitazioni chiave|comuneid |--> |comuneid |<---|comuneid|
PK -> |cassonettoid|--> |cassonettoid| | | |chiaveid |<---|chiaveid| |abilitata
|
Whenever I try to insert a row in abilitazione that hasn't a match row
in cassonetto or abilitazione, an exception is raised (referential
integrity violation : key referenced from abilitazione not found in
cassonetto).
So far, so good.
Now if I Add a rule to abilitazione in order to avoid a duplication of a
row when a user try to insert one with an existing primary key:
CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione WHERE (EXISTS ( SELECT 1
FROMabilitazione WHERE (((abilitazione.comuneid = new.comuneid ) AND (abilitazione.cassonettoid =
new.cassonettoid)) AND (abilitazione.chiaveid = new.chiaveid )))) DO INSTEAD UPDATE abilitazione SET abilitata =
new.abilitata WHERE (((abilitazione.comuneid = new.comuneid ) AND (abilitazione.cassonettoid = new.cassonettoid
)) AND (abilitazione.chiaveid = new.chiaveid ));
the constraints related to the foreign kesy disappears (i.e. I'm able to
insert a row in abilitazioni that hasn't a key referenced in cassonetto
or abilitazioni).
Is this a bug or a feauture? If is a feature, it is possible to
abilitate again the check of the contraints?
Thank you in advance for any advice.
ciao, Michele
On Thu, 30 Oct 2003, Michele Bendazzoli wrote: > I have found a strange behaviour that I don't know if is a bug or not. > > I have three tables: > * abilitazione with a primary key of (comuneid, cassonettoid, chiaveid) > * cassonetto with a primary key of (comuneid, cassonettoid) > * chiave with a primary key of (comuneid, chiaveid) > > and two foreign key from abilitazioni to cassonetto and chiave tables > respectivly. Here is the picture (I hope it will be clear) > > cassonetto abilitazioni chiave > |comuneid |--> |comuneid |<---|comuneid| > PK -> |cassonettoid|--> |cassonettoid| | | > |chiaveid |<---|chiaveid| > |abilitata | > > Whenever I try to insert a row in abilitazione that hasn't a match row > in cassonetto or abilitazione, an exception is raised (referential > integrity violation : key referenced from abilitazione not found in > cassonetto). > > So far, so good. > > Now if I Add a rule to abilitazione in order to avoid a duplication of a > row when a user try to insert one with an existing primary key: > > CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione > WHERE (EXISTS ( > SELECT 1 > FROM abilitazione > WHERE (((abilitazione.comuneid = new.comuneid ) > AND (abilitazione.cassonettoid = new.cassonettoid )) > AND (abilitazione.chiaveid = new.chiaveid )))) > DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata > WHERE (((abilitazione.comuneid = new.comuneid ) > AND (abilitazione.cassonettoid = new.cassonettoid )) > AND (abilitazione.chiaveid = new.chiaveid )); > > the constraints related to the foreign kesy disappears (i.e. I'm able to > insert a row in abilitazioni that hasn't a key referenced in cassonetto > or abilitazioni). > > Is this a bug or a feauture? If is a feature, it is possible to Hmm, I'd say a bug, but can you send a standalone example that replicates it?
Michele Bendazzoli wrote: > I have found a strange behaviour that I don't know if is a bug or not. > > I have three tables: > * abilitazione with a primary key of (comuneid, cassonettoid, chiaveid) > * cassonetto with a primary key of (comuneid, cassonettoid) > * chiave with a primary key of (comuneid, chiaveid) > > and two foreign key from abilitazioni to cassonetto and chiave tables > respectivly. Here is the picture (I hope it will be clear) > > cassonetto abilitazioni chiave > |comuneid |--> |comuneid |<---|comuneid| > PK -> |cassonettoid|--> |cassonettoid| | | > |chiaveid |<---|chiaveid| > |abilitata | Not entirely. On which table(s) are the REFERENCES constraints and are they separate per column constraints or are they multi-column constraints? It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE statements that are used to create the constraints. That way we know exactly what you're talking about. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Thu, 2003-10-30 at 18:29, Jan Wieck wrote: > Not entirely. On which table(s) are the REFERENCES constraints and are > they separate per column constraints or are they multi-column constraints? here are the constraints of the abilitazione table ALTER TABLE public.abilitazione ADD CONSTRAINT abilitazione_pkey PRIMARY KEY(comuneid, cassonettoid, chiaveid); ALTER TABLE public.abilitazione ADD CONSTRAINT abilitazione_cassonettoid_fkey FOREIGN KEY (comuneid, cassonettoid) REFERENCES public.cassonetto (comuneid, cassonettoid) ON UPDATE RESTRICT ON DELETE RESTRICT; ALTER TABLE public.abilitazione ADD CONSTRAINT abilitazione_chiaveid_fkey FOREIGN KEY (comuneid, chiaveid) REFERENCES public.chiave (comuneid, chiaveid) ON UPDATE RESTRICT ON DELETE RESTRICT; here those of cassonetto and chiave: ALTER TABLE public.cassonetto ADD CONSTRAINT cassonetto_pkey PRIMARY KEY(comuneid, cassonettoid); ALTER TABLE public.chiave ADD CONSTRAINT chiave_pkey PRIMARY KEY(comuneid, chiaveid); I get the SQL from pgAdmin3 (great piece of sofware!;-) > It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE > statements that are used to create the constraints. That way we know > exactly what you're talking about. Excuse me for the missing SQL, but i had tried to keep the message as simple as possible. The unique difference form when the exception was raised and now (that it isn't) is the rule added: CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione WHERE (EXISTS ( SELECT 1 FROM abilitazione WHERE (((abilitazione.comuneid = new.comuneid ) AND (abilitazione.cassonettoid = new.cassonettoid)) AND (abilitazione.chiaveid = new.chiaveid ))))ù DO INSTEAD UPDATE abilitazione SET abilitata =new.abilitata WHERE (((abilitazione.comuneid = new.comuneid ) AND (abilitazione.cassonettoid = new.cassonettoid ))AND (abilitazione.chiaveid = new.chiaveid )); I hope now is more clear. The version is that come with debian unstable (7.3.4 if I remember correctly) Thank you for the immediate responses ciao, Michele
Confirmed, that's a bug - pgsql-hackers CC'd and scipt for full
reproduction attached.
This can also be reproduced in 7.4-beta5.
My guess out of the blue would be, that the rewriter expands the insert
into one insert with the where clause, one update with the negated where
clause. Executed in that order, they are both true ... first there is no
such row, the insert happens, second the row exists and is being updated.
IIRC the refint trigger queue run at the end of the whole statement
tries to heap_fetch() the originally inserted tuple, which is invisible
by that time. I seem to remember that the original version did fetch
them with some snapshot override mode to get it anyway and fire the
trigger. That apparently does not happen any more, so now the duty would
be up to the on update refint trigger which ... er ... recently got
fixed not to check non-changed key references any more ... duh.
I will look a bit deeper into it later tonight. I think if we let the on
update refint trigger check the referenced key again if the old tuple
has xmin = current_xid we should be fine.
Thanks for reporting, Michele. In the meantime, you might want to use a
BEFORE INSERT trigger in PL/pgSQL that tries to UPDATE the row and if
GET DIAGNOSTICS tells it it succeeded, returns NULL to suppress the
INSERT. That should work around the bug for the time being.
Jan
Michele Bendazzoli wrote:
> On Thu, 2003-10-30 at 18:29, Jan Wieck wrote:
>
>> Not entirely. On which table(s) are the REFERENCES constraints and are
>> they separate per column constraints or are they multi-column constraints?
>
> here are the constraints of the abilitazione table
>
> ALTER TABLE public.abilitazione
> ADD CONSTRAINT abilitazione_pkey PRIMARY KEY(comuneid, cassonettoid,
> chiaveid);
>
> ALTER TABLE public.abilitazione
> ADD CONSTRAINT abilitazione_cassonettoid_fkey FOREIGN KEY (comuneid,
> cassonettoid) REFERENCES public.cassonetto (comuneid, cassonettoid) ON
> UPDATE RESTRICT ON DELETE RESTRICT;
>
> ALTER TABLE public.abilitazione
> ADD CONSTRAINT abilitazione_chiaveid_fkey FOREIGN KEY (comuneid,
> chiaveid) REFERENCES public.chiave (comuneid, chiaveid) ON UPDATE
> RESTRICT ON DELETE RESTRICT;
>
> here those of cassonetto and chiave:
>
> ALTER TABLE public.cassonetto
> ADD CONSTRAINT cassonetto_pkey PRIMARY KEY(comuneid, cassonettoid);
>
> ALTER TABLE public.chiave
> ADD CONSTRAINT chiave_pkey PRIMARY KEY(comuneid, chiaveid);
>
> I get the SQL from pgAdmin3 (great piece of sofware!;-)
>
>> It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE
>> statements that are used to create the constraints. That way we know
>> exactly what you're talking about.
>
> Excuse me for the missing SQL, but i had tried to keep the message as
> simple as possible.
>
> The unique difference form when the exception was raised and now (that
> it isn't) is the rule added:
>
> CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione
> WHERE (EXISTS (
> SELECT 1 FROM abilitazione
> WHERE (((abilitazione.comuneid = new.comuneid )
> AND (abilitazione.cassonettoid = new.cassonettoid ))
> AND (abilitazione.chiaveid = new.chiaveid ))))ù
> DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata
> WHERE (((abilitazione.comuneid = new.comuneid )
> AND (abilitazione.cassonettoid = new.cassonettoid ))
> AND (abilitazione.chiaveid = new.chiaveid ));
>
> I hope now is more clear.
>
> The version is that come with debian unstable (7.3.4 if I remember
> correctly)
>
> Thank you for the immediate responses
>
> ciao, Michele
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
#!/bin/sh
DBNAME=testdb
export DBNAME
dropdb ${DBNAME}
createdb ${DBNAME}
psql -e ${DBNAME} <<_EOF_
create table t1 (
id1a integer,
id1b integer,
primary key (id1a, id1b)
);
create table t2 (
id2a integer,
id2c integer,
primary key (id2a, id2c)
);
create table t3 (
id3a integer,
id3b integer,
id3c integer,
data text,
primary key (id3a, id3b, id3c),
foreign key (id3a, id3b) references t1 (id1a, id1b),
foreign key (id3a, id3c) references t2 (id2a, id2c)
);
insert into t1 values (1, 11);
insert into t1 values (1, 12);
insert into t1 values (2, 21);
insert into t1 values (2, 22);
insert into t2 values (1, 11);
insert into t2 values (1, 12);
insert into t2 values (2, 21);
insert into t2 values (2, 22);
insert into t3 values (1, 11, 11, 'row1');
insert into t3 values (1, 11, 12, 'row2');
insert into t3 values (1, 12, 11, 'row3');
insert into t3 values (1, 12, 12, 'row4');
insert into t3 values (1, 11, 13, 'row5');
insert into t3 values (1, 13, 11, 'row6');
create rule t3_ins as on insert to t3
where (exists (select 1 from t3
where (((t3.id3a = new.id3a)
and (t3.id3b = new.id3b))
and (t3.id3c = new.id3c))))
do instead update t3 set data = new.data
where (((t3.id3a = new.id3a)
and (t3.id3b = new.id3b))
and (t3.id3c = new.id3c));
insert into t3 values (1, 11, 13, 'row7');
insert into t3 values (1, 13, 11, 'row8');
select * from t3;
select version();
_EOF_
Jan Wieck wrote:
> Confirmed, that's a bug - pgsql-hackers CC'd and scipt for full
> reproduction attached.
Assumptions where correct, bug fixed in REL7_3_STABLE and HEAD. I also
added a slightly modified version of the script that reproduced the bug
to the foreign_key regression test.
Jan
>
> This can also be reproduced in 7.4-beta5.
>
> My guess out of the blue would be, that the rewriter expands the insert
> into one insert with the where clause, one update with the negated where
> clause. Executed in that order, they are both true ... first there is no
> such row, the insert happens, second the row exists and is being updated.
>
> IIRC the refint trigger queue run at the end of the whole statement
> tries to heap_fetch() the originally inserted tuple, which is invisible
> by that time. I seem to remember that the original version did fetch
> them with some snapshot override mode to get it anyway and fire the
> trigger. That apparently does not happen any more, so now the duty would
> be up to the on update refint trigger which ... er ... recently got
> fixed not to check non-changed key references any more ... duh.
>
> I will look a bit deeper into it later tonight. I think if we let the on
> update refint trigger check the referenced key again if the old tuple
> has xmin = current_xid we should be fine.
>
>
>
> Thanks for reporting, Michele. In the meantime, you might want to use a
> BEFORE INSERT trigger in PL/pgSQL that tries to UPDATE the row and if
> GET DIAGNOSTICS tells it it succeeded, returns NULL to suppress the
> INSERT. That should work around the bug for the time being.
>
>
> Jan
>
>
> Michele Bendazzoli wrote:
>
>> On Thu, 2003-10-30 at 18:29, Jan Wieck wrote:
>>
>>> Not entirely. On which table(s) are the REFERENCES constraints and are
>>> they separate per column constraints or are they multi-column constraints?
>>
>> here are the constraints of the abilitazione table
>>
>> ALTER TABLE public.abilitazione
>> ADD CONSTRAINT abilitazione_pkey PRIMARY KEY(comuneid, cassonettoid,
>> chiaveid);
>>
>> ALTER TABLE public.abilitazione
>> ADD CONSTRAINT abilitazione_cassonettoid_fkey FOREIGN KEY (comuneid,
>> cassonettoid) REFERENCES public.cassonetto (comuneid, cassonettoid) ON
>> UPDATE RESTRICT ON DELETE RESTRICT;
>>
>> ALTER TABLE public.abilitazione
>> ADD CONSTRAINT abilitazione_chiaveid_fkey FOREIGN KEY (comuneid,
>> chiaveid) REFERENCES public.chiave (comuneid, chiaveid) ON UPDATE
>> RESTRICT ON DELETE RESTRICT;
>>
>> here those of cassonetto and chiave:
>>
>> ALTER TABLE public.cassonetto
>> ADD CONSTRAINT cassonetto_pkey PRIMARY KEY(comuneid, cassonettoid);
>>
>> ALTER TABLE public.chiave
>> ADD CONSTRAINT chiave_pkey PRIMARY KEY(comuneid, chiaveid);
>>
>> I get the SQL from pgAdmin3 (great piece of sofware!;-)
>>
>>> It's usually best to cut'n'paste the CREATE TABLE or ALTER TABLE
>>> statements that are used to create the constraints. That way we know
>>> exactly what you're talking about.
>>
>> Excuse me for the missing SQL, but i had tried to keep the message as
>> simple as possible.
>>
>> The unique difference form when the exception was raised and now (that
>> it isn't) is the rule added:
>>
>> CREATE OR REPLACE RULE abilita_ins_rl AS ON INSERT TO abilitazione
>> WHERE (EXISTS (
>> SELECT 1 FROM abilitazione
>> WHERE (((abilitazione.comuneid = new.comuneid )
>> AND (abilitazione.cassonettoid = new.cassonettoid ))
>> AND (abilitazione.chiaveid = new.chiaveid ))))ù
>> DO INSTEAD UPDATE abilitazione SET abilitata = new.abilitata
>> WHERE (((abilitazione.comuneid = new.comuneid )
>> AND (abilitazione.cassonettoid = new.cassonettoid ))
>> AND (abilitazione.chiaveid = new.chiaveid ));
>>
>> I hope now is more clear.
>>
>> The version is that come with debian unstable (7.3.4 if I remember
>> correctly)
>>
>> Thank you for the immediate responses
>>
>> ciao, Michele
>
>
>
>
> ------------------------------------------------------------------------
>
> #!/bin/sh
>
> DBNAME=testdb
> export DBNAME
>
> dropdb ${DBNAME}
> createdb ${DBNAME}
>
> psql -e ${DBNAME} <<_EOF_
>
> create table t1 (
> id1a integer,
> id1b integer,
>
> primary key (id1a, id1b)
> );
>
> create table t2 (
> id2a integer,
> id2c integer,
>
> primary key (id2a, id2c)
> );
>
> create table t3 (
> id3a integer,
> id3b integer,
> id3c integer,
> data text,
>
> primary key (id3a, id3b, id3c),
>
> foreign key (id3a, id3b) references t1 (id1a, id1b),
> foreign key (id3a, id3c) references t2 (id2a, id2c)
> );
>
>
> insert into t1 values (1, 11);
> insert into t1 values (1, 12);
> insert into t1 values (2, 21);
> insert into t1 values (2, 22);
>
> insert into t2 values (1, 11);
> insert into t2 values (1, 12);
> insert into t2 values (2, 21);
> insert into t2 values (2, 22);
>
> insert into t3 values (1, 11, 11, 'row1');
> insert into t3 values (1, 11, 12, 'row2');
> insert into t3 values (1, 12, 11, 'row3');
> insert into t3 values (1, 12, 12, 'row4');
> insert into t3 values (1, 11, 13, 'row5');
> insert into t3 values (1, 13, 11, 'row6');
>
> create rule t3_ins as on insert to t3
> where (exists (select 1 from t3
> where (((t3.id3a = new.id3a)
> and (t3.id3b = new.id3b))
> and (t3.id3c = new.id3c))))
> do instead update t3 set data = new.data
> where (((t3.id3a = new.id3a)
> and (t3.id3b = new.id3b))
> and (t3.id3c = new.id3c));
>
> insert into t3 values (1, 11, 13, 'row7');
> insert into t3 values (1, 13, 11, 'row8');
>
> select * from t3;
>
> select version();
> _EOF_
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Thu, 2003-10-30 at 20:13, Jan Wieck wrote: > Thanks for reporting, Michele. Thank to you! The speed and level of your responses exceeds every my more rose-colored expectation ;-) > In the meantime, you might want to use a > BEFORE INSERT trigger in PL/pgSQL that tries to UPDATE the row and if > GET DIAGNOSTICS tells it it succeeded, returns NULL to suppress the > INSERT. That should work around the bug for the time being. It is not a problem for me, the db is not still in production, so I think i wait for the patch. Thank to all you again. ciao, Michele