Re: Another perplexity with PG rules - Mailing list pgsql-general

Tom ~

Thanks ever so much for - again - helping me get unstuck.  See comments and
results inserted below.

~ Ken

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Sunday, February 26, 2006 1:47 PM
> To: ken@sunward.org
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] Another perplexity with PG rules
>
> "Ken Winter" <ken@sunward.org> writes:
> > After trying about a million things, I'm wondering about the meaning of
> > "OLD." as the actions in a rule are successively executed.  What I have
> done
> > assumes that:
> > ...
> > (b) The "OLD." values that appear in the second (INSERT) action in the
> rule
> > are not changed by the execution of the first (UPDATE) rule.
>
> I believe this is mistaken.  OLD is effectively a macro for "the
> existing row(s) satisfying the rule's WHERE clause".  You've got two
> problems here --- one is that the UPDATE may have changed the data in
> those rows, and the other is that the UPDATE may cause them to not
> satisfy the WHERE clause anymore.

I was afraid of this.  Your conclusions do seem to fit my results.
>
> > (c) Whatever the truth of the above assumptions, the second (INSERT)
> action
> > in the 'on_update_2_preserve_h' rule should insert SOMEthing.
>
> See above.  If no rows remain satisfying WHERE, nothing will happen.

Yep, that's what was happening.
>
> > How to make this whole thing do what is required?
>
> I'd suggest seeing if you can't do the INSERT first then the UPDATE.
> This may require rethinking which of the two resulting rows is the
> "historical" one and which the "updated" one, but it could probably
> be made to work.

Yes, I had already had it working with such a scheme.  It expired the
existing record, and then inserted a new record with the updated values.
However this scheme seemed to be causing troubles with other triggers on the
base tables.  That's why I was trying to recast it into a scheme that
updated the existing record and then inserted a new record containing the
"old" data.
>
> Also, you might think about keeping the historical info in a separate
> table (possibly it could be an inheritance child of the master table).
> This would make it easier to distinguish the historical and current info
> when you need to.

I've been striving mightily to avoid taking this path, because it threatens
to hopelessly complicate my foreign keys.
>
> Lastly, I'd advise using triggers not rules wherever you possibly can.
> In particular, generation of the historical-log records would be far
> more reliable if implemented as an AFTER UPDATE trigger on the base
> table.
>
This appears to be the WINNER!  I eliminated the INSERT action from my
UPDATE rule:

CREATE OR REPLACE RULE on_update_2_preserve_h AS
    ON UPDATE TO person
...
    DO
        (
        /* 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 person_h
            SET person_id = NEW.person_id,
            first_name = NEW.first_name,
            middle_names = NEW.middle_names,
            last_name_prefix = NEW.last_name_prefix,
            last_name = NEW.last_name,
            name_suffix = NEW.name_suffix,
            preferred_full_name = NEW.preferred_full_name,
            preferred_business_name = NEW.preferred_business_name,
            user_name = NEW.user_name,
            _action = NEW._action,
            effective_date_and_time =
                CASE
                    WHEN NEW.effective_date_and_time =
OLD.effective_date_and_time
                         THEN CURRENT_TIMESTAMP -- Query assigned no value
                    ELSE NEW.effective_date_and_time -- Query assigned value
                END
            WHERE person_id = OLD.person_id
                AND effective_date_and_time = OLD.effective_date_and_time
        ;
        /* 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 person_h (
            person_id,
            first_name,
            middle_names,
            last_name_prefix,
            last_name,
            name_suffix,
            preferred_full_name,
            preferred_business_name,
            user_name,
            _action,
            effective_date_and_time,
            expiration_date_and_time)
        VALUES (
            OLD.person_id,
            OLD.first_name,
            OLD.middle_names,
            OLD.last_name_prefix,
            OLD.last_name,
            OLD.name_suffix,
            OLD.preferred_full_name,
            OLD.preferred_business_name,
            OLD.user_name,
            OLD._action,
            OLD.effective_date_and_time,
            CASE
                WHEN NEW.effective_date_and_time =
OLD.effective_date_and_time
                     THEN CURRENT_TIMESTAMP-- Query assigned no value
                ELSE NEW.effective_date_and_time-- Query assigned a value
            END)
        ;
    )
;

And turned it instead into this AFTER UPDATE trigger function:

CREATE OR REPLACE FUNCTION public.history_for_person()
RETURNS trigger AS
'
BEGIN
    IF NEW._action = ''preserve'' THEN
        /* 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 person_h (
            person_id,
            first_name,
            middle_names,
            last_name_prefix,
            last_name,
            name_suffix,
            preferred_full_name,
            preferred_business_name,
            user_name,
            effective_date_and_time,
            expiration_date_and_time,
            _action )
        VALUES (
            OLD.person_id,
            OLD.first_name,
            OLD.middle_names,
            OLD.last_name_prefix,
            OLD.last_name,
            OLD.name_suffix,
            OLD.preferred_full_name,
            OLD.preferred_business_name,
            OLD.user_name,
            OLD.effective_date_and_time,
            NEW.effective_date_and_time,
            ''old'' )
        ;
    END IF;
    RETURN NULL;
END;
'
LANGUAGE plpgsql VOLATILE
;

I haven't fully tested this, but at worst - unlike the previous two-action
rule - it properly handles these action queries:

INSERT INTO person (first_name, last_name) VALUES ('Lou', 'Foo');

UPDATE person SET first_name = 'Who' WHERE last_name like 'Foo%';

That is, in response to the UPDATE query, it leaves me with one current
record with first name = 'Who' AND one expired record with first_name =
'Lou'.

My quick-and-dirty of why this works is that it eliminates the instability
of the OLD record when a rule contains multiple actions.  Instead, it gives
me the stability of OLD inside of a trigger function.  Of course, I still
have to deal with the tendency of a trigger function to execute no matter
what was the source of the action query that triggered it, and I have
resorted to the "_action" column as a sort of parameter.  But I intend to
encapsulate this so that the ordinary users of the "person" table don't have
to know about it.

> (Over the years I've gotten less and less satisfied with Postgres' rules
> feature --- it just seems way too hard to make it do what people want
> reliably.  I'm afraid there's not much we can do to fix it without
> creating an enormous compatibility problem unfortunately :-(.  But by
> and large, triggers are a lot easier for people to wrap their brains
> around, once they get over the notational hurdle of having to write a
> trigger function.  I'd like to see us allow triggers on views, and then
> maybe rules could fade into the sunset for any but the most abstruse
> applications.)

Amen, amen amen!  PostgreSQL's rewrite rules seemed a great idea at first
look and in the abstract, but I have wasted a few weeks now trying to get
them to do something that really isn't that complicated, which is to
implement encapsulated history-keeping.  If I could put triggers on views, I
would junk all my rules and do all my history-keeping with triggers.  An
alternate workaround that I could live with would be to make a base table
that behaves like an updatable view:  It has triggers that divert all action
queries to its underlying table(s), so the table never actually contains
anything.  But to have that fully work, I would have to be able to declare a
"SELECT trigger" on my table-imitating-a-view, so that SELECTs against that
table would be answered by data from the underlying table(s).

Anyway, I think the mixed rules-and-triggers solution works for now (if not,
watch this space for more please for help), and I'm grateful to you for
triggering (pun intended) the idea.

~ Ken