Date: Mon, 31 Mar 2025 21:26:48 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: pgsql-hackers@postgresql.org
Subject: Add comments about fire_triggers argument in ri_triggers.c
Message-Id: <20250331212648.ad4ab804559001d7f0788741@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Mon__31_Mar_2025_21_26_48_+0900_1OSvr8H/6=PK4=NG"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?qGm8afl5n3tt4XosWjPAF8KpS1cqdQAbpEbr2/y35iiOKje2elMjIycv5XX8?=
 =?us-ascii?Q?W3CJxQMCZ++w95h1YE2yFa+P59wP5JzqTjBcTBzSE0P+lsEfvld/P0W1yWPT?=
 =?us-ascii?Q?TZXmVsz2LYXSTXbt0ql1qNjq3u9/cfUPZK9Fa9b8jubUBx0ae/SaqhuRnRLr?=
 =?us-ascii?Q?QyLzImWSkq6g7jOZQ2eEABqnfrFvcLnFEoXqRy7JE31CbIZqIlqOnStcqIqH?=
 =?us-ascii?Q?Zx9fDePVmetts2a7ZarJZW2sLwUkpPF7WAN2lNYw3m6AL5jSYrL/IEfqBEq3?=
 =?us-ascii?Q?1qUPT3liaBCj2AHu8GV2oeBt4M2Xx42akcdSZkruN3dZGzSWhViL7s2aM/dh?=
 =?us-ascii?Q?34jG1fhHjzCra0ZGMZr75G2er1udITGxm3R9rIEj5BHyESOWWKjeF5txO59u?=
 =?us-ascii?Q?TT4aFzeKIlEECoDD/WQl32jFvz7C1GO/wHR3zWZcbQsKN2J1f0NvBbzBvUPd?=
 =?us-ascii?Q?KOKaTOsy2TDvVtumudn4QlotvOdfnJxK9HCgOeysesohA9KK5iM54am04+5c?=
 =?us-ascii?Q?d163NoAVw07aM5TIwDuy+uVCMxurplMRCegY8gjjpyGmCI89Ex8bRwU2+1av?=
 =?us-ascii?Q?9MUnZ9Qx8zhrOnIGFEMB7b2V/V6ZLXwe4RS/z4gD3WnIcaV+BkhIUoea3u6J?=
 =?us-ascii?Q?dsAzwnC53VKCnXVNaiMw9BBdnFxS4lwo37j3DLNKOSypxpbXW3ed4+cYzR0P?=
 =?us-ascii?Q?XwmqFrFPdJ+VDuLHdumch8qjRIX/gQfhtCmj0vHtmWiflQPA/oco8m+LVsSb?=
 =?us-ascii?Q?ol9MB7hVP6wp6fPBInvwyjOwXLlYgdz4ZGKsvoSBLbaYMg2eVPgtIvDS+IeX?=
 =?us-ascii?Q?DmLieDWw9c20r93Dg9I6g8ErsnvXTA4CIYMTVhWQf3rIrUg/VeP535gJZ4/O?=
 =?us-ascii?Q?cpm8c9DSzLEqbA2JiKX3GnRzg1GHeUvzDTtmmJpudbqH1Nr+Vq23B/F0Zy+g?=
 =?us-ascii?Q?JLKxnjGdEwOY/EBxj45TnOf4O4DXYga8Qd7nFIoAcotxzOlvk3RZi3J5EQlE?=
 =?us-ascii?Q?meD/QmGuFLRfYsgVir7u9jfKoqDA8SHSohCBlkpiO9SmJPS4zU26GkF49mo5?=
 =?us-ascii?Q?xLI4oF3SNVJc1eoJ1jlKW65W0aVSC2bD3kq7HH7nlvAPGU6nvirREQiumISN?=
 =?us-ascii?Q?GJlOgi9Tr9bR7X3JFScdbVbPkPzH3PhFk6hGanuRXI+J5kxV77nVoz/P3ZyG?=
 =?us-ascii?Q?ZrhWOcrQq/sozq2bssi3WxJKgEtlNvlj6zYxvMDAFS7o0YqGzanm4H1Czk8k?=
 =?us-ascii?Q?kmrZbYb6v54cDylV4YHvoqirAFCRUurta6y69/i5OM/fOHj7FvMpI/U/XqYO?=
 =?us-ascii?Q?wI3vXesQJWB2/Cx68SJSzq8GT/RrWJfsLnb9vVXTROnn4T0e4lvl0uOxV/xj?=
 =?us-ascii?Q?+16vC03Qk7vGABHtsnvQvIdUyZM0UBRuKRxtdm+KcLJ6Ir0a4JqwtHKXTn2Y?=
 =?us-ascii?Q?O3IDRdZy+qhg/XGyr5zFwAtmYu3sqoVWjvfVVHZNQvr3UqGYMgn/ge5vkTeV?=
 =?us-ascii?Q?/QuAD29RPi7bFT1wph4mn+fa673zlvSYwsqEMg5VfFx6gOGxD8zb96ZuOp/C?=
 =?us-ascii?Q?daSVkp4Yfx+heCkC7udmoHDW7GCu/gk0j+J1JPHOtw328Rh+NX/vDa2/66zZ?=
 =?us-ascii?Q?VBplkVEyKSfHy3cWXZYlivvSrbMAX5/hiXLN4wf0cgMj?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 e2e00aef-001d-4384-b279-08dd704f4f31
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 31 Mar 2025 12:26:49.1141
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 tmlSfuVXt7n0R83yRqwc1rjlGe372iElH7LNw/J23M7ulI/Nsk2yJ2zcQkrsCLFuVLSegDioyjyyyOHDpVoP3g==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYYP286MB1473
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20250331212648.ad4ab804559001d7f0788741%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Mon__31_Mar_2025_21_26_48_+0900_1OSvr8H/6=PK4=NG
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi,

SPI_execute_snapshot() has a argument called "fire_triggers".  If this is false,
AFTER triggers are postponed to end of the query. This is true in normal case,
but set to false in RI triggers.

This is introduced by 9cb84097623e in 2007. It is aimed to fire check triggers
after all RI updates on the same row are complete.

 However, I cannot find explanation of"why this is required" in the codebase. 
Therefore, I've attached a patch add comments in ri_trigger.c for explaining why
fire_triggers is specified to false.

SPI_execute_snapshot() are used in a few places in ri_trigger.c, but I added
the comments only in ri_PerformCheck() because SPI_execute_snapshot() are used
only for SELECT quereis in other places. Therefore, I wonder fire_triggers is
not needed to be false in these places, but I left them as is.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>

--Multipart=_Mon__31_Mar_2025_21_26_48_+0900_1OSvr8H/6=PK4=NG
Content-Type: text/x-diff;
 name="add_comments_fire_triggers_in_ri_triggers.patch"
Content-Disposition: attachment;
 filename="add_comments_fire_triggers_in_ri_triggers.patch"
Content-Transfer-Encoding: 7bit

diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index c4ff18ce65e..45d7e6268e4 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -2580,7 +2580,13 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 						   save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
 						   SECURITY_NOFORCE_RLS);
 
-	/* Finally we can run the query. */
+	/*
+	 * Finally we can run the query.
+	 *
+	 * Set fire_triggers to false so that AFTER triggers run at the end of
+	 * the query. This ensures check triggers fire after all RI updates on
+	 * the same row are complete.
+	 */
 	spi_result = SPI_execute_snapshot(qplan,
 									  vals, nulls,
 									  test_snapshot, crosscheck_snapshot,

--Multipart=_Mon__31_Mar_2025_21_26_48_+0900_1OSvr8H/6=PK4=NG--