PGHist

pghist.hist_enable
[schema].[table]_hist
[schema].[table]_changes
[schema].[table]_at_timestamp
pghist.hist_disable
SQL-выражения описаний
Функции кастомизации
Таблица истории
Общие таблицы
pghist.pghist_version
pghist.hist_sql_log

pghist.hist_enable(schema, table_name, master_table_schema, master_table_name, columns_excluded)

Процедура включает ведение истории изменений для указанной таблицы, создает таблицу истории, триггеры и функции получения данных

Параметры:

schema	-	схема таблицы (необязательный)
table_name	-	имя таблицы
master_table_schema	-	схема мастер-таблицы (необязательный)
master_table_name	-	имя мастер-таблицы (необязательный)
columns_excluded	-	исключенные столбцы (необязательный)

Примеры вызова:

call pghist.hist_enable('document');

call pghist.hist_enable('example', 'invoice');
call pghist.hist_enable('example', 'invoice_product', 'example', 'invoice');

call pghist.hist_enable('orm', 'myclass', columns_excluded => array['inserted_at','updated_at']);

[schema].[table]_hist

Представление содержит список изменений по строкам, создается при включении ведения истории.
Сортируется по первым двум столбцам, что обеспечивает хронологию по времени

Столбцы:

hist_statement_num	-	глобальный номер SQL-выражения
hist_row_num	-	номер строки в наборе данных, который изменяет SQL-выражения
hist_timestamp	-	дата-время начала выполнения SQL-выражения
hist_operation	-	операция: INSERT,UPDATE,DELETE,TRUNCATE
hist_update_columns	-	список обновленных столбцов для операции UPDATE
hist_db_user	-	пользователь базы данных
hist_app_user	-	пользователь приложения, определяется как current_setting('app.user')
hist_application_name	-	имя приложения
hist_query_text	-	текст SQL-запроса
hist_statement_id	-	ID SQL-выражения, ссылается на таблицу pghist.hist_statement
[primary key column(s)]	-	значение первичного ключа (можно быть несколько)
[column(s) of foreign key on master table]	-	значение внешного ключа на мастер-таблицу (необязательно, можно быть несколько)
[column...]_old	-	старое значение для каждого столбца таблицы [schema].[table]

Примеры вызова:

select * from example.document_hist;
select * from example.document_hist where id=2;
select * from example.document_hist where hist_operation='DELETE';

[schema].[table]_changes(where_clause text, where_param anyelement, cascade boolean)

Функция возвращает изменения в таблице (setof pghist.table_change), создается при включении ведения истории.
Сортировка по первым трем столбцам обеспечивает хронологию по времени, для получения связанных данных из нескольких таблиц рекомендуется использовать оператор union all

Параметры:

where_clause	-	условие на таблицу (необязательный)
where_param	-	значение параметра в where_clause (необязательный)
columns_immutable	-	возвращать значения неизменямых столбцов (по умолчанию false)
insert_detail	-	детализировать операцию INSERT по столбцам (по умолчанию false)
cascade	-	учитывать унаследованные таблицы (по умолчанию true)

Столбцы возвращаемого набора данных:

statement_num	-	глобальный номер SQL-выражения
row_num	-	номер строки в наборе данных, который изменяет SQL-выражения
column_num	-	номер столбца в строке
timestamp	-	дата-время операции
operation	-	операция: INSERT,UPDATE,DELETE,TRUNCATE
operation_name	-	наименование операции, задается переопределяемой функцией pghist.hist_custom_operation_name
column_name	-	имя столбца
column_comment	-	комментарий столбца
value_old	-	старое значение
value_old_desc	-	описание старого значения, можно переопредилить через процедуру pghist.hist_expression_value_desc
value_new	-	новое значение
value_new_desc	-	описание нового значение, можно переопредилить через процедуру pghist.hist_expression_value_desc
row_desc	-	описание строки, можно переопредилить через процедуру pghist.hist_expression_row_desc
db_user	-	пользователь базы данных
db_user_name	-	имя пользователя базы данных, задается переопределяемой функцией pghist.hist_custom_db_user_name
app_user	-	пользователь приложения, задается переопределяемой функцией pghist.hist_custom_app_user
app_user_name	-	имя пользователя приложения, задается переопределяемой функцией pghist.hist_custom_app_user_name
schema	-	схема
table_name	-	имя таблицы
table_comment	-	комментарий таблицы

Примеры вызова:

select * from example.document_changes();
select * from example.invoice_product_changes('id=$1',2)
  where column_name not in ('id','invoice_id')
  order by hist_id desc,column_pos

[schema].[table]_at_timestamp(transaction_start timestamptz, cascade boolean)

Функция создает временную таблицу [schema]_[table]_at_timestamp как копию исходной таблицы по состоянию на момент времени и возращает ее

Параметры:

	transaction_timestamp	-	дата-время завершения транзации, по умолчанию current_setting('pghist.at_timestamp')
	cascade	-	учитывать унаследованные таблицы (по умолчанию true)

Примеры вызова:

select * from example.document_at_timestamp(now()-interval '1 hour');

do $$
begin
  perform set_config('pghist.at_timestamp', '2024-04-06 10:00:00', true);    
  perform example.invoice_at_timestamp();    
  perform example.customer_at_timestamp();
end; 
$$;

pghist.hist_disable(schema name, table_name name)

Процедура выключает ведение истории изменений для указанной таблицы, удаляет необходимые объекты

Параметры:

	schema	-	схема таблицы (необязательный)
	table_name	-	имя таблицы

Примеры вызова:

call pghist.hist_disable('document');
call pghist.hist_disable('example', 'invoice');

SQL-выражения описаний

