Обновления клиента PostgreSQL в реальном времени на основе уведомления

Я пытаюсь создать клиент -> система уведомлений/обновления базы данных. Клиент написан на Java/Hibernate. Чтобы уведомить клиента, я использую триггер:

CREATE OR REPLACE FUNCTION notifyUsers() RETURNS TRIGGER AS $$
DECLARE 
    data integer;
    notification json;

BEGIN

    IF (TG_OP = 'DELETE') THEN
        data = OLD.id;
    ELSE
        data = NEW.id;
    END IF;

    -- Contruct the notification
    notification = json_build_object(
                      'table',TG_TABLE_NAME,
                      'action', TG_OP,
                      'id', data);


    -- Execute pg_notify(channel, notification)
    PERFORM pg_notify('events',notification::text);

    -- Result is ignored since this is an AFTER trigger
    RETURN NULL; 
END;
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER notifyUsersAccountData AFTER INSERT OR UPDATE OR DELETE ON document FOR EACH ROW EXECUTE PROCEDURE notifyUsers();

но когда уведомление получено клиентом, я не могу определить, кто его запускает. Могу ли я отправить какой-либо дополнительный параметр, который будет определять, кто его запускает (session_id? В моем приложении есть настраиваемые пользователи, но я хотел бы избежать отправки User.id, поскольку вход в систему для одного и того же пользователя на 2 ПК нарушит мою систему уведомлений / обновлений)

После получения уведомления клиент обновляет значение, «идентификатор сеанса» необходим, чтобы определить, внес ли изменение «ЭТОТ клиент» (конечно, в этом случае клиент не должен обновляться) или кто-то другой (в этом случае обновление должно применяться)

РЕДАКТИРОВАТЬ - - -

Я хотел бы обновить эту тему, так как она не решена. Решение с вызовом pg_backend_pid иногда ломается, в результате чего «PID вызывающей стороны, выполняющей SQL-запрос», не совпадает с PGNotification.getPid. Код:

public int getSessionPid() {
int lResult = -1;
try
{
    connect();
    Session session = _sessionFactory.getCurrentSession();
    Transaction lTransaction = session.beginTransaction();
    SQLQuery lQuery = session.createSQLQuery("SELECT pg_backend_pid()");

    List lResultQuery = lQuery.list();
    lResult = Integer.parseInt(lResultQuery.get(0).toString());
    lTransaction.commit();
    }catch(org.hibernate.SessionException e)
    {
        e.printStackTrace();
    }

    return lResult;
}

и код, где появляется сравнение:

int lPid = Facade.databaseConnector.warehouse.getSessionPid();
for(int i = 0; i < notifications.length; ++i)
{
    if(lPid == notifications[i].getPID())
...

Кроме того, я вижу, что в таблице, где видны текущие бэкэнд-соединения select * from pg_stat_activity, есть 3 соединения на один экземпляр моей программы.

  1. SELECT 1 - просто для обновления с уведомлениями, т.к. без "прикосновения" к базе данных уведомления не сработают
  2. одно соединение связано со спящим режимом
  3. одно соединение связано с SQL-запросами в базе данных (например, вставка/обновление/удаление строк и т. д.)

Все внутренние соединения имеют разные порты и PID в файле pg_stat_activity.

Любые идеи?


postgresql java hibernate sql
person Bartek Szczypien    schedule 17.03.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Документация PostgreSQL для pg_notify, кажется, решает эту проблему.

Обычно клиент, который выполняет NOTIFY, сам прослушивает тот же канал уведомлений. В этом случае он вернет событие уведомления, как и все другие сеансы прослушивания. В зависимости от логики приложения это может привести к бесполезной работе, например к чтению таблицы базы данных для поиска тех же обновлений, которые только что были записаны в этом сеансе. Можно избежать такой дополнительной работы, заметив, совпадает ли PID серверного процесса уведомляющего сеанса (предоставленный в сообщении о событии уведомления) с PID собственного сеанса (доступный из libpq). Когда они совпадают, событие уведомления — это собственная работа, возвращающаяся в норму, и ее можно игнорировать.

Таким образом, ваш клиент получает полезную нагрузку следующим образом:

SELECT pg_notify('foo', 'payload');
Asynchronous notification of 'foo' received from backend pid 13976
    Data: payload

Вы можете просто разобрать этот pid из него. Думаю, это похоже на session_id.

Вы можете получить свой текущий pid сеанса следующим образом:

SELECT pg_backend_pid();
13976
person Łukasz Kamiński    schedule 17.03.2017
comment
Было бы неплохо добавить ссылку на jdbc.postgresql.org/documentation/head/listennotify .html к этому ответу. - person Laurenz Albe; 17.03.2017
comment
Хорошо, это работает хорошо, но есть ли способ избежать вызова: SELECT pg_backend_pid(); каждый раз, когда я получаю уведомление? - person Bartek Szczypien; 17.03.2017
comment
Если ваш сеанс сохраняется во время прослушивания событий, вы можете получить pid один раз и сохранить его в какой-либо переменной. Если вы не знаете, сохраняется ли он, я бы проверил его, сохранив все pids в массиве и проверив его содержимое или просто сохранив oldPid и сравнив его со свежим. - person Łukasz Kamiński; 17.03.2017

arrow_upward
0
arrow_downward

Мне потребовалось некоторое время, чтобы отшлифовать решение, но я нашел действительно классное решение. Я хотел быть независимым от количества открытых сеансов в одном экземпляре моего программного обеспечения и быть уверенным, что экземпляр программного обеспечения правильно определяет, исходит ли триггер от этого экземпляра или нет (даже если один экземпляр может открывать более одного сеанса, и на на одном ПК можно открыть более одного экземпляра). И вот оно:

  1. При входе в базу данных я использую Application_name для имени записи приложения в сеансе (в базе данных): jdbc:postgresql://localhost:5432/?ApplicationName=based_on_run_time_hash, вы можете увидеть это, используя SELECT * from pg_stat_activity
  2. в функции триггера у меня есть

EXECUTE 'SELECT application_name from pg_stat_activity where pid IN (SELECT pg_backend_pid())' INTO session_app_name; notification = json_build_object( 'session', session_app_name, ...);

и когда экземпляр программного обеспечения ПК получает триггер, он сравнивает, совпадает ли локальное имя_приложения с именем, отправленным в json («сеанс»), и таким образом я уверен, что триггер был создан действием, которое пришло из этого экземпляра

person Bartek Szczypien    schedule 18.02.2018