Re: 7.4 and 7.3.5 showstopper

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(at)Yahoo(dot)com #