Re: Fix failing assert in deferred constraint trigger

[Fujii Masao]

On Wed, Sep 9, 2026 at 4:04 AM Fabrizio Mello <fabri...@planetscale.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