Insert-only complete history database model via Postgresql Rules

posts.sql

-- Cf. http://www.drmaciver.com/2009/11/filtering-deleted-documents-with-postgresql-rules/
-- and related comments

BEGIN;
create sequence post_ids;

create table unfiltered_posts(id int primary key default nextval('post_ids'),
                                 parent_id int references unfiltered_posts (id),
                              created timestamp not null default current_timestamp, 
                              body text);

create or replace rule unfiltered_posts_update as on update to unfiltered_posts do instead nothing;
create or replace rule unfiltered_posts_delete as on delete to unfiltered_posts do instead nothing;

 
create view posts as select coalesce(parent_id, id) as id, created as updated, body 
        from unfiltered_posts where body is not null and id in 
       (select max(id) from unfiltered_posts group by coalesce(parent_id, id));
 
create or replace rule insert_into_posts as 
  on insert to posts do instead 
    insert into unfiltered_posts 
    values(coalesce(NEW.id, nextval('post_ids')), 
           null,
           coalesce(NEW.updated, current_timestamp),
           NEW.body
           );
 
create or replace rule update_to_posts as 
  on update to posts  do instead              
    insert into unfiltered_posts
    values(nextval('post_ids'),
           OLD.id,
           coalesce(NEW.updated, current_timestamp),
           NEW.body
           );
 
create or replace rule delete_posts as 
  on delete to posts do instead 
    insert into unfiltered_posts
    select nextval('post_ids'),
           OLD.id,
           current_timestamp,
           null
     where exists (select id from posts where id = OLD.id);




insert into posts (body) values ('I like kittens');
insert into posts (body) values ('I am the very model of a modern major general');

select * from posts;

--  id |          updated          |                     body                      
-- ----+---------------------------+-----------------------------------------------
--   1 | 2009-12-06 21:43:09.53681 | I like kittens
--   2 | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- (2 rows)

select * from unfiltered_posts order by id desc;

--  id | parent_id |          created          |                     body                      
-- ----+-----------+---------------------------+-----------------------------------------------
--   2 |           | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
--   1 |           | 2009-12-06 21:43:09.53681 | I like kittens
-- (2 rows)



update posts set body = 'I like puppies' where id = 1;

select * from posts;

--  id |          updated          |                     body                      
-- ----+---------------------------+-----------------------------------------------
--   2 | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
--   1 | 2009-12-06 21:43:09.53681 | I like puppies
-- (2 rows)

select * from unfiltered_posts order by id desc;

--  id | parent_id |          created          |                     body                      
-- ----+-----------+---------------------------+-----------------------------------------------
--   3 |         1 | 2009-12-06 21:43:09.53681 | I like puppies
--   2 |           | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
--   1 |           | 2009-12-06 21:43:09.53681 | I like kittens
-- (3 rows)



delete from posts where id = 1;

select * from posts;

--  id |          updated          |                     body                      
-- ----+---------------------------+-----------------------------------------------
--   2 | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- (1 row)

select * from unfiltered_posts order by id desc;

--  id | parent_id |          created          |                     body                      
-- ----+-----------+---------------------------+-----------------------------------------------
--   4 |         1 | 2009-12-06 21:43:09.53681 | 
--   3 |         1 | 2009-12-06 21:43:09.53681 | I like puppies
--   2 |           | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
--   1 |           | 2009-12-06 21:43:09.53681 | I like kittens
-- (4 rows)



delete from posts;

select * from posts;

--  id | updated | body 
-- ----+---------+------
-- (0 rows)

select * from unfiltered_posts order by id desc;

--  id | parent_id |          created          |                     body                      
-- ----+-----------+---------------------------+-----------------------------------------------
--   5 |         2 | 2009-12-06 21:43:09.53681 | 
--   4 |         1 | 2009-12-06 21:43:09.53681 | 
--   3 |         1 | 2009-12-06 21:43:09.53681 | I like puppies
--   2 |           | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
--   1 |           | 2009-12-06 21:43:09.53681 | I like kittens
-- (5 rows)



update unfiltered_posts set body = 'I do not like kittens' where id = 1;

select body from unfiltered_posts where id = 1;

--       body      
-- ----------------
--  I like kittens
-- (1 row)

-- Postgres doesn't lie!



delete from unfiltered_posts;

select count(*)=5 from unfiltered_posts;

--  ?column? 
-- ----------
--  t
-- (1 row)