Date: Mon, 05 Jan 2004 08:39:51 +0900 (JST)
Message-Id: <20040105.083951.78727440.t-ishii@sra.co.jp>
To: tgl@sss.pgh.pa.us
Cc: pgsql-sql@postgresql.org, JanWieck@Yahoo.com
Subject: Re: DO INSTEAD in rule 
From: Tatsuo Ishii <t-ishii@sra.co.jp>
In-Reply-To: <21119.1073238506@sss.pgh.pa.us>
References: <20040104.225115.71101762.t-ishii@sra.co.jp>
	<21119.1073238506@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> 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.

Oh I see. I think I can live with it. However I guess documentations
should be clearner about this...

> 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.

Now I remember the discussion. Probably I should not use rules like my
examples for real world applications.
--
Tatsuo Ishii