[LT] Delete or not delete

An easy way to start store deleted records in PostgreSQL if your project has many plain SQL queries

Transcript

1. DELETE OR NOT DELETE Makar Ermokhin
 Teachbase
3. DEFAULT WAY DELETE FROM table_name WHERE id=?
4. DEFAULT WAY • Pros • Default :) • Cons • Unrecoverable
5. WHEN YOU NEED TO RECOVER DELETED RECORD
6. FLAG WAY UPDATE table_name SET deleted='t' WHERE id=?
7. FLAG WAY • Pros • Recoverable • Cons • Can be worked around (with delete_all in Rails) • Code base with a lot of plain SQL
8. TEACHBASE WAY CREATE OR REPLACE FUNCTION store_deleted() RETURNS TRIGGER AS $$ BEGIN INSERT INTO deleted_records(table_name, record_id, deleted_at, transaction_id, data) VALUES(TG_TABLE_NAME, OLD.id, now() AT TIME ZONE 'utc', txid_current(), row_to_json(OLD)); RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER store_deleted_table_name AFTER DELETE ON table_name FOR EACH ROW EXECUTE PROCEDURE store_deleted();
9. TEACHBASE WAY • Pros • Recoverable • Code base friendly • Hard to work around • Cons • Schema migrations
10. THANK YOU Makar Ermokhin
 twitter.com/earendil_code