pghist.hist_expression_row_desc(schema name, table_name name, expression varchar)
pghist.hist_expression_value_desc(schema name, table_name name, column_name name, expression varchar)

Процедуры устанавливают выражение для описания строки/значения поля, в выражении доступна переменная $1 c строкой/значением поля

pghist.hist_expression_row_desc_current(schema name, table_name name)
pghist.hist_expression_value_desc_current(schema name, table_name name, column_name name)

Функции возвращают текущее выражение для описания строки/значения поля

pghist.hist_expression_row_desc_default(schema name, table_name name)
pghist.hist_expression_value_desc_default(schema name, table_name name, column_name name)

Функции возвращают выражение по умолчанию для описания строки/значения поля. Для строки возращается комментарий таблицы с первичным ключом, для столбца, являющегося внешним ключом, - первое текстовое поля связанной таблицы.

Параметры:

schema	-	схема таблицы
table	-	имя таблицы
column_name	-	имя столбца
expression	-	выражение, null отменяет сохраненное описание

Примеры вызова:

call pghist.hist_expression_row_desc('example', 'invoice', '''Invoice''');   
call pghist.hist_expression_row_desc('example', 'invoice_product', $$ 'Row #'||$1.id||' / '||(select name from example.product where id=$1.product_id) $$);
call pghist.hist_expression_value_desc('example', 'invoice_product', 'color', $$ case when $1='R' then 'Red' when $1='B' then 'Blue' when $1='G' then 'Green' else $1 end $$);

select pghist.hist_expression_row_desc_current('example', 'invoice_product');
select pghist.hist_expression_value_desc_default('example', 'invoice_product', 'color');

Функции кастомизации

Для кастомизации и локализации полей конкретного приложения можно определить собственные функции.
Рекомендуется в SQL-менеджере (например, pgAdmin или DBeaver) взять за основу функцию по умолчанию, переопределить ее и сохранить в схеме приложения под другим именем.

Столбцы, доступные для переопределения:

operation_name	-	наименование операции
db_user_name	-	имя пользователя базы данных
app_user	-	пользователь приложения
app_user_name	-	имя пользователя приложения
app_client_addr	-	IP адрес клиентского приложения
app_client_hostname	-	Имя хоста(компьютера) клиентского приложения

pghist.hist_column_custom_function(column_name name, custom_function name)

Процедура устанавливает функцию для получения значения поля

Параметры:

	column_name	-	имя столбца
	custom_function	-	имя функции без параметров

pghist.hist_column_custom_function_current(column_name name)

Функция возвращает текущую функцию для получения значения поля

Параметр:

column_name

имя столбца

Пример:

select pghist.hist_column_custom_function_current('db_user_name');

create or replace function myapp.user_name(db_user name) returns varchar language plpgsql as $$ 
begin 
  return (select name from myapp.users where login=db_user);                                        
end; $$;

call pghist.hist_column_custom_function('db_user_name', 'myapp.user_name');

Таблица истории pghist.hist_data$[schema]_[table]

При включении истории дополнительно к основной таблице создается таблица истории, разработчику (владельцу основной таблицы) доступна через представление [schema].[table]_hist

Столбцы:

hist_statement_id	-	ID SQL-выражения, ссылается на таблицу pghist.hist_statement
hist_row_num	-	номер строки в наборе данных, который изменяет SQL-выражения
hist_update_columns	-	список обновленных столбцов для операции UPDATE
[primary key column(s)]	-	значение первичного ключа (можно быть несколько)
[column(s) of foreign key on master table]	-	значение внешного ключа на мастер-таблицу (необязательно, можно быть несколько)
[column...]_old	-	старое значение для каждого столбца таблицы [schema].[table]

Пример использования:

select *
  from pghist.hist_data$example_invoice h
  join pghist.hist_statement s on s.id=h.hist_statement_id
  join pghist.hist_query q on q.hash=s.query_hash
  where q.text like 'do%begin%'

Общие таблицы

pghist.hist_transaction	-	транзакции
pghist.hist_query	-	SQL-запросы
pghist.hist_statement	-	SQL-выражения
pghist.hist_table	-	таблицы с историей
pghist.hist_table_column	-	столбцы таблиц с историей
pghist.hist_column_custom_function	-	кастомизированные функции столбцов

При установке создаются таблицы, которые хранят общую информацию по изменениям (дата-время транзакции и SQL-выражения, операция, SQL-запрос, пользователь, сессия и т.д.), и таблицы с настройками. Изменение общих таблиц осуществляется через хранимые процедуры, допускается только чтение. По умолчанию разработчикам (владельцам основных таблицы) общие таблицы недоступны, при необходимости доступ выдается вручную.

Таблица истории pghist.hist_data$[schema]_[table] по полю statement_id ссылается на pghist.hist_statement, которая в свою очередь ссылается на pghist.hist_transaction и pghist.hist_query.

Пример использования:

select *
  from pghist.hist_statement s
  join pghist.hist_transaction t on t.id=s.transaction_id
  join pghist.hist_query q on q.hash=s.query_hash
  where t.timestamp_commit between '2024-04-06 10:00:00' and '2024-04-06 11:00:00';

select *
  from pghist.hist_table ht
  left join pghist.hist_table_column htc on htc.schema=ht.schema and htc.table_name=ht.name;

pghist.pghist_version()

Функция возвращает версию инструмента

pghist.hist_sql_log

Все команды, выполняемые утилитой, записываются в лог-таблицу pghist.hist_sql_log

Примеры использования:

select * from pghist.hist_sql_log where schema='example' and table_name='document' order by id;
select * from pghist.hist_sql_log l
  join pghist.hist_transaction t on t.id=l.transaction_id
  where schema='example' and table_name='document'
  order by 1 desc;

История изменений таблиц в PostgreSQL

PGHist

Документация