Re: Problem with execution of an update rule - Mailing list pgsql-general

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
>
> 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','','','');
>   )
> ;