By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE module(id integer, name text, state text);
INSERT INTO module VALUES
(1, 'website', 'installed'),
(2, 'purchase', 'installed'),
(3, 'crm', 'installed'),
(4, 'sale', 'uninstalled'),
(5, 'account', 'installed'),
(6, 'website_sale', 'installed'),
(7, 'purchase_bonus', 'installed'),
(8, 'website_blog', 'installed'),
(9, 'sale_discount', 'installed'),
(10, 'website_online', 'installed');
CREATE TABLE module_dependency(id integer, dependency_name text, module_id integer);
INSERT INTO module_dependency VALUES
(1, 'website_sale', 1),
(2, 'sale_bonus', 4),
(3, 'website_blog', 1),
(4, 'sale_discount', 4),
(5, 'website_online', 1),
(6, 'crm', 10),
(7, 'account', 3);
10 rows affected
7 rows affected
WITH RECURSIVE rcte_modules_to_upgrade AS
(
SELECT
m.id as module_id
, m.name as module_name
, md.id as dependency_id
, md.dependency_name
, m.id as base_module_id
, 1 as depth
FROM module m
JOIN module_dependency md
ON md.module_id = m.id
WHERE m.name = 'website'
UNION ALL
SELECT
m.id
, m.name
, md.id
, md.dependency_name
, rcte.base_module_id
, rcte.depth + 1
FROM rcte_modules_to_upgrade rcte
JOIN module m
ON m.name = rcte.dependency_name
LEFT JOIN module_dependency md
ON md.module_id = m.id
)
select *
from rcte_modules_to_upgrade;
| module_id | module_name | dependency_id | dependency_name | base_module_id | depth |
|---|---|---|---|---|---|
| 1 | website | 1 | website_sale | 1 | 1 |
| 1 | website | 3 | website_blog | 1 | 1 |
| 1 | website | 5 | website_online | 1 | 1 |
| 6 | website_sale | null | null | 1 | 2 |
| 8 | website_blog | null | null | 1 | 2 |
| 10 | website_online | 6 | crm | 1 | 2 |
| 3 | crm | 7 | account | 1 | 3 |
| 5 | account | null | null | 1 | 4 |
WITH RECURSIVE rcte_modules_to_upgrade AS
(
SELECT
m.id as module_id
, md.dependency_name
FROM module m
JOIN module_dependency md
ON md.module_id = m.id
WHERE m.name = 'website'
UNION ALL
SELECT
m.id
, md.dependency_name
FROM rcte_modules_to_upgrade rcte
JOIN module m
ON m.name = rcte.dependency_name
LEFT JOIN module_dependency md
ON md.module_id = m.id
)
update module
set state = 'to upgrade'
where id in (select distinct module_id
from rcte_modules_to_upgrade);
6 rows affected
select * from module order by id;
| id | name | state |
|---|---|---|
| 1 | website | to upgrade |
| 2 | purchase | installed |
| 3 | crm | to upgrade |
| 4 | sale | uninstalled |
| 5 | account | to upgrade |
| 6 | website_sale | to upgrade |
| 7 | purchase_bonus | installed |
| 8 | website_blog | to upgrade |
| 9 | sale_discount | installed |
| 10 | website_online | to upgrade |