Обсуждение: 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

On 2 July 2010 23:27, A.M. <agentm@themactionfaction.com> wrote:
> Hello,
>
> I have encountered an odd behavior involving rules which the following script demonstrates (in postgresql 8.4.3).
Notethat at the end of the run, the "dud" table contains one row "spam1" when the update rule clearly contains two
insertsto the "dud" table. It seems that the update rule on "test.job" cuts off execution after the first update
executes(and succeeds)- why? 

The problem is that after the first update, "deprecated" is non-NULL
and so no longer matches the view definition. All subsequent actions
in the rule are combined with the view definition, and so find no
matching rows.

Rules are total pain, full of gotchas like this. You're almost
certainly better off using triggers on your tables. You could patch
your rule by moving the update that marks the row as deprecated to the
end and changing its WHERE clause, but really you're just inviting
further pain by continuing to use rules IMO.

Regards,
Dean