To collect accurate data is expensive !
To structure data is very expensive !
To record accurate structured data is very very expensive !
Once data is recorded in a database, it is not a good idea to definitely delete it. It is better to archive the records we don’t want to see anymore in the database.
We will build a very simple framework for a soft delete with the PostgreSQL trigger functions. In a previous post, we developed an audit system that we will use again. We have just to modify the pre-delete trigger.
The components are :
- a schema called ‘deleted’ having the same tables that the database
- a trigger pre-delete on each table
and, inherited from the audit system :
- an additional column on each table called audit_id
- a table called audit_history
Let’s begin with the creation of the schema ‘deleted’ :
create or replace function public.create_schema_deleted() returns void as $$ declare r record; stmt varchar; begin execute 'create schema deleted;' for r in select quote_ident(table_name) as table_name from information_schema.tables where table_schema = 'public' loop stmt := 'create table deleted.' || r.table_name || ' (like public.' || r.table_name || ')'; execute stmt; end loop; execute 'grant usage on schema deleted to generic user' execute 'grant insert on all tables in schema deleted to generic_user;' end $$ language 'plpgsql' volatile; select public.create_schema_deleted();
This schema contains all the table of your production database without indexes, without pk/fk constraints, without sequences and without triggers. The role generic_user is granted to actual users.
Just after the creation of the schema, the tables are empty.
Each table has a column audit_id. It is a big serial that is incremented automatically when we insert a record in the production database.
The tables in the schema ‘deleted’ have of course also this column.
We create now the trigger function that will be executed each time an user deletes a record in the production database :
create or replace function public.pre_delete() returns trigger as $$ declare stmt varchar; begin insert into public.audit_history (table_name, operation, audit_id, user_name, audit_date) values (TG_TABLE_NAME, TG_OP, old.audit_id, current_user, now()) stmt := 'insert into deleted.' || quote_ident(TG_TABLE_NAME) || ' select * from public.' || quote_ident(TG_TABLE_NAME) || ' where audit_id = $1;'; execute stmt using old.audit_id; return old; end; $$ language 'plpgsql';
and a trigger pre-delete for each table :
create or replace function public.create_pre_delete_triggers() returns void as $$ declare r record; stmt varchar; begin for r in select table_name from information_schema.tables where table_schema = 'public' loop stmt := 'create trigger ' || quote_ident(r.table_name || '_pre_delete') || ' after insert on public.' || quote_ident(r.table_name) || ' for each row execute procedure public.pre_delete();'; execute stmt; end loop; end; $$ language 'plpgsql'; select public.create_pre_delete_triggers();
If we want to find a deleted record, we have a look in the table audit_history that has the following structure :
id_audit_history bigserial unique ,table_name text ,operation text ,audit_id bigint ,user_name text ,audit_date timestamp
And we do a select in the schema ‘deleted’ for this table and for this audit_id.
That’s simple !