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 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 $$
 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 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 $$
 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 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 !

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s