{"id":10505,"date":"2024-04-03T14:11:08","date_gmt":"2024-04-03T13:11:08","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=10505"},"modified":"2024-04-03T14:11:08","modified_gmt":"2024-04-03T13:11:08","slug":"pg_recursively_delete-simplifier-les-suppressions-recursives","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/pg_recursively_delete-simplifier-les-suppressions-recursives\/","title":{"rendered":"pg_recursively_delete : Simplifier les suppressions r\u00e9cursives"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Si vous avez \u00e9t\u00e9 amen\u00e9 au fil de votre carri\u00e8re \u00e0 manipuler de gros volumes de donn\u00e9es contenus dans plusieurs tables poss\u00e9dant des r\u00e9f\u00e9rences crois\u00e9es entre elles, d\u00e9pendantes d’autres tables, qui elles-m\u00eames d\u00e9pendent d’autres tables, vous savez \u00e0 quel point il peut \u00eatre compliqu\u00e9 de remonter l’int\u00e9gralit\u00e9 de l’arbre de d\u00e9pendance pour supprimer la moindre ligne. Cela peut \u00eatre long et fastidieux.<\/p>\n

Vous ne savez pas vraiment ce que vous supprimez, dans quelles tables, et quels impacts cela peut avoir sur votre base de donn\u00e9es. Si les d\u00e9pendances sont nombreuses, il est d’autant plus compliqu\u00e9 de tout retracer et d’\u00eatre s\u00fbr \u00e0 100 % de ce que votre DELETE va entra\u00eener.<\/p>\n

Dans cet article, je vais vous pr\u00e9senter rapidement un petit outil sous la forme d’une extension que je trouve pratique \u00e0 utiliser dans ce cas de figure. L’outil s’appelle pg_recursively_delete, et il permet de tracer avant d’ex\u00e9cuter l’ordre de suppression de votre ligne, et d’avoir une arborescence des diff\u00e9rentes donn\u00e9es que vous allez impacter.<\/p>\n

Installation d’un moteur et de l’extension :<\/h2>\n

Pour cet article, j’ai choisi d’utiliser PostgreSQL en version 16 pour tester si l’extension fonctionnait toujours.<\/p>\n

root:~#sudo apt update &amp;&amp; sudo apt upgrade\r\nroot:~#sudo sh -c 'echo "deb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main" &gt; \/etc\/apt\/sources.list.d\/pgdg.list'\r\nroot:~#wget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -\r\nroot:~#sudo apt -y update\r\nroot:~#sudo apt -y install postgresql-16<\/pre>\n
Notre moteur de base de donn\u00e9es est install\u00e9, \u00e0 pr\u00e9sent il nous faut t\u00e9l\u00e9charger les sources de l’extension, et l’installer.<\/p>\n
root:~# git clone https:\/\/github.com\/trlorenz\/PG-recursively_delete.git\r\nCloning into 'PG-recursively_delete'...\r\nremote: Enumerating objects: 155, done.\r\nremote: Counting objects: 100% (95\/95), done.\r\nremote: Compressing objects: 100% (62\/62), done.\r\nremote: Total 155 (delta 41), reused 74 (delta 29), pack-reused 60\r\nReceiving objects: 100% (155\/155), 38.55 KiB | 3.21 MiB\/s, done.\r\nResolving deltas: 100% (70\/70), done.\r\nroot:~# cd PG-recursively_delete\/\r\nroot:~\/PG-recursively_delete# make\r\ncp sql\/recursively_delete.sql sql\/recursively_delete--0.1.5.sql\r\nroot:~\/PG-recursively_delete# sudo make install\r\n\/bin\/mkdir -p '\/usr\/share\/postgresql\/16\/extension'\r\n\/bin\/mkdir -p '\/usr\/share\/postgresql\/16\/extension'\r\n\/bin\/mkdir -p '\/usr\/share\/doc\/postgresql-doc-16\/extension'\r\n\/usr\/bin\/install -c -m 644 .\/\/recursively_delete.control '\/usr\/share\/postgresql\/16\/extension\/'\r\n\/usr\/bin\/install -c -m 644 .\/\/sql\/recursively_delete--0.1.5.sql  '\/usr\/share\/postgresql\/16\/extension\/'\r\n\/usr\/bin\/install -c -m 644 .\/\/doc\/changelog.md '\/usr\/share\/doc\/postgresql-doc-16\/extension\/'<\/pre>\n
Mise en place de l’environnement<\/h2>\n
Pour illustrer le fonctionnement de l’extension, je vais utiliser la base de donn\u00e9es de d\u00e9monstration dvdrental. Nous allons donc la t\u00e9l\u00e9charger et la charger dans une toute nouvelle base de donn\u00e9es que nous aurons cr\u00e9\u00e9e sur notre instance fra\u00eechement cr\u00e9\u00e9e :<\/p>\n
 postgres:~$ wget https:\/\/www.postgresqltutorial.com\/wp-content\/uploads\/2019\/05\/dvdrental.zip\r\n--2024-03-11 08:34:54--  https:\/\/www.postgresqltutorial.com\/wp-content\/uploads\/2019\/05\/dvdrental.zip\r\nResolving www.postgresqltutorial.com (www.postgresqltutorial.com)... 104.21.2.174, 172.67.129.129, 2606:4700:3037::6815:2ae, ...\r\nConnecting to www.postgresqltutorial.com (www.postgresqltutorial.com)|104.21.2.174|:443... connected.\r\nHTTP request sent, awaiting response... 200 OK\r\nLength: 550906 (538K) [application\/zip]\r\nSaving to: \u2018dvdrental.zip\u2019\r\n\r\ndvdrental.zip                                               100%[========================================================================================================================================>] 537.99K  --.-KB\/s    in 0.01s\r\n\r\n2024-03-11 08:34:54 (46.0 MB\/s) - \u2018dvdrental.zip\u2019 saved [550906\/550906]  <\/pre>\n
Une fois t\u00e9l\u00e9charg\u00e9e, on la dezippe :<\/p>\n
postgres:~$ ls -l\r\ntotal 544\r\ndrwxr-xr-x 3 postgres postgres   4096 Mar 11 08:30 16\r\n-rw-rw-r-- 1 postgres postgres 550906 May 12  2019 dvdrental.zip\r\npostgres:~$ unzip dvdrental.zip\r\nArchive:  dvdrental.zip\r\n  inflating: dvdrental.tar\r\npostgres:~$ ls -l\r\ntotal 3316\r\ndrwxr-xr-x 3 postgres postgres    4096 Mar 11 08:30 16\r\n-rw-rw-r-- 1 postgres postgres 2835456 May 12  2019 dvdrental.tar\r\n-rw-rw-r-- 1 postgres postgres  550906 May 12  2019 dvdrental.zip<\/pre>\n
On cr\u00e9\u00e9 la base de donn\u00e9es pour accueillir nos donn\u00e9es, et on charge le fichier de sauvegarde :<\/p>\n
postgres:~$ psql\r\npsql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))\r\nType "help" for help. <\/pre>\n
postgres=# create database dvdrental;\r\nCREATE DATABASE\r\npostgres=# \\l\r\n                                                   List of databases\r\n   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges\r\n-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------\r\n dvdrental | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |\r\n postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |\r\n template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c\/postgres          +\r\n           |          |          |                 |         |         |            |           | postgres=CTc\/postgres\r\n template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c\/postgres          +\r\n           |          |          |                 |         |         |            |           | postgres=CTc\/postgres\r\n(4 rows)<\/pre>\n
postgres:~$ pg_restore -U postgres -d dvdrental dvdrental.tar<\/pre>\n
Une fois que c’est fait, on peut se connecter pour v\u00e9rifier que tout a bien \u00e9t\u00e9 charg\u00e9 :<\/p>\n
postgres:~$ psql\r\npsql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))\r\nType "help" for help.\r\n<\/pre>\n
postgres=# \\c dvdrental\r\nYou are now connected to database "dvdrental" as user "postgres".\r\ndvdrental=# \\dt\r\n             List of relations\r\n Schema |     Name      | Type  |  Owner\r\n--------+---------------+-------+----------\r\n public | actor         | table | postgres\r\n public | address       | table | postgres\r\n public | category      | table | postgres\r\n public | city          | table | postgres\r\n public | country       | table | postgres\r\n public | customer      | table | postgres\r\n public | film          | table | postgres\r\n public | film_actor    | table | postgres\r\n public | film_category | table | postgres\r\n public | inventory     | table | postgres\r\n public | language      | table | postgres\r\n public | payment       | table | postgres\r\n public | rental        | table | postgres\r\n public | staff         | table | postgres\r\n public | store         | table | postgres\r\n(15 rows)\r\n<\/pre>\n
L’extension :<\/h2>\n
Pour tester l’extension, nous allons essayer de supprimer un client de la liste des clients.
\nLe sch\u00e9ma de la base de donn\u00e9es dvdrental est le suivant :
\n\"\"<\/a><\/p>\n
Si l’on observe attentivement le sch\u00e9ma ci-dessus, en voulant supprimer une donn\u00e9e de la table customer, cela devrait avoir un impact sur les tables rental et payment qui sont directement li\u00e9es \u00e0 la table customer. De plus, ces deux tables sont \u00e9galement li\u00e9es entre elles, ce qui signifie que supprimer une donn\u00e9e dans la table rental modifiera n\u00e9cessairement la table payment.<\/p>\n
Prenons l’exemple de la suppression du client num\u00e9ro 1. Si nous recherchons les d\u00e9pendances de ce client dans la table rental, nous obtenons 32 lignes associ\u00e9es au customer_id 1 :<\/p>\n
 dvdrental=# select count(*) from rental where customer_id = 1;\r\n count\r\n-------\r\n    32\r\n(1 row)\r\n<\/pre>\n
