BUG #19437: temp_tablespaces doesn't work inside a cursor? - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      19437
Logged by:          Dmitriy Kuzmin
Email address:      kuzmin.db4@gmail.com
PostgreSQL version: 18.3
Operating system:   doesnt matter
Description:

Greetings

I'm seeing strange behavior in Postgres when changing the temp_tablespaces
parameter and suspect a bug. At least, I haven't found a description of this
behavior in the documentation.
All described scenarios are reproducible on the current version 19devel from
master branch.

Situation one: after running SELECT pg_reload_conf(), the first query
execution creates temporary files in the pg_default tablespace, not the one
specified in temp_tablespaces. This can be reproduced with the following
psql commands:

create tablespace tempts location '/tmp/tempts';
alter system set temp_tablespaces to tempts;
alter system set log_temp_files to 0;
select pg_reload_conf();
set work_mem to '4MB';
\o /dev/null
select c, c c2  from generate_series(0, 100000) x(c) order by c;
select pg_sleep(1);
select c, c c2  from generate_series(0, 100000) x(c) order by c;
select c, c c2  from generate_series(0, 100000) x(c) order by c;
select pg_reload_conf();
select pg_sleep(1);
select c, c c2  from generate_series(0, 100000) x(c) order by c;
select c, c c2  from generate_series(0, 100000) x(c) order by c;
select c, c c2  from generate_series(0, 100000) x(c) order by c;
\o
alter system reset temp_tablespaces;
alter system reset log_temp_files;
select pg_reload_conf();
drop tablespace tempts;

In the logs, you'll see that the first three commands create temporary files
in the new tablespace, but after calling SELECT pg_reload_conf(), the first
query creates temporary files in "base/pgsql_tmp/."
Interestingly, at this point, calling pg_reload_conf() doesn't change the
temp_tablespaces value. We simply reread the configuration:

2026-03-25 12:04:05.749 MSK [3313186] LOG:  received SIGHUP, reloading
configuration files
2026-03-25 12:04:05.750 MSK [3313186] LOG:  parameter "temp_tablespaces"
changed to "tempts"
2026-03-25 12:04:05.750 MSK [3313186] LOG:  parameter "log_temp_files"
changed to "0"
2026-03-25 12:04:05.784 MSK [3313212] LOG:  temporary file: path
"pg_tblspc/16386/PG_19_202603201/pgsql_tmp/pgsql_tmp3313212.0", size 1810432
2026-03-25 12:04:05.784 MSK [3313212] STATEMENT:  select c, c c2  from
generate_series(0, 100000) x(c) order by c;
2026-03-25 12:04:06.863 MSK [3313212] LOG:  temporary file: path
"pg_tblspc/16386/PG_19_202603201/pgsql_tmp/pgsql_tmp3313212.1", size 1810432
2026-03-25 12:04:06.863 MSK [3313212] STATEMENT:  select c, c c2  from
generate_series(0, 100000) x(c) order by c;
2026-03-25 12:04:06.940 MSK [3313212] LOG:  temporary file: path
"pg_tblspc/16386/PG_19_202603201/pgsql_tmp/pgsql_tmp3313212.2", size 1810432
2026-03-25 12:04:06.940 MSK [3313212] STATEMENT:  select c, c c2  from
generate_series(0, 100000) x(c) order by c;
2026-03-25 12:04:06.983 MSK [3313186] LOG:  received SIGHUP, reloading
configuration files
2026-03-25 12:04:08.016 MSK [3313212] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313212.3", size 1810432
2026-03-25 12:04:08.016 MSK [3313212] STATEMENT:  select c, c c2  from
generate_series(0, 100000) x(c) order by c;
2026-03-25 12:04:08.089 MSK [3313212] LOG:  temporary file: path
"pg_tblspc/16386/PG_19_202603201/pgsql_tmp/pgsql_tmp3313212.4", size 1810432
2026-03-25 12:04:08.089 MSK [3313212] STATEMENT:  select c, c c2  from
generate_series(0, 100000) x(c) order by c;
2026-03-25 12:04:08.163 MSK [3313212] LOG:  temporary file: path
"pg_tblspc/16386/PG_19_202603201/pgsql_tmp/pgsql_tmp3313212.5", size 1810432
2026-03-25 12:04:08.163 MSK [3313212] STATEMENT:  select c, c c2  from
generate_series(0, 100000) x(c) order by c;
2026-03-25 12:04:08.209 MSK [3313186] LOG:  received SIGHUP, reloading
configuration files
2026-03-25 12:04:08.209 MSK [3313186] LOG:  parameter "temp_tablespaces"
removed from configuration file, reset to default
2026-03-25 12:04:08.209 MSK [3313186] LOG:  parameter "log_temp_files"
removed from configuration file, reset to default


Situation two: queries within a cursor create temporary files in the default
tablespace. This can be reproduced by the following queries.

Create a tablespace:

create tablespace tempts location '/tmp/tempts';
alter system set log_temp_files to 0;
alter system set temp_tablespaces to tempts;
select pg_reload_conf();


