Rules with "Where" Referencing Other Tables

Started by Ian Hardingabout 23 years ago2 messagesgeneral
Jump to latest
#1Ian Harding
ianh@tpchd.org

I got away with this under 7.2.3. I didn't read the docs. However, under 7.3.1, it blew up the backend. Since there is obviously a good reason for this prohibition, why does the system allow creation of rules that reference other tables in the where clause?

Here is a very simple case. It doesn't blow anything up. My more complex case did, when I did repeated inserts within a transaction. I don't want to spend a lot of time running it to ground since I should have read the docs and not even tried it!

create table test (
testid int,
testdata varchar);

create table test2 (
test2id int,
test2data varchar);

insert into test2 values (1, 'blah');

create or replace view testview as
select * From test;

create or replace rule testrule as on insert to test where
(select test2data from test2 where test2id = 1) = 'blah'
do instead nothing;

insert into test values (2, 'foobar');

update test2 set test2data = 'asdkljf';

insert into test values (2, 'foobar');

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549

"If any man can convince me and bring home to me that I do not think or act aright, gladly will I change; for I search after truth, by which man never yet was harmed. But he is harmed who abideth on still in his deception and ignorance"

-- Marcus Aurelius

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ian Harding (#1)
Re: Rules with "Where" Referencing Other Tables

"Ian Harding" <ianh@tpchd.org> writes:

I got away with this under 7.2.3. I didn't read the docs. However, under 7.3.1, it blew up the backend. Since there is obviously a good reason for this prohibition, why does the system allow creation of rules that reference other tables in the where clause?
Here is a very simple case. It doesn't blow anything up.

Would you show us a case where it *does* blow up?

I'd prefer not to forbid subselects entirely in rule WHERE clauses,
but if we have to ...

regards, tom lane