[GENERAL] CREATE RULE on VIEW with INSERT after UPDATE does not work
Hi all,
I have a table "mytable" to log the validity of
data records with start and stop time.
To see, which records are still valid,
I created a view on all entries with stop=null.
The DB-Interaction should be done over the view,
so I added rules for INSERT, UPDATE an DELETE.
Insert and Update work fine, but the DELETE_RULE
stopps after the first UPDATE statement in the Rule-Body,
any further statements are ignored!!
Multiple Statements are not the Problem (Log=1,2),
and the first UPDATE statement works also. (Stop=now())
Is this a known Problem? Am I doing something wrong?
Is there any workaround for it?
Thanks, Peter
PS: Here is the code for testing, mylog should contain 1,2,3,4:
DROP VIEW myview;
DROP TABLE mytable;
DROP TABLE mylog;
CREATE TABLE mylog(id int);
CREATE TABLE mytable(id serial, proc text, start timestamp(4), stop
timestamp(4));
CREATE VIEW myview AS SELECT id, proc, start, stop FROM mytable WHERE stop IS
null;
CREATE RULE sri AS ON INSERT TO myview DO INSTEAD
INSERT INTO mytable (proc, start, stop) VALUES (new.proc, now(), null);
CREATE RULE srd AS ON DELETE TO myview DO INSTEAD
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD (
INSERT INTO mylog (id) VALUES (1);
INSERT INTO mylog (id) VALUES (2);
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
INSERT INTO mylog (id) VALUES (3);
UPDATE mytable SET stop = now() WHERE id = old.id+1 AND stop IS null;
INSERT INTO mylog (id) VALUES (4);
);
-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
INSERT INTO myview (proc) VALUES ('delta');
-- Both Table and View are identical
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;
-- Delete a row works fine, too
DELETE FROM myview WHERE id = 4;
-- Row 4 is deleted
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;
-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;
-- The Process 2 is updated, but there is no entry in the log
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;
--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] CREATE RULE on VIEW with INSERT after UPDATE does not work
Hi Tom,
thanks for your answer, I have also thought of combining
the statements, but my SQL-knowledge is too small for that.
I thought, the example with "mylog" would be better to
demonstrate the problem, but it's missing the point.
Below, if have added the code with my real problem.
What I want to do is a log of all starts and stops of validity.
So if a record is altered, I want the current one to be marked
by setting the stop-field to now() and another row to be added
to "mytable" with the same ID like the previous and start=now().
The Code below does only the mark-deleted-thing,
but does not insert a new record.
There is no unique-constraint on ID, I can add lines manually.
I also tried to swap the two lines in my Rule,
then a new row is inserted (good), but it is set
to end!=null by the second statement. (bad)
Maybe someone can give me a hint,
what's wrong with my code or my thinking?
Thanks, Peter
PS: Here's the NEW code with the uncooperative update-rule:
DROP VIEW myview;
DROP TABLE mytable;
CREATE TABLE mytable(id serial, proc text, start timestamp(4), stop
timestamp(4));
CREATE VIEW myview AS SELECT id, proc, start, stop FROM mytable WHERE stop IS
null;
CREATE RULE sri AS ON INSERT TO myview DO INSTEAD
INSERT INTO mytable (proc, start, stop) VALUES (new.proc, now(), null);
CREATE RULE srd AS ON DELETE TO myview DO INSTEAD
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD
(
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
INSERT INTO myview (id, proc, start, stop) VALUES (old.id, old.proc, now(),
null);
);
-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
-- Both Table and View are identical
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;
-- The Process 2 is updated, but there is no entry in the log
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
--
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] CREATE RULE on VIEW with INSERT after UPDATE does not work
> AFAICS, all you need to do is swap the ordering of those two operations.
>
> It might help to understand that what you write as an INSERT/VALUES is
> really more like INSERT ... SELECT ... FROM myview WHERE ..., the WHERE
> condition being the same as was given in the "UPDATE myview" command
> that the rule rewrites. As soon as you change the stop value in the
> UPDATE mytable, the SELECT from the view will find nothing.
>
> regards, tom lane
Ok, but swapping the two statements leads to another problem.
When executing these three statements,
I want the beta-line to have stop=null.
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
UPDATE myview SET proc='beta' WHERE id = 2;
But I always get this result, because the id is 2 in both rows:
id | proc | start | stop
+---+--+--
1 | alpha | 2007-08-11 02:32:04.7866 |
2 | omega | 2007-08-11 02:32:04.793 | 2007-08-11 02:32:04.8127
2 | beta | 2007-08-11 02:32:04.8127 | 2007-08-11 02:32:04.8127
So maybe, I need another condition in the update-statement,
but I don't know, which one to use.
Thanks in advance, Peter
PS: New Code with swapped lines:
DROP VIEW myview;
DROP TABLE mytable;
CREATE TABLE mytable(id serial, proc text, start timestamp(4), stop
timestamp(4));
CREATE VIEW myview AS SELECT id, proc, start, stop FROM mytable WHERE stop IS
null;
CREATE RULE sri AS ON INSERT TO myview DO INSTEAD
INSERT INTO mytable (proc, start, stop) VALUES (new.proc, now(), null);
CREATE RULE srd AS ON DELETE TO myview DO INSTEAD
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD
(
INSERT INTO mytable (id, proc, start, stop) VALUES (old.id, new.proc, now(),
null);
UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null; -- AND
;
);
-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;
SELECT * FROM mytable ORDER BY id,start;
SELECT * FROM myview ORDER BY id,start;
--
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
