Re: Change in rule behavior?

Hello,

I need use a notify in rules with where conditions. In 7.1.3, i found a
bug and i fix it. Then I downloaded the version 7.2 to verify the bug
and to send them the patch, but the behavior had been changed.

test=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

The tables:

test=# \d rd
Table "rd"
Column | Type | Modifiers
--------+----------+-----------
a | smallint |
b | smallint |
c | text |

Table "ri"
Column | Type | Modifiers
--------+----------+--------------
d | smallint |
e | text |
a | smallint | default 1000
b | smallint | default 2000

When i try to create the rule:

CREATE RULE upd_rd
AS ON
UPDATE TO ri
WHERE
NEW.a IS NOT NULL
AND NEW.b IS NOT NULL
DO (
notify foo;
update rd set a=new.a,b=new.b
where a=old.a and b=old.b;
)

the output was:

psql:sql/rd_ri/bug_upd.sql:11: pqReadData() -- backend closed the
channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:sql/rd_ri/bug_upd.sql:11: connection to server was lost

I fix it with the attached patch and the rule works well. But in 7.2b2 i
can't do it...

With the same rule, when i when I try to create it in 7.2b3 the output
was:

test=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.2b3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

psql:sql/rd_ri/bug_upd.sql:11: ERROR: Rules with WHERE conditions may
only have SELECT, INSERT, UPDATE, or DELETE actions

Why was changed?
Is it possible to maintain the previous behavior with my correction?

Thanks in advance,
Sergio

Sergio Pili <sergiop(at)sinectis(dot)com(dot)ar> writes:
> psql:sql/rd_ri/bug_upd.sql:11: ERROR: Rules with WHERE conditions may
> only have SELECT, INSERT, UPDATE, or DELETE actions
> Why was changed?
> Is it possible to maintain the previous behavior with my correction?

Because it didn't work. Your patch might prevent a coredump, but it
doesn't make the rule work correctly. See the comment on the error
message:

/*
* We cannot support utility-statement actions (eg NOTIFY)
* with nonempty rule WHERE conditions, because there's no way
* to make the utility action execute conditionally.
*/
if (top_subqry->commandType == CMD_UTILITY &&
stmt->whereClause != NULL)
elog(ERROR, "Rules with WHERE conditions may only have SELECT, INSERT, UPDATE, or DELETE actions");

What actually happened in your patched 7.1 was that the NOTIFY message
was sent regardless of whether the WHERE condition was true or not.
If you want that behavior, it's easy enough to get: put the NOTIFY in a
separate rule that has no WHERE.

regards, tom lane

> What actually happened in your patched 7.1 was that the NOTIFY message
> was sent regardless of whether the WHERE condition was true or not.
> If you want that behavior, it's easy enough to get: put the NOTIFY in a
> separate rule that has no WHERE.

Oh!, you are all right of course.

In fact I not discovered this change with the Notify but with the
Deactivate Rule...
We proposed this command and you responded that you needed an example.
We send it and we don't receive answer. I take advantage to wonder for
that topic...

Using the Deactivate Rule that we propose, doesn't care that the same
one is executed regardless of whether the WHERE condition was true or
not, but that would be already a detail of the new command and not
something general as me it proposed.

Thanks and regards

Sergio.