Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRHxV-0007Iz-M4 for pgsql-sql@arkaria.postgresql.org; Mon, 07 Mar 2022 18:19:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nRHxU-0005mA-HX for pgsql-sql@arkaria.postgresql.org; Mon, 07 Mar 2022 18:19:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRHxU-0005ls-78 for pgsql-sql@lists.postgresql.org; Mon, 07 Mar 2022 18:19:48 +0000 Received: from mx0c-001a4c01.pphosted.com ([67.231.158.153]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRHxR-0003b3-6q for pgsql-sql@lists.postgresql.org; Mon, 07 Mar 2022 18:19:47 +0000 Received: from pps.filterd (m0210019.ppops.net [127.0.0.1]) by mx0c-001a4c01.pphosted.com (8.16.1.2/8.16.1.2) with ESMTP id 227HgkpV031595 for ; Mon, 7 Mar 2022 13:19:41 -0500 Authentication-Results: ppops.net; spf=pass smtp.mailfrom=sebastien.flaesch@4js.com; dkim=pass header.s=selector2-ourvolaris-onmicrosoft-com header.d=ourvolaris.onmicrosoft.com Received: from mail.ourvolaris.com ([206.25.40.41]) by mx0c-001a4c01.pphosted.com (PPS) with ESMTPS id 3em4ps70d0-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT) for ; Mon, 07 Mar 2022 13:19:41 -0500 Received: from VOL-SLO-EXCH2.vgnet.volgrp.com (172.23.0.52) by VOL-SLO-EXCH1.vgnet.volgrp.com (172.23.0.51) with Microsoft SMTP Server (TLS) id 15.0.1497.28; Mon, 7 Mar 2022 18:19:38 +0000 Received: from EUR04-HE1-obe.outbound.protection.outlook.com (104.47.13.51) by VOL-SLO-EXCH2.vgnet.volgrp.com (172.23.0.52) with Microsoft SMTP Server (TLS) id 15.0.1497.28 via Frontend Transport; Mon, 7 Mar 2022 18:19:38 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=eLxL7KPAnraTgcGN2IuCZ8XthVLIQ5uk3O0XpmWhwTAwxwQU8/qw8gUNdtaTCsZvVHUjFs3mFL4gl/0P2jirPoadlvWwdkc3HVxwnI1aodK28fbU/5U/3QB6g3keQ81fowaiC4tf9I2uJEYiR5+pm0AYLAEbOEYdSxjkkgtM0WYH6CRq2SvNRXK6tzO5QRz/qlIxPMTr/PfiNNi9Ocasr6gulBvW1Dr03cw+5fnf+f44ciIzM9q0FCLRiktW3Saz30XZIpjMBmGrsebP5Yf7x6Jl5s1LJH3JMAAt8gy2AwH8l7brU8vypNRm4dJ9TXtU/96AcgYKxhqyQsdRkhJg0w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=jtzuexuEeJkgdWcU7L768bKZsdY2yVjDEgErLdgkx10=; b=iAu+V0iUrlnpei7nD7Bd8XFCxLQZEUrtAMn2lcCT9jFsLV5QkrpoQIx/bXn31ZyHqYhjkSWncrs56+tOR0imqCCN36Xmk19sXwaA+cjH8p6N+dBdHDEaATzOSmnkTgSQH4PVH+Wp72hoTTa11bZssGRFpfeTYiUqTVMuvMGBtqB+sBjOyJV//rEncV6BWcOREqadeR+g/D1y7sdoaPi1diwAvwPpCxLY5aJUfpnon3G/hpUX/CCs3NkpsiXLHOejMUkQbyVAfUZAIn2KZlVVjUgfBr4bF5EW8X0d4POOEJkzEdw56hbAVCTsILLkTcZRzsj6ORh262VDPOPpY3ZQ1g== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=4js.com; dmarc=pass action=none header.from=4js.com; dkim=pass header.d=4js.com; arc=none Received: from DBAP191MB1289.EURP191.PROD.OUTLOOK.COM (2603:10a6:10:1c9::19) by PAXP191MB2120.EURP191.PROD.OUTLOOK.COM (2603:10a6:102:274::9) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.5038.14; Mon, 7 Mar 2022 18:19:37 +0000 Received: from DBAP191MB1289.EURP191.PROD.OUTLOOK.COM ([fe80::a533:756:799f:8c50]) by DBAP191MB1289.EURP191.PROD.OUTLOOK.COM ([fe80::a533:756:799f:8c50%4]) with mapi id 15.20.5038.026; Mon, 7 Mar 2022 18:19:37 +0000 From: Sebastien Flaesch To: "pgsql-sql@lists.postgresql.org" Subject: Best practice for naming temp table trigger functions Thread-Topic: Best practice for naming temp table trigger functions Thread-Index: AQHYMk3wKpd6RixG7ki7DRsQNFeEBw== Date: Mon, 7 Mar 2022 18:19:37 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: suggested_attachment_session_id: 8890d669-1d73-3a76-8a51-857df17cc49c x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 039a27ed-a6f8-42e8-a128-08da006709e3 x-ms-traffictypediagnostic: PAXP191MB2120:EE_ x-microsoft-antispam-prvs: x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: h/bDhzU23Bd+l/ivUDEmVPgii0E6pvpIwA1WrI2SV163cue35g1hCEKF9Yc0Rs1Z02n4rjy6g7rzO79WxbdP+dI5gkNRJfk4Z7/96FmmdYif1dTH5t4al0whcX2C6bGtsOZQ4zGCM0xSYEUZ2MXeFsD6FZvrQGblSlhiZ9K0j+SP1Tw4+7kLd24Frz64hKI4v7VIDa47moQchPUIfc6FzYa36nwjHWwWIc4K2lejFv1aobuNep7bPesJsgWRImfVPaaCFj3Gzt9cXr2P5h0RvVRX2EmIJ9hLIShEMau+bkrY5Bs5/o5kYu+6JWd2EMixO1AjEO80ouiswFQjTsjOJAg9XhrLrJSuecc14EMrjdaSEJI407O/4e/pc7C4v9dp2qg4UCGF0PDTOTRuggdUVbE+m2ZAoMpgdIo9/sZUQT7isjpxJhkSVqZwTwUucaL9QrldlF0Ps7KigchUTNBu1ABbQIFiWziyg/oYvHM0MGyHLOuvd1NYFv4hnY9rEFVSYfKDxjL54WHmDCLpfTLmiDzKZTrbhxnWem7vOak5pquYT5VhsgEGLfNvsmMk09QMFvdSf6KYU2drPsyFqPFJdaaEVroux7WXUkm96OG1XTSIdNdeqhQCTb3kY0qgwIKmVcpNllGy7tV+7nClZROs8eOEjdqFDzpIpotlF0SvfyLr5S9QLtjIBs1Lkg/J7yfPgPC+r3v0eacKeV8oLLtuww== x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DBAP191MB1289.EURP191.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230001)(4636009)(366004)(66446008)(38070700005)(19627405001)(91956017)(8676002)(76116006)(66946007)(86362001)(6916009)(316002)(83380400001)(33656002)(64756008)(66476007)(26005)(44832011)(186003)(66556008)(55016003)(71200400001)(122000001)(508600001)(38100700002)(9686003)(7696005)(6506007)(8936002)(5660300002)(2906002)(4744005)(52536014);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?lBHlvxnYNmvN6brINN2JdmNIz7PhTg/dfLdFrpuTnGxlXn/zWtH27HDoJr?= =?iso-8859-1?Q?R3VFb/PoFsT1EdN+nC04UAEiLN+/oKaXRuK3siIo+JsfqOGwJ1xbGrcGCS?= =?iso-8859-1?Q?xS7ZDfRpX+c//xdoKDamw9UeScIBI4nqCHRcfXYxwslWmQe4xX0MUGhhV0?= =?iso-8859-1?Q?gFI3gQY56JEhPDGqtePpG9/IV/9tw0d4YaztZ+ywaCuuY4sIbCypAhmT5y?= =?iso-8859-1?Q?QkpXg5VS2vSyPAQBps/sDvEyc1fXkUofmj6OCs/aH0owSKK+vWga+3di9d?= =?iso-8859-1?Q?fA31urTlnLjvuB8XyOtWWDK/SP0GuTrMl5TcL4B2Hltq0O15O0IErWIga+?= =?iso-8859-1?Q?NoP93/DMBbEpiUx+S7UKmI2PfMNBJxT31Y7zLX/y6XJdpGSZnkNGbJt5Jh?= =?iso-8859-1?Q?RYlZqki8PPKGfPEvdDYe1WL5MIjujyV1fJ+vqKg1bFY4GbXflW/YyAcLfz?= =?iso-8859-1?Q?vI0CuLx8NfKJ1+jo6EVly1IH9qUJ4DjFe1arPyC0xwSgGEv4f076JCh/0U?= =?iso-8859-1?Q?Y/666WiPWzt+bZn8EMMp3Cwfv8y4aba0Jr3yCZmYMKhKTvlF+cZDAyT2aL?= =?iso-8859-1?Q?pF9stf8sttgGTWME0brjazyeR9Bll0paZE/6XN2GYQGX7sG8h1gp0exuQW?= =?iso-8859-1?Q?FYVIYZQtbvktmFrx9izZ72oWNM28BrjhFffymn2Y5BmYslem5OPAZPKkfi?= =?iso-8859-1?Q?HjHeME8wQvWcznSsAXBRPlpXoYdSSJVXWEVmSn5Gva8KB6Yj6OEDFxQmmi?= =?iso-8859-1?Q?Nqc4kDw596HkVxgWSpngj5mjnRPHT60kAccgdevCh8c1kWcfz4HDoq2cEV?= =?iso-8859-1?Q?VJ2bvQtXhcKXe84lmBHfEuPu5jN7ATHJ25b6G3yabtxzJ9gap+sVPJfCqW?= =?iso-8859-1?Q?5NGh7Ja/191EA7BT79LmDq49fx+zAGFKSz13rQJAWqW19Hn3QW4Na2SB+1?= =?iso-8859-1?Q?G1aO25dM7Vdat2i4Q3QGFho6s5Y5nsJiFM6un+JlwYSj1FRt9tQe4VuqJ9?= =?iso-8859-1?Q?F1PBWLR4OgCauseRMAa/1gTx5i7S+IZHDMBRyhromT9EXveoXY+n2m0Gwm?= =?iso-8859-1?Q?1ya8Uqcmm4QehaLvH/h1Sdp7AgqzmLCSuwMDo0TIeBld4Qx0z3kNL+dExS?= =?iso-8859-1?Q?YQHenpVq/6VOaPk4l+8YAYX7By+tU9xLsxuoCBpL1AEsErEddnSveLMd0e?= =?iso-8859-1?Q?uxcBDgO4xCQ/kP4N3askgO9Qlxx693wBhliarX8YmYggPL4tO5K9GZX/mM?= =?iso-8859-1?Q?WqtaPE4BiyAVtgn7USW1cJoLl8ub36viBFrY8jeZCJqpvGDP81TDfUOHCZ?= =?iso-8859-1?Q?g8OwodpuvWMetDprMZ+wHp6v4+pAw+zRVlvfOd1FswkGYwZuIKO3kLdTAs?= =?iso-8859-1?Q?yU13EapzG1HN588/dnAct7gwfk9N4r2SBPb7TiTaT1/iR5nAqCPRcsxnnu?= =?iso-8859-1?Q?qROZt84xmAwAx2IuL6hG4qQ0xKHTPb1UxbZ9dXVce8fycGVa7cAJTCKmoH?= =?iso-8859-1?Q?UjUGjC2UThGUwl9lQV/0S8kk48DQkuvLuJZCmIMo2souxDnqUqJ/BrOek0?= =?iso-8859-1?Q?QwoGCjPG994+f9M/5o3F7ChSD9ihH+Mh9mN1CHeblQZwCB7+sSsoBjLJ1G?= =?iso-8859-1?Q?Ug5xyxG0J+mXfIBg1j8S9LJpb3j2ABEz4gpgG8ok5nDx4PSS0LbQnxVKiJ?= =?iso-8859-1?Q?ZDdbisMtVkKsDPrkmjlbHGEriP0XP81k+qk8ZrBZWj7nUC5D8VHNUfTpTh?= =?iso-8859-1?Q?Tnwg=3D=3D?= Content-Type: multipart/alternative; boundary="_000_DBAP191MB128959B0FE1ABE28C04ACDFFB0089DBAP191MB1289EURP_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DBAP191MB1289.EURP191.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 039a27ed-a6f8-42e8-a128-08da006709e3 X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Mar 2022 18:19:37.3265 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 75c696ec-5bfb-4892-9a0c-9187a9061cd6 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: tqEVSL99mUNn+c8LkhiQfw8fQ6Vvryhh+5Krjh6eV/fy3TfnykXxFLVJiW7t4qaO6dmVHNwfLSO9QrnktXK0hnhQaboGn7vsKUopR2tQRyM= X-MS-Exchange-Transport-CrossTenantHeadersStamped: PAXP191MB2120 X-OriginatorOrg: 4js.com X-C2ProcessedOrg: 2f0c79df-a80b-40c2-9362-4c5fa2dae201 X-Proofpoint-GUID: 4LO2fLG1oz3CCOJj3X7XSsG7cX1svGag X-Proofpoint-ORIG-GUID: 4LO2fLG1oz3CCOJj3X7XSsG7cX1svGag X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.205,Aquarius:18.0.816,Hydra:6.0.425,FMLib:17.11.64.514 definitions=2022-03-07_09,2022-03-04_01,2022-02-23_01 X-Proofpoint-Spam-Reason: safe List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DBAP191MB128959B0FE1ABE28C04ACDFFB0089DBAP191MB1289EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! Temporary tables can get triggers in PostgreSQL. Triggers are defined with a trigger function. A temp table name is local to the current SQL session so there is no confli= ct with concurrent code doing the same CREATE TEMP TABLE mytable ... However, user functions called by triggers are global to the schema and can= enter in conflict... What is the best practice, to avoid such issues? I guess I could use some session id to build a unique function name. But I would like to have that function dropped when the temp table is destr= oyed ... Or, is there a way to define triggers directly with some anonymous code blo= ck? Seb --_000_DBAP191MB128959B0FE1ABE28C04ACDFFB0089DBAP191MB1289EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello!

Temporary tables can get triggers in PostgreSQL.

Triggers are defined with a trigger function.

A temp table name is local to the current SQL session so there is no confli= ct with concurrent code doing the same CREATE TEMP TABLE mytable ...

However, user functions called by triggers are global to the schema and can= enter in conflict...

What is the best practice, to avoid such issues?

I guess I could use some session id to build a unique function name.

But I would like to have that function dropped when the temp table is destr= oyed ...

Or, is there a way to define triggers directly with some anonymous code blo= ck?

Seb
--_000_DBAP191MB128959B0FE1ABE28C04ACDFFB0089DBAP191MB1289EURP_--