Re: Another perplexity with PG rules

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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Sunday, February 26, 2006 1:47 PM
> To: ken(at)sunward(dot)org
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] Another perplexity with PG rules
>
> "Ken Winter" <ken(at)sunward(dot)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