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

I'm trying to create a table that maintains change-history and a modifable
view over it showing only current data.  The delete and insert rules work
well, but I'm having trouble getting my update rule to work (it contains
two statements, the others contain only one each).

Here's a simple demonstration of my problem:

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.

What am I missing?  Any pointers?  I've read through the docs (I'm on
8.2.6) for rules several times, and nothing seems to explain this.

Thanks,
David Owen

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

On Tue, 29 Jan 2008, David Owen wrote:

> On Tue, 29 Jan 2008, David Owen wrote:
>
> 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.

Sorry, that's a bad path (it's late).  After the first update, there are
now two rows in simple1.  The second update applys to both, so attempts to
create two new rows having the same timestamp.  Ah well.

-David