Re: Can LISTEN/NOTIFY deal with more than 100 every second?

Hi, Greg,

Thanks for your reply, and I described my case more clearly inline.

Regards, Gavin Mu

2010/2/1 Greg Sabino Mullane <greg(at)turnstep(dot)com>:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> I am prototyping a system which sends all INSERT/UPDATE/DELETE events
>> to a third party software, I do:
>
> ...
>> CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO
>> ALSO NOTIFY logevent;
>
> This is better off as a statement-level trigger, so you won't have
> to issue one notify per insert, but one per group of inserts.
I need all detail info of a row to be logged, though I defined only
one data column in this case.
>
> It also looks like you might be reinventing a wheel - maybe can you do
> what you want with Slony's log shipping?
Thanks for your reminder. I have quickly gone through the documents of
Slony-I today, though I can't fully understand how it works, it seems
that the slon does this work by polling when a threshold value is
exceeded. Maybe this is a good solution when the performance is not
satisfied by LISTEN/NOTIFY signal.
>
>> When I inserted data to TABLE data with the rate of about 25 every
>> second, the client can receive the notifies without any problem, and
>> when I use 3 similar programs to feed data, which means about 75
>> events every second, I found that Postgres didn't send NOTIFY
>> opportunely, since the client do SELECT query every several hundreds
>> seconds, which is too long to be acceptable.
>>
>> So what I want to know is, is there anything wrong with my idea? and
>> how frequence can LISTEN/NOTIFY support? Thanks.
>
> The question is, how often does the other side need to get notified? If
> things are coming in that fast, there is no need for the client to
> check for notifies, just have it continously poll your log table.

I preferred a 'real-time' solution since any INSERT event is urgent,
you can understand it as an alert event which need to be reported
immediately. That's also why I don't like polling.
>
> We can probably answer your question better if it were clearer what your
> program is doing and where it is failing, particularly this bit:
>
> "the client do SELECT query every several hundreds seconds"

Sorry that I didn't tell this clearly. My demo client waits on the
select(3C) for the notifies, once received, it does select query on
log table and prints the result on the screen, then delete them from
log table. When the INSERT rate is about 75 every second, the client
didn't have any output for about several hundreds of seconds,
meantime, I can see the rows in log table increased persistently to
about 30K+ before the client deleted them from 'SELECT COUNT(*) from
log' in psql. I guess the backend can't deal with the signals on time.
>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201002010912
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAktm4f8ACgkQvJuQZxSWSshLUQCg2/TLbE0L8o6SncclQg3eNtVX
> UUsAnjRx9Ki6j0ATebUqTXjEs9zMrQIu
> =1cnk
> -----END PGP SIGNATURE-----
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>