From: Richard Huxton <dev@archonet.com>
To: Ken Winter <ken@sunward.org>
Cc: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
Subject: Re: Rule causes baffling error
Date: Tue, 20 Dec 2005 09:15:39 +0000
Message-ID: <43A7CBBB.2000704@archonet.com> (raw)
In-Reply-To: <00a701c604c8$d297c590$6603a8c0@kenxp>
References: <00a701c604c8$d297c590$6603a8c0@kenxp>
Ken Winter wrote:
> 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.
Oh - while I think of it, be VERY VERY careful that your system clock
doesn't get put back. I've done this sort of thing and been bitten by 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 "standard" approach in so far as there is one would be to have a
first line IF CURRENT_USER = 'MAINTENANCE' THEN RETURN ... or perhaps a
boolean stored in a system-settings table to turn them on or off in
en-masse. In your case the user-test seems better.
> 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.
If you're going to do this with multiple tables you actually need (at
least) three. For example, if you had different versions of e.g.
documents being stored you would want:
document - invariants: the id, perhaps document-type.
FKeys link to this.
A row is only deleted from here if all live+history
is also deleted.
document_live - the one that gets edited.
1:1 relationship with document if still live
document_hist - with timestamps. N:1 with document
Have a google for Temporal Databases too - there's a lot of thinking
been done about this.
>
> 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:
>
[snip]
> 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.)
Make sure you test it with inserts/updates of multiple rows too.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, ken@sunward.org
Subject: Re: Rule causes baffling error
In-Reply-To: <43A7CBBB.2000704@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox