From: Tatsuo Ishii <t-ishii@sra.co.jp>
To: tgl@sss.pgh.pa.us
Cc: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com
Subject: Re: DO INSTEAD in rule
Date: Mon, 05 Jan 2004 08:39:51 +0900 (JST)
Message-ID: <20040105.083951.78727440.t-ishii@sra.co.jp> (raw)
In-Reply-To: <21119.1073238506@sss.pgh.pa.us>
References: <20040104.225115.71101762.t-ishii@sra.co.jp>
<21119.1073238506@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.
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
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: t-ishii@sra.co.jp, tgl@sss.pgh.pa.us, JanWieck@Yahoo.com
Subject: Re: DO INSTEAD in rule
In-Reply-To: <20040105.083951.78727440.t-ishii@sra.co.jp>
* 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