Thread: About dropped notifications

The docs state:

"NOTIFY behaves like Unix signals in one important
respect: if the same notification name is signaled
multiple times in quick succession, recipients may get
only one notification event for several executions of
NOTIFY. So it is a bad idea to depend on the number of
notifications received. Instead, use NOTIFY to wake up
applications that need to pay attention to something,
and use a database object (such as a sequence) to keep
track of what happened or how many times it happened."

I'm considering setting up a script that listens for
notifications for a table and if a row is deleted the
script will delete that row's corresponding files. If
there are thousands of rows in the table, and I do
"delete from table", or even "delete from table where
id >1000 and id<2000", will the script be notified of
the deletion of each and every row (and subsequently
be able to delete that row's files), or will only one
notify event be received (or some number less than the
actual number of rows deleted)?

Thanks,
CSN




____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

CSN <cool_screen_name90001@yahoo.com> writes:
> I'm considering setting up a script that listens for
> notifications for a table and if a row is deleted the
> script will delete that row's corresponding files. If
> there are thousands of rows in the table, and I do
> "delete from table", or even "delete from table where
> id >1000 and id<2000", will the script be notified of
> the deletion of each and every row (and subsequently
> be able to delete that row's files), or will only one
> notify event be received (or some number less than the
> actual number of rows deleted)?

Depends where you are doing the notify from ... but I think
with the current implementation, a transaction will emit only
one notify per notify event name, even if NOTIFY is executed
many times within the transaction.

            regards, tom lane

tgl@sss.pgh.pa.us (Tom Lane) writes:
> CSN <cool_screen_name90001@yahoo.com> writes:
>> I'm considering setting up a script that listens for
>> notifications for a table and if a row is deleted the
>> script will delete that row's corresponding files. If
>> there are thousands of rows in the table, and I do
>> "delete from table", or even "delete from table where
>> id >1000 and id<2000", will the script be notified of
>> the deletion of each and every row (and subsequently
>> be able to delete that row's files), or will only one
>> notify event be received (or some number less than the
>> actual number of rows deleted)?
>
> Depends where you are doing the notify from ... but I think
> with the current implementation, a transaction will emit only
> one notify per notify event name, even if NOTIFY is executed
> many times within the transaction.

An interesting question is whether or not the relevant tuple in
pg_listener gets invalidated once, or whether doing 2500 attempted
NOTIFY requests blows through 2500 copies.
--
"cbbrowne","@","cbbrowne.com"
http://www.ntlug.org/~cbbrowne/internet.html
"As  long as  each  individual is  facing  the TV  tube alone,  formal
freedom poses no threat to privilege."  --Noam Chomsky

Chris Browne <cbbrowne@acm.org> writes:
> tgl@sss.pgh.pa.us (Tom Lane) writes:
>> with the current implementation, a transaction will emit only
>> one notify per notify event name, even if NOTIFY is executed
>> many times within the transaction.

> An interesting question is whether or not the relevant tuple in
> pg_listener gets invalidated once, or whether doing 2500 attempted
> NOTIFY requests blows through 2500 copies.

Once.  (Per transaction...)

            regards, tom lane

CSN <cool_screen_name90001@yahoo.com> writes:

> I'm considering setting up a script that listens for
> notifications for a table and if a row is deleted the
> script will delete that row's corresponding files.

One way to deal with this would be to have a boolean flag in the table like
"deleted". Update that flag to true, and have a partial index "where deleted".

Then your daemon can quickly query "select file_name where deleted", process
the files and actually complete the deletion. All your other queries need to
test "where not deleted" or go through a view with a clause like that.

--
greg