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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.