Detecting if current transaction is modifying the database

Поиск
Список
Период
Сортировка
От Christian Ohler
Тема Detecting if current transaction is modifying the database
Дата
Msg-id CAOsiKEL-q49K9vsYwxvcG6reo-KNG98twnahtVaks_CynKDG8A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Detecting if current transaction is modifying the database  (Alex Ignatov <a.ignatov@postgrespro.ru>)
Список pgsql-general
Hi,

I'm trying to find a way to have Postgres tell me if the current transaction would modify database if I committed it now.  I can live with a conservative approximation (sometimes – ideally, rarely – get a "yes" even though nothing would be modified, but never get a "no" even though there are pending modifications).  It's acceptable (probably even desirable) if a no-op write operation like "UPDATE foo SET bar = 1 WHERE bar = 1" is considered a modification.

(The use case is an audit log mechanism vaguely similar to pgMemento.)



> If a permanent ID is assigned to the transaction (which normally happens
> only if the transaction changes the state of the database), it also holds
> an exclusive lock on its permanent transaction ID until it ends.

makes me think that I can perhaps do it as follows:

SELECT count(*) FROM pg_locks WHERE pid=pg_backend_pid() AND locktype='transactionid' AND mode='ExclusiveLock' AND granted;

Is that right?  "Permanent transaction ID" refers to the XID, correct?  Are there other, better ways?  Are there ways to avoid false positives due to temp tables?

Thanks in advance,
Christian.

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

Предыдущее
От: Alex Ignatov
Дата:
Сообщение: Re: fun fact about temp tables
Следующее
От: Alex Ignatov
Дата:
Сообщение: Re: Detecting if current transaction is modifying the database