Re: Rule causes baffling error

Richard ~

Let me zoom out for a moment, for the bigger picture.

As you have inferred, what I'm trying to do is develop a history-preserving
table ("my_data" in the example that started this thread). *Most* user
programs would see and manipulate this table as if it contained only the
current rows (marked by effective_date_and_time <= 'now' and
expiration_date_and_time = 'infinity').

When these programs do an INSERT, I need automatic actions that set the
expiration and date timestamps to 'now' and 'infinity'; when they do an
UPDATE, I need automatic actions that save the old data in a history record
and expire it as of 'now' and the new data in a record that's effective
'now' and expires at 'infinity'; when they do a DELETE, I need an automatic
action to expire the target record as of 'now' rather than actually deleting
it.

However, I also need certain maintenance programs, designed to enable
certain users to correct inaccurately entered data. These need to be able
to "rewrite history" by doing actions against "my_data" without these
automatic actions occurring. It may prove advisable to provide some
automatic actions for these programs too, but they definitely won't be the
actions described above. If the above actions were implemented as triggers,
all the ways I could think of to conditionally disable them (and possibly
replace them with other actions) seemed architecturally very klunky. That's
when I decided I needed the "my_data_now" view, and from that I inferred
(apparently correctly) that the actions would have to be implemented as
rewrite rules.

The cascading problem was solkable. But the solution was a bit hard to
reach because the user-invoked UPDATE action triggered both an INSERT and an
UPDATE on the same table (and user DELETE triggered an UPDATE), and so one
had to take into account that all of these triggered actions would cause
their triggers to fire again. Not a deal-killer, but the solution felt
brittle.

Yes, I did consider having a "live" table and a separate "history" table.
The killer of that idea was my inability to find a way to implement foreign
keys that could refer to both tables and that could follow a record when it
was moved from "live" to "history". Much of the history I'm trying to
preserve is not in the "my_data" table; it's in related tables that refer to
it. I presumably could do this by not declaring the FKs to PostgreSQL, and
implementing the necessary referential integrity with triggers, but - well,
in a word, yuck.

As it happens, I have found a rewrite of my UPDATE rule that works, so my
immediate need is past. FYI, the old update rule was:

CREATE OR REPLACE RULE upd_my_data_now AS
ON UPDATE TO my_data_now
DO INSTEAD
(
/* Update current record, and make it effective now. */
UPDATE my_data
SET id = NEW.id,
user_name = NEW.user_name,
effective_date_and_time = CURRENT_TIMESTAMP
WHERE effective_date_and_time = CURRENT_TIMESTAMP
AND id = OLD.id;
/* Insert a record containing the old values,
and expire it as of now. */
INSERT INTO my_data (
effective_date_and_time,
expiration_date_and_time,
id,
user_name)
VALUES (
OLD.effective_date_and_time,
CURRENT_TIMESTAMP,
OLD.id,
OLD.user_name)
)
;

And the one that works is:

CREATE OR REPLACE RULE upd_my_data_now AS
ON UPDATE TO my_data_now
DO INSTEAD
(
/* Expire the current record. */
UPDATE my_data
SET expiration_date_and_time = CURRENT_TIMESTAMP
WHERE effective_date_and_time =
OLD.effective_date_and_time
AND id = OLD.id
AND effective_date_and_time <= CURRENT_TIMESTAMP
AND expiration_date_and_time >= CURRENT_TIMESTAMP;
/* Insert a record containing the new values,
effective as of now. */
INSERT INTO my_data (
effective_date_and_time,
id,
user_name)
VALUES (
CURRENT_TIMESTAMP,
NEW.id,
NEW.user_name)
)
;

The relevant change is that I'm now expiring the record with the old data
and inserting the one with the new data, rather than vice versa. I still
don't know why the old rule didn't work and this one does, but hey,
whatever. Another advantage of the new one is that I don't have to re-point
foreign keys that were already pointed to the record containing the old
data, because that record stays in place.

(The other change, adding the lines
AND effective_date_and_time <= CURRENT_TIMESTAMP
AND expiration_date_and_time >= CURRENT_TIMESTAMP;
to the UPDATE, was necessary to keep updates to the "my_data_now" from
updating the expired rows as well.)

Thanks for your help. I hope this little essay is of some value to others.

~ Ken

> -----Original Message-----
> From: Richard Huxton [mailto:dev(at)archonet(dot)com]
> Sent: Monday, December 19, 2005 11:05 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> > Richard ~
> >
> > Thanks for your response.
> >
> > Can a trigger be written on a *view*? I can't find anything in the
> > PostgreSQL docs that answers this question.
>
> There's nothing for them to fire against even if you could attach the
> trigger. I suppose you could have a statement-level trigger in more
> recent versions, but for row-level triggers there aren't any rows in the
> view to be affected.
>
> > I originally wrote these actions (described in my original message) as a
> > trigger on my base table, but then realized I was getting in deeper and
> > deeper trouble because (a) I was getting into cascading triggers that I
> > didn't want and (b) I need to enable some queries to access the base
> table
> > without triggering these actions. That's why I set up the view, and
> then I
> > assumed that the only way I could implement these actions was as rules.
>
> Hmm - the cascading should be straightforward enough to deal with. When
> you are updating check if NEW.expiration_date_and_time = now() and if so
> exit the trigger function (since there's nothing to do anyway).
>
> The other thing you might want to consider is whether the "live" data
> should be in the same table as the "old" data. That will depend on how
> you want to use it - conceptually is it all one continuum or is the
> "old" data just for archive purposes.
>
> Now, having got this feature working, why do you want to bypass it? Will
> it be a specific user, involve specific patterns of values or what?
>
> --
> Richard Huxton
> Archonet Ltd