problem with ON UPDATE rule

From: Phil Dagosto <phildagosto(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: problem with ON UPDATE rule
Date: 2010-08-02 17:18:16
Message-ID: AANLkTinyVHkfLaZGY6nNAAN5hig2FXnDibES4UJGmXvP@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'm new to Postgres and I'm not really a database expert but I was wondering
if someone could help me out with this issue.

I am trying to use event notifications controlled by an ON UPDATE rule. In
the table I'm interested in I have created a rule that should be invoked
when a particular column is updated to a value of 'complete':

Table "public.table1"
Column | Type | Modifiers
------------+---------+-------
----
run_id | text |
run_status | text |
flag | boolean |
Rules:
r1 AS
ON UPDATE TO table1
WHERE new.run_status = 'complete'::text DO INSERT INTO table2 (r_id,
r_status)
VALUES (new.run_id, new.run_status)

The second table is supposed to have a row inserted into it when the
conditional update takes place and has an ON INSERT rule that fires the
notification:

Table "public.table2"
Column | Type | Modifiers
----------+------+-----------
r_id | text |
r_status | text |
Rules:
r2 AS
ON INSERT TO table2 DO
NOTIFY table2

To test this I am using the example program in the section of the Postgres
documentation that discusses event notification and libpq.

And, this all works, when I update table1 and set a particular row's value
for run_status to "complete" a row is inserted into table2, the rule on
table2 fires and the example program, which is listening for "table2", is
notified as expected.

The problem is, this also happens when an update to table1 that sets the
value of run_status to something other than "complete". In these cases, no
row is inserted into table2 but the notification is issued just the same and
is received by the example program.

How is the notification being issued if no row is being inserted into
table2?

By the way I have also tried modifying the WHERE clause in R1 to:

WHERE old.run_status <> new.run_status AND new.run_status = 'complete'

but this did not change the behavior at all.

I am using Postgres 8.3.11 and before you tell me to upgrade I do not have
any choice about that. I'm working with a vendor system that uses Postgres
and I have no control (read ZERO influence) on what version of Postgres is
being used.

Thanks in advance for any help or insight into how I can debug this problem.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-08-02 17:34:29 Re: problem with ON UPDATE rule
Previous Message Jason Dixon 2010-08-02 16:04:42 Register now for Surge 2010