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.
2
Answers
If your goal is to delete any
edge
referencing the updatednode
, you can add adelete
in a cte, as a part of the same statement: demo at db<>fiddleOr the other way around, taking
version
into account:Try use constraint check time.
Set DEFERRABLE INITIALLY DEFERRED for references (that is default setting?);
Then update or delete in one query. Constraint checks executed at the end of query.
Then update
Example
Here examples