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 1nRbve-0002Le-5W for pgsql-sql@arkaria.postgresql.org; Tue, 08 Mar 2022 15:39:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nRbv6-000250-Ng for pgsql-sql@arkaria.postgresql.org; Tue, 08 Mar 2022 15:38:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRbv6-00024p-60 for pgsql-sql@lists.postgresql.org; Tue, 08 Mar 2022 15:38:40 +0000 Received: from mx0d-001a4c01.pphosted.com ([67.231.151.23] helo=mx0c-001a4c01.pphosted.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRbv0-00010a-KD for pgsql-sql@lists.postgresql.org; Tue, 08 Mar 2022 15:38:38 +0000 Received: from pps.filterd (m0075702.ppops.net [127.0.0.1]) by mx0d-001a4c01.pphosted.com (8.16.1.2/8.16.1.2) with ESMTP id 228Ch4T1023651 for ; Tue, 8 Mar 2022 10:38:32 -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 mx0d-001a4c01.pphosted.com (PPS) with ESMTPS id 3em5b09yt6-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT) for ; Tue, 08 Mar 2022 10:38:32 -0500 Received: from VOL-SLO-EXCH4.vgnet.volgrp.com (172.23.0.54) by VOL-SLO-EXCH4.vgnet.volgrp.com (172.23.0.54) with Microsoft SMTP Server (TLS) id 15.0.1497.28; Tue, 8 Mar 2022 15:38:25 +0000 Received: from EUR02-HE1-obe.outbound.protection.outlook.com (104.47.5.51) by VOL-SLO-EXCH4.vgnet.volgrp.com (172.23.0.54) with Microsoft SMTP Server (TLS) id 15.0.1497.28 via Frontend Transport; Tue, 8 Mar 2022 15:38:25 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=mv7vBPc4IW9t5EaawIpuuaIqqTDOKTSDbyEKkOUfTrZbaz5RKhoWrZNVaVZbXkAOZJSm2NEcGdORWNy9sBHQJh4Gq/ErGw4Etw5ErIcTsPyp4FlM6ofJUSmiCSNyHN428zo6GK68eTuzN33H6xN33EswYDOF2k+s50t6BSnDv+7LYpJs23msSsoNEOZifF4yX1jXZao3YcRrSB6pkoYbzCsxdmah6rhciQP05E98lUoVsRPw0L7h9BGVyvhWRKFRB9NUnl6SiAr2bD93HLxefNs59gv6lZavtFdosmDVaZxyxggHmS1731X7hWpADo3tje7Smi9IJ6wntZIXtYzT3w== 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=5XbUXZNU1pRAUfUBATIqJQ5bOxfVAhPb8aYNjmGEPfg=; b=eFfOe6zjwGxYelyHI/q+ymVU1WJ56POzr+8AuN1uMFNZjcy6VjQrgbaLGCpH5/KW3b7YI1STUtIbyDXuOVMpxM3vu4AD0X1sdS0MhRFa1IfLNIeiI4oiXIYR5lb+q7ACK9zOg5gNaa5du2drOuJcC5NSQJKh0zs5KUrFjQpnHRe0RcnvleKFVJcHBnYTztGP1b8SvdkwD1UOLXNXLd+HdXRZo9VZwtxgXdBJT1QJnf3YgbPSmHcWvfMVYJBRFA/KJaqssI8+TGqit3ix/TMH+6QklGONy2W+IzdmcXJpXgZPSx0RjTgzIDeImpBJ0s5oS99g48HBGuymiUeVixmCvw== 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 DBAP191MB1242.EURP191.PROD.OUTLOOK.COM (2603:10a6:10:1c3::18) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.5038.16; Tue, 8 Mar 2022 15:38:24 +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.027; Tue, 8 Mar 2022 15:38:24 +0000 From: Sebastien Flaesch To: "pgsql-sql@lists.postgresql.org" Subject: Re: Best practice for naming temp table trigger functions Thread-Topic: Best practice for naming temp table trigger functions Thread-Index: AQHYMk3wKpd6RixG7ki7DRsQNFeEB6y1nug6 Date: Tue, 8 Mar 2022 15:38:24 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: suggested_attachment_session_id: c90b5186-84e7-4bdf-f6cf-0da24679777b x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 48dbff4d-2962-4c7d-edde-08da0119aee4 x-ms-traffictypediagnostic: DBAP191MB1242: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: Z8qfI0Nagm+uCu1xRrXXfBLEs60Qbs8zkfGdkB4SaIiQLqeHpYYMDHY3tKzvHthREpujhNezv7e9xcRMWL0CqMTD42MBmJaMgtl5sDBXZt4oFfQSePD6A6tYPPya24JgXcV/gtw53ghW+m6dv11QFQq8o2noRgUyUUtnm7nvSiDOKximSRuiwjU7dYPoJGMubqyR2JWXj9iVkY3yQeTQwmUgDzQ63njdD0OjGgQtW9P8pEvOXFo1ivrUXoctu7dUi4sUHD4pdrRfjN12/59HbZKPQWdQDRDJqFpxZPtAaZ0T6FemF9ETBnWvx+mnWXGbGGAEXsUbuWssuPqgEWFt6DfbcKCSjNGLq7c2lhidJghKWn764D9CU7BCAfML0ycAHagW6DYY8Gp8B1UqCz/c7xnarThrv6rUgLtBvQ60uOgCXqHcYXXLfcj1PMBqLH6H856U/x4GxE4WVr3V9ye+S4HQihswD/sjZABdRNBk5GnzBxrBXPuTw3gpvoQlV6PF5aKgNEMDK7UzdTDSoHOh43/xCRUCMFe8oU6LM/zBDD/Ts6mDwag1uH2ZtMlYCP/60iEY4c8MyiXqTGm6OjNUzt051IdC5L+xlXFqZ0rVdUh4AejUNGSGSwKqPGAxPv0YR8aYtrmUZGXF7RfAEXOhCPtIDgMwKMM03ReWgxylpfM3lnCGQye6fFz/wTpzVypULItC/MA+XzLdgZlhU6RX2f3VxpX+T0gGAHmoM0cQvFh6eCceYlqAjYYlx9R1mAktG8+A8mmtcB6V0SYBpA5y320gOa/t4TedSWB7biwdglE= 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)(83380400001)(9686003)(19627405001)(8676002)(21615005)(186003)(91956017)(76116006)(64756008)(66946007)(66476007)(66556008)(122000001)(38070700005)(26005)(55016003)(508600001)(166002)(66446008)(7696005)(33656002)(6506007)(5660300002)(52536014)(71200400001)(44832011)(86362001)(966005)(8936002)(53546011)(2906002)(6916009)(316002)(38100700002);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?ScsYsNMJkAq3/41/pVGFh/5ejn4zKjAHHZ0f7n8NIwVozr9nt6rplJVmwGGb?= =?us-ascii?Q?M2Xdb1NgJCrnOZycdc2N6oaqavzFIHKXJVzlEPny9A03lwZsBZC+MxVY2QWS?= =?us-ascii?Q?60xPb6Og1tPu1fnUIxYdgElCGeerUYIQoYLYN87UsCDbEfpu7wclPatN/rwU?= =?us-ascii?Q?kb546nR9fIvqS9wuSLZx0O+m5OHtMl0H5xsm99EgzpoNezhsLdXTdbEymkVq?= =?us-ascii?Q?7vN30GWVQRtXYCVqvaSvfUftRVpyNLIZPMZS7hRa/nJFw0A9Z8S5UKcLtGdP?= =?us-ascii?Q?MoDc87tzQHHC3V8uli7Oy7ahW9Q7fUiwrsauPP87XELkbn5LjbP4t82IxVWe?= =?us-ascii?Q?AUQcEl4ed+vGEPBFD3givcUCZwg/NEEbUoh7dHWP6y9jFywcNL6DdkHFhC+S?= =?us-ascii?Q?sd2FPkTpSIOI/oh6McecLgdus9cMdv+z8phlQRXwAzqoUG3I12csOKFMkLD7?= =?us-ascii?Q?4Hfj8xjhG/D2Jn7gBinLPxlFm5SVVSPv9MfWXDnpMBAfTGURaP9Nf69igNMg?= =?us-ascii?Q?aDXTmfbex1m3j4wSBTJHXUX5dEkcAN8DSJtdLTegLXTONWwYt70/0ZTsnie/?= =?us-ascii?Q?q07MyThw6pCpy6FKMy+cQN3VX0rPyUCHlK5sXb3tq17QIsi4kaX8rSQeN7Fh?= =?us-ascii?Q?OpopqtShY1JPw7EylrvFu1k8ZKnvF1J0ozNFpN9uiE/ZmdekzMaw4ukEiEVK?= =?us-ascii?Q?R/m5HiOBPxLlzICOcq+P1uPbRXtWOb9Zqds9l/hfACwD1qJlSDX7oIq8n3ju?= =?us-ascii?Q?iUuC9X06tqB7rRw2fsOzF/CNk0DUpJ/7HDmSMkl4tTYwBWEHsXk62eSXzCNf?= =?us-ascii?Q?uFcw4EAPlluZGL3H1CyrJC0Q/Su/PoJ95LOBGV4KjqOYbur5Hc4x9ES1oqZn?= =?us-ascii?Q?R8wjdj7XsdI9kFmphwYoM64HFm39hmtA3LvsLnxHu9maTI6pc1NBc4CfpQoe?= =?us-ascii?Q?yi06ZwVOilYIRPiGZ6OjhIdNC/oYrgxvUYVGV7TBGXtvAbiZRtYfl7NxAdAF?= =?us-ascii?Q?j4e1/xbf7S/j7nbFgIEUrp2jvEFq74uHGzXfHQ6VBCiezn9He/opXhrdqZZq?= =?us-ascii?Q?NZDjS6oYC5Cgmpyen9BSkftquQNSArl48y+IWgAXDvokOkyExZazbEW7h0cH?= =?us-ascii?Q?14St6eVEOaolor1topeoh71wG7kvWDUCwUEi/CHdmwj6HqoPKBrMtpgm79rj?= =?us-ascii?Q?+nklKVKSNtQxP36dGGmrlas9n0J0ETGhnJ7bT/NkJVD4MTCIMXZIjMZD0zLj?= =?us-ascii?Q?H/cRbbMwfNu+/MkSYJk8BNoXTyv0W3+puwiOMvNqV+SGU2nranUkBHVZhKdH?= =?us-ascii?Q?3vNj676dx4iJqcOqEzg/pMmYGCD3NvmBK5ii3OIj28bMachpipSHIBjkigRv?= =?us-ascii?Q?wpQhVkO6Bxn6zuhD4gSaLrqYqyRqxzt3R56w1CuJ/me1KumdG3V8vfFM+Nqx?= =?us-ascii?Q?A21IITRsRoAp70PKr51LVhMbkwWIpxlhoNZgI3AkcpP9ai4iSbnWNrrlkSEc?= =?us-ascii?Q?KbGRa7/GmMUWVBS5fMx5Cieu0/HYbW9qaHBvPjoA+k2w4TBh7A95nxDWLJCG?= =?us-ascii?Q?K3NPjx6N9a9fSg5sN+ih8aAvnhAzYRe4QEyAolHvVcPR9mqtRniYHI5j36Ix?= =?us-ascii?Q?uNdMcoRhGb+p3y9SMf0kKes7TX9p/3/B8HyC5cpxJa+DGRsVx/Pkhgyl9/6i?= =?us-ascii?Q?0/sSHQ=3D=3D?= Content-Type: multipart/alternative; boundary="_000_DBAP191MB1289C893FE38B9C831465376B0099DBAP191MB1289EURP_" 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: 48dbff4d-2962-4c7d-edde-08da0119aee4 X-MS-Exchange-CrossTenant-originalarrivaltime: 08 Mar 2022 15:38:24.5614 (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: euEQO43h6Nh7/4613ARYU9BOb5x0WVXvazX8d2dRVUtPJ2VWkD/9k4ynR+XCKNvRzwATxMRSTUPVsXgh/6F7fh5bjIF2DiDVOh6s7Z6/hCk= X-MS-Exchange-Transport-CrossTenantHeadersStamped: DBAP191MB1242 X-OriginatorOrg: 4js.com X-C2ProcessedOrg: 2f0c79df-a80b-40c2-9362-4c5fa2dae201 X-Proofpoint-GUID: hl_OF9CsZ_r1pnekTvCdsRodhGoyFhpL X-Proofpoint-ORIG-GUID: hl_OF9CsZ_r1pnekTvCdsRodhGoyFhpL 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-08_05,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_DBAP191MB1289C893FE38B9C831465376B0099DBAP191MB1289EURP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable About CREATE TRIGGER names on temp tables: CREATE TRIGGER TT1_SRLT BEFORE INSERT ON TT1 FOR EACH ROW EXECUTE PROCEDU= RE TT1_8589_SRL() I was wondering what happens with the trigger name when created on a temp t= able. It appears that no conflict can occur, and the same trigger name can be use= d by different processes. Checking the system tables, it appears that the trigger is created in user'= s pg_my_temp_schema() ... The doc should describe that it's allowed to create triggers on temp tables= : https://www.postgresql.org/docs/14/sql-createtrigger.html Seb ________________________________ From: Sebastien Flaesch Sent: Monday, March 7, 2022 7:19 PM To: pgsql-sql@lists.postgresql.org Subject: Best practice for naming temp table trigger functions EXTERNAL: Do not click links or open attachments if you do not recognize th= e sender. 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_DBAP191MB1289C893FE38B9C831465376B0099DBAP191MB1289EURP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

About CREATE TRIGGER names on temp tables:

  CREATE TRIGGER TT1_SRLT<= /b> BEFORE INSERT ON TT1 FOR EACH ROW EXECUTE PROCEDURE TT1_8589_SRL= ()

I was wondering what happens with the trigger name when created on a temp t= able.

It appears that no conflict can occur, and the same trigger name can b= e used by different processes.

Checking the system tables, it appears that the trigger is created in user'= s pg_my_temp_schema() ...

The doc should describe that it's allowed to create triggers on temp tables= :


Seb
From: Sebastien Flaesch <= ;sebastien.flaesch@4js.com>
Sent: Monday, March 7, 2022 7:19 PM
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.or= g>
Subject: Best practice for naming temp table trigger functions
 

EXTERNAL: Do not c= lick links or open attachments if you do not recognize the sender.=

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_DBAP191MB1289C893FE38B9C831465376B0099DBAP191MB1289EURP_--