Postgres 15

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?.

DROP TABLE IF EXISTS resource_actions CASCADE;
CREATE TABLE resource_actions(
resource TEXT NOT NULL,
actions TEXT NOT NULL,
entity TEXT NOT NULL,
UNIQUE(resource, actions, entity)
);

INSERT INTO resource_actions (resource, actions, entity) VALUES
('reading-list', 'read', 'Citizen'),
('reading-list', 'read', 'Employee'),
('books', 'read', 'Employee'),
('titles', 'update', 'Citizen'),
('titles', 'read', 'Citizen'),
('titles', 'update', 'Employee'),
('titles', 'read', 'Employee'),
('authors', 'update', 'Employee'),
('authors', 'read', 'Citizen'),
('reviews', 'read', 'Citizen'),
('reviews', 'read', 'Employee'),
('reviews', 'create', 'Citizen'),
('reviews', 'create', 'Employee'),
('employees', 'read', 'Employee'),
('employees', 'read', 'Boss'),
('employees', 'delete', 'Boss'),
('employees', 'create', 'Boss'),
('employees', 'update', 'Boss'),
('employee-schedule', 'read', 'Boss'),
('employee-schedule', 'delete', 'Boss'),
('employee-schedule', 'create', 'Boss'),
('employee-schedule', 'update', 'Boss');

select jsonb_pretty(json_agg(jsonResult)::jsonb)
from (select json_build_object(actions, json_agg(groupedResources)) as jsonResult
      from (select actions, json_build_object(entity, array_agg(resource)) as groupedResources
            from resource_actions
            group by actions, entity) tmp
      group by actions) x;

DROP TABLE

CREATE TABLE

INSERT 0 22

jsonb_pretty
[ { "create": [ { "Citizen": [ "reviews" ] }, { "Employee": [ "reviews" ] }, { "Boss": [ "employees", "employee-schedule" ] } ] }, { "read": [ { "Boss": [ "employees", "employee-schedule" ] }, { "Citizen": [ "reading-list", "titles", "authors", "reviews" ] }, { "Employee": [ "reading-list", "books", "titles", "reviews", "employees" ] } ] }, { "update": [ { "Boss": [ "employees", "employee-schedule" ] }, { "Citizen": [ "titles" ] }, { "Employee": [ "titles", "authors" ] } ] }, { "delete": [ { "Boss": [ "employees", "employee-schedule" ] } ] } ]

jsonb_pretty

[
{
"create": [
{
"Citizen": [
"reviews"
]
},
{
"Employee": [
"reviews"
]
},
{
"Boss": [
"employees",
"employee-schedule"
]
}
]
},
{
"read": [
{
"Boss": [
"employees",
"employee-schedule"
]
},
{
"Citizen": [
"reading-list",
"titles",
"authors",
"reviews"
]
},
{
"Employee": [
"reading-list",
"books",
"titles",
"reviews",
"employees"
]
}
]
},
{
"update": [
{
"Boss": [
"employees",
"employee-schedule"
]
},
{
"Citizen": [
"titles"
]
},
{
"Employee": [
"titles",
"authors"
]
}
]
},
{
"delete": [
{
"Boss": [
"employees",
"employee-schedule"
]
}
]
}
]

SELECT 1