Postgres Pro
Downloads
Home   >   mailing lists

Thread: followup on view/rule/delete problem.

followup on view/rule/delete problem.

From
Pete Leonard
Date:
I wanted to thank Tom Lane and Andrew Snow for their help - definitely got
me going in the right direction.

Unfortunately, I'm still not quite there - I'm still experiencing DB
hangs, and was able to duplicate it with the following simplified schema.
Is this a 7.1-specific bug?  Is it breaking 7.1.2 as well?  Can anyone
offer a workaround?

Thanks everyone,

    --pete


Running on FreeBSD-3.4, Postgres 7.1

drop view foobar;
drop table foo;
drop table bar;
drop table log;
drop sequence foo_id_seq;

create sequence foo_id_seq;

create table foo (
id     integer default nextval('foo_id_seq') primary key,
name   varchar(20)
);


create table bar (
id     integer not null constraint foo_chk references foo on delete cascade,
name   varchar(20)
);


create table log (
id     integer,
action varchar(20)
);


create view foobar as
select f.id, f.name, b.name as barname from foo f, bar b
where f.id=b.id;


create rule delete_foobar as on delete to foobar
do instead (
insert into log (id, action) values (OLD.id, 'deleted');
delete from foo where id=OLD.id;
);

insert into foo (name) values ('aa');
insert into foo (name) values ('bb');
insert into bar (id, name) values (1, 'bar-aa');
insert into bar (id, name) values (2, 'bar-bb');


delete from foobar where id=2;

Re: followup on view/rule/delete problem.

From
Pete Leonard
Date:
As a followup -

No, I haven't applied the patch Tom posted yesterday to 7.1.2 - I'm trying
to get one of the admins here to take care of that - but I was curious as
to whether the patch fixed the example below.

    thanks,

    --pete


On Wed, 13 Jun 2001, Pete Leonard wrote:

>
> I wanted to thank Tom Lane and Andrew Snow for their help - definitely got
> me going in the right direction.
>
> Unfortunately, I'm still not quite there - I'm still experiencing DB
> hangs, and was able to duplicate it with the following simplified schema.
> Is this a 7.1-specific bug?  Is it breaking 7.1.2 as well?  Can anyone
> offer a workaround?
>
> Thanks everyone,
>
>     --pete
>
>
> Running on FreeBSD-3.4, Postgres 7.1
>
> drop view foobar;
> drop table foo;
> drop table bar;
> drop table log;
> drop sequence foo_id_seq;
>
> create sequence foo_id_seq;
>
> create table foo (
> id     integer default nextval('foo_id_seq') primary key,
> name   varchar(20)
> );
>
>
> create table bar (
> id     integer not null constraint foo_chk references foo on delete cascade,
> name   varchar(20)
> );
>
>
> create table log (
> id     integer,
> action varchar(20)
> );
>
>
> create view foobar as
> select f.id, f.name, b.name as barname from foo f, bar b
> where f.id=b.id;
>
>
> create rule delete_foobar as on delete to foobar
> do instead (
> insert into log (id, action) values (OLD.id, 'deleted');
> delete from foo where id=OLD.id;
> );
>
> insert into foo (name) values ('aa');
> insert into foo (name) values ('bb');
> insert into bar (id, name) values (1, 'bar-aa');
> insert into bar (id, name) values (2, 'bar-bb');
>
>
> delete from foobar where id=2;
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: followup on view/rule/delete problem.

From
Tom Lane
Date:
Pete Leonard <pete@hero.com> writes:
> Unfortunately, I'm still not quite there - I'm still experiencing DB
> hangs, and was able to duplicate it with the following simplified schema.
> Is this a 7.1-specific bug?  Is it breaking 7.1.2 as well?
> ...
> create rule delete_foobar as on delete to foobar
> do instead (
> insert into log (id, action) values (OLD.id, 'deleted');
> delete from foo where id=OLD.id;
> );

Almost anything involving a multi-action rule and references to views
is probably vulnerable to the bug I found yesterday :-(.  Please try
it with 7.1.2 plus the patch I posted.

            regards, tom lane

Re: followup on view/rule/delete problem.

From
Tom Lane
Date:
Pete Leonard <pete@hero.com> writes:
> No, I haven't applied the patch Tom posted yesterday to 7.1.2 - I'm trying
> to get one of the admins here to take care of that - but I was curious as
> to whether the patch fixed the example below.

It worked for me ...

            regards, tom lane

Re: followup on view/rule/delete problem.

From
"bugi"
Date:
I'v got the same problem with view/rule/delete in v. 7.1.2 (view/rule/insert
and update - work)
Could you tell me where is the patch ?

U�ytkownik "Tom Lane" <tgl@sss.pgh.pa.us> napisa� w wiadomo�ci
news:27340.992455816@sss.pgh.pa.us...
> Pete Leonard <pete@hero.com> writes:
> > Unfortunately, I'm still not quite there - I'm still experiencing DB
> > hangs, and was able to duplicate it with the following simplified
schema.
> > Is this a 7.1-specific bug?  Is it breaking 7.1.2 as well?
> > ...
> > create rule delete_foobar as on delete to foobar
> > do instead (
> > insert into log (id, action) values (OLD.id, 'deleted');
> > delete from foo where id=OLD.id;
> > );
>
> Almost anything involving a multi-action rule and references to views
> is probably vulnerable to the bug I found yesterday :-(.  Please try
> it with 7.1.2 plus the patch I posted.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly