agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedBest practice for naming temp table trigger functions
5+ messages / 2 participants
[nested] [flat]
* Best practice for naming temp table trigger functions
@ 2022-03-07 18:19 Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sebastien Flaesch @ 2022-03-07 18:19 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practice for naming temp table trigger functions
@ 2022-03-08 15:38 Sebastien Flaesch <sebastien.flaesch@4js.com>
parent: Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sebastien Flaesch @ 2022-03-08 15:38 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practice for naming temp table trigger functions
@ 2022-03-08 16:16 David G. Johnston <david.g.johnston@gmail.com>
parent: Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2022-03-08 16:16 UTC (permalink / raw)
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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:
> https://www.postgresql.org/docs/14/sql-createtrigger.html
>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practice for naming temp table trigger functions
@ 2022-03-08 17:24 Sebastien Flaesch <sebastien.flaesch@4js.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sebastien Flaesch @ 2022-03-08 17:24 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best practice for naming temp table trigger functions
@ 2022-03-09 08:26 Sebastien Flaesch <sebastien.flaesch@4js.com>
parent: Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 0 replies; 5+ messages in thread
From: Sebastien Flaesch @ 2022-03-09 08:26 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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));
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');
SELECT * FROM tt1 ORDER BY pk;
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-03-09 08:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-07 18:19 Best practice for naming temp table trigger functions Sebastien Flaesch <sebastien.flaesch@4js.com>
2022-03-08 15:38 ` Sebastien Flaesch <sebastien.flaesch@4js.com>
2022-03-08 16:16 ` David G. Johnston <david.g.johnston@gmail.com>
2022-03-08 17:24 ` Sebastien Flaesch <sebastien.flaesch@4js.com>
2022-03-09 08:26 ` Sebastien Flaesch <sebastien.flaesch@4js.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox