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

Поиск
Список
Период
Сортировка
От Gavin Mu
Тема Can LISTEN/NOTIFY deal with more than 100 every second?
Дата
Msg-id 708189661001311738m691300ael4dd0a0b8c85cccd0@mail.gmail.com
обсуждение исходный текст
Ответы Re: Can LISTEN/NOTIFY deal with more than 100 every second?  (Yeb Havinga <yhavinga@gmail.com>)
Re: Can LISTEN/NOTIFY deal with more than 100 every second?  ("Greg Sabino Mullane" <greg@turnstep.com>)
Re: Can LISTEN/NOTIFY deal with more than 100 every second?  (Yeb Havinga <yebhavinga@gmail.com>)
Список pgsql-general
Hi,

I am prototyping a system which sends all INSERT/UPDATE/DELETE events
to a third party software, I do:

CREATE TABLE data (id Serial PRIMARY KEY, data VARCHAR(255));
CREATE TABLE log (op CHAR(6), id integer, data VARCHAR(255));
CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO ALSO NOTIFY logevent;
CREATE OR REPLACE FUNCTION log_event() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO log VALUES ('DELETE', OLD.id, OLD.data);
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO log VALUES ('UPDATE', NEW.id, NEW.data);
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO log VALUES ('INSERT', NEW.id, NEW.data);
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_event_trigger AFTER INSERT OR UPDATE OR DELETE ON
data FOR EACH ROW EXECUTE PROCEDURE log_event();

A simple client program is used to wait for the NOTIFY logevent and
query the log table to send the changes, then delete what he has sent.

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.

Regards,
Gavin Mu

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

Предыдущее
От: ray
Дата:
Сообщение: How to test my new install
Следующее
От: Jayadevan M
Дата:
Сообщение: Re: how to look for duplicate rows?