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 type membership_state_enum as enum('invited', 'joined', 'left', 'banned');
create table allowed_transitions(source_state,target_state)
as values
(null::membership_state_enum,'invited'::membership_state_enum)
,(null, 'banned')
,('invited','joined')
,('invited','banned')
,('joined','left')
,('joined','banned')
,('left','invited')
,('left','banned')
,('banned','invited')
,('banned','left');
alter table allowed_transitions
add constraint uniq unique(source_state,target_state);
create table user_group_membership(user_id,group_id,state)as values
(1,3,'joined'::membership_state_enum);
alter table user_group_membership
add constraint pk primary key(user_id,group_id);
CREATE TYPE
SELECT 10
ALTER TABLE
SELECT 1
ALTER TABLE
create function trg1_f()returns trigger as $f$
begin
create table if not exists user_group_membership_history
as select TG_OP,transaction_timestamp(),clock_timestamp()
,OLD.user_id as OLD_user_id
,OLD.group_id as OLD_group_id
,OLD.state as OLD_state
,NEW.*
limit 0;
insert into user_group_membership_history
select TG_OP,now(),clock_timestamp(),OLD.*,NEW.*;
return new;
end $f$ language plpgsql;
create trigger trg1
after insert or update or delete
on user_group_membership
for each row execute function trg1_f();
CREATE FUNCTION
CREATE TRIGGER
create function find_validate_apply(int,int,membership_state_enum)
returns setof user_group_membership as $f$
with find as(
select state
from user_group_membership as f
where $1=user_id and $2=group_id
for update of f
--limit 1--unncessary given the uniqueness and non-nullability
),empty_as_null as(--`coalesce()` for rows
(select state from find)
union(select null)
order by 1 nulls last limit 1
),validate as(
select exists(select from allowed_transitions as t
where t.source_state is not distinct from found.state
and t.target_state is not distinct from $3)
as is_transition_allowed
from empty_as_null as found
),apply as(
insert into user_group_membership
select $1,$2,$3
from validate
where is_transition_allowed
on conflict(user_id,group_id)do update
set state=$3
returning *)
select*from apply
$f$ language sql;
CREATE FUNCTION
--"empty->left" transition is not allowed, so it does nothing
select find_validate_apply(1,1,'left');
| find_validate_apply |
|---|
SELECT 0
select find_validate_apply(1,1,'invited');
| find_validate_apply |
|---|
| (1,1,invited) |
SELECT 1
select find_validate_apply(1,1,'joined');
| find_validate_apply |
|---|
| (1,1,joined) |
SELECT 1
select find_validate_apply(1,1,'left');
| find_validate_apply |
|---|
| (1,1,left) |
SELECT 1
create extension dblink;--spawn parallel workers
select dblink_connect('another_worker1','');
select clock_timestamp() as before_deploying_task1_that_takes_2s;
select dblink_send_query('another_worker1',
$q$ begin;
select pg_sleep(1);
select find_validate_apply(9,9,'invited');
select pg_sleep(1);
commit;
$q$);
select clock_timestamp() as before_deploying_task2_that_takes_2s;
select dblink_connect('another_worker2','');
select dblink_send_query('another_worker2',
$q$ begin;
select pg_sleep(1.2);
select find_validate_apply(9,9,'banned');
select pg_sleep(0.8);
commit;
$q$);
select clock_timestamp() as after_deploying_tasks_that_take_2s;
select pg_sleep(4);
table user_group_membership;
table user_group_membership_history;
CREATE EXTENSION
| dblink_connect |
|---|
| OK |
SELECT 1
| before_deploying_task1_that_takes_2s |
|---|
| 2024-11-13 16:18:18.928696+00 |
SELECT 1
| dblink_send_query |
|---|
| 1 |
SELECT 1
| before_deploying_task2_that_takes_2s |
|---|
| 2024-11-13 16:18:18.928783+00 |
SELECT 1
| dblink_connect |
|---|
| OK |
SELECT 1
| dblink_send_query |
|---|
| 1 |
SELECT 1
| after_deploying_tasks_that_take_2s |
|---|
| 2024-11-13 16:18:18.932805+00 |
SELECT 1
| pg_sleep |
|---|
SELECT 1
| user_id | group_id | state |
|---|---|---|
| 1 | 3 | joined |
| 1 | 1 | left |
| 9 | 9 | banned |
SELECT 3
| tg_op | transaction_timestamp | clock_timestamp | old_user_id | old_group_id | old_state | user_id | group_id | state |
|---|---|---|---|---|---|---|---|---|
| INSERT | 2024-11-13 16:18:18.907951+00 | 2024-11-13 16:18:18.910972+00 | null | null | null | 1 | 1 | invited |
| UPDATE | 2024-11-13 16:18:18.91135+00 | 2024-11-13 16:18:18.912029+00 | 1 | 1 | invited | 1 | 1 | joined |
| UPDATE | 2024-11-13 16:18:18.912142+00 | 2024-11-13 16:18:18.912608+00 | 1 | 1 | joined | 1 | 1 | left |
| INSERT | 2024-11-13 16:18:18.929716+00 | 2024-11-13 16:18:19.94236+00 | null | null | null | 9 | 9 | invited |
| UPDATE | 2024-11-13 16:18:18.932864+00 | 2024-11-13 16:18:20.946128+00 | 9 | 9 | invited | 9 | 9 | banned |
SELECT 5