Re: Add notification on BEGIN ATOMIC SQL functions using temp relations

Hi

ne 21. 9. 2025 v 13:49 odesílatel Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>
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
>