fkbench.init.sql

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;