History of table changes in PostgreSQL

Description
Simple example
Important qualities
Full examples
Restrictions
Video

Description

Tool PGHIST keeps history of table changes and allows to get log(audit) of changes by row, list of changes by field indicating user, time of the change, SQL query, transaction, other technical information and table as of date-time in the past (versioning). To display information in user interface, SQL expressions are defined to describe changed table rows and fields. It is possible to override the operation name and username functions.

Design and working principle

PGHIST is a schema with procedures and common tables: transactions, SQL expressions. When history is enabled (procedure pghist.hist_enable), for specified table created additional table, triggers for insert,update,delete,truncate, stored procedures and view for obtaining data. When a table is changed, triggers are fired that modify the history table. There are also event triggers that rebuild the history table and recreate the stored procedures.

Main functions and view

pghist.hist_enable([schema],[table])	-	enable history keeping
[schema].[table]_hist	-	log(audit) of changes by row, optimized for analysis
[schema].[table]_changes	-	list of changes by field, optimized for display to the user
[schema].[table]_at_timestamp	-	table at date-time in the past (versioning)

-- Create table
create table example(
  id int primary key,
  name varchar(20),
  number numeric(10,2),
  date date
);

-- Endable keeping history
call pghist.hist_enable('example');

-- Change table
insert into example values (1, 'Example', 10, current_date);
update example set number=20, date=date-1;

-- View change log by row
select * from example_hist;

-- View changes by field
select * from example_changes();

-- View table at timestamp 
select * from example_at_timestamp(now()-interval '10 second');

-- drop table example cascade;

Storage optimization - saving only old values of changed fields and primary key. The Transaction-Expression-Row storage structure matches the operation of the DBMS and minimizes redundancy.
Versatility - possible to get change log, list of only changed data from several tables and table at a point in time
Descriptions - for each table and its columns, it is possible to define SQL expressions to describe changed rows and field values. By default, descriptions are created for foreign key fields and table rows
Inheritance - stored procedures have parameter "cascade" that allows you to get data with or without inheritance
Transaction and SQL statements - changes have a reference to SQL statements, that references a transaction. Can get all the changes made within a single transaction or rows within one expression
Indexes - for a history table, an index is built on the primary key column(s), for a table at a point in time - standard indexes on columns
Condition (optional) - when getting a list of changes, you can specify a condition with or without parameter
Autocorrection - when performing DDL operations on a table (alter table, create index, etc.), a trigger fires, that corrects the history keeping. When a table is deleted, its history is also deleted

Log(audit) of changes by row

-- Create schema and table, enable history, change data 

drop schema if exists example cascade;
create schema example;

create table example.document(
  id int primary key,
  number varchar(10),
  amount numeric(10,2)
);

call pghist.hist_enable('example', 'document');

insert into example.document values (11, '#10', 100);
insert into example.document values (12, '#20', 200);
update example.document set number='#20/2',amount=210 where id=12;
update example.document set amount=220 where id=12;
delete from example.document where id=11;

-- Select full info
select * from example.document_hist;

-- Select partial info by id
select hist_timestamp,hist_operation,hist_db_user,amount_old 
  from example.document_hist
  where id=12 and (hist_operation!='UPDATE' or 'amount'=any(hist_update_columns))
  order by hist_statement_id;

-- Select partial info with current values
select * from example.document_hist
union all 
select null,'[CURRENT_VALUES]',null,null,null,null,null,null,* from example.document
order by id,1

-- Create extended view and grant select on it to developer
-- (alternatively to 'grant select on all tables in schema pghist to developer');  
select *
  from pghist.hist_example_document h  
  join pghist.hist_statement s on s.id = h.hist_statement_id
  join pghist.hist_query q on q.hash = s.query_hash
  join pghist.hist_transaction t on t.id = s.transaction_id
  order by h.hist_statement_id, h.id;
 
create or replace view example.document_hist_ext as  
  select h.id,t.xid,t.xact_start,t.db_client_addr,q.text query_text
    from pghist.hist_example_document h  
    join pghist.hist_statement s on s.id = h.hist_statement_id
    join pghist.hist_query q on q.hash = s.query_hash
    join pghist.hist_transaction t on t.id = s.transaction_id
    order by h.hist_statement_id, h.id;
   
select * from example.document_hist_ext where id=12;   

grant select on example.document_hist_ext to developer_1;

List of changes by field

-- Developer create schema and tables, enable history, change default desc, grant privileges to user

drop schema if exists example cascade;
create schema example;

create table example.customer(
  id int primary key,
  name varchar(100) not null
);
insert into example.customer values (1,'Horns'),(2,'Hooves');

create table example.invoice(
  id int primary key,  
  number varchar(10),
  date date,  
  customer_id int references example.customer(id),
  amount numeric(20,2) 
);
comment on table example.invoice is 'Invoice';
comment on column example.invoice.id is 'Identifier';
comment on column example.invoice.number is 'Number';
comment on column example.invoice.date is 'Date';
comment on column example.invoice.customer_id is 'Сustomer';
comment on column example.invoice.amount is 'Amount';

create table example.product(
  id int primary key,
  name varchar(100) not null,
  code varchar(10) not null
);

