[SQL] Using UNION inside a cursor

[Kumar]

Dear Friends,
 
I am working on Postgresql 7.3.4 on RH Linux Server 7.3. 

 
I have problem in executing the following 
procedure
 
CREATE OR REPLACE FUNCTION list_history()  
RETURNS refcursor 
AS'DECLARE ref   REFCURSOR;BEGIN OPEN 
ref FOR (SELECT  * FROM history  WHERE obs_type = 
\'AA\' ) UNION  (SELECT  * 
FROM  history  WHERE   obs_type = 
\'TA\');
 
 RETURN ref;END;'  LANGUAGE 'plpgsql' 
VOLATILE;
 
While executing this I got the following error
 
WARNING:  plpgsql: ERROR during compile of 
list_history near line 5
ERROR:  syntax error at "("
 
While I execute the following code it is working fine and 
fetches values.
 (SELECT  * FROM history  WHERE 
obs_type = \'AA\' ) UNION  (SELECT  * 
FROM  history  WHERE   obs_type = 
\'TA\');
 
Where I am wrong. Please shed some light,
 
Regards
Kumar

Re: [SQL] Using UNION inside a cursor

[Christoph Haller]

> 
> Dear Friends,
> 
> I am working on Postgresql 7.3.4 on RH Linux Server 7.3.=20
> 
> I have problem in executing the following procedure
> 
> CREATE OR REPLACE FUNCTION list_history()
>   RETURNS refcursor AS
> 'DECLARE
>  ref   REFCURSOR;
> BEGIN
>  OPEN ref FOR
>  (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
>  UNION=20
>  (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\');
> 
>  RETURN ref;
> END;'
>   LANGUAGE 'plpgsql' VOLATILE;
> 
> While executing this I got the following error
> 
> WARNING:  plpgsql: ERROR during compile of list_history near line 5
> ERROR:  syntax error at "("
> 
> While I execute the following code it is working fine and fetches values.
>  (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
>  UNION=20
>  (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\');
> 
> Where I am wrong. Please shed some light,
> 
> Regards
> Kumar
> 
My suspicion is the plpgsql parser doesn't accept the opening 
parenthesis. What happens on 

  OPEN ref FOR SELECT * FROM (
  (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
  UNION=20
  (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\')
  ) ;

Regards, Christoph 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Using UNION inside a cursor

[Kumar]

Hi Christoph Haller,

Thanks you very much. It worked.

- Original Message - 
From: "Christoph Haller" <[EMAIL PROTECTED]>
To: ""Kumar"" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, October 30, 2003 3:56 PM
Subject: Re: [SQL] Using UNION inside a cursor


> >
> > Dear Friends,
> >
> > I am working on Postgresql 7.3.4 on RH Linux Server 7.3.=20
> >
> > I have problem in executing the following procedure
> >
> > CREATE OR REPLACE FUNCTION list_history()
> >   RETURNS refcursor AS
> > 'DECLARE
> >  ref   REFCURSOR;
> > BEGIN
> >  OPEN ref FOR
> >  (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
> >  UNION=20
> >  (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\');
> >
> >  RETURN ref;
> > END;'
> >   LANGUAGE 'plpgsql' VOLATILE;
> >
> > While executing this I got the following error
> >
> > WARNING:  plpgsql: ERROR during compile of list_history near line 5
> > ERROR:  syntax error at "("
> >
> > While I execute the following code it is working fine and fetches
values.
> >  (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
> >  UNION=20
> >  (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\');
> >
> > Where I am wrong. Please shed some light,
> >
> > Regards
> > Kumar
> >
> My suspicion is the plpgsql parser doesn't accept the opening
> parenthesis. What happens on
>
>   OPEN ref FOR SELECT * FROM (
>   (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
>   UNION=20
>   (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\')
>   ) ;
>
> Regards, Christoph


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Bug in Rule+Foreing key constrain?

[Michele Bendazzoli]

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
abilitate again the check of the contraints?

Thank you in advance for any advice.

ciao, Michele


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] strange postgresql failure

[Tomasz Myrta]

 postgres[666]: [1] LOG:  server process (pid 3166) was terminated by 
signal 11
 postgres[666]: [2] LOG:  terminating any other active server processes
 postgres[806]: [591-1] WARNING:  Message from PostgreSQL backend:
 postgres[806]: [591-2] ^IThe Postmaster has informed me that some 
other backend
 billy postgres[806]: [591-3] ^Idied abnormally and possibly corrupted 
shared memory.
 billy postgres[806]: [591-4] ^II have rolled back the current 
transaction and am
 billy postgres[806]: [591-5] ^Igoing to terminate your database system 
connection and exit.
 billy postgres[806]: [591-6] ^IPlease reconnect to the database system 
and repeat your query.

After few seconds all backends were disconnected and postgres restarted. 
It didn't help :-( Even reboot didn't help...

Postgres is  7.3.2-2 on Debian Woody.

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] strange postgresql failure

[Tomasz Myrta]

Dnia 2003-10-30 12:32, Użytkownik Tomasz Myrta napisał:
After few seconds all backends were disconnected and postgres restarted. 
It didn't help :-( Even reboot didn't help...

Postgres is  7.3.2-2 on Debian Woody.
Sorry for my panic. It was my C function which raised segmentation fault.

Anyway it would be nice to have some human readable control over C 
functions...

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[SQL] varlena versus null-terminated strings

[sad]

Hi Developers.

I think the problem is the input/output functions pass 
CSTRING -- not varlena.

this waste all profits of using varlena storage
cause values are not transparently passed. 
then application forced to convert them reverse.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] psqlODBC with SSL

[2000info]

Hello, peopleware !!!
 
psqlODBC with SSL, it´s possible ?
 
 
 
SPS
2000Info
 

Re: [SQL] Bug in Rule+Foreing key constrain?

[Stephan Szabo]

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?

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Using UNION inside a cursor

[Tom Lane]

Christoph Haller <[EMAIL PROTECTED]> writes:
>> OPEN ref FOR
>> (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
>> UNION
>> (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\');

> My suspicion is the plpgsql parser doesn't accept the opening 
> parenthesis.

Yeah.  Looking at the plpgsql grammar, it expects the token after FOR to
be either SELECT or EXECUTE.  This example demonstrates that that code
is wrong, so I'll fix it for 7.4.  But in the meantime, why don't you
just leave out the parentheses?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] strange postgresql failure

[Jan Wieck]

Tomasz Myrta wrote:

Dnia 2003-10-30 12:32, Użytkownik Tomasz Myrta napisał:
After few seconds all backends were disconnected and postgres restarted. 
It didn't help :-( Even reboot didn't help...

Postgres is  7.3.2-2 on Debian Woody.
Sorry for my panic. It was my C function which raised segmentation fault.

Anyway it would be nice to have some human readable control over C 
functions...
There is, sort of ... it's called "core" and if your backend and C 
function shared object have been compiled with symbol information you 
can use a debugger to get a stack trace and look at variables. If that's 
not "human readable" ... well ... I suppose I'm not human.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Bug in Rule+Foreing key constrain?

[Jan Wieck]

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.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Bug in Rule+Foreing key constrain?

[Michele Bendazzoli]

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



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] strange postgresql failure

[Tomasz Myrta]

Dnia 2003-10-30 18:33, Użytkownik Jan Wieck napisał:
There is, sort of ... it's called "core" and if your backend and C 
function shared object have been compiled with symbol information you 
can use a debugger to get a stack trace and look at variables. If that's 
not "human readable" ... well ... I suppose I'm not human.
I know I can load core into gdb...
I thought about some kind of message before postgres restarts. I read in 
syslog that backend terminated with segmentation fault signal and 
probably corrupted shared memory. It didn't help me to find what really 
happened. I was wondering if it is possible to know the real reason of 
restart and leave some message in syslog just before postgres restarts.

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Using UNION inside a cursor

[Kumar]

Hi Tom,
I need the parentheses, because the where condition is different for the 1
and 2 nd query . Moreover my actual use includes a 'Limit' clause also. so
the parentheses is needed.

I have tried with execute but it showed error.

But I have managed to fix the problem like this.
OPEN ref FOR SELECT * FROM
(SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
UNION
(SELECT  * FROM  history  WHERE   obs_type =3D \'TA\') as foo;

Regards
Kumar

- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Christoph Haller" <[EMAIL PROTECTED]>
Cc: ""Kumar"" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, October 30, 2003 8:50 PM
Subject: Re: [SQL] Using UNION inside a cursor


> Christoph Haller <[EMAIL PROTECTED]> writes:
> >> OPEN ref FOR
> >> (SELECT  * FROM history  WHERE obs_type =3D \'AA\' )
> >> UNION
> >> (SELECT  * FROM  history  WHERE   obs_type =3D \'TA\');
>
> > My suspicion is the plpgsql parser doesn't accept the opening
> > parenthesis.
>
> Yeah.  Looking at the plpgsql grammar, it expects the token after FOR to
> be either SELECT or EXECUTE.  This example demonstrates that that code
> is wrong, so I'll fix it for 7.4.  But in the meantime, why don't you
> just leave out the parentheses?
>
> regards, tom lane


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] 7.4 and 7.3.5 showstopper

[Jan Wieck]

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)

7.4 and 7.3.5 showstopper (was: Re: [SQL] Bug in Rule+Foreing key constrain?)

[Jan Wieck]

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.  #
#== [EMAIL PROTECTED] #
#!/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