problem with ON UPDATE rule

Поиск
Список
Период
Сортировка
От Phil Dagosto
Тема problem with ON UPDATE rule
Дата
Msg-id AANLkTinyVHkfLaZGY6nNAAN5hig2FXnDibES4UJGmXvP@mail.gmail.com
обсуждение исходный текст
Ответы Re: problem with ON UPDATE rule  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Jason Dixon
Дата:
Сообщение: Register now for Surge 2010
Следующее
От: Tom Lane
Дата:
Сообщение: Re: problem with ON UPDATE rule