public inbox for [email protected]
help / color / mirror / Atom feedFrom: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19437: temp_tablespaces doesn't work inside a cursor?
Date: Wed, 25 Mar 2026 10:02:09 +0000
Message-ID: <[email protected]> (raw)
The following bug has been logged on the website:
Bug reference: 19437
Logged by: Dmitriy Kuzmin
Email address: [email protected]
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?
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19437: temp_tablespaces doesn't work inside a cursor?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox