Re: Fix failing assert in deferred constraint trigger

On Wed, Sep 9, 2026 at 4:04 AM Fabrizio Mello <fabrizio(at)planetscale(dot)com> wrote:
> Check the attached patch for the fix.

Thanks for the patch! It looks good to me.

One comment: isn't it be better to add a test for exception handling
in a deferred constraint trigger at COMMIT? For example, in triggers.sql:

----------------------------------------------------
@@ -1590,6 +1590,31 @@ create constraint trigger crtr
after insert on foo not enforced
for each row execute procedure foo ();

+-- Test exception handling in a deferred constraint trigger at COMMIT.
+create table deferred_trigger_test (a int);
+create function deferred_trigger_func() returns trigger
+ language plpgsql as $$
+begin
+ perform 1 / 0;
+ return new;
+exception when division_by_zero then
+ raise notice 'caught division_by_zero';
+ return new;
+end;
+$$;
+create constraint trigger deferred_trigger
+ after insert on deferred_trigger_test
+ deferrable initially deferred
+ for each row execute function deferred_trigger_func();
+
+begin;
+insert into deferred_trigger_test values (1);
+commit;
+select * from deferred_trigger_test;
+
+drop table deferred_trigger_test;
+drop function deferred_trigger_func();
+
--
-- Constraint triggers and partitioned tables
create table parted_constr_ancestor (a int, b text)
----------------------------------------------------

BTW, WITH HOLD cursor seems to be able to cause the same issue:

CREATE FUNCTION hoge() RETURNS integer
LANGUAGE plpgsql VOLATILE AS $$
BEGIN
PERFORM 1 / 0;
RETURN 0;
EXCEPTION WHEN division_by_zero THEN
RETURN 1;
END;
$$;

BEGIN;
DECLARE c NO SCROLL CURSOR WITH HOLD FOR SELECT hoge();
COMMIT;

Regards,

--
Fujii Masao