Thread: RULES doesn't work as expected

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

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

... John


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[]);
INSERT 0 1
db1=# SELECT * FROM test;
 a |   b
---+-------
 1 | {1,1}
(1 row)

db1=# INSERT INTO test (a,b) VALUES (1,'{1}'::int4[]);
INSERT 0 0
db1=# SELECT * FROM test;
 a |    b
---+---------
 1 | {1,1,1}
(1 row)

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

UmlnaHQsIGV4Y2VwdDoNCg0KY3JlYXRlIHRhYmxlIHRlc3QgKGEgdGV4dCwg
YiBpbnQpOyANCmNyZWF0ZSBvciByZXBsYWNlIHJ1bGUgdGVzdF9ydWxlIGFz
IG9uIGluc2VydCB0byB0ZXN0IHdoZXJlIGV4aXN0cyhzZWxlY3QgMSBmcm9t
IHRlc3Qgd2hlcmUgYSA9IE5FVy5hKSBkbyBpbnN0ZWFkIHNlbGVjdCAqIGZy
b20gdGVzdDsgDQoNCmluc2VydCBpbnRvIHRlc3QgKGEsYikgVkFMVUVTICgn
Zmlyc3QnLDIpOw0KICAgYSAgIHwgYg0KLS0tLS0tLSstLS0NCiBmaXJzdCB8
IDINCigxIHJvdykNCg0Kc2VsZWN0ICogZnJvbSB0ZXN0Ow0KICAgYSAgIHwg
Yg0KLS0tLS0tLSstLS0NCiBmaXJzdCB8IDINCigxIHJvdykNCg0KTm93LCB0
aGUgc2VsZWN0IG9uIHRoZSBmaXJzdCBpbnNlcnQgc2hvdWxkIE5PVCBoYXZl
IGhhcHBlbmVkLi4uLi4gU2luY2UgdGhpcyBpcyBhIGRvIGluc3RlYWQgcnVs
ZS4NClRoZSBpbnNlcnQgc2hvdWxkIG9mIGNvdXJzZSBoYXBwZW4sIHNpbmNl
IGl0J3Mgbm90IHByZXNlbnQgaW4gdGhlIHRhYmxlLg0KDQpPciBhbSBJIG1p
c3NpbmcgdGhlIHBvaW50IGNvbXBsZXRlbHk/DQoNCi4uLiBKb2huDQoNCj4g
LS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCj4gRnJvbTogUmljaGFyZCBI
dXh0b24gW21haWx0bzpkZXZAYXJjaG9uZXQuY29tXSANCj4gU2VudDogV2Vk
bmVzZGF5LCBKYW51YXJ5IDEyLCAyMDA1IDEwOjIyIFBNDQo+IFRvOiBKb2hu
IEhhbnNlbg0KPiBDYzogcGdzcWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZw0KPiBT
dWJqZWN0OiBSZTogW0JVR1NdIFJVTEVTIGRvZXNuJ3Qgd29yayBhcyBleHBl
Y3RlZA0KPiANCj4gUmVtb3ZlZCBjYyB0byBwZ3NxbC1wYXRjaGVzIHNpbmNl
IHRoYXQncyBub3QgdGhlIGxpc3QgZm9yIHRoaXMuDQo+IA0KPiBKb2huIEhh
bnNlbiB3cm90ZToNCj4gPiBJdCBzZWVtcyBydWxlcyBkb24ndCB3b3JrIGFz
IGV4cGVjdGVkLg0KPiA+IEkgY291bGQgYmUgd3JvbmcsLi4uIEluIHdoaWNo
IGNhc2UsIHdoYXQgYW0gSSBkb2luZyB3cm9uZz8NCj4gDQo+IEEgcnVsZSBp
cyBsaWtlIGEgbWFjcm8sIHJld3JpdGluZyB0aGUgcXVlcnkgcGxhbi4gWW91
J3JlIA0KPiB0cnlpbmcgdG8gdXNlIGl0IGFzIHRob3VnaCBpdCBpcyBhIHRy
aWdnZXIuIFRoZSBzaWRlLWVmZmVjdHMgDQo+IG9mIHJ1bGVzIGNhbiBiZSBx
dWl0ZSBzdWJ0bGUgYW5kIGNhdGNoZXMgbW9zdCBvZiB1cyBvdXQgYXQgbGVh
c3Qgb25jZS4NCj4gDQo+ID4gQ2xlYXJseSwgdGhlIGZpcnN0IGluc2VydCBi
ZWxvdyBzaG91bGQgbm90IHVwZGF0ZSB0aGUgdGFibGUgYXMgd2VsbC4NCj4g
DQo+ID4gQ1JFQVRFIFRBQkxFIHRlc3QgKGEgdGV4dCwgYiBpbnQ0W10pOw0K
PiA+IA0KPiA+IENSRUFURSBSVUxFIHRlc3RfcnVsZSBBUyANCj4gPiAgIE9O
IElOU0VSVCBUTyB0ZXN0IA0KPiA+ICAgV0hFUkUgZXhpc3RzKFNFTEVDVCAx
IEZST00gdGVzdCBXSEVSRSBhID0gTkVXLmEpDQo+ID4gICBETyBJTlNURUFE
DQo+ID4gICAgIFVQREFURSB0ZXN0IFNFVCBiID0gYiArIE5FVy5iIFdIRVJF
IGEgPSBORVcuYTsNCj4gPiANCj4gPiANCj4gPiBkYjE9IyBJTlNFUlQgSU5U
TyB0ZXN0IChhLGIpIFZBTFVFUyAoMSwnezF9Jzo6aW50NFtdKTsNCj4gDQo+
IFRoZSBORVcuYSBkb2Vzbid0IHJlZmVyIHRvIGEgdmFyaWFibGUgYXMgc3Vj
aCwgaXQgcmVmZXJzIHRvIA0KPiB0aGUgdXBkYXRlZC9pbnNlcnRlZCB2YWx1
ZSBvZiBhbiBhY3R1YWwgcm93IGluICJ0ZXN0Ii4gRG9lcyANCj4gdGhhdCBj
bGFyaWZ5Pw0KPiANCj4gSW4geW91ciBwYXJ0aWN1bGFyIHVzYWdlIHlvdSdk
IHdhbnQgdG8gY29uc2lkZXIgY29uY3VycmVuY3kgDQo+IGFuZCBsb2NraW5n
IGlzc3VlcyB0b28uDQo+IA0KPiBSZXBvc3QgeW91ciBxdWVzdGlvbiBvbiB0
aGUgZ2VuZXJhbC9zcWwgbGlzdHMgaWYgeW91J2QgbGlrZSANCj4gc29tZSBk
aXNjdXNzaW9uLiBJdCdzIHByb2JhYmx5IHdvcnRoIGNoZWNraW5nIHRoZSBs
aXN0IA0KPiBhcmNoaXZlcyB0b28gLSBwbGVudHkgaW4gdGhlcmUgYWJvdXQg
cnVsZS90cmlnZ2VyIGRpZmZlcmVuY2VzLg0KPiAtLQ0KPiAgICBSaWNoYXJk
IEh1eHRvbg0KPiAgICBBcmNob25ldCBMdGQNCj4gDQo+IA0K

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