soft_delete.sql

# Clean the database
DROP TABLE IF EXISTS _orders CASCADE;
DROP TABLE IF EXISTS _users CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS users CASCADE;

# Build the database (for hard deletion)
CREATE TABLE users (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL
);
CREATE TABLE orders (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  user_id integer NOT NULL,
  number text NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

# Populate it with some data
INSERT INTO users (name) VALUES ('Andrew'), ('Vladimir'), ('Sara');
INSERT INTO orders (user_id, number) VALUES (1, 'A1'), (1, 'A2'), (2, 'V1'), (2, 'V2'), (3, 'S3');

# Add columns for soft deletion
ALTER TABLE users ADD COLUMN deleted boolean NOT NULL DEFAULT false;
ALTER TABLE orders ADD COLUMN deleted boolean NOT NULL DEFAULT false;

# Hide tables on behind of views
ALTER TABLE users RENAME TO _users;
CREATE VIEW users AS SELECT * FROM _users WHERE NOT deleted;
ALTER TABLE orders RENAME TO _orders;
CREATE VIEW orders AS SELECT * FROM _orders WHERE NOT deleted;

# Add rewriting rules for views
CREATE RULE _soft_deletion AS ON DELETE TO orders DO INSTEAD (
  UPDATE _orders SET deleted = true WHERE id = old.id
);
CREATE RULE _soft_deletion AS ON DELETE TO users DO INSTEAD (
  UPDATE _users SET deleted = true WHERE id = old.id
);

# Add rewriting rules for associated table
CREATE RULE _delete_orders AS ON UPDATE TO _users
  WHERE NOT old.deleted AND new.deleted
  DO ALSO UPDATE _orders SET deleted = true WHERE user_id = old.id;

# Then check the results
DELETE FROM users WHERE name = 'Andrew';
SELECT * FROM users;
# Should not contain 'Andrew'

SELECT * FROM _users;
# Should have all records, including 'Andrew' marked as deleted

SELECT * FROM orders;
# Should not contain 'A1' and 'A2'

SELECT * FROM _orders;
# Should have all records, including 'A1' and 'A2' marked as deleted

I am getting syntax error for ALTER SETTINGS SET rules.soft_deletion TO on;
ERROR: syntax error at or near "SETTINGS"
LINE 1: ALTER SETTINGS SET rules.soft_deletion TO on;

This soft deletion dones't trigger after_destroy callback of ActiveRecord, because PG return cms_tuples = 0 and AR think there is nothing deleted.

=> #<PG::Result:0x000000010e99ff98 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>

try set_config

begin;
  select set_config('rules.soft_deletion', 'on', true);
  select current_setting('rules.soft_deletion');
end;

I fixed the destroy callback issue with this patch:

# patch lib/active_record/persistence.rb
ActiveRecord::Persistence.class_eval do
  def destroy_row
    affected_rows = _delete_row
    self.class._soft_deletion ? 1 : affected_rows
  end
end

module ActsAsLowLevelSoftDeletion
  extend ActiveSupport::Concern

  included do
    class_attribute :_soft_deletion, instance_accessor: false, instance_predicate: false
    self._soft_deletion = false
  end

  class_methods do
    def acts_as_low_level_soft_deletion
      self._soft_deletion = true
    end
  end
end

ActiveSupport.on_load(:active_record) { include ActsAsLowLevelSoftDeletion }

class Order < ActiveRecord::Base
  acts_as_low_level_soft_deletion
end