From: "Ken Winter" <ken@sunward.org>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: "'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
Subject: Re: Rule causes baffling error
Date: Mon, 19 Dec 2005 13:19:58 -0500
Message-ID: <00a701c604c8$d297c590$6603a8c0@kenxp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Importance: Normal
In-Reply-To: <43A6DA39.2080509@archonet.com>

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 <=3D 'now' and
expiration_date_and_time =3D 'infinity'). =20

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. =20

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 =3D NEW.id,=20
				user_name =3D NEW.user_name,=20
				effective_date_and_time =3D CURRENT_TIMESTAMP
			WHERE effective_date_and_time =3D CURRENT_TIMESTAMP
				AND id =3D OLD.id;
		/* Insert a record containing the old values,=20
		and expire it as of now. */
		INSERT INTO my_data (
			effective_date_and_time,
			expiration_date_and_time,  =20
			id,=20
			user_name)=20
			VALUES ( =20
				OLD.effective_date_and_time,
				CURRENT_TIMESTAMP,
				OLD.id,=20
				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 =3D CURRENT_TIMESTAMP
			WHERE effective_date_and_time =3D
				OLD.effective_date_and_time=20
				AND id =3D OLD.id
                        AND effective_date_and_time <=3D =
CURRENT_TIMESTAMP
                        AND expiration_date_and_time >=3D =
CURRENT_TIMESTAMP;
/* Insert a record containing the new values,=20
		effective as of now. */
		INSERT INTO my_data (
			effective_date_and_time,
			id,=20
			user_name)=20
			VALUES ( =20
				CURRENT_TIMESTAMP,
				NEW.id,=20
				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 <=3D CURRENT_TIMESTAMP
      AND expiration_date_and_time >=3D 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@archonet.com]
> Sent: Monday, December 19, 2005 11:05 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>=20
> 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.
>=20
> 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.
>=20
> > 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.
>=20
> Hmm - the cascading should be straightforward enough to deal with. =
When
> you are updating check if NEW.expiration_date_and_time =3D now() and =
if so
> exit the trigger function (since there's nothing to do anyway).
>=20
> 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.
>=20
> 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?
>=20
> --
>    Richard Huxton
>    Archonet Ltd