Horton

Thread

  1. rules on INSERT can't UPDATE new instance?

    Louis-David Mitterrand <cunctator@apartia.ch> — 2000-05-20T09:00:56Z

     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

  2. Re: rules on INSERT can't UPDATE new instance?

    Stephan Szabo <sszabo@kick.com> — 2000-05-20T09:24:23Z

    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?

  3. Re: rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-20T11:35:38Z

    > 
>  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

  4. Re: rules on INSERT can't UPDATE new instance?

    Louis-David Mitterrand <cunctator@apartia.ch> — 2000-05-20T13:44:17Z

    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

  5. Re: rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-20T14:41:53Z

    > 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

  6. Re: rules on INSERT can't UPDATE new instance?

    Louis-David Mitterrand <cunctator@apartia.ch> — 2000-05-20T16:06:35Z

    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.

  7. Re: rules on INSERT can't UPDATE new instance?

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-20T16:19:51Z

    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

  8. Re: rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-22T00:23:00Z

    > 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

  9. Re: rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-13T07:50:49Z

    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

  10. Re: rules on INSERT can't UPDATE new instance?

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-13T08:01:12Z

    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.

  11. Re: [GENERAL] rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-10-09T19:58:25Z

    Any comments?

> 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.
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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

  12. Re: [GENERAL] rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-24T13:45:44Z

    Comments on this?  Seems INSERT should happen at the end.  Is this a
trivial change?


> 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.
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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

  13. Re: [GENERAL] rules on INSERT can't UPDATE new instance?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-29T19:54:07Z

    Jan, can you handle this TODO item?

	* Evaluate INSERT rules at end of query, rather than beginning


> 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.
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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