Обсуждение: BUG #19437: temp_tablespaces doesn't work inside a cursor?
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?
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
I think you are imagining that pg_reload_conf() is a synchronous
operation. It is not. It merely signals the postmaster process
to start a configuration reload. After the postmaster has done
that, it in turn signals all its children to reload configuration.
If memory serves, a child process honors that signal next time
it arrives at its outer wait-for-a-command loop. So it's not
exactly surprising if your script is able to execute a command or
three before it takes up the ALTER SYSTEM changes. (Your log
shows that it can get through several commands before the
postmaster even gets the reload signal, let alone sends it back.)
If you want to set temp_tablespaces locally and have it take
effect immediately, just use SET. ALTER SYSTEM is quite the
wrong tool for the job.
> Ensure that temporary files are created in it:
> \o /dev/null
What does "\o /dev/null" have to do with this? That's a
psql-side operation.
regards, tom lane
On Wednesday, March 25, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
I think you are imagining that pg_reload_conf() is a synchronous
operation.
The use of sleep does indicate awareness of the async nature of this.
> Ensure that temporary files are created in it:
> \o /dev/null
What does "\o /dev/null" have to do with this? That's a
psql-side operation.
The comment applies to all three lines in the following block, not just the first line.
There is something odd here. Look at the log entry at 12:04:08.016; it uses base/ while the surrounding ones use pg_tblspace/ and the setting itself hasn’t changed during the sequence.
I haven’t tried to validate the cursor claim yet but; this definitely isn’t the easiest format to consume and I can’t do much on my own presently.
David J.
What does "\o /dev/null" have to do with this? That's a psql-side operation.
This is a set of commands for psql, and I use \o /dev/null to prevent SELECT results from being printed to the screen. This is unrelated to the problem.
The use of sleep does indicate awareness of the async nature of this.
Correct. Pg_sleep is used to achieve a consistently reproducible result when executing a script.
The same result (creating temporary files in different tablespaces) can be achieved by executing the specified commands manually without use of pg_sleep.
There is something odd here. Look at the log entry at 12:04:08.016; it uses base/ while the surrounding ones use pg_tblspace/ and the setting itself hasn’t changed during the sequence.
Absolutely correct. Furthermore, each execution of SELECT pg_reload_conf() will cause the next query to create temporary files in base/ once, regardless of the temp_tablespaces value. Try running the above commands manually and reviewing the logs; you'll see what I mean
ср, 25 мар. 2026 г. в 23:52, David G. Johnston <david.g.johnston@gmail.com>:
On Wednesday, March 25, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
I think you are imagining that pg_reload_conf() is a synchronous
operation.The use of sleep does indicate awareness of the async nature of this.> Ensure that temporary files are created in it:> \o /dev/null
What does "\o /dev/null" have to do with this? That's a
psql-side operation.The comment applies to all three lines in the following block, not just the first line.There is something odd here. Look at the log entry at 12:04:08.016; it uses base/ while the surrounding ones use pg_tblspace/ and the setting itself hasn’t changed during the sequence.I haven’t tried to validate the cursor claim yet but; this definitely isn’t the easiest format to consume and I can’t do much on my own presently.David J.