fkbench.init.sql

\connect postgres
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 update 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;