Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJmBQ-00000000QYH-2CMb for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJmBO-00000009A75-322n for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJb0p-00000006d9l-13jK for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:26:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJb0m-00000000GpN-3wXw for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:26:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=Hf4E4WPgAaL57tp5RkOYDo7yGCufrQOLIey8X4DRrYs=; b=QD6UHKh85yEG1n7RXST8x2PP/y kjpMonP2aMhHzSnHMm2DtM80Hq7keJQon/E8wREXM9ibzkBTpeKwAH5lhQncU6ay4MwZlH5KASwsH 9mP6NaWwJMZFiZahVu1gOtEiNQXrrFn8nbrxldrYyqEG5kYVld+k+tdsBvwcBE0feHKF5EHF8RYqB 2FpeWdradjSoM+N4MnN973UgxVDQJIozVLSOqXNqVPlKDdmmx62FnxbaIO9912+GikOMOYi+hJZcz FZKJDRLEiUM71RDHXUuDuPoJ/ggfeZvwzD/2HZYsU8vQ/Z8LHbWd9kxAGgCHCxee64YbPVlvsv3iq o8p87OpQ==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJb0m-00000000gwo-1IE2 for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:26:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJb0l-0000000230x-0mr7 for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:26:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19782: SET CONSTRAINTS in a released savepoint survives ROLLBACK TO an enclosing savepoint To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 21 Oct 2026 18:25:09 +0000 Message-ID: <19782-02b66fb19c9ce85b@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19782 Logged by: Alperen Keles Email address: alpkeles99@gmail.com PostgreSQL version: 14.24 Operating system: macOS 26 arm64 Description: =20 Hello, SET CONSTRAINTS run in a savepoint is undone by ROLLBACK TO that savepoint. But if it runs in an inner savepoint that is then released, a ROLLBACK TO the enclosing savepoint does not undo it, and the constraints keep the mode the rolled-back command gave them. CREATE TABLE p (k int PRIMARY KEY); CREATE TABLE c (f int CONSTRAINT c_f REFERENCES p DEFERRABLE); BEGIN; SAVEPOINT s0; SAVEPOINT s1; SET CONSTRAINTS ALL DEFERRED; RELEASE s1; ROLLBACK TO s0; INSERT INTO c VALUES (99); -- INSERT 0 1: c_f is still deferred COMMIT; ERROR: insert or update on table "c" violates foreign key constraint "c_f" DETAIL: Key (f)=3D(99) is not present in table "p". Without the inner savepoint the INSERT fails right away, as expected: BEGIN; SAVEPOINT s0; SET CONSTRAINTS ALL DEFERRED; ROLLBACK TO s0; INSERT INTO c VALUES (99); ERROR: insert or update on table "c" violates foreign key constraint "c_f" RELEASE SAVEPOINT merges s1's changes into s0, and ROLLBACK TO s0 rolls back all commands executed after s0 was established, so the two cases should behave the same: c_f should be immediate again after ROLLBACK TO s0. It works in the other direction too. With a DEFERRABLE INITIALLY DEFERRED foreign key, SET CONSTRAINTS ALL IMMEDIATE in a released inner savepoint survives ROLLBACK TO s0, so a transaction that inserts the child row before the parent row fails at the first INSERT even though it would commit without the savepoints: CREATE TABLE d (f int CONSTRAINT d_f REFERENCES p DEFERRABLE INITIALLY DEFERRED); BEGIN; SAVEPOINT s0; SAVEPOINT s1; SET CONSTRAINTS ALL IMMEDIATE; RELEASE s1; ROLLBACK TO s0; INSERT INTO d VALUES (5); ERROR: insert or update on table "d" violates foreign key constraint "d_f" INSERT INTO p VALUES (5); ERROR: current transaction is aborted, commands ignored until end of transaction block The same happens with SET CONSTRAINTS by name, with deferrable UNIQUE and EXCLUDE constraints, at deeper nesting (the SET in s2, RELEASE s2, then ROLLBACK TO s1 or s0), and with PL/pgSQL exception blocks, which are subtransactions too: an inner block that runs SET CONSTRAINTS ALL DEFERRED and exits normally, inside an outer block whose exception is caught, leaves the constraints deferred; so does calling a function whose exception block runs SET CONSTRAINTS inside a savepoint that is then rolled back. A ROLLBACK of the whole transaction is not affected. I could not get a violating row committed this way: the wrongly deferred check still runs at COMMIT, which fails. The effect is that the check happens at the wrong statement, so an error that the application expects to catch at the INSERT (with a savepoint or an exception handler) aborts the whole transaction at COMMIT instead, or, in the IMMEDIATE direction, a valid transaction is rejected. Reproduced on 14.24 (REL_14_STABLE b1c44848ce3), 18.6 (REL_18_STABLE a85b5523e67), 19rc1 (REL_19_STABLE 0fb192f1b02) and master dd568b047ac (2026-10-21), macOS arm64, all --enable-cassert. As far as I can tell, the cause is in trigger.c. The SET CONSTRAINTS state is saved for a subtransaction level only when SET CONSTRAINTS runs at that level (AfterTriggerSetState copies it into trans_stack[my_level].state). When the inner level commits, AfterTriggerEndSubXact frees its saved copy ("If we saved a prior state, we don't need it anymore") and does not pass it to the parent level, whose slot is still NULL. So when the parent level aborts, it finds nothing to restore ("If the saved state is NULL, then this subxact didn't save it, so it doesn't need restoring"). The code seems to date from the nested transactions commit (573a71a5da7, 8.0). Found with a query fuzzer. Regards, Alperen Keles