Fix failing assert in deferred constraint trigger

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

On Wed, Sep 9, 2026 at 12:51 PM Fujii Masao <masao.fujii@gmail.com> wrote:

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:

I've added this test. Attached is the updated version of the patch.

BTW, I found that Patrick Reynolds <patrick@piki.org> is listed in the
From field of the patch. Does that mean he is the author of the patch,
rather than you? I'm just asking because I need to know who to list in the
Author tag of the commit message.

Regards,

--
Fujii Masao

On Sep 11, 2026, at 09:44, Fujii Masao <masao.fujii@gmail.com> wrote:

On Wed, Sep 9, 2026 at 12:51 PM Fujii Masao <masao.fujii@gmail.com> wrote:

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:

I've added this test. Attached is the updated version of the patch.

BTW, I found that Patrick Reynolds <patrick@piki.org> is listed in the
From field of the patch. Does that mean he is the author of the patch,
rather than you? I'm just asking because I need to know who to list in the
Author tag of the commit message.

Regards,

--
Fujii Masao
<v2-0001-Fix-failing-assert-in-deferred-constraint-trigger.patch>

V2 LGTM.

One nitpick is that, the fix covers both COMMIT and PREPARE TRANSACTION, but v2 only adds a regression test for the COMMIT case. Should we test both paths?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Fri, Sep 11, 2026 at 11:33 AM Chao Li <li.evan.chao@gmail.com> wrote:

V2 LGTM.

Thanks for the review!

One nitpick is that, the fix covers both COMMIT and PREPARE TRANSACTION, but v2 only adds a regression test for the COMMIT case. Should we test both paths?

I think the COMMIT test is enough for this small assert-only fix.

But,iIf many others prefer explicit coverage for PREPARE TRANSACTION as well,
I'm fine with adding that test. It would probably require some additional test
logic to handle environments where max_prepared_transactions is disabled
(e.g., add "skip" like prepared_xacts.sql does).

Regards,

--
Fujii Masao

On Thu, Sep 10, 2026 at 10:44 PM Fujii Masao <masao.fujii@gmail.com> wrote:

On Wed, Sep 9, 2026 at 12:51 PM Fujii Masao <masao.fujii@gmail.com> wrote:

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:

I've added this test. Attached is the updated version of the patch.

Thanks

BTW, I found that Patrick Reynolds <patrick@piki.org> is listed in the
From field of the patch. Does that mean he is the author of the patch,
rather than you? I'm just asking because I need to know who to list in the
Author tag of the commit message.

Yes he is the original author of the patch, which he worked on
internally at PlanetScale. If you don't mind, please use
piki@planetscale.com in the commit message.

Regards,

--
Fabrízio Mello
PlanetScale Postgres Core Team

On Wed, Sep 9, 2026 at 12:52 AM Fujii Masao <masao.fujii@gmail.com> wrote:

[...]

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;

Having a look into it thanks!

--
Fabrízio Mello
PlanetScale Postgres Core Team

On Fri, Sep 11, 2026 at 10:48 AM Fabrizio Mello
<fabrizio@planetscale.com> wrote:

On Wed, Sep 9, 2026 at 12:52 AM Fujii Masao <masao.fujii@gmail.com> wrote:

[...]

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;

Having a look into it thanks!

Confirmed that the patch works fine also with "WITH HOLD" cursor.

Regards,

--
Fabrízio Mello
PlanetScale Postgres Core Team

On Fri, Sep 11, 2026 at 10:43 PM Fabrizio Mello
<fabrizio@planetscale.com> wrote:

Yes he is the original author of the patch, which he worked on
internally at PlanetScale. If you don't mind, please use
piki@planetscale.com in the commit message.

OK, I've updated the commit log message and used that email address for the
Author tag.

I've also listed your name in this email, "Fabrízio Mello", in the commit
log as Reported-by. However, I found that your name, "Fabrízio de Royes
Mello", was used in some previous commits you were involved in. Which name
would you prefer me to use?

Since this issue can occur in all supported versions, I've also created
patches for the back branches. The patches are attached.

Regards,

--
Fujii Masao

On Tue, Sep 15, 2026 at 12:51 PM Fujii Masao <masao.fujii@gmail.com> wrote:

On Fri, Sep 11, 2026 at 10:43 PM Fabrizio Mello
<fabrizio@planetscale.com> wrote:

Yes he is the original author of the patch, which he worked on
internally at PlanetScale. If you don't mind, please use
piki@planetscale.com in the commit message.

OK, I've updated the commit log message and used that email address for the
Author tag.

Thanks!

I've also listed your name in this email, "Fabrízio Mello", in the commit
log as Reported-by. However, I found that your name, "Fabrízio de Royes
Mello", was used in some previous commits you were involved in. Which name
would you prefer me to use?

Fabrízio de Royes Mello

Since this issue can occur in all supported versions, I've also created
patches for the back branches. The patches are attached.

LGTM thanks!

Regards,

--
Fabrízio de Royes Mello
PlanetScale Postgres Core Team

On Thu, Sep 17, 2026 at 5:13 AM Fabrizio Mello <fabrizio@planetscale.com> wrote:

Since this issue can occur in all supported versions, I've also created
patches for the back branches. The patches are attached.

LGTM thanks!

I've pushed the patch. Thanks!

Regards,

--
Fujii Masao