Skip site navigation (1) Skip section navigation (2)

Re: Only first statement of two in update-rule is executing?

From: David Owen <dsowen(at)fugue88(dot)ws>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Only first statement of two in update-rule is executing?
Date: 2008-01-30 05:18:16
Message-ID: Pine.LNX.4.64.0801292213390.11253@tux.l.fugue88.ws (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 29 Jan 2008, David Owen wrote:

> CREATE TABLE simple1(a varchar, b varchar, c timestamp DEFAULT now(),
>    d timestamp, PRIMARY KEY(a, c));
> CREATE VIEW simple2 AS SELECT a, b FROM simple1 WHERE d IS NULL;
> INSERT INTO simple1(a, b) VALUES('a', '1');
> CREATE RULE simple2_upd AS ON UPDATE TO simple2 DO INSTEAD
>    (UPDATE simple1 SET d = now() WHERE a = NEW.a AND d IS NULL;
>     INSERT INTO simple1(a, b) VALUES(NEW.a, NEW.b));
>
> Now, if I do
>
>    UPDATE simple2 SET b='2';
>
> ... I expect the the original row in simple1 to now have d=(some time), and a 
> new row with (a, b, c, d) = ('a', '2', some time, NULL).
>
> However, the new row isn't inserted; only the previous row is changed.

I discovered that if I change the rule to only do the insert, I still have 
troubles.  The first update to simple2 will insert an row, but a second 
update will give a duplicate primary key error.

The timestamp being inserted doesn't correspond to the creation of the 
rule.  Is the rule's query tree maybe absorbing the default value into 
itself, but not doing so until the rules first use?

Thanks,
David Owen

In response to

Responses

pgsql-general by date

Next:From: David OwenDate: 2008-01-30 05:47:14
Subject: Re: Only first statement of two in update-rule is executing?
Previous:From: Ow Mun HengDate: 2008-01-30 04:42:40
Subject: [OT] Slony + Alter table using pgadmin

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group