Skip site navigation (1) Skip section navigation (2)

Re: 7.4 and 7.3.5 showstopper

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: Michele Bendazzoli <mickymouse(at)mickymouse(dot)it>,pgsql-sql <pgsql-sql(at)postgresql(dot)org>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 7.4 and 7.3.5 showstopper
Date: 2003-10-31 04:00:54
Message-ID: 3FA1DE76.3030707@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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(at)Yahoo(dot)com #


In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2003-10-31 04:13:53
Subject: Re: 7.4RC1 planned for Monday
Previous:From: Jan WieckDate: 2003-10-31 03:38:19
Subject: Re: CREATE TYPE for case insensitive text and varchar

pgsql-sql by date

Next:From: Michele BendazzoliDate: 2003-10-31 08:49:42
Subject: Re: 7.4 and 7.3.5 showstopper (was: Re: Bug in Rule+Foreing
Previous:From: KumarDate: 2003-10-31 03:46:51
Subject: Re: Using UNION inside a cursor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group