Et si nous allons maintenant chercher toutes les occurrences de ce m\u00eame client dans la table des paiements, nous obtenons :<\/p>\n
dvdrental=# select count(*) from payment where customer_id = 1;\r\n count\r\n-------\r\n    30\r\n(1 row) <\/pre>\n
\u00c0 pr\u00e9sent, avec l’extension recursive_delete, nous allons chercher \u00e0 obtenir le sch\u00e9ma de suppression pour v\u00e9rifier si les r\u00e9sultats que nous avons trouv\u00e9s sont corrects :<\/p>\n
dvdrental=# create extension recursively_delete;\r\nCREATE EXTENSION\r\ndvdrental=# \\set VERBOSITY terse\r\ndvdrental=# select recursively_delete('customer', 1);\r\nINFO:  DAMAGE PREVIEW (recursively_delete v0.1.5)\r\nINFO:\r\nINFO:          1     customer\r\nINFO:         30 r   | payment.["customer_id"]\r\nINFO:         32 r   | rental.["customer_id"]\r\nINFO:          ~ n   | | payment.["rental_id"]\r\nINFO:\r\n recursively_delete\r\n--------------------\r\n                  0\r\n(1 row) <\/pre>\n
La fonction de suppression de l’extension fonctionne avec les param\u00e8tres suivants :<\/p>\n