Обсуждение: Best practice for naming temp table trigger functions
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
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:
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
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
Please don't top-post.
On Tue, Mar 8, 2022 at 8:38 AM Sebastien Flaesch <sebastien.flaesch@4js.com> wrote:
The doc should describe that it's allowed to create triggers on temp tables:
A temporary table IS a table, so the fact this works on temporary tables is unsurprising. There is nothing special about create trigger in this regard that warrants a special mention of this fact. The trigger is created in the same schema as the table is attached to, and is namespaced to the table as well. Both of these facts are documented on that page, under the description for the "name" parameter.
What is the best practice, to avoid such issues?
Don't define triggers on temporary tables. Or just write the function in such a way that it can be attached to the triggers of any table - i.e., don't have one trigger function per trigger/table.
But I would like to have that function dropped when the temp table is destroyed ...
Then you are back to constructing some kind of unique name. Though maybe, and I've not tried this myself or done any research, you could schema qualify the function using the pg_temp schema, and then make sure it properly goes away when the session ends.
Or, is there a way to define triggers directly with some anonymous code block?
Unfortunately that is not presently an option. It seems interesting but frankly the motivation for that largely stems from a desire to add triggers to temporary tables and usually that isn't a particularly efficient nor easy to learn design choice. Putting the trigger logic into the same routine as the temporary table and doing explicit bulk execution against sets of rows instead of per-row triggers or even transition table sets is going to be easier to read, modify, and likely run faster too. Plus you reduce the amount of catalog churn to just the temporary table, not that and a trigger and a function.
David J.
Would it make sense (seems possible!) to create the trigger function in the pg_my_temp_schema() schema ?
create function pg_temp_4.TT1_SRL() returns trigger ...
Same question for sequences... (used in my triggers)
Background: All this stuff is to mimic Informix-style SERIALs.
If PostgreSQL native serial would have options to behave like Informix serial, we would not need triggers on our tables.
With Informix serial/bigserial:
- INSERT with zero value specified for serial column generates a new serial value from underlying sequence.
- INSERT with non-zero value specified for serial column sets that value and automatically increments the underlying sequence.
Seb
Hello!
Using pg_temp as schema prefix for trigger function and sequence seems to do the job...
Just need confirmation that this is valid PostgreSQL code...
Tx!
Seb
CREATE TEMP TABLE tt1 (pk INTEGER NOT NULL, name VARCHAR(50));
SELECT * FROM tt1 ORDER BY pk;
CREATE SEQUENCE pg_temp.tt1_seq START 1;
CREATE FUNCTION pg_temp.tt1_srl() RETURNS TRIGGER AS
'DECLARE ls BIGINT;
BEGIN
SELECT INTO ls nextval(''pg_temp.tt1_seq'');
IF new.pk ISNULL OR new.pk=0 THEN
new.pk:=ls;
ELSE
IF new.pk>=ls THEN
PERFORM setval(''pg_temp.tt1_seq'',new.pk);
END IF;
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER tt1_srlt BEFORE INSERT ON tt1 FOR EACH ROW EXECUTE PROCEDURE pg_temp.tt1_srl();
INSERT INTO tt1 (name) VALUES ('aaaa');
SELECT 'Insert #1:', currval('pg_temp.tt1_seq');
INSERT INTO tt1 VALUES (0,'bbbb');
SELECT 'Insert #2:', currval('pg_temp.tt1_seq');
INSERT INTO tt1 VALUES (100,'cccc');
SELECT 'Insert #3:', currval('pg_temp.tt1_seq');
INSERT INTO tt1 (name) VALUES ('dddd');
SELECT 'Insert #4:', currval('pg_temp.tt1_seq');
Output:
CREATE TABLE
CREATE SEQUENCE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
?column? | currval
------------+---------
Insert #1: | 1
(1 row)
INSERT 0 1
?column? | currval
------------+---------
Insert #2: | 2
(1 row)
INSERT 0 1
?column? | currval
------------+---------
Insert #3: | 100
(1 row)
INSERT 0 1
?column? | currval
------------+---------
Insert #4: | 101
(1 row)
pk | name
-----+------
1 | aaaa
2 | bbbb
100 | cccc
101 | dddd
(4 rows)