digoal
2024-01-25
PostgreSQL , PolarDB , DuckDB , 审计 , 触发器 , flashback query
https://github.com/supabase/supa_audit
类似trigger实现:
- 《USE hstore store table's trace record》
- 《PostgreSQL 通过old version tuple模拟update, delete闪回》
- 《PostgreSQL 闪回 - flash back query emulate by trigger》
Source Code: https://github.com/supabase/supa_audit
The supa_audit PostgreSQL extension is a generic solution for tracking changes to tables' data over time.
The audit table, audit.record_version, leverages each records primary key values to produce a stable record_id::uuid, enabling efficient (linear time) history queries.
create extension supa_audit cascade;
create table public.account(
id int primary key,
name text not null
);
-- Enable auditing
select audit.enable_tracking('public.account'::regclass);
-- Insert a record
insert into public.account(id, name)
values (1, 'Foo Barsworth');
-- Update a record
update public.account
set name = 'Foo Barsworht III'
where id = 1;
-- Delete a record
delete from public.account
where id = 1;
-- Truncate the table
truncate table public.account;
-- Review the history
select
*
from
audit.record_version;
/*
id | record_id | old_record_id | op | ts | table_oid | table_schema | table_name | record | old_record
----+--------------------------------------+--------------------------------------+----------+---------------------------------+-----------+--------------+------------+----------------------------------------+------------------------------------
1 | 57ca384e-f24c-5af5-b361-a057aeac506c | | INSERT | Thu Feb 10 17:02:25.621095 2022 | 16439 | public | account | {"id": 1, "name": "Foo Barsworth"} |
2 | 57ca384e-f24c-5af5-b361-a057aeac506c | 57ca384e-f24c-5af5-b361-a057aeac506c | UPDATE | Thu Feb 10 17:02:25.622151 2022 | 16439 | public | account | {"id": 1, "name": "Foo Barsworht III"} | {"id": 1, "name": "Foo Barsworth"}
3 | | 57ca384e-f24c-5af5-b361-a057aeac506c | DELETE | Thu Feb 10 17:02:25.622495 2022 | 16439 | public | account | | {"id": 1, "name": "Foo Barsworth III"}
4 | | | TRUNCATE | Thu Feb 10 17:02:25.622779 2022 | 16439 | public | account | |
(4 rows)
*/
-- Disable auditing
select audit.disable_tracking('public.account'::regclass); nix-shell --run "pg_13_supa_audit make installcheck" Tests are located in test/sql/ and the expected output is in test/expected/
The output of the most recent test run is stored in results/.
When the output for a test in results/ is correct, copy it to test/expected/ and the test will pass.
nix-shell --run "pg_13_supa_audit psql" Auditing tables reduces throughput of inserts, updates, and deletes.
It is not recommended to enable tracking on tables with a peak write throughput over 3k ops/second.
When querying a table's history, filter on the indexed table_oid rather than the table_name and schema_name columns.
select
*
from
audit.record_version
where
table_oid = 'public.account'::regclass::oid; 