1

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
7
  • 1
    That looks like it should work fine, as long as all these statements are run in the same database connections. Mar 21, 2023 at 14:58
  • The BEGIN - END block should assure that, right?
    – Sotis
    Mar 21, 2023 at 15:04
  • I just needed to make sure. You could still run the parts in different sessions (the END; would then generate a warning). Mar 21, 2023 at 15:08
  • What if you separate the currval('families_id_seq') statement into a separate statement, then pass that value to each of the INSERT INTO people statements? It would make the intent of your code clearer.
    – RonJohn
    Mar 21, 2023 at 15:18
  • @LaurenzAlbe sure! I implied that it was sent as a single statement, but you're right. My last comment was to double check I understand you correctly. :)
    – Sotis
    Mar 21, 2023 at 15:34

2 Answers 2

0

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');
2
  • Thank you for providing an example! I understand the code, but I don't understand how it solves my doubts. I mean, the point of my question is that I'm not sure if using the functions that get the current value of a sequence so to speak, are a robust way to make multiple inserts at the same time. Maybe your code is clearer, but the point is more about knowing if there is a drawback in using this system, and if is reliable under a stressed environment.
    – Sotis
    Mar 21, 2023 at 17:09
  • @Sotis the beauty of my method is that each statement can be in it's own transaction. Thus, each function call is really short. That increases throughput. (Heck, your program can just call nextval() directly. Once you have the b_id and f_id values held in application program variables, use them for as long as needed.
    – RonJohn
    Mar 21, 2023 at 17:20
0

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.

1
  • I see.. So if there is a trigger that inserts in one of those tables the ids would be misaligned.
    – Sotis
    Mar 22, 2023 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.