Half-applied UPDATE rule on view

Hello,

I have encountered an odd behavior involving rules which the following script demonstrates (in postgresql 8.4.3). Note
thatat the end of the run, the "dud" table contains one row "spam1" when the update rule clearly contains two inserts
tothe "dud" table. It seems that the update rule on "test.job" cuts off execution after the first update executes (and
succeeds)-why? 


------------------------------------------------------------------------------------------------------------------------------------------------------


DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
SET search_path TO test;

CREATE TABLE job_history
(
    id SERIAL PRIMARY KEY,
    logicalid INTEGER NOT NULL, --ID shown through views
    created TIMESTAMP NOT NULL,
    createdby TEXT NOT NULL,
    deprecated TIMESTAMP, --if NULL, then it is still valid (and must be the latest "version" for this historical ID)
    deprecatedby TEXT,
    name TEXT
);

CREATE TABLE dud
(
    name TEXT
);

CREATE SEQUENCE job_logicalid OWNED BY job_history.logicalid;

CREATE INDEX job_history_live_idx ON job_history(deprecated) WHERE deprecated IS NULL;

CREATE VIEW job AS
SELECT
jh.logicalid AS id,
jh.name AS name
FROM job_history AS jh
WHERE
deprecated IS NULL;

CREATE RULE job_insert AS ON INSERT TO job DO INSTEAD INSERT INTO
job_history(id,logicalid,created,createdby,deprecated,deprecatedby,name)VALUES
(DEFAULT,nextval('job_logicalid'),now(),current_role,NULL,NULL,NEW.name)RETURNING job_history.logicalid,name; 
CREATE RULE job_update AS ON UPDATE TO job DO INSTEAD
(
    INSERT INTO dud(name) VALUES ('spam1');
    UPDATE job_history SET deprecated=now(),deprecatedby=current_role WHERE id=(SELECT MAX(jh.id) FROM job_history AS
jhWHERE jh.logicalid=NEW.id); 
    INSERT INTO dud(name) VALUES ('spam2');
    INSERT INTO job_history(logicalid,created,createdby,deprecated,deprecatedby,name) VALUES
(NEW.id,now(),current_role,NULL,NULL,NEW.name);
);
CREATE RULE job_delete AS ON DELETE TO job DO INSTEAD UPDATE job_history SET deprecated=now(),deprecatedby=current_role
WHEREid=(SELECT MAX(jh.id) FROM job_history AS jh WHERE jh.logicalid=OLD.id); 

INSERT INTO test.job(name) VALUES ('jobA'),('jobB');
SELECT * FROM job_history;
UPDATE test.job SET name='jobC' WHERE id=2;
SELECT * FROM job_history;
SELECT * FROM dud;

------------------------------------------------------------------------------------------------------------------------------------------------------


Here is a run of it in postgresql 8.4.3:


psql -a -1 -f job_history test

------------------------------------------------------------------------------------------------------------------------------------------------------
DROP SCHEMA IF EXISTS test CASCADE;
psql:/Users/agentm/Desktop/job_history:1: NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table test.job_history
drop cascades to table test.dud
drop cascades to view test.job
DROP SCHEMA
CREATE SCHEMA test;
CREATE SCHEMA
SET search_path TO test;
SET
CREATE TABLE job_history
(
    id SERIAL PRIMARY KEY,
    logicalid INTEGER NOT NULL, --ID shown through views
    created TIMESTAMP NOT NULL,
    createdby TEXT NOT NULL,
    deprecated TIMESTAMP, --if NULL, then it is still valid (and must be the latest "version" for this historical ID)
    deprecatedby TEXT,
    name TEXT
);
psql:/Users/agentm/Desktop/job_history:14: NOTICE:  CREATE TABLE will create implicit sequence "job_history_id_seq" for
serialcolumn "job_history.id" 
psql:/Users/agentm/Desktop/job_history:14: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"job_history_pkey"for table "job_history" 
CREATE TABLE
CREATE TABLE dud
(
    name TEXT
);
CREATE TABLE
CREATE SEQUENCE job_logicalid OWNED BY job_history.logicalid;
CREATE SEQUENCE

CREATE INDEX job_history_live_idx ON job_history(deprecated) WHERE deprecated IS NULL;
CREATE INDEX
CREATE VIEW job AS
SELECT
jh.logicalid AS id,
jh.name AS name
FROM job_history AS jh
WHERE
deprecated IS NULL;
CREATE VIEW
CREATE RULE job_insert AS ON INSERT TO job DO INSTEAD INSERT INTO
job_history(id,logicalid,created,createdby,deprecated,deprecatedby,name)VALUES
(DEFAULT,nextval('job_logicalid'),now(),current_role,NULL,NULL,NEW.name)RETURNING job_history.logicalid,name; 
CREATE RULE
CREATE RULE job_update AS ON UPDATE TO job DO INSTEAD
(
    INSERT INTO dud(name) VALUES ('spam1');
    UPDATE job_history SET deprecated=now(),deprecatedby=current_role WHERE id=(SELECT MAX(jh.id) FROM job_history AS
jhWHERE jh.logicalid=NEW.id); 
    INSERT INTO dud(name) VALUES ('spam2');
    INSERT INTO job_history(logicalid,created,createdby,deprecated,deprecatedby,name) VALUES
(NEW.id,now(),current_role,NULL,NULL,NEW.name);
);
CREATE RULE
CREATE RULE job_delete AS ON DELETE TO job DO INSTEAD UPDATE job_history SET deprecated=now(),deprecatedby=current_role
WHEREid=(SELECT MAX(jh.id) FROM job_history AS jh WHERE jh.logicalid=OLD.id); 
CREATE RULE
INSERT INTO test.job(name) VALUES ('jobA'),('jobB');
INSERT 0 2
SELECT * FROM job_history;
 id | logicalid |          created           | createdby | deprecated | deprecatedby | name
----+-----------+----------------------------+-----------+------------+--------------+------
  1 |         1 | 2010-07-02 18:11:20.330974 | agentm    |            |              | jobA
  2 |         2 | 2010-07-02 18:11:20.330974 | agentm    |            |              | jobB
(2 rows)

UPDATE test.job SET name='jobC' WHERE id=2;
UPDATE 1
SELECT * FROM job_history;
 id | logicalid |          created           | createdby |         deprecated         | deprecatedby | name
----+-----------+----------------------------+-----------+----------------------------+--------------+------
  1 |         1 | 2010-07-02 18:11:20.330974 | agentm    |                            |              | jobA
  2 |         2 | 2010-07-02 18:11:20.330974 | agentm    | 2010-07-02 18:11:20.330974 | agentm       | jobB
(2 rows)

SELECT * FROM dud;
 name
-------
 spam1
(1 row)





Cheers,
M