[GENERAL] 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.
Re: [GENERAL] create rule ... as on insert
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? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] create rule ... as on insert
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
