ON UPDATE RULE/NOTIFY problem

From: phildagosto(at)gmail(dot)com
To: pgsql-novice(at)postgresql(dot)org
Subject: ON UPDATE RULE/NOTIFY problem
Date: 2010-07-31 00:38:12
Message-ID: 001636c9267aabb604048ca42ec3@google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 on ANY update to table1, that is, an
update 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.

Browse pgsql-novice by date

  From Date Subject
Next Message Sumeet Jauhar 2010-07-31 05:33:01 Urgent help needed Question on PQexec
Previous Message Phil Dagosto 2010-07-30 20:28:44 NOTIFY action invoked when it shouldn't be