Is it safe to use lastval/currval for multiple inserts?

Question

Lets say I have those tables:

CREATE TABLE buildings (
    id_building serial4 NOT NULL,
    street  text NULL,
    CONSTRAINT buildings_pkey PRIMARY KEY (id_building)
);

CREATE TABLE families (
    id_family serial4 NOT NULL,
    id_building int NOT NULL,
    name    text NULL,
    CONSTRAINT families_pkey PRIMARY KEY (id_building),
    CONSTRAINT families_id_building_fkey FOREIGN KEY (id_building) REFERENCES buildings (id_building)
);

CREATE TABLE people (
    id_person serial4 NOT NULL,
    id_family int NOT NULL,
    name    text NULL,
    CONSTRAINT people_pkey PRIMARY KEY (id_person),
    CONSTRAINT people_id_family_fkey FOREIGN KEY (id_family) REFERENCES buildings (id_family)
);

So that in a building you can have 0 or many families, and in every family you can have 1 or more people.

I need to make one query to insert in this connected tables without knowing the number of rows that are inserted. I know well this method https://stackoverflow.com/a/41595442 but I think the syntax would become quite complex rather fast.

So I'm exploring this system https://stackoverflow.com/a/41596194 that would be simpler to read and write. But is it robust and safe? My database is busy with many simultaneous inserts, even on those tables, from different sources and codes. I don't want to have inconsistencies in the reference keys.

The query would be something like this

BEGIN;
INSERT INTO buildings (street) VALUES ('St Michael''s Rd');
INSERT INTO families (id_building, name) VALUES (currval('buildings_id_seq'), 'White');
INSERT INTO people (id_family, name) VALUES (currval('families_id_seq'), 'John');
INSERT INTO people (id_family, name) VALUES (currval('families_id_seq'), 'Jenny');
INSERT INTO people (id_family, name) VALUES (currval('families_id_seq'), 'Pepy');
INSERT INTO families (id_building, name) VALUES (currval('buildings_id_seq'), 'Brown');
INSERT INTO people (id_family, name) VALUES (currval('families_id_seq'), 'Conny');

INSERT INTO buildings (street) VALUES ('Crossford St');

INSERT INTO buildings (street) VALUES ('Stockwell Park Cres');
INSERT INTO families (id_building, name) VALUES (currval('buildings_id_seq'), 'Smith');
INSERT INTO people (id_family, name) VALUES (currval('families_id_seq'), 'John');

END;

And I expect an output like this:

SELECT *
FROM buildings
-- 1, St Michael''s Rd
-- 2, Crossford St
-- 3, Stockwell Park Cres

SELECT *
FROM families
-- 1, 1, White
-- 2, 1, Brown
-- 3, 3, Smith

SELECT *
FROM people
-- 1, 1, John
-- 2, 1, Jenny
-- 3, 1, Pepy
-- 4, 2, Conny
-- 5, 3, John

Answer 1

A method using sequences and functions. Slightly different building and families table definitions.

I think it'll only work when called from a programming language (i.e. not directly in psql), since it needs to store b_id and f_id somewhere.

CREATE SEQUENCE building_id_seq 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 1;

CREATE SEQUENCE family_id_seq 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 1;

CREATE TABLE buildings (
    id_building integer primary key,
    street  text NULL,
);

CREATE TABLE families (
    id_family integer primary key,
    id_building int NOT NULL,
    name    text NULL,
    CONSTRAINT families_id_building_fkey 
        FOREIGN KEY (id_building) 
            REFERENCES buildings (id_building)
);

CREATE OR REPLACE FUNCTION get_building_seq() returns INTEGER
    LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
BEGIN
    RETURN nextval('building_id_seq');
end;
$$;

CREATE OR REPLACE FUNCTION get_family_seq() returns INTEGER
    LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
BEGIN
    RETURN nextval('families_id_seq');
end;
$$;

CREATE OR REPLACE PROCEDURE create_building(b_id integer, the_name text)
        LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
BEGIN
    INSERT INTO buildings values (
        b_id, the_name);
end;
$$;

CREATE OR REPLACE PROCEDURE create_family(f_id integer, b_id integer, the_name text)
        LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
BEGIN
    INSERT INTO families values (
        f_id, b_id, the_name);
end;
$$;


b_id := get_building_seq();
call create_building(b_id, 'St Michael''s Rd');

f_id := get_family_seq();
call create_family(f_id, b_id, 'White');

insert into people (id_family, name) values (f_id, 'John');
insert into people (id_family, name) values (f_id, 'Jenny');
insert into people (id_family, name) values (f_id, 'Pepy');

f_id := get_family_seq();
call create_family(f_id, b_id, 'Brown');
insert into people (id_family, name) values (f_id, 'Connie');

Answer 2

currval is safe in the face of concurrent threads/sessions doing inserts at the same time.

But it is not safe in the face of triggers. Your best bet is to use a RETURNING clause. See also this answer on Stack Overflow.

You can chain them all together in CTEs. Note that this does not cause the insert to happen multiple times, as each set-returning insert is cached.

WITH b AS (
    INSERT INTO buildings (street)
    VALUES
        ('St Michael''s Rd'),
        ('Crossford St'),
        ('Stockwell Park Cres')
    RETURNING id_building, street
),
f AS (
    INSERT INTO families (id_building, name)
    SELECT b.id_building, f.name
    FROM b
    JOIN (VALUES
        ('St Michael''s Rd', 'White'),
        ('St Michael''s Rd', 'Brown'),
        ('Stockwell Park Cres', 'Smith')
    ) AS f(street, name) ON f.street = b.street
    RETURNING id_family, name
)
INSERT INTO people (id_family, name)
SELECT f.id_family, p.name
FROM f
JOIN (VALUES
    ('White', 'John'),
    ('White', 'Jenny'),
    ('White', 'Pepy'),
    ('Brown', 'Conny'),
    ('Smith', 'John')
) AS p(family, name) ON p.family = f.name;

db<>fiddle

You can also do the above insert selecting from a JSON object or other bulk insert method.