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 delete it definitely. 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 PostgreSQL trigger functions. In a previous post, we developed an audit system that we will use again. We have to modify just the pre-delete trigger.
The components are :
- a schema 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 database without indexes no pk/fk constraints.
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 sequence is automatically created when we define this column in the create table statement.
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 gives the name of table, the user who deleted, the timestamp of deletion and the audit_id. And we do a select in the schema ‘deleted’ for this table and for this audit_id.
It is simple and it works !