rules on INSERT can't UPDATE new instance?

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?

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

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

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

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