Hello,
I have encountered an odd behavior involving rules which the following script demonstrates (in postgresql 8.4.3). Note that at the end of the run, the "dud" table contains one row "spam1" when the update rule clearly contains two inserts to the "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 jh WHERE 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 WHERE id=(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 serial column "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 jh WHERE 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 WHERE id=(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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
