Hi ne 21. 9. 2025 v 13:49 odesílatel Jim Jones <[email protected]> napsal:
> Hi, > > While reviewing a patch I noticed that SQL functions defined with BEGIN > ATOMIC can reference temporary relations, and such functions are > (rightfully) dropped at session end --- but without any notification to > the user: > > $ /usr/local/postgres-dev/bin/psql postgres > psql (19devel) > Type "help" for help. > > postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val; > SELECT 1 > > postgres=# CREATE FUNCTION tmpval_atomic() > RETURNS int LANGUAGE sql > BEGIN ATOMIC; > SELECT val FROM tmp; > END; > CREATE FUNCTION > > postgres=# \df > List of functions > Schema | Name | Result data type | Argument data types | Type > --------+---------------+------------------+---------------------+------ > public | tmpval_atomic | integer | | func > (1 row) > > postgres=# \q > > $ /usr/local/postgres-dev/bin/psql postgres > psql (19devel) > Type "help" for help. > > postgres=# \df > List of functions > Schema | Name | Result data type | Argument data types | Type > --------+------+------------------+---------------------+------ > (0 rows) > > > Although this behaviour is expected, it can be surprising. A NOTICE or > WARNING at CREATE FUNCTION time could save some head-scratching later. > We already have a precedent. When creating a view that depends on a > temporary relation, postgres automatically makes it a temporary view and > emits a NOTICE: > > postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val; > SELECT 1 > > postgres=# CREATE VIEW v AS SELECT * FROM tmp; > NOTICE: view "v" will be a temporary view > CREATE VIEW > > postgres=# \d > List of relations > Schema | Name | Type | Owner > ------------+------+-------+------- > pg_temp_74 | tmp | table | jim > pg_temp_74 | v | view | jim > (2 rows) > > postgres=# \q > > $ /usr/local/postgres-dev/bin/psql postgres > psql (19devel) > Type "help" for help. > > postgres=# \d > Did not find any relations. > > > Attached a PoC that issues a WARNING if a BEGIN ATOMIC function is > created using temporary objects: > > postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val; > SELECT 1 > > postgres=# CREATE FUNCTION tmpval_atomic() > RETURNS int LANGUAGE sql > BEGIN ATOMIC; > SELECT val FROM tmp; > END; > WARNING: function defined with BEGIN ATOMIC depends on temporary > relation "tmp" > DETAIL: the function will be dropped automatically at session end. > CREATE FUNCTION > > This PoC adds a parameter to check_sql_fn_statements() and > check_sql_fn_statement(), so I’m not entirely sure if that’s the best > approach. I’m also not sure whether a NOTICE would be a better fit than > a WARNING here. Feedback is welcome. > > Any thoughts? > i understand your motivation, but with this warning temp tables cannot be used in SQL function due log overhead. Regards Pavel > > Best regards, Jim >