Ensure that temporary files are created in it:
\o /dev/null
select c, c c2  from generate_series(0, 1000000) x(c) order by c;
select c, c c2  from generate_series(0, 1000000) x(c) order by c;

In the logs, you'll see that the first query created temporary files in
"base/pgsql_tmp/," as described above, in situation one. All subsequent
executions will create temporary files in
"pg_tblspc/xxxxxx/PG_19_xxxxxxxxxx," indicating that the new
temp_tablespaces value has taken effect.
Next, run:

begin;
declare cur1 cursor for select c, c c2  from generate_series(0, 1000000)
x(c) order by c;
fetch all from cur1;
fetch backward all from cur1;
fetch all from cur1;
fetch backward all from cur1;
\o
close cur1;
rollback;

In the logs you will see that all FETCH queries create temporary files in
"base/pgsql_tmp/", while CLOSE and non-cursor commands create temporary
files in "pg_tblspc/xxxxxx/PG_19_xxxxxxxxxx":

2026-03-25 12:25:36.038 MSK [3313186] LOG:  received SIGHUP, reloading
configuration files
2026-03-25 12:25:36.038 MSK [3313186] LOG:  parameter "log_temp_files"
changed to "0"
2026-03-25 12:25:36.038 MSK [3313186] LOG:  parameter "temp_tablespaces"
changed to "tempts"
2026-03-25 12:25:59.503 MSK [3313415] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313415.43", size 18128896
2026-03-25 12:25:59.503 MSK [3313415] STATEMENT:  select c, c c2  from
generate_series(0, 1000000) x(c) order by c;
2026-03-25 12:25:59.504 MSK [3313415] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313415.42", size 14000014
2026-03-25 12:25:59.504 MSK [3313415] STATEMENT:  select c, c c2  from
generate_series(0, 1000000) x(c) order by c;
2026-03-25 12:26:03.412 MSK [3313415] LOG:  temporary file: path
"pg_tblspc/16403/PG_19_202603201/pgsql_tmp/pgsql_tmp3313415.45", size
18128896
2026-03-25 12:26:03.412 MSK [3313415] STATEMENT:  select c, c c2  from
generate_series(0, 1000000) x(c) order by c;
2026-03-25 12:26:03.413 MSK [3313415] LOG:  temporary file: path
"pg_tblspc/16403/PG_19_202603201/pgsql_tmp/pgsql_tmp3313415.44", size
14000014
2026-03-25 12:26:03.413 MSK [3313415] STATEMENT:  select c, c c2  from
generate_series(0, 1000000) x(c) order by c;
2026-03-25 12:26:10.510 MSK [3313415] LOG:  temporary file: path
"pg_tblspc/16403/PG_19_202603201/pgsql_tmp/pgsql_tmp3313415.47", size
18128896
2026-03-25 12:26:10.510 MSK [3313415] STATEMENT:  select c, c c2  from
generate_series(0, 1000000) x(c) order by c;
2026-03-25 12:26:10.510 MSK [3313415] LOG:  temporary file: path
"pg_tblspc/16403/PG_19_202603201/pgsql_tmp/pgsql_tmp3313415.46", size
14000014
2026-03-25 12:26:10.510 MSK [3313415] STATEMENT:  select c, c c2  from
generate_series(0, 1000000) x(c) order by c;
2026-03-25 12:26:23.928 MSK [3313415] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313415.50", size 18000018
2026-03-25 12:26:23.928 MSK [3313415] STATEMENT:  fetch all from cur1;
2026-03-25 12:26:24.631 MSK [3313415] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313415.51", size 18000018
2026-03-25 12:26:24.631 MSK [3313415] STATEMENT:  fetch backward all from
cur1;
2026-03-25 12:26:25.334 MSK [3313415] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313415.52", size 18000018
2026-03-25 12:26:25.334 MSK [3313415] STATEMENT:  fetch all from cur1;
2026-03-25 12:26:26.045 MSK [3313415] LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp3313415.53", size 18000018
2026-03-25 12:26:26.045 MSK [3313415] STATEMENT:  fetch backward all from
cur1;
2026-03-25 12:26:26.512 MSK [3313415] LOG:  temporary file: path
"pg_tblspc/16403/PG_19_202603201/pgsql_tmp/pgsql_tmp3313415.49", size
22110208
2026-03-25 12:26:26.512 MSK [3313415] STATEMENT:  close cur1;
2026-03-25 12:26:26.513 MSK [3313415] LOG:  temporary file: path
"pg_tblspc/16403/PG_19_202603201/pgsql_tmp/pgsql_tmp3313415.48", size
14000014
2026-03-25 12:26:26.513 MSK [3313415] STATEMENT:  close cur1;

Question 1: temp_tablespaces doesn't work inside a cursor?
Question 2: Why does rereading the configuration (without changing it)
result in temporary files being created in a tablespace other than the one
specified by the temp_tablespaces parameter for the first query in this
backend?
Question 3: Is this expected behavior and isn't documented, or are these
bugs that need to be fixed?