Thread: rules on INSERT can't UPDATE new instance?
From the create_rule man page this example is offered:
CREATE RULE example_5 AS
ON INERT TO emp WHERE new.salary > 5000
DO
UPDATE NEWSET SET salary = 5000;
But what is "NEWSET"? Is it a keyword?
My problem is that on an insert with an invalid amount I try to perform
an update with a corrected amount, but the action part of the rule
doesn't affect or "see" the newly inserted row (or so it seems).
I tried: CREATE RULE ON INSERT TO bid WHERE new.price > limit
DO UPDATE bid SET price = 0.1;
and all price columns in the bid table would be set to 0.1 _except_ the
newly inserted row.
Am I missing something obvious?
TIA
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr
Although not exactly what you were asking about, it might be easier to get the effect with a before insert trigger written in plpgsql. (only minimally tested -- and against a 6.5 db - and replace the 100 and 0.1 with real values) create function checktriggerfunc() returns opaque as ' begin if (NEW.price>100) then NEW.price=0.1; end if; return NEW; end; ' language 'plpgsql'; create trigger checktrigger before insert on bid for each row execute procedure checktriggerfunc(); ----- Original Message ----- From: "Louis-David Mitterrand" <cunctator@apartia.ch> To: <pgsql-general@hub.org> Sent: Saturday, May 20, 2000 2:00 AM Subject: [GENERAL] rules on INSERT can't UPDATE new instance? > > From the create_rule man page this example is offered: > > CREATE RULE example_5 AS > ON INERT TO emp WHERE new.salary > 5000 > DO > UPDATE NEWSET SET salary = 5000; > > But what is "NEWSET"? Is it a keyword? > > My problem is that on an insert with an invalid amount I try to perform > an update with a corrected amount, but the action part of the rule > doesn't affect or "see" the newly inserted row (or so it seems). > > I tried: CREATE RULE ON INSERT TO bid WHERE new.price > limit > DO UPDATE bid SET price = 0.1; > > and all price columns in the bid table would be set to 0.1 _except_ the > newly inserted row. > > Am I missing something obvious?
>
> From the create_rule man page this example is offered:
>
> CREATE RULE example_5 AS
> ON INERT TO emp WHERE new.salary > 5000
> DO
> UPDATE NEWSET SET salary = 5000;
>
> But what is "NEWSET"? Is it a keyword?
It should be:
CREATE RULE example_5 AS
ON INERT TO emp WHERE new.salary > 5000
DO
UPDATE emp SET salary = 5000
WHERE emp.oid = new.oid;
Fixing now.
>
> My problem is that on an insert with an invalid amount I try to perform
> an update with a corrected amount, but the action part of the rule
> doesn't affect or "see" the newly inserted row (or so it seems).
>
> I tried: CREATE RULE ON INSERT TO bid WHERE new.price > limit
> DO UPDATE bid SET price = 0.1;
>
> and all price columns in the bid table would be set to 0.1 _except_ the
> newly inserted row.
>
> Am I missing something obvious?
No, buggy documentation. My book has a section on rules too, but you
should be fine now.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Sat, May 20, 2000 at 07:35:38AM -0400, Bruce Momjian wrote: > > From the create_rule man page this example is offered: > > > > CREATE RULE example_5 AS > > ON INERT TO emp WHERE new.salary > 5000 > > DO > > UPDATE NEWSET SET salary = 5000; > > > > But what is "NEWSET"? Is it a keyword? > > It should be: > > CREATE RULE example_5 AS > ON INERT TO emp WHERE new.salary > 5000 > DO > UPDATE emp SET salary = 5000 > WHERE emp.oid = new.oid; > > Fixing now. But this doesn't work in PG 7.0: auction=> create table test (price float); CREATE auction=> create rule price_control AS ON INSERT TO test WHERE new.price > 100 DO UPDATE test SET price = 100 where test.oid= new.oid; CREATE 27913 1 auction=> INSERT INTO test VALUES (101); INSERT 27914 1 auction=> SELECT test.*; price ------- 101 (1 row) -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr MACINTOSH == Most Applications Crash If Not The Operatings System Hangs
> On Sat, May 20, 2000 at 07:35:38AM -0400, Bruce Momjian wrote: > > > From the create_rule man page this example is offered: > > > > > > CREATE RULE example_5 AS > > > ON INERT TO emp WHERE new.salary > 5000 > > > DO > > > UPDATE NEWSET SET salary = 5000; > > > > > > But what is "NEWSET"? Is it a keyword? > > > > It should be: > > > > CREATE RULE example_5 AS > > ON INERT TO emp WHERE new.salary > 5000 > > DO > > UPDATE emp SET salary = 5000 > > WHERE emp.oid = new.oid; > > > > Fixing now. > > But this doesn't work in PG 7.0: > > auction=> create table test (price float); > CREATE > auction=> create rule price_control AS ON INSERT TO test WHERE new.price > 100 DO UPDATE test SET price = 100 where test.oid= new.oid; > CREATE 27913 1 > auction=> INSERT INTO test VALUES (101); > INSERT 27914 1 > auction=> SELECT test.*; > price > ------- > 101 > (1 row) Yes, I see it failing too. I tried old.oid, and that failed too. I know there is a recursive problem with rules acting on their own table, where if you have an INSERT rule that performs an INSERT on the same table, the rules keep firing in a loop. I thought an INSERT rule with an UPDATE action would work on the same table, but that fails. Seems the rule is firing before the INSERT happens. I am not really sure what to recommend. The INSERT rule clearly doesn't fix cases where someone UPDATE's the row to != 100. A CHECK constraint could be used to force the column to contain 100, but that doesn't silently fix non-100 values, which seemed to be your goal. A trigger will allow this kind of action, on INSERT and UPDATE, though they are a little more complicated than rules. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Sat, May 20, 2000 at 10:41:53AM -0400, Bruce Momjian wrote: > > But this doesn't work in PG 7.0: > > > > auction=> create table test (price float); > > CREATE > > auction=> create rule price_control AS ON INSERT TO test WHERE new.price > 100 DO UPDATE test SET price = 100 where test.oid= new.oid; > > CREATE 27913 1 > > auction=> INSERT INTO test VALUES (101); > > INSERT 27914 1 > > auction=> SELECT test.*; > > price > > ------- > > 101 > > (1 row) > > Yes, I see it failing too. I tried old.oid, and that failed too. > > I know there is a recursive problem with rules acting on their own > table, where if you have an INSERT rule that performs an INSERT on the > same table, the rules keep firing in a loop. > > I thought an INSERT rule with an UPDATE action would work on the same > table, but that fails. Seems the rule is firing before the INSERT > happens. > > I am not really sure what to recommend. The INSERT rule clearly doesn't > fix cases where someone UPDATE's the row to != 100. A CHECK constraint > could be used to force the column to contain 100, but that doesn't > silently fix non-100 values, which seemed to be your goal. A trigger > will allow this kind of action, on INSERT and UPDATE, though they are a > little more complicated than rules. Thanks for all your help. You are right: this seems more like the job of a trigger and I am exploring that topic in depth right now. Cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr Parkinson's Law: Work expands to fill the time alloted it.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I thought an INSERT rule with an UPDATE action would work on the same
> table, but that fails. Seems the rule is firing before the INSERT
> happens.
Yes, a trigger is the right way to do surgery on a tuple before it is
stored. Rules are good for generating additional SQL queries that will
insert/update/delete other tuples (usually, but not necessarily, in
other tables). Even if it worked, a rule would be a horribly
inefficient way to handle modification of the about-to-be-inserted
tuple, because (being an independent query) it'd have to scan the table
to find the tuple you are talking about!
The reason the additional queries are done before the original command
is explained thus in the source code:
* The original query is appended last if not instead
* because update and delete rule actions might not do
* anything if they are invoked after the update or
* delete is performed. The command counter increment
* between the query execution makes the deleted (and
* maybe the updated) tuples disappear so the scans
* for them in the rule actions cannot find them.
This seems to make sense for UPDATE/DELETE, but I wonder whether
the ordering should be different for the INSERT case: perhaps it
should be original-query-first in that case.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I thought an INSERT rule with an UPDATE action would work on the same > > table, but that fails. Seems the rule is firing before the INSERT > > happens. > > Yes, a trigger is the right way to do surgery on a tuple before it is > stored. Rules are good for generating additional SQL queries that will > insert/update/delete other tuples (usually, but not necessarily, in > other tables). Even if it worked, a rule would be a horribly > inefficient way to handle modification of the about-to-be-inserted > tuple, because (being an independent query) it'd have to scan the table > to find the tuple you are talking about! > > The reason the additional queries are done before the original command > is explained thus in the source code: > > * The original query is appended last if not instead > * because update and delete rule actions might not do > * anything if they are invoked after the update or > * delete is performed. The command counter increment > * between the query execution makes the deleted (and > * maybe the updated) tuples disappear so the scans > * for them in the rule actions cannot find them. > > This seems to make sense for UPDATE/DELETE, but I wonder whether > the ordering should be different for the INSERT case: perhaps it > should be original-query-first in that case. > Thanks, Tom. I was writing the Trigger section of my book the past few days, and this helped me define when to use rules and when to use triggers. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Is the INSERT rule re-ordering mentioned a TODO item? > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I thought an INSERT rule with an UPDATE action would work on the same > > table, but that fails. Seems the rule is firing before the INSERT > > happens. > > Yes, a trigger is the right way to do surgery on a tuple before it is > stored. Rules are good for generating additional SQL queries that will > insert/update/delete other tuples (usually, but not necessarily, in > other tables). Even if it worked, a rule would be a horribly > inefficient way to handle modification of the about-to-be-inserted > tuple, because (being an independent query) it'd have to scan the table > to find the tuple you are talking about! > > The reason the additional queries are done before the original command > is explained thus in the source code: > > * The original query is appended last if not instead > * because update and delete rule actions might not do > * anything if they are invoked after the update or > * delete is performed. The command counter increment > * between the query execution makes the deleted (and > * maybe the updated) tuples disappear so the scans > * for them in the rule actions cannot find them. > > This seems to make sense for UPDATE/DELETE, but I wonder whether > the ordering should be different for the INSERT case: perhaps it > should be original-query-first in that case. > > regards, tom lane > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is the INSERT rule re-ordering mentioned a TODO item?
Darn if I know. I threw the thought out for discussion, but didn't
see any comments. I'm not in a hurry to change it, unless there's
consensus that we should.
regards, tom lane
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>>> I thought an INSERT rule with an UPDATE action would work on the same
>>>> table, but that fails. Seems the rule is firing before the INSERT
>>>> happens.
>>
>> Yes, a trigger is the right way to do surgery on a tuple before it is
>> stored. Rules are good for generating additional SQL queries that will
>> insert/update/delete other tuples (usually, but not necessarily, in
>> other tables). Even if it worked, a rule would be a horribly
>> inefficient way to handle modification of the about-to-be-inserted
>> tuple, because (being an independent query) it'd have to scan the table
>> to find the tuple you are talking about!
>>
>> The reason the additional queries are done before the original command
>> is explained thus in the source code:
>>
>> * The original query is appended last if not instead
>> * because update and delete rule actions might not do
>> * anything if they are invoked after the update or
>> * delete is performed. The command counter increment
>> * between the query execution makes the deleted (and
>> * maybe the updated) tuples disappear so the scans
>> * for them in the rule actions cannot find them.
>>
>> This seems to make sense for UPDATE/DELETE, but I wonder whether
>> the ordering should be different for the INSERT case: perhaps it
>> should be original-query-first in that case.