Re: 7.4 and 7.3.5 showstopper - Mailing list pgsql-sql

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 #