Обсуждение: Unexpected behavior

Hey,

I am trying to figure out some unexpected behavior in Postgresql.

When I create a rule that fires on a table after an update, and if
that rule has a SELECT statement in it, it seems to be attempting to
fire (on an empty set) regardless of how the conditional evaluates
after an update.

The result being that if I run an update on a table with such a rule,
instead of getting a message along the lines of "UPDATE (# of rows)" I
get the column names of the select statement with no rows and the
message "row number -1 is out of range 0..-1".

So first off, is having a select statement (I'm actually trying to run
a function) inside a rule that fires on an update considered bad
practice? I could do this through a trigger, but a rule just seems
more natural.

Here is some sql to setup an example of what I'm talking about:

CREATE TABLE test_table
(
  id varchar(36) NOT NULL,
  amount float8,
  CONSTRAINT test_table_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE test_table OWNER TO postgres;

CREATE OR REPLACE RULE protect_id AS
    ON UPDATE TO test_table
   WHERE new.id::text <> old.id::text DO INSTEAD  SELECT 'abc' AS test_select;

INSERT INTO test_table (id, amount) values ('a', 123);

Now, to cause the error, just run an update:

UPDATE test_table set amount = 1 where id = 'a';

You will find that it returns:
 test_select
-------------
(0 rows)

Rather than what I expect:
UPDATE 1

When that rule should never fire (the id hasn't changed). If I change
the conditional of the rule to something that must always be false
(like false, or 1 = 0), it will still behave in this manner.

So am I doing something wrong or am I seeing a bug?

Thanks,
Thomas Meeks

On 4/27/06, Strobhen <strobhen@gmail.com> wrote:
> Hey,
>
> I am trying to figure out some unexpected behavior in Postgresql.
>
> When I create a rule that fires on a table after an update, and if
> that rule has a SELECT statement in it, it seems to be attempting to
> fire (on an empty set) regardless of how the conditional evaluates
> after an update.
>
> The result being that if I run an update on a table with such a rule,
> instead of getting a message along the lines of "UPDATE (# of rows)" I
> get the column names of the select statement with no rows and the
> message "row number -1 is out of range 0..-1".
>
> So first off, is having a select statement (I'm actually trying to run
> a function) inside a rule that fires on an update considered bad
> practice? I could do this through a trigger, but a rule just seems
> more natural.

<snip>

> When that rule should never fire (the id hasn't changed). If I change
> the conditional of the rule to something that must always be false
> (like false, or 1 = 0), it will still behave in this manner.

Does anyone know what's going on here? I'm experiencing an identical
situation, and it doesn't seem logical. If it evaluates to false, why
on earth is the function result set attempting to be returned? Maybe
not a bug, but definitely unexpected behavior

Thanks,
Steve

On Tue, May 02, 2006 at 12:11:34PM -0500, Steve V wrote:
> >When that rule should never fire (the id hasn't changed). If I change
> >the conditional of the rule to something that must always be false
> >(like false, or 1 = 0), it will still behave in this manner.
>
> Does anyone know what's going on here? I'm experiencing an identical
> situation, and it doesn't seem logical. If it evaluates to false, why
> on earth is the function result set attempting to be returned? Maybe
> not a bug, but definitely unexpected behavior

A rule is a macro. This means that the expression in the rule will
always happen. If you have a rule to do a SELECT after an UPDATE, the
client will see the results of the SELECT.

You almost never want RULEs. It's not clear what you want to do, but a
trigger is more likely to do what you want (as well as a lot easier to
understand).

If you set debug_print_rewritten=on you should be able to see what is
actually being executed...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.