create table example.invoice_product(
  id serial primary key,
  invoice_id int references example.invoice(id),   
  product_id int references example.product(id),  
  quantity int,
  color char(1) check (color in ('R','G','B'))  
);
comment on table example.invoice_product is 'Product of invoice';
comment on column example.invoice_product.id is 'Identifier';
comment on column example.invoice_product.invoice_id is 'Invoice';
comment on column example.invoice_product.product_id is 'Product';
comment on column example.invoice_product.quantity is 'Quantity';
comment on column example.invoice_product.color is 'Color';
create index on example.invoice_product(invoice_id);

grant usage on schema example to user_1;  
grant select,insert,update,delete on example.invoice,example.product,example.invoice_product to user_1;

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

grant execute on function example.invoice_changes,example.invoice_product_changes to user_1;

-- User fills in tables, looks changes
insert into example.invoice values (12,'#20', current_date, 1, 120.00);
update example.invoice set customer_id=2 where id=12;
insert into example.product(id,name,code) values (101,'Pensil','030'),(102,'Notebook','040');
insert into example.invoice_product(id, invoice_id, product_id, quantity, color) values (1,12,101,1000,'R'),(2,12,101,10,'G');

do $$
begin
  update example.invoice_product set quantity=quantity+1,color='B' where id=1;
  delete from example.invoice_product where id=2;
  update example.invoice set amount=150 where id=12;
end; 
$$;

-- Select full data, first three columns provide chronological
select * from example.invoice_changes() order by 1,2,3;

-- Select full data with column id (immutable), insert detail by columns  
select * from example.invoice_changes(insert_detail=>true, columns_immutable=>true) order by 1,2,3; 

-- Select partial data by id
select * from example.invoice_changes('id=12') order by 1,2,3;

-- Select changes in two related tables, fast execution provides index invoice_product(invoice_id)
select * from example.invoice_changes('id=$1',12)
union all
select * from example.invoice_product_changes('invoice_id=$1', 12)
order by 1,2,3

-- Developer create function that displays information in custom format for user
create or replace function example.invoice_changes_ui(id int) returns setof pghist.table_change language sql security definer as $$
  select * from example.invoice_changes('id=$1',id,insert_detail=>true)
  union all
  select * from example.invoice_product_changes('invoice_id=$1', id)
  order by 1,2,3;
$$;

-- Set description expression for columns row_desc and value_desc   
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 $$);

-- Replace function for column db_user_name
create or replace function pghist.hist_custom_db_user_name(db_user name) returns varchar language plpgsql as $$
begin 
  return '['||db_user||']';
end; $$;      

-- Select changes for user interface 
select * from example.invoice_changes_ui(12);
select timestamp,operation_name,db_user_name,row_desc,column_comment,value_old_desc,value_new_desc from example.invoice_changes_ui(12);

Table at date-time in the past (versioning)

--  Create schema and tables, enable history
drop schema if exists example cascade;
create schema example;

create table example.customer(
  id int primary key,
  name varchar(100) not null
);
insert into example.customer values (1,'Horns'),(2,'Hooves');

create table example.document(
  id int primary key,
  number varchar(10),
  date date
);

create table example.invoice(
  primary key (id),
  customer_id int references example.customer(id),
  amount numeric(20,2)
) inherits (example.document);
create index on example.invoice(customer_id);

call pghist.hist_enable('example', 'customer');
call pghist.hist_enable('example', 'document');
call pghist.hist_enable('example', 'invoice');

-- Enter data into tables   
insert into example.document values (11,'#10', current_date);
insert into example.invoice  values (12,'#20', current_date,   1, 120.00);
insert into example.invoice  values (13,'#30', current_date-1, 2, 130.00);

-- Select data in past
select * from example.document_at_timestamp(now()-interval '10 second');

-- Erroneous update and recovery
update example.invoice set customer_id=2,amount=300 where date=current_date;

update example.invoice i
    set amount = h.amount
  from example.invoice_at_timestamp('2024-04-06 10:00:00') h
  where i.id = h.id
    and i.date=current_date;

-- Сombination log and versioning   
select * 
  from example.invoice_at_timestamp('2024-04-06 10:00:00')
  where id in (
          select id from example.invoice_hist
            where hist_timestamp>'2024-04-06 10:00:00'
              and hist_db_user=current_user
        );

-- Сomplex query in past        
select * 
  from example.invoice i
  join example.customer c on c.id=i.customer_id;
 
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; 
$$;
 
select * 
  from example_invoice_at_timestamp i
  join example_customer_at_timestamp c on c.id=i.customer_id;

Update primary key values - updating the values of primary key columns is not supported (exception will be raised)
Extended names - extended table and column names (in double quotes) not supported
Drop columns - when a column is dropped or changed, the view [schema].[table]_hist is dropped and created again (PostgreSQL support only add columns to a view), recommended to create a stored function(wrapper) with a dynamic query
Significant table changes - when the table is significantly changed (for example, reassigning the primary key to another field), the history table may be incorrectly rebuilded, recommended to remove and create history again (pghist.hist_disable and pghist.hist_enable)

PGHist

History of table changes in PostgreSQL

PGHist

About