Обсуждение: create rule ... as on insert
I'm using PostgreSQL 7.4.2, and I'm having a little issue with rules. I created a rule to watch for any inserts to table XYZ, and registered a listener. But as I simply do a select on the table, I receive several notifications when nothing has been inserted into the table. db=# create rule xyz_inserts as on insert to xyz do notify xyz_inserted; CREATE RULE db=# listen xyz_inserted; LISTEN db=# notify xyz_inserted; NOTIFY Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 44586. All is well up until this point...I'm not too sure about the multiple notifications, but the number I receive just continues to grow and grow even though there are multiple notifications coming from the same processes, over and over. I'm the only one with full rights to this table, and the only one that is currently working on this database. There is another rule that I've created (and is currently active), that does inserts into xyz from another table, but as you can see below, there are no new inserts. db=# select * from xyz; (2 rows) Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48433. db=# select * from xyz; (2 rows) Asynchronous notification "xyz_inserted" received from server process with PID 48438. db=# select * from xyz; (2 rows) Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48434. db=# select * from xyz; (2 rows) Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48444. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48444. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48440. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48433. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48445. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48436. db=# unlisten xyz_inserted; UNLISTEN Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48436. Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48443. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48435. Asynchronous notification "xyz_inserted" received from server process with PID 48434. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48437. Asynchronous notification "xyz_inserted" received from server process with PID 48438. Asynchronous notification "xyz_inserted" received from server process with PID 48442. Asynchronous notification "xyz_inserted" received from server process with PID 48441. Asynchronous notification "xyz_inserted" received from server process with PID 48435.
On Fri, 2005-06-24 at 10:05, Omachonu Ogali wrote: > I'm using PostgreSQL 7.4.2, and I'm having a little issue with rules. > > I created a rule to watch for any inserts to table XYZ, and registered > a listener. But as I simply do a select on the table, I receive several > notifications when nothing has been inserted into the table. > > db=# create rule xyz_inserts as on insert to xyz do notify xyz_inserted; > CREATE RULE > db=# listen xyz_inserted; > LISTEN > db=# notify xyz_inserted; > NOTIFY > Asynchronous notification "xyz_inserted" received from server process with PID 48442. > Asynchronous notification "xyz_inserted" received from server process with PID 48433. > Asynchronous notification "xyz_inserted" received from server process with PID 48445. > Asynchronous notification "xyz_inserted" received from server process with PID 48436. > Asynchronous notification "xyz_inserted" received from server process with PID 48435. > Asynchronous notification "xyz_inserted" received from server process with PID 44586. > > All is well up until this point...I'm not too sure about the multiple > notifications, but the number I receive just continues to grow and grow > even though there are multiple notifications coming from the same > processes, over and over. > > I'm the only one with full rights to this table, and the only one that > is currently working on this database. There is another rule that I've > created (and is currently active), that does inserts into xyz from > another table, but as you can see below, there are no new inserts. Can you post a short "test case" that demonstrates this behaviour?
Am Freitag, 24. Juni 2005 17:05 schrieb Omachonu Ogali: > I'm using PostgreSQL 7.4.2, and I'm having a little issue with rules. > > I created a rule to watch for any inserts to table XYZ, and registered > a listener. But as I simply do a select on the table, I receive several > notifications when nothing has been inserted into the table. [...] > I'm the only one with full rights to this table, and the only one that > is currently working on this database. There is another rule that I've > created (and is currently active), that does inserts into xyz from > another table, but as you can see below, there are no new inserts. > > db=# select * from xyz; > ( 2 rows) there is no need that something is really inserted i guess. from the docs: "Presently, if a rule action contains a NOTIFY command, the NOTIFY command will be executed unconditionally, that is, the NOTIFY will be issued even if there are not any rows that the rule should apply to. For example, in CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable; UPDATE mytable SET name = 'foo' WHERE id = 42; " [sql-createrule.html] So maybe your other rule is trying to insert nothing like the UPDATE command in the example above. kind regards, janning