pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule causes baffling error
7+ messages / 3 participants
[nested] [flat]
* Rule causes baffling error
@ 2005-12-16 21:07 Ken Winter <ken@sunward.org>
0 siblings, 1 reply; 7+ messages in thread
From: Ken Winter @ 2005-12-16 21:07 UTC (permalink / raw)
To: pgsql-sql
I'm trying to figure out why a rule gives me a uniqueness violation when I
try to do an update.
I have a table, "my_data", defined as:
create table my_data (
id INT8 not null default nextval('person_seq'),
effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
CURRENT_TIMESTAMP,
expiration_date_and_time TIMESTAMP WITH TIME ZONE null default
'infinity',
user_name VARCHAR(255)null,
constraint PK_MY_DATA primary key
(effective_date_and_time, id)
);
I have a view, my_data_now, defined as:
SELECT
my_data.id,
my_data.user_name,
my_data.effective_date_and_time,
my_data.expiration_date_and_time
FROM my_data
WHERE my_data.effective_date_and_time <= 'now'::text::timestamp(6)
with time zone
AND my_data.expiration_date_and_time >=
'now'::text::timestamp(6) with time zone;
And I have this rule (among others):
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 =
('now'::text)::timestamp(6) with time zone
WHERE effective_date_and_time =
OLD.effective_date_and_time
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,
('now'::text)::timestamp(6) with time zone,
OLD.id,
OLD.user_name)
)
;
This rule is supposed to (1) cause an update directed to the view
"my_data_now" to be made to the underlying table "my_data", (2) reset the
"effective_date_and_time" of that row to 'now', (3) insert a record
containing the old values into "my_data", and (4) expire that "old" record
by setting its "expiration_date_and_time" to 'now'.
But when I try to do an update against the view "my_data_now" with a query
such as:
update my_data_now set user_name = 'Suzy' where id = 1;
I get:
ERROR: duplicate key violates unique constraint "pk_my_data"
Presumably this happens when the rule tries to insert the new row. The new
row does indeed contain the "old" id and effective_date_and_time. However,
the rule is structured so that the current row's "effective_date_and_time"
gets updated to 'now' *before* the new row is inserted, making its value
different from the old "effective_date_and_time". So the uniqueness
conflict shouldn't occur.
I figure either there's some bug in my code that I can't see, or else the
PostgreSQL rule processor works in some way that I don't understand.
In either case, help!
~ TIA
~ Ken
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule causes baffling error
@ 2005-12-19 09:08 Richard Huxton <dev@archonet.com>
parent: Ken Winter <ken@sunward.org>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2005-12-19 09:08 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: pgsql-sql
Ken Winter wrote:
> This rule is supposed to (1) cause an update directed to the view
> "my_data_now" to be made to the underlying table "my_data", (2) reset the
> "effective_date_and_time" of that row to 'now', (3) insert a record
> containing the old values into "my_data", and (4) expire that "old" record
> by setting its "expiration_date_and_time" to 'now'.
I think you want a trigger rather than a rule.
Rules rewrite the query structure, triggers let you deal with values on
a row-by-row basis (for row-level triggers).
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule causes baffling error
@ 2005-12-19 15:44 Ken Winter <ken@sunward.org>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ken Winter @ 2005-12-19 15:44 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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.
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.
~ Ken
> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Monday, December 19, 2005 4:08 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> > This rule is supposed to (1) cause an update directed to the view
> > "my_data_now" to be made to the underlying table "my_data", (2) reset
> the
> > "effective_date_and_time" of that row to 'now', (3) insert a record
> > containing the old values into "my_data", and (4) expire that "old"
> record
> > by setting its "expiration_date_and_time" to 'now'.
>
> I think you want a trigger rather than a rule.
>
> Rules rewrite the query structure, triggers let you deal with values on
> a row-by-row basis (for row-level triggers).
>
> --
> Richard Huxton
> Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule causes baffling error
@ 2005-12-19 16:05 Richard Huxton <dev@archonet.com>
parent: Ken Winter <ken@sunward.org>
0 siblings, 2 replies; 7+ messages in thread
From: Richard Huxton @ 2005-12-19 16:05 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule causes baffling error
@ 2005-12-19 16:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-12-19 16:27 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Ken Winter <ken@sunward.org>; pgsql-sql
Richard Huxton <dev@archonet.com> writes:
> Ken Winter wrote:
>> Can a trigger be written on a *view*?
> There's nothing for them to fire against even if you could attach the
> trigger.
Currently we reject CREATE TRIGGER on a view, but it occurred to me the
other day that that could be relaxed, at least for BEFORE triggers.
The system could feed the trigger with the synthetic view row, and the
trigger could update the view's underlying tables and then return NULL
to suppress any actual "operation" on the view proper.
To do this, instead of erroring out in the rewriter if a view has no
DO INSTEAD rule, we would have to error out down in the guts of
the executor if control got as far as trying to actually
insert/update/delete a tuple in a view.
The trickiest part of this is probably generating the "old" row for
UPDATE and DELETE cases. I think you'd need to adjust the planner
so that it would generate all the "old" view columns, rather than
the current situation in which it generates just the "new" columns
for an UPDATE, or no columns at all (only the CTID) for a DELETE.
I don't see any fundamental reason why this couldn't be made to work
though.
Triggers would be better than rules for quite a few view-rewriting
scenarios, mainly because you'd avoid all the gotchas with double
evaluation and so on. So it seems like it might be worth doing.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule causes baffling error
@ 2005-12-19 18:19 Ken Winter <ken@sunward.org>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ken Winter @ 2005-12-19 18:19 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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@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
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rule causes baffling error
@ 2005-12-20 09:15 Richard Huxton <dev@archonet.com>
parent: Ken Winter <ken@sunward.org>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2005-12-20 09:15 UTC (permalink / raw)
To: Ken Winter <ken@sunward.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-12-20 09:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-16 21:07 Rule causes baffling error Ken Winter <ken@sunward.org>
2005-12-19 09:08 ` Richard Huxton <dev@archonet.com>
2005-12-19 15:44 ` Ken Winter <ken@sunward.org>
2005-12-19 16:05 ` Richard Huxton <dev@archonet.com>
2005-12-19 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-19 18:19 ` Ken Winter <ken@sunward.org>
2005-12-20 09:15 ` Richard Huxton <dev@archonet.com>
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