agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Sebastien Flaesch <sebastien.flaesch@4js.com>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: Best practice for naming temp table trigger functions
Date: Tue, 8 Mar 2022 15:38:24 +0000
Message-ID: <DBAP191MB1289C893FE38B9C831465376B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <DBAP191MB128959B0FE1ABE28C04ACDFFB0089@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
References: <DBAP191MB128959B0FE1ABE28C04ACDFFB0089@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
About CREATE TRIGGER names on temp tables:
CREATE TRIGGER TT1_SRLT 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 table.
It appears that no conflict can occur, and the same trigger name can be 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:
https://www.postgresql.org/docs/14/sql-createtrigger.html
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.org>
Subject: Best practice for naming temp table trigger functions
EXTERNAL: Do not click 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 conflict 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 destroyed ...
Or, is there a way to define triggers directly with some anonymous code block?
Seb
view thread (5+ messages) latest in thread
Message-ID: <DBAP191MB1289C893FE38B9C831465376B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
Permalink: ../DBAP191MB1289C893FE38B9C831465376B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM/
Also on: postgresql.org/message-id/DBAP191MB1289C893FE38B9C831465376B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sebastien.flaesch@4js.com, pgsql-sql@lists.postgresql.org
Subject: Re: Best practice for naming temp table trigger functions
In-Reply-To: <DBAP191MB1289C893FE38B9C831465376B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox