pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
DO INSTEAD in rule 3+ messages / 2 participants [nested] [flat]
* DO INSTEAD in rule @ 2004-01-04 13:51 Tatsuo Ishii <t-ishii@sra.co.jp> 2004-01-04 17:48 ` Re: DO INSTEAD in rule Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Tatsuo Ishii @ 2004-01-04 13:51 UTC (permalink / raw) To: pgsql-sql Hi, 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? DROP TABLE t1 CASCADE; DROP TABLE CREATE TABLE t1 ( i INTEGER, j INTEGER DEFAULT 0 ); CREATE TABLE 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; CREATE RULE INSERT INTO t1 VALUES (1); INSERT 1690668 1 SELECT * FROM t1; i | j ---+--- 1 | 1 (1 row) -- Tatsuo Ishii ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DO INSTEAD in rule 2004-01-04 13:51 DO INSTEAD in rule Tatsuo Ishii <t-ishii@sra.co.jp> @ 2004-01-04 17:48 ` Tom Lane <tgl@sss.pgh.pa.us> 2004-01-04 23:39 ` Re: DO INSTEAD in rule Tatsuo Ishii <t-ishii@sra.co.jp> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2004-01-04 17:48 UTC (permalink / raw) To: Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: pgsql-sql@postgresql.org, Jan Wieck <JanWieck@Yahoo.com> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DO INSTEAD in rule 2004-01-04 13:51 DO INSTEAD in rule Tatsuo Ishii <t-ishii@sra.co.jp> 2004-01-04 17:48 ` Re: DO INSTEAD in rule Tom Lane <tgl@sss.pgh.pa.us> @ 2004-01-04 23:39 ` Tatsuo Ishii <t-ishii@sra.co.jp> 0 siblings, 0 replies; 3+ messages in thread From: Tatsuo Ishii @ 2004-01-04 23:39 UTC (permalink / raw) To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql; JanWieck@Yahoo.com > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-04 23:39 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-01-04 13:51 DO INSTEAD in rule Tatsuo Ishii <t-ishii@sra.co.jp> 2004-01-04 17:48 ` Tom Lane <tgl@sss.pgh.pa.us> 2004-01-04 23:39 ` Tatsuo Ishii <t-ishii@sra.co.jp>
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