Soft delete cascade in PostgreSQL🐘 and YugabyteDB🚀

It is a fast instance to reply This comment about smooth deletes:

An attention-grabbing RDBMS function can be cascading smooth deletes.

02:10 AM – 15 Apr 2022

I do not know which database Kelly Sommers makes use of, however PostgreSQL has many options that, mixed, assist implementing this information processing logic, in a declarative approach. I do know folks do not prefer to put code within the databases, however this isn’t enterprise logic. That is pure information logic: implementing smooth deletes. SQL has big profit for this: it’s a declarative language. You declare it as soon as, take a look at it, and you might be accomplished. No want for extra code or further exams.

I’ve run this on YugabyteDB to confirm that it really works the identical as in PostgreSQL. In fact, no suprise, YugabyteDB re-uses the postgres SQL processing layer, for the very best compatibility.

Tables

Right here is the dad or mum desk which has a parent_deleted timestamp set to the date of deletion. The default, infinity, is for legitimate data. This column is a part of the first key as a result of there could also be a number of deletion for a similar parent_id. However just one legitimate.

CREATE TABLE dad or mum (
 parent_id int, parent_deleted timestamptz default 'infinity',
 major key (parent_id,parent_deleted)
);

The kid desk inherits the dad or mum major key and provides a child_number to it as its major key. The overseas secret is declared with on replace cascade because the smooth deletes will likely be cascaded as updates to this major key.

CREATE TABLE little one (
 parent_id int, parent_deleted timestamptz default 'infinity',
 child_number int,
 major key (parent_id,parent_deleted, child_number),
 overseas key (parent_id,parent_deleted) 
  references dad or mum(parent_id,parent_deleted)
  on replace cascade
);

Views

Tables may very well be ample. However the great thing about SQL is the logical independence. I need to question my tables, from the appliance or by the consumer, with out caring in regards to the smooth delete implementation.

I declare views for that. The appliance will question valid_parent and valid_child to see the present variations, filtering out the smooth deleted rows:

create view valid_parent as
 choose parent_id from dad or mum the place parent_deleted>=now();

create view valid_child as
 choose parent_id,child_number from little one the place parent_deleted>=now();

Because of re-using the first key, there isn’t any want to hitch the tables there. That is the best selection when deletes are uncommon (the cascading replace overhead is suitable) however selects are frequent. And folks are likely to suppose that joins do not scale.

Process

I need to encapsulate this logic within the database and create a process to do be known as for this smooth deletion:

create process soft_delete_parent(id int) as $SQL$
replace dad or mum
set parent_deleted=now()
the place parent_id=id;
$SQL$ language sql;

I am going to present an alternate later in the event you don’t love saved procedures. However, personally, I like this process encapsulation as a result of the semantic is evident: the appliance calls a selected process.

Information

I am inserting few rows there. I am inserting legitimate rows, and insert them although the view, as a result of a view is a digital desk, with all DML allowed. The default infinity worth is about routinely:

insert into valid_parent
 choose n from generate_series(1,3) n;

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

Here’s a screenshot from my take a look at:

You may simply reproduce it – did you strive the YugabyteDB managed free tier?

Check

While you implement information logic in SQL, a easy unit take a look at is normally ample, as a result of the database takes care of all multi-user consistency.

choose * from valid_parent;

choose * from valid_child;

This exhibits solely the legitimate rows. I name the process to soft-delete one dad or mum:

name soft_delete_parent(2);

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

choose * from valid_parent;

choose * from valid_child;

Right here is the consequence:

Within the tables behind the views, we will see all of the rows, with the soft-deleted ones:

yugabyte=# choose * from dad or mum;

 parent_id |        parent_deleted
-----------+-------------------------------
         1 | infinity
         2 | 2022-04-15 10:21:45.635693+00
         3 | infinity
(3 rows)

yugabyte=# choose * from little one;

 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)

Word that, with GRANT and REVOKE, you can provide entry to the views solely, or to those tables. And revoke the best to onerous delete.

Rule

You can also make this fully clear, in order that customers do not must name the process, however merely run DELETE on the view, with a DO INSTEAD code:

create or exchange rule soft_delete_parent as 
on delete to valid_parent do as an alternative
replace dad or mum
set parent_deleted=now()
the place parent_id=outdated.parent_id;

That is easy. Now any delete will really do a smooth delete:

This appears to be like nice, as the appliance is simply interacting with the usual SQL API (SELECT, INSERT, UPDATE, DELETE). And it comes useful when the appliance can’t be modified. However, for higher code high quality, I desire a process in order that the appliance developer is aware of what she does (my process identify is specific about smooth deletes). You too can see that this RULE is just not 100% clear in its output, exhibiting DELETE 0.

PostgreSQL-compatible

This system is simple on PostgreSQL and PostgreSQL-compatible databases which re-use the PostgreSQL open-source code, like YugabyteDB. Right here is the listing of SQL options that makes it simple, declarative, and clear:

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

That is the place having all PostgreSQL options in YugabyteDB makes it the best answer for a lot of enterprise functions. Even when you do not need to put enterprise logic into the database, there’s at some point the place you will have a saved process, triggers, rule, or any of these trendy SQL options which have confirmed their worth on monolithic databases and at the moment are obtainable in distributed SQL.