To: Tatsuo Ishii <t-ishii@sra.co.jp>
Cc: pgsql-sql@postgresql.org, Jan Wieck <JanWieck@Yahoo.com>
Subject: Re: DO INSTEAD in rule 
In-reply-to: <20040104.225115.71101762.t-ishii@sra.co.jp> 
References: <20040104.225115.71101762.t-ishii@sra.co.jp>
Comments: In-reply-to Tatsuo Ishii <t-ishii@sra.co.jp>
	message dated "Sun, 04 Jan 2004 22:51:15 +0900"
Date: Sun, 04 Jan 2004 12:48:26 -0500
Message-ID: <21119.1073238506@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO
> INSTEAD in the rule and the default value for j is 0. Am I missing
> something?

> CREATE rule t1_ins AS ON INSERT TO t1
> 	WHERE (EXISTS (SELECT 1 FROM t1
> 			WHERE i = new.i))
> 	DO INSTEAD UPDATE t1 SET j = j + 1
> 	WHERE i = new.i;

Hm.  The problem is that the rule query runs after the INSERT and so it
sees the inserted row as something to update.  The logic is essentially

	if (not (EXISTS ...)) then do the INSERT;
	if (EXISTS ...) then do the UPDATE;

and the second command sees the inserted row as existing, so it updates
it.

Without an if-then-else kind of control structure for the executor,
I'm not sure we can do better.  (Even with one, I'm not sure how to
handle cases where the INSERT inserts multiple rows.)

Consider using a trigger instead of a rule to do this.  Or, accept
that the UPDATE will happen unconditionally, and start J off one less
than it should be.

Note that either solution will have race conditions if multiple
processes try to insert the same row at the same time.  There are
discussions in the archives about how to avoid that, but I'm not
sure anyone found a really satisfactory answer that didn't involve
an unpleasant amount of locking.

			regards, tom lane