PostgreSQL Deferrable constraints

Question

In this part of code I insert data in tables.

//some code...
const classIdArr = request.body.classIdArr;

await dbPool.query('BEGIN');
const result = await dbPool.query('Insert Into T_SUBJECT (NAME, NAME_ENG) Values ($1, $2) Returning ID', [subject.name, subject.name_eng]);

const subjectId = result.rows[0].id;

await Promise.all(classIdArr.map(async (classId) => {
    await dbPool.query('Insert Into T_BIND_SUBJECT_CLASS (CLASS_ID, SUBJECT_ID) Values ($1, $2)', [classId, subjectId]);
}));
await dbPool.query('COMMIT');

//some code...

T_BIND_SUBJECT_CLASS has constraints. There is one of them

...
CONSTRAINT t_bind_subject_class_fk_subject FOREIGN KEY (subject_id)
        REFERENCES public.t_subject (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED
        NOT VALID
...

if classIdArr has length === 1 there is no problem, but if > 1 - postgreSQL throws error:

insert or update on table "t_bind_subject_class" violates foreign key constraint "t_bind_subject_class_fk_subject"

Why? This constraint is DEFERRABLE INITIALLY DEFERRED. As I understand it, this means that all checks occur at the end of the transaction, but in this case they are checked after the first insertion

Please, help. Thank you!

Answer 1

You are using a connection pool, so the statements in your code may be executed using different connections, i.e. in different transactions.