For reasons outside of my control I am trying to build a graph-like data structure in PostgreSQL. The business requirement is that everytime a node changes, its edges need to be re-computed. Also, there is the possibility of stale reads from the edge creation algorithm, which means I need to guard the edges somehow. So far I've come up with
drop table if exists node;
drop table if exists edge;
create table node (
id varchar(32),
version int not null default 1,
data varchar(32),
primary key (id),
unique (id, version)
);
create table edge
(
id1 varchar(32) not null,
version1 int not null,
id2 varchar(32) not null,
version2 int not null,
constraint normalize check (id1 < id2),
foreign key (id1, version1) references node (id, version) on delete cascade,
foreign key (id2, version2) references node (id, version) on delete cascade,
primary key (id1, id2)
);
However, when I try to update the version of a node, I get into trouble:
insert into node (id, version, data) values ('id1', 1, 'Emmy'), ('id2', 1, 'Marie');
insert into edge (id1, version1, id2, version2) values ('id1', 1, 'id2', 1);
update node set version = version + 1 where id = 'id1'; -- should delete the edge; instead [23503] ERROR: update or delete on table "node" violates foreign key constraint "edge_id1_version1_fkey" on table "edge"
Is there a way to make this work in a single query without having to start a transaction? I kinda wish for an on update delete clause.
If your goal is to delete any edge referencing the updated node, you can add a delete in a cte, as a part of the same statement: demo at db<>fiddle
with cte as (delete from edge where 'id1' in (id1,id2))
update node
set version = version + 1
where id = 'id1';
Or the other way around, taking version into account:
with cte as (
update node
set version = version + 1
where id = 'id1'
returning version)
delete from edge
using cte
where ('id1',cte.version-1)
in ((id1,version1),(id2,version2));