# Overriding default DELETE behavior with logical deletes

**URL:** <https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988>\
**Category:** Rails\
**Created:** [May 19, 2006, 1:21am UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988 "2006-05-19T01:21:31Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![kigster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/df705f/32.png) [@kigster](https://www.ruby-forum.com/u/kigster)\
**Post date:** [May 19, 2006, 1:21am UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988/1 "2006-05-19T01:21:31Z")

</div>

Hello!

I am trying to figure out how to implement _logical deletes_ instead of  
physical deletes using ActiveRecord. Basically, in many applications  
that deal with E-Commerce, you really can’t physically delete almost  
anything, because records must be kept for auditing and customer service  
tracking purposes.

In the past, I’ve implemented logical deletes as follows:

1. Oracle implementation:

Each table that must implement logical deletes has a view, eg:

USERS\_RAW =\> real table, has columns:  
is\_deleted(boolean,default false)  
date\_deleted(timestamp, default null)

USERS =\> is a view on USERS\_RAW (where isdeleted = false)

Oracle allows updating such a view without any extra triggers, so from  
RoR point of view this is completely transparent.

PostgreSQL allows updating views, but with extra triggers, which is ugly  
and not what I would like to do.

1. Hibernate Implementation

In hibernate when you map a table you can specify that any query on this  
table must also have an extra clause appended to it, so adding  
“is\_deleted = false” makes it work out of the box (except for primary  
key fetches unfortunately, but this can be overriden in a model  
superclass).

1. Rails Implementation

What I would like is that all finders and by-id look ups automatically  
append “is\_deleted == false” to all SQL queries for a specific set of  
classes (which could subclass eg “LogicallyDeletable” model super  
class).

Any pointers on how to implement this elegantly are much appreciated.

Thanks,  
Konstantin

---

<div class="post-metadata">

**Author:** ![kigster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/df705f/32.png) [@kigster](https://www.ruby-forum.com/u/kigster)\
**Post date:** [May 19, 2006, 1:26am UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988/2 "2006-05-19T01:26:43Z")

</div>

On Friday, May 19, 2006, at 1:21 AM, Konstantin G. wrote:

> 1. Oracle implementation:  
> RoR point of view this is completely transparent.  
> superclass).  
> Thanks,  
> Konstantin
> 
> –  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

look for the acts\_as\_paranoid plugin.

\_Kevin

---

<div class="post-metadata">

**Author:** ![kigster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/df705f/32.png) [@kigster](https://www.ruby-forum.com/u/kigster)\
**Post date:** [May 19, 2006, 3:02am UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988/3 "2006-05-19T03:02:14Z")

</div>

Put an on delete do instead rule on the base table. Easy, and not ugly.

---

<div class="post-metadata">

**Author:** ![kigster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/df705f/32.png) [@kigster](https://www.ruby-forum.com/u/kigster)\
**Post date:** [May 19, 2006, 9:11am UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988/4 "2006-05-19T09:11:25Z")

</div>

On 5/19/06, Konstantin G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Hello!
> 
> I am trying to figure out how to implement _logical deletes_ instead of  
> physical deletes using ActiveRecord.  
> [snip]  
> PostgreSQL allows updating views, but with extra triggers, which is ugly  
> and not what I would like to do.

## What would you say about pure pl/pgSQL acts\_as\_paranoid ? :)))

BEGIN;  
CREATE AGGREGATE array\_accum (  
sfunc = array\_append,  
basetype = anyelement,  
stype = anyarray,  
initcond = ‘{}’ );

CREATE FUNCTION acts\_as\_paranoid(\_table\_name text, \_view\_name text,  
\_pkey\_name text, \_is\_deleted\_column\_name text) returns void as $$  
DECLARE  
\_column\_list TEXT;  
\_update\_list TEXT;  
\_insert\_list TEXT;  
\_stmt\_text TEXT;  
BEGIN  
SELECT  
array\_to\_string(col\_list, ‘,’) as column\_list,  
array\_to\_string(new\_list, ‘,’) as insert\_list,  
array\_to\_string(upd\_list, ‘,’) as update\_list  
FROM  
(SELECT  
array\_accum(attname) as col\_list,  
array\_accum(‘NEW.’||attname) as new\_list,  
array\_accum(attname || ’ = NEW.'||attname) as upd\_list  
FROM pg\_attribute JOIN pg\_class c ON (attrelid = c.oid)  
WHERE NOT attisdropped AND attnum \> 0 AND relname = \_table\_name  
AND attname \<\> \_is\_deleted\_column\_name ) as A  
INTO \_column\_list, \_insert\_list, \_update\_list;

EXECUTE 'CREATE VIEW ’ || \_view\_name ||  
’ AS SELECT ’ || \_column\_list ||  
’ FROM ’ || \_table\_name || ’ WHERE NOT ’ ||  
\_is\_deleted\_column\_name;

EXECUTE 'CREATE RULE ’ || \_view\_name || '\_del AS ON DELETE TO '||  
\_view\_name ||  
’ DO INSTEAD UPDATE ’ || \_table\_name ||  
’ SET '||\_is\_deleted\_column\_name || ’ = true WHERE ‘||  
\_pkey\_name || ’ = OLD.’|| \_pkey\_name;

EXECUTE 'CREATE RULE ’ || \_view\_name || '\_ins AS ON INSERT TO ‘||  
\_view\_name ||  
’ DO INSTEAD INSERT INTO ‘|| \_table\_name ||  
‘(’||\_column\_list || ‘) VALUES (’||\_insert\_list||’)’;

EXECUTE 'CREATE RULE ’ || \_view\_name || '\_upd AS ON UPDATE TO '||  
\_view\_name ||  
’ DO INSTEAD UPDATE '|| \_table\_name || ’ SET ‘||\_update\_list  
||  
’ WHERE ’ || \_pkey\_name || ’ = OLD.’ || \_pkey\_name;

RETURN;  
END$$ language ‘plpgsql’;

CREATE FUNCTION acts\_as\_paranoid (\_table\_name text, \_view\_name text)  
returns void as $$  
BEGIN  
PERFORM acts\_as\_paranoid( $1 , $2, ‘id’, ‘is\_deleted’);  
RETURN;  
END$$ language ‘plpgsql’;

– EXAMPLE OF USAGE  
create table a\_data (  
id serial primary key,  
my\_data text not null,  
is\_deleted boolean not null default false  
);

SELECT acts\_as\_paranoid (‘a\_data’, ‘data’);

insert into data (id, my\_data) values (1, ‘Test 1’);  
insert into data (id, my\_data) values (2, ‘Test 2’);  
insert into data (id, my\_data) values (3, ‘Test 3’);  
select \* from data;  
select \* from data where id \> 2;  
delete from data where id \> 2;  
select \* from data;  
update data set my\_data = my\_data || ’ updated ’ || text(id) ;  
select \* from data;  
ROLLBACK;

---

<div class="post-metadata">

**Author:** ![kigster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/df705f/32.png) [@kigster](https://www.ruby-forum.com/u/kigster)\
**Post date:** [May 19, 2006, 8:30pm UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988/5 "2006-05-19T20:30:55Z")

</div>

On May 19, 2006, at 12:08 AM, Lugovoi N. wrote:

> What would you say about pure pl/pgSQL acts\_as\_paranoid ? :)))

I’d say “that’s super hot, man!”

jeremy

---

<div class="post-metadata">

**Author:** ![kigster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/df705f/32.png) [@kigster](https://www.ruby-forum.com/u/kigster)\
**Post date:** [May 19, 2006, 8:30pm UTC](https://www.ruby-forum.com/t/overriding-default-delete-behavior-with-logical-deletes/60988/6 "2006-05-19T20:30:58Z")

</div>

On May 19, 2006, at 11:29 AM, Jeremy K. wrote:

> > > and not what I would like to do.
> > 
> > What would you say about pure pl/pgSQL acts\_as\_paranoid ? :)))
> 
> I’d say “that’s super hot, man!”

That’s super hot, man!

jeremy