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

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.