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@xxxxxxxxxxxxx] > Sent: Sunday, February 26, 2006 1:47 PM > To: ken@xxxxxxxxxxx > Cc: PostgreSQL pg-general List > Subject: Re: [GENERAL] Another perplexity with PG rules > > "Ken Winter" <ken@xxxxxxxxxxx> 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
