Re: BUG #10006: 'DO INSTEAD UPDATE' error

lucybird(at)126(dot)com writes:
> CREATE TABLE dns (
> customer_id integer NOT NULL,
> hits integer,
> primary key (customer_id)
> );

> CREATE RULE replace_dns AS
> ON INSERT TO dns
> WHERE EXISTS (SELECT 1 FROM dns WHERE dns.customer_id =
> new.customer_id)
> DO INSTEAD UPDATE dns
> SET hits = hits + 1
> WHERE dns.customer_id = new.customer_id;

> insert into dns (customer_id, hits) values (1,0);

> select * from dns;

> customer_id,hits
> 1, 1

> why? hits value must be 0, why 1?

Doing an EXPLAIN on the insert gives a clue:

Insert on dns (cost=8.17..8.18 rows=1 width=0)
InitPlan 1 (returns $0)
-> Index Only Scan using dns_pkey on dns dns_1 (cost=0.15..8.17 rows=1 width=0)
Index Cond: (customer_id = 1)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: ($0 IS NOT TRUE)
Planning time: 0.132 ms

Update on dns (cost=8.33..16.35 rows=1 width=14)
InitPlan 1 (returns $0)
-> Index Only Scan using dns_pkey on dns dns_1 (cost=0.15..8.17 rows=1 width=0)
Index Cond: (customer_id = 1)
-> Result (cost=0.15..8.17 rows=1 width=14)
One-Time Filter: $0
-> Index Scan using dns_pkey on dns (cost=0.15..8.17 rows=1 width=14)
Index Cond: (customer_id = 1)

What you've got from expansion of the rule is basically

insert into dns (customer_id, hits) select 1,0
where not EXISTS (SELECT 1 FROM dns WHERE dns.customer_id = 1);

update dns set hits = hits + 1 where dns.customer_id = 1
and EXISTS (SELECT 1 FROM dns WHERE dns.customer_id = 1);

so the INSERT is allowed to proceed (because the EXISTS is false at that
point) and then the UPDATE is allowed to proceed too (because the EXISTS
is now true).

In short, rules don't work the way you'd need them to work for this
application.

Currently, best practice for UPSERT-like functionality in Postgres
involves catching unique-key failures by hand, see for instance
the example in
http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
We're looking at more convenient ways to do it, but there's no easy fix.

regards, tom lane