Thread: 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
"Peter Marius" <Peter.Marius@gmx.de> writes:
> 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!!
Once you change the stop field to not be null, the row's no longer part
of the view, so there's nothing to update. You need to re-order the
operations (and, probably, combine the two UPDATE commands) so that you
don't remove the row from the view until the last step.
regards, tom lane
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;
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
"Peter Marius" <Peter.Marius@gmx.de> writes:
> 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.
> 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);
> );
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
> 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 <some-condition>;
);
-- 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;
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger