Обсуждение: problem with ON UPDATE rule
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
------------+---------+-------
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.
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.
Phil Dagosto <phildagosto@gmail.com> writes:
> I am trying to use event notifications controlled by an ON UPDATE
> rule.
Rules don't work the way you are hoping --- in particular, the NOTIFY
will fire whether or not any individual rows get inserted. You might
have better luck using a trigger instead.
regards, tom lane