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

Re: RULES doesn't work as expected

From: Richard Huxton <dev(at)archonet(dot)com>
To: John Hansen <john(at)geeknet(dot)com(dot)au>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: RULES doesn't work as expected
Date: 2005-01-12 11:22:14
Message-ID: 41E50866.1090007@archonet.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-patches
Removed cc to pgsql-patches since that's not the list for this.

John Hansen wrote:
> 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.

> 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

In response to

pgsql-bugs by date

Next:From: Magnus HaganderDate: 2005-01-12 11:42:41
Subject: Re: rc4, PostgreSQL-installer on WinXP: anybody can read, write and delete in data-dir
Previous:From: Christoph BeckerDate: 2005-01-12 10:22:49
Subject: rc4, restore of a db with psql freezes without warning if plpythonu is needed, but is not installed

pgsql-patches by date

Next:From: toczekDate: 2005-01-12 14:56:22
Subject: Updated version of Polish translation.
Previous:From: Reinhard MaxDate: 2005-01-12 10:38:55
Subject: Re: SUSE port (was [ANNOUNCE] PostgreSQL 8.0.0 Release

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