Soft delete cascade in PostgreSQL and YugabyteDB

This is a quick example to answer This remark about soft deletes:

Kelly Sommers

@kellabyte

An interesting RDBMS feature would be cascading soft deletes.

02:10 AM - 15 Apr 2022

I've run this on YugabyteDB to verify that it works the same as in PostgreSQL. Of course, no suprise, YugabyteDB re-uses the postgres SQL processing layer, for the best compatibility.

Tables

Here is the parent table which has a parent_deleted timestamp set to the date of deletion. The default, infinity, is for valid records. This column is part of the primary key because there may be multiple deletion for the same parent_id. But only one valid.

CREATE TABLE parent ( parent_id int, parent_deleted timestamptz default 'infinity', primary key (parent_id,parent_deleted));

The child table inherits the parent primary key and adds a child_number to it as its primary key. The foreign key is declared with on update cascade as the soft deletes will be cascaded as updates to this primary key.

CREATE TABLE child ( parent_id int, parent_deleted timestamptz default 'infinity', child_number int, primary key (parent_id,parent_deleted, child_number), foreign key (parent_id,parent_deleted)   references parent(parent_id,parent_deleted)  on update cascade);

Views

Tables could be sufficient. But the beauty of SQL is the logical independence. I want to query my tables, from the application or by the user, without caring about the soft delete implementation.

I declare views for that. The application will query valid_parent and valid_child to see the current versions, filtering out the soft deleted rows:

create view valid_parent as select parent_id from parent where parent_deleted>=now();create view valid_child as select parent_id,child_number from child where parent_deleted>=now();

Thanks to re-using the primary key, there is no need to join the tables there. This is the right choice when deletes are rare (the cascading update overhead is acceptable) but selects are frequent. And people tend to think that joins don't scale.

Procedure

I want to encapsulate this logic in the database and create a procedure to do be called for this soft deletion:

create procedure soft_delete_parent(id int) as $SQL$update parentset parent_deleted=now()where parent_id=id;$SQL$ language sql;

I'll show an alternative later if you don't like stored procedures. But, personally, I like this procedure encapsulation because the semantic is clear: the application calls a specific procedure.

Data

I'm inserting few rows there. I'm inserting valid rows, and insert them though the view, because a view is a virtual table, with all DML allowed. The default infinity value is set automatically:

insert into valid_parent select n from generate_series(1,3) n;insert into valid_child select parent_id,n from valid_parent,generate_series(1,2) n;

Here is a screenshot from my test:

You can easily reproduce it - did you try the YugabyteDB managed free tier?

Test

When you implement data logic in SQL, a simple unit test is usually sufficient, because the database takes care of all multi-user consistency.

select * from valid_parent;select * from valid_child;

This shows only the valid rows. I call the procedure to soft-delete one parent:

call soft_delete_parent(2);

When querying the views, the rows have been virtually deleted:

select * from valid_parent;select * from valid_child;

Here is the result:

In the tables behind the views, we can see all the rows, with the soft-deleted ones:

yugabyte=# select * from parent; parent_id |        parent_deleted-----------+-------------------------------         1 | infinity         2 | 2022-04-15 10:21:45.635693+00         3 | infinity(3 rows)yugabyte=# select * from child; parent_id |        parent_deleted         | child_number-----------+-------------------------------+--------------         1 | infinity                      |            1         1 | infinity                      |            2         2 | 2022-04-15 10:21:45.635693+00 |            1         2 | 2022-04-15 10:21:45.635693+00 |            2         3 | infinity                      |            1         3 | infinity                      |            2(6 rows)

Note that, with GRANT and REVOKE, you can give access to the views only, or to these tables. And revoke the right to hard delete.

Rule

You can make this completely transparent, so that users don't have to call the procedure, but simply run DELETE on the view, with a DO INSTEAD code:

create or replace rule soft_delete_parent as on delete to valid_parent do insteadupdate parentset parent_deleted=now()where parent_id=old.parent_id;

This is simple. Now any delete will actually do a soft delete:

This looks great, as the application is just interacting with the standard SQL API (SELECT, INSERT, UPDATE, DELETE). And it comes handy when the application cannot be modified. But, for better code quality, I prefer a procedure so that the application developer knows what she does (my procedure name is explicit about soft deletes). You can also see that this RULE is not 100% transparent in its output, showing DELETE 0.

PostgreSQL-compatible

This technique is easy on PostgreSQL and PostgreSQL-compatible databases which re-use the PostgreSQL open-source code, like YugabyteDB. Here is the list of SQL features that makes it easy, declarative, and transparent:

(1) Temporal Validity is an alternative
(2) Triggers and deferred constraints may be an alternative
(3) Displayed as 294276-12-31 23:59:59.999999+00

This is where having all PostgreSQL features in YugabyteDB makes it the right solution for many enterprise applications. Even when you don't want to put business logic into the database, there is one day where you will need a stored procedure, triggers, rule, or any of those modern SQL features that have proven their value on monolithic databases and are now available in distributed SQL.