drop database if exists fkbench; create database fkbench; \connect fkbench create table parent (id integer not null primary key); create table child (parent_id integer not null); create index idx_parent_id on child (parent_id); create or replace function run(v_nparents integer) returns void as $body$ declare v_parent_id integer default 1 + floor(random() * v_nparents); begin -- Verify consistency if (select count(*) from child left join parent on parent_id = id where id is null) > 0 then raise exception 'database inconsistent'; end if; if random() < 0.5 THEN -- Add a child to a random parent perform true from parent where id = v_parent_id for share of parent; if not found then insert into parent (id) values (v_parent_id); end if; insert into child (parent_id) values (v_parent_id); else -- Make race conditions more likely perform pg_sleep(0.01); -- Delete parent delete from parent where id = v_parent_id; delete from child where parent_id = v_parent_id; end if; exception when serialization_failure then null; when unique_violation then null; end; $body$ language plpgsql volatile strict;