Why is my deferred constraint checking an intermediate state and not the final state of the record?
We use PostgreSQL 16.9 for our database and we use Python3 and Psycopg2 to manage our data pipeline.
We have a table in our database, member that contains the member data (primary key: member_id), along with its parent_id that is a foreign key reference to another member.
This table is populated through three means:
The source of the record is identified through the source_id field on the table.
There are two fields in the member table that are managed through our application. These fields are required for all application or external data drop source records.
I implemented this constraint using a constraint trigger rather than a check(function) due to the foreign key reference existing on the source field. I also set it to be deferrable initially deferred due to the stored procedure for the external data drops.
For the external data drops, we use a stored procedure to load the data from our staging table stage to our working table member. This stored procedure inserts new members or updates existing members and then sets the parent_id and then sets the child's inherited values using the parent_id.
Each record's parent references either a record that existed BEFORE the new drop, a record that was inserted DURING the new drop, or is null.
The stored procedure does not set the parent_id at the same time as the inherited values because if the parent_id record is a new record with a null parent_id then the inherited values should be set to the default values.
The expected behavior is that the exception is only thrown if the END state of the record violates the constraint due to both the trigger being defined as deferrable initially deferred and that set constraints all deferred; is the first line in the procedure.
I have added raise notice statements throughout the stored procedure that log the output of various select statements that get the values from the member table at various times in the procedure.
The last raise has the final values as being not null which satisfies the constraint, but when the procedure ends, it is throwing an exception and the exception is outputting the values of a previous state.
I have added the CTID value to be logged during the procedure and returned in the exception from the trigger in order to confirm the assumption that it was not validating the final version of the record being inserted and the final CTID value does not match the CTID value returned in the exception.
-- create tables:
drop table if exists member;
drop table if exists first_inherited_field;
drop table if exists second_inherited_field;
drop table if exists source;
drop table if exists stage;
CREATE TABLE source
(
source_id integer primary key generated always as identity ,
name varchar UNIQUE not null
);
insert into source (name)
values ('APP'),
('PROC');
CREATE TABLE first_inherited_field
(
first_inherited_field_id integer primary key generated always as identity,
name varchar not null unique
);
insert into first_inherited_field (name)
values ('DEFAULT'),
('TEST');
CREATE TABLE second_inherited_field
(
second_inherited_field_id integer primary key generated always as identity,
name varchar not null unique
);
insert into second_inherited_field (name)
values ('DEFAULT'),
('TEST');
drop table if exists member;
CREATE TABLE member
(
member_id integer primary key generated always as identity,
name varchar NOT NULL,
first_inheritable_field_id integer references first_inherited_field (first_inherited_field_id),
second_inheritable_field_id integer references second_inherited_field (second_inherited_field_id),
source_id integer NOT NULL references source (source_id),
parent_member_id integer references member (member_id)
-- <other fields>
);
CREATE UNIQUE INDEX family_name_source_unq ON member (name, source_id);
CREATE TABLE stage
(
name varchar,
parent_name varchar
-- <other fields>
);
INSERT INTO stage (name, parent_name)
values ('TEST', 'PARENT'),
('PARENT', null);
-- function and trigger definition:
CREATE OR REPLACE FUNCTION required_fields() RETURNS TRIGGER AS
$$
DECLARE
app_source_id integer = (select source_id from source where name = 'APP'); -- the application has specific restrictions that do not apply to the data pipeline data
proc_source_id integer = (select source_id from source where name = 'PROC'); -- this is the source handling the stored proc data called by python
BEGIN
IF new.source_id = app_source_id
THEN
RETURN NEW;
--<do some checks based on app constraints>
ELSEIF new.source_id = proc_source_id
THEN
IF (new.first_inheritable_field_id is not null
AND new.second_inheritable_field_id is not null)
AND tg_op ILIKE 'UPDATE'
THEN
RETURN NEW;
ELSE
RAISE EXCEPTION 'Required fields are null. Member_Id: %, MEMBER NAME: %, first_inheritable_trait_id: %, second_inheritable_trait_id: %, parent_member_id: %, CTID: %', new.member_id, new.name, new.first_inheritable_field_id, new.second_inheritable_field_id, new.parent_member_id, new.ctid;
END IF;
ELSE
RETURN NEW;
END IF;
END;
$$ LANGUAGE PLPGSQL;
CREATE CONSTRAINT TRIGGER check_required_fields
AFTER INSERT OR UPDATE
ON member
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION required_fields();
-- stored proc:
CREATE OR REPLACE PROCEDURE load_proc_data() AS
$$
DECLARE
proc_source_id integer = (select source_id from source where name = 'PROC');
insert_count integer;
default_first_trait integer = (select first_inherited_field_id from first_inherited_field where name = 'DEFAULT');
default_second_trait integer = (select second_inherited_field_id from second_inherited_field where name = 'DEFAULT');
BEGIN
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO member(name, source_id)
SELECT name,
proc_source_id
FROM stage
WHERE not exists (SELECT 1
FROM member
WHERE member.name = stage.name
AND member.source_id = proc_source_id);
get diagnostics insert_count = row_count;
with parents as (select stage.name,
stage.parent_name,
member.member_id as child_id,
member.name,
parent.member_id as parent_id,
parent.name as parent_name
from stage
left join member
on member.name = stage.name
left join member parent
on parent.name = stage.parent_name)
UPDATE member
SET parent_member_id = parents.parent_id
from parents
where parents.child_id = member.member_id
and member.parent_member_id is distinct from parents.parent_id;
if insert_count > 0 THEN
update member
set first_inheritable_field_id = default_first_trait,
second_inheritable_field_id = default_second_trait
where member.parent_member_id is null
and member.source_id = proc_source_id;
update member
set first_inheritable_field_id = coalesce(parent.first_inheritable_field_id, default_first_trait),
second_inheritable_field_id = coalesce(parent.second_inheritable_field_id, default_second_trait)
from member parent
where parent.member_id = member.parent_member_id
and member.first_inheritable_field_id is null
and member.second_inheritable_field_id is null
and member.source_id = proc_source_id;
raise notice '(LOCATION: In IF Statement) Member Name: TEST - first_inherited_field_id: %)', (select first_inheritable_field_id from member where name = 'TEST');
raise notice 'second_inherited_field_id:%', (select second_inheritable_field_id from member where name = 'TEST');
raise notice 'CTID: %', (select CTID from member where name = 'TEST');
end if;
truncate stage;
raise notice '(LOCATION: AT END) Member Name: TEST - first_inherited_field_id: %)', (select first_inheritable_field_id from member where name = 'TEST');
raise notice 'second_inherited_field_id:%', (select second_inheritable_field_id from member where name = 'TEST');
raise notice 'CTID: %', (select CTID from member where name = 'TEST');
end;
$$ LANGUAGE PLPGSQL;
call load_proc_data();
Output:
public> call load_proc_data()
[2025-08-22 11:13:24] [P0001] ERROR: Required fields are null. Member_Id: 1, MEMBER NAME: TEST, first_inheritable_trait_id: <NULL>, second_inheritable_trait_id: <NULL>, parent_member_id: <NULL>
[2025-08-22 11:13:24] Where: PL/pgSQL function required_fields() line 18 at RAISE
(LOCATION: In IF Statement) Member Name: TEST - first_inherited_field_id: 1)
second_inherited_field_id:1
(LOCATION: AT END) Member Name: TEST - first_inherited_field_id: 1)
second_inherited_field_id:1
The constraint rejects all INSERTs coming from the procedure:
IF new.source_id = app_source_id THEN RETURN NEW;
ELSEIF new.source_id = proc_source_id
THEN IF (new.first_inheritable_field_id is not null
AND new.second_inheritable_field_id is not null)
AND tg_op ILIKE 'UPDATE'-----------------here
THEN RETURN NEW;
ELSE RAISE EXCEPTION ''
END IF;
ELSE RETURN NEW;
END IF;
Even if you managed to fix the two fields, it'd still throw the exception because of the third condition. The trigger fires separately for the insert, then the updates, even though they concern the same row.
The less obvious problems:
update is really a delete of the old row and an insert of a completely new one - which is why ctid's you've been inspecting, change and you're technically considering multiple different rows, not just "the one I'm working on".update statements locate and modify the row you just inserted earlier, they do not affect the new record that the already fired constraint trigger is waiting to inspect to check that initial insert payload. Only another trigger firing earlier in the chain could affect that record.In this example, the reason deferred unique works fine if you insert a duplicate then update it to fix that, is because Postgres sees the original row with the offending value got invalidated. In your case, you're only checking the unchanged new record, expecting it to reflect consequent updates. Here's another example where you see even though the row got invalidated and it worked with a deferred unique, a constraint trigger checking new doesn't work the same.
A simple way to to fix that would be to not insert at the start of your procedure. Prepare the record in a set of variables per field, or member-type record variable, modify it through assignments throughout the procedure and only insert it at the end, when its final, non-violating state is ready.