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.
