Re: [BUGS] RULES doesn't work as expected

#1

Removed cc to pgsql-patches since that's not the list for this.

John Hansen wrote:

Quote:

It seems rules don't work as expected.
I could be wrong,... In which case, what am I doing wrong?

A rule is like a macro, rewriting the query plan. You're trying to use
it as though it is a trigger. The side-effects of rules can be quite
subtle and catches most of us out at least once.

Quote:

Clearly, the first insert below should not update the table as well.

CREATE TABLE test (a text, b int4[]);

CREATE RULE test_rule AS
ON INSERT TO test
WHERE exists(SELECT 1 FROM test WHERE a = NEW.a)
DO INSTEAD
UPDATE test SET b = b + NEW.b WHERE a = NEW.a;

db1=# INSERT INTO test (a,b) VALUES (1,'{1}'::int4[]);

The NEW.a doesn't refer to a variable as such, it refers to the
updated/inserted value of an actual row in "test". Does that clarify?

In your particular usage you'd want to consider concurrency and locking
issues too.

Repost your question on the general/sql lists if you'd like some
discussion. It's probably worth checking the list archives too - plenty
in there about rule/trigger differences.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#2

John Hansen wrote:

Quote:

Right, except:

create table test (a text, b int); create or replace rule test_rule
as on insert to test where exists(select 1 from test where a = NEW.a)
do instead select * from test;

insert into test (a,b) VALUES ('first',2); a | b -------+--- first
| 2 (1 row)

select * from test; a | b -------+--- first | 2 (1 row)

Now, the select on the first insert should NOT have happened.....
Since this is a do instead rule. The insert should of course happen,
since it's not present in the table.

Or am I missing the point completely?

You are, but it's not your fault. You're still thinking of it as a
sequence of instructions, try thinking of it as a single expression that
gets evaluated.

To quote from the (v8.0) docs (ch 32 - The Rule System), for your case:
"Qualification given and INSTEAD
the query tree from the rule action with the rule qualification and
the original query tree's qualification; and the original query tree
with the negated rule qualification added"

So, in your case you get two branches:
1. INSERT ... WHERE NOT EXISTS (...)
2. SELECT * FROM TEST WHERE EXISTS (...)

Is this making sense?
--
Richard Huxton
Archonet Ltd

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] RULES doesn't work as expected

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] RULES doesn't work as expected - 01-12-2005 , 05:24 AM

Re: [BUGS] RULES doesn't work as expected - 01-12-2005 , 07:56 AM