How to safely delete records ?

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 $$
 r	record;
 stmt	varchar;

 execute 'create schema deleted;'
 for r in
   quote_ident(table_name) as table_name
   table_schema = 'public'


   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 $$
 stmt 	varchar;

 insert into public.audit_history
  (table_name, operation, audit_id, user_name, audit_date)
  (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 $$
 r	record;
 stmt	varchar;
 for r in 
    select table_name 
    from information_schema.tables 
    where table_schema = 'public'

    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 !