Thread: Problem with execution of an update rule
I'm trying to implement a history-keeping scheme using PostgreSQL views and
update rules. My problem is that one of the commands in one of my crucial
update rules apparently never executes.
Briefly, the history-keeping scheme involves:
* Two tables: an "h table" that contains the columns for which we want to
preserve a full history of all updates, and an "i table" that contains
columns whose history we don't want to preserve.
* A view of the two tables, showing all the columns of the h and I tables.
* A set of rules that makes the view behave like a fully updatable table,
while invisibly preserving a copy of the record as it existed prior to each
update.
The problem rule (see example in the "PS" below) is the one that fires when
the user issues a SQL UPDATE against the view. This rule fires if the
UPDATE has changed any column value. It is supposed to execute three
commands:
1. Insert a new record into the _h table, containing the old values of the
record being updated. This is the record that preserves the prior state of
the record.
2. Update the existing h table record with the new values.
3. Update the existing i table record with the new values.
The problem is that command 1 apparently never executes. That is, in
response to an UPDATE against the view, a new h table record is NOT inserted
- even though data changes in both the h and the i table are successfully
recorded, and no error messages occur.
I have tried changing the order of the 3 commands in the rule - no effect.
Can you tell me what's wrong with this picture?
~ TIA
~ Ken
PS:
This example involves a view named "people", an h table named "people_h"
(including columns "first_name" and "last_name"), an i table named
"people_i" (including column "birth_date"), a sequence-assigned identifier
"people_id" in both tables, some "effective" and "expiration" timestamps in
"people_h", and some rules including this troublesome one:
CREATE OR REPLACE RULE on_update_2_preserve AS
ON UPDATE TO people
WHERE (
(OLD.people_id <> NEW.people_id
OR (OLD.people_id IS NULL AND NEW.people_id IS NOT
NULL)
OR (OLD.people_id IS NOT NULL AND NEW.people_id IS
NULL ))
OR (OLD.effective_date_and_time <>
NEW.effective_date_and_time
OR (OLD.effective_date_and_time IS NULL
AND NEW.effective_date_and_time IS NOT NULL)
OR (OLD.effective_date_and_time IS NOT NULL
AND NEW.effective_date_and_time IS NULL ))
OR (OLD.first_name <> NEW.first_name
OR (OLD.first_name IS NULL AND NEW.first_name IS NOT
NULL)
OR (OLD.first_name IS NOT NULL AND NEW.first_name IS
NULL ))
OR (OLD.last_name <> NEW.last_name
OR (OLD.last_name IS NULL AND NEW.last_name IS NOT
NULL)
OR (OLD.last_name IS NOT NULL AND NEW.last_name IS
NULL ))
OR (OLD._action <> NEW._action
OR (OLD._action IS NULL AND NEW._action IS NOT NULL)
OR (OLD._action IS NOT NULL AND NEW._action IS NULL
))
OR (OLD.birth_date <> NEW.birth_date
OR (OLD.birth_date IS NULL AND NEW.birth_date IS NOT
NULL)
OR (OLD.birth_date IS NOT NULL AND NEW.birth_date IS
NULL )))
)
DO
(
/* Copy the old values to a new record.
Expire it either now (if no effective date
was provided) or whenever the update query specifies.*/
INSERT INTO people_h (
people_id,
first_name,
last_name,
effective_date_and_time,
expiration_date_and_time)
VALUES (
OLD.people_id,
OLD.first_name,
OLD.last_name,
OLD.effective_date_and_time,
NEW.effective_date_and_time)
;
/* Update the current H record and make it effective
as of either now (if no effective date
was provided) or whenever the update query specifies.*/
UPDATE people_h
SET
people_id = NEW.people_id,
first_name = NEW.first_name,
last_name = NEW.last_name,
_action = 'preserved',
effective_date_and_time =
CASE
WHEN NEW.effective_date_and_time =
OLD.effective_date_and_time
THEN CURRENT_TIMESTAMP
ELSE NEW.effective_date_and_time
END
WHERE
people_id = OLD.people_id
AND effective_date_and_time =
OLD.effective_date_and_time
;
/* Update I table. */
UPDATE people_i
SET
people_id = NEW.people_id,
birth_date = NEW.birth_date,
WHERE
people_id = OLD.people_id;
SELECT public.debug('Rule on_update_2_preserve
fired','','','');
)
;
_____
I am using the Free version of SPAMfighter <http://www.spamfighter.com/len>
.
We are a community of 6 million users fighting spam.
SPAMfighter has removed 392 of my spam emails to date.
The Professional version does not have this message.
Mark this one solved. I finally stumbled across an old, forgotten e-mail thread from 2006 where Tom Lane solved exactly this problem. See http://archives.postgresql.org/pgsql-general/2006-02/msg01039.php. ~ Thanks again, Tom! ~ Ken > > -----Original Message----- > From: Ken Winter [mailto:ken@sunward.org] > Sent: Friday, January 22, 2010 7:00 PM > To: 'PostgreSQL pg-general List' > Subject: Problem with execution of an update rule > > Im trying to implement a history-keeping scheme using PostgreSQL views and update rules. My problem is that one of the commands in one of my crucial update rules apparently never executes. > > Briefly, the history-keeping scheme involves: > > * Two tables: an "h table" that contains the columns for which we want to preserve a full history of all updates, and an "i table" that contains columns whose history we don't want to preserve. > > * A view of the two tables, showing all the columns of the h and I tables. > > * A set of rules that makes the view behave like a fully updatable table, while invisibly preserving a copy of the record as it existed prior to each update. > > The problem rule (see example in the "PS" below) is the one that fires when the user issues a SQL UPDATE against the view. This rule fires if the UPDATE has changed any column value. It is supposed to execute three commands: > > 1. Insert a new record into the _h table, containing the old values of the record being updated. This is the record that preserves the prior state of the record. > > 2. Update the existing h table record with the new values. > > 3. Update the existing i table record with the new values. > The problem is that command 1 apparently never executes. That is, in response to an UPDATE against the view, a new h table record is NOT inserted - even though data changes in both the h and the i table are successfully recorded, and no error messages occur. > > I have tried changing the order of the 3 commands in the rule - no effect. > Can you tell me what's wrong with this picture? > > ~ TIA > ~ Ken > > PS: > This example involves a view named "people", an h table named "people_h" (including columns "first_name" and "last_name"), an i table named "people_i" (including column "birth_date"), a sequence-assigned identifier "people_id" in both tables, some "effective" and "expiration" timestamps in "people_h", and some rules including this troublesome one: > > CREATE OR REPLACE RULE on_update_2_preserve AS > ON UPDATE TO people > WHERE ( > (OLD.people_id <> NEW.people_id > OR (OLD.people_id IS NULL AND NEW.people_id IS NOT NULL) > OR (OLD.people_id IS NOT NULL AND NEW.people_id IS NULL )) > OR (OLD.effective_date_and_time <> NEW.effective_date_and_time > OR (OLD.effective_date_and_time IS NULL > AND NEW.effective_date_and_time IS NOT NULL) > OR (OLD.effective_date_and_time IS NOT NULL > AND NEW.effective_date_and_time IS NULL )) > OR (OLD.first_name <> NEW.first_name > OR (OLD.first_name IS NULL AND NEW.first_name IS NOT NULL) > OR (OLD.first_name IS NOT NULL AND NEW.first_name IS NULL )) > OR (OLD.last_name <> NEW.last_name > OR (OLD.last_name IS NULL AND NEW.last_name IS NOT NULL) > OR (OLD.last_name IS NOT NULL AND NEW.last_name IS NULL )) > OR (OLD._action <> NEW._action > OR (OLD._action IS NULL AND NEW._action IS NOT NULL) > OR (OLD._action IS NOT NULL AND NEW._action IS NULL )) > OR (OLD.birth_date <> NEW.birth_date > OR (OLD.birth_date IS NULL AND NEW.birth_date IS NOT NULL) > OR (OLD.birth_date IS NOT NULL AND NEW.birth_date IS NULL ))) > ) > DO > ( > /* Copy the old values to a new record. > Expire it either now (if no effective date > was provided) or whenever the update query specifies.*/ > INSERT INTO people_h ( > people_id, > first_name, > last_name, > effective_date_and_time, > expiration_date_and_time) > VALUES ( > OLD.people_id, > OLD.first_name, > OLD.last_name, > OLD.effective_date_and_time, > NEW.effective_date_and_time) > ; > /* Update the current H record and make it effective > as of either now (if no effective date > was provided) or whenever the update query specifies.*/ > UPDATE people_h > SET > people_id = NEW.people_id, > first_name = NEW.first_name, > last_name = NEW.last_name, > _action = 'preserved', > effective_date_and_time = > CASE > WHEN NEW.effective_date_and_time = OLD.effective_date_and_time > THEN CURRENT_TIMESTAMP > ELSE NEW.effective_date_and_time > END > WHERE > people_id = OLD.people_id > AND effective_date_and_time = OLD.effective_date_and_time > ; > /* Update I table. */ > UPDATE people_i > SET > people_id = NEW.people_id, > birth_date = NEW.birth_date, > WHERE > people_id = OLD.people_id; > SELECT public.debug('Rule on_update_2_preserve fired','','',''); > ) > ;