From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tatsuo Ishii <t-ishii@sra.co.jp>
Cc: pgsql-sql@postgresql.org, Jan Wieck <JanWieck@Yahoo.com>
Subject: Re: DO INSTEAD in rule
Date: Sun, 04 Jan 2004 12:48:26 -0500
Message-ID: <21119.1073238506@sss.pgh.pa.us> (raw)
In-Reply-To: <20040104.225115.71101762.t-ishii@sra.co.jp>
References: <20040104.225115.71101762.t-ishii@sra.co.jp>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, t-ishii@sra.co.jp, JanWieck@Yahoo.com
Subject: Re: DO INSTEAD in rule
In-Reply-To: <21119.1073238506@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox