NOTIFY action invoked when it shouldn't be

From: Phil Dagosto <phildagosto(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: NOTIFY action invoked when it shouldn't be
Date: 2010-07-30 20:28:44
Message-ID: AANLkTikAaRtJKwOo6mBGpCgb7p5NbCC5OS6=31CEBAeF@mail.gmail.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?

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 phildagosto 2010-07-31 00:38:12 ON UPDATE RULE/NOTIFY problem
Previous Message Leon Starr 2010-07-29 22:31:55 Trouble accessing %ROWTYPE attributes returned by function