public inbox for [email protected]help / color / mirror / Atom feed
BUG #19437: temp_tablespaces doesn't work inside a cursor? 5+ messages / 4 participants [nested] [flat]
* BUG #19437: temp_tablespaces doesn't work inside a cursor? @ 2026-03-25 10:02 PG Bug reporting form <[email protected]> 2026-03-25 13:31 ` Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: PG Bug reporting form @ 2026-03-25 10:02 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] 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? ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? 2026-03-25 10:02 BUG #19437: temp_tablespaces doesn't work inside a cursor? PG Bug reporting form <[email protected]> @ 2026-03-25 13:31 ` Tom Lane <[email protected]> 2026-03-25 13:52 ` BUG #19437: temp_tablespaces doesn't work inside a cursor? David G. Johnston <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2026-03-25 13:31 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] PG Bug reporting form <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* BUG #19437: temp_tablespaces doesn't work inside a cursor? 2026-03-25 10:02 BUG #19437: temp_tablespaces doesn't work inside a cursor? PG Bug reporting form <[email protected]> 2026-03-25 13:31 ` Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? Tom Lane <[email protected]> @ 2026-03-25 13:52 ` David G. Johnston <[email protected]> 2026-03-25 14:30 ` Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? Dmitriy Kuzmin <[email protected]> 2026-04-06 01:16 ` Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? Dmitriy Kuzmin <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: David G. Johnston @ 2026-03-25 13:52 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]>; [email protected] <[email protected]> On Wednesday, March 25, 2026, Tom Lane <[email protected]> wrote: > PG Bug reporting form <[email protected]> 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? 2026-03-25 10:02 BUG #19437: temp_tablespaces doesn't work inside a cursor? PG Bug reporting form <[email protected]> 2026-03-25 13:31 ` Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? Tom Lane <[email protected]> 2026-03-25 13:52 ` BUG #19437: temp_tablespaces doesn't work inside a cursor? David G. Johnston <[email protected]> @ 2026-03-25 14:30 ` Dmitriy Kuzmin <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Dmitriy Kuzmin @ 2026-03-25 14:30 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] <[email protected]> > > 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 <[email protected]>: > On Wednesday, March 25, 2026, Tom Lane <[email protected]> wrote: > >> PG Bug reporting form <[email protected]> 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. > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? 2026-03-25 10:02 BUG #19437: temp_tablespaces doesn't work inside a cursor? PG Bug reporting form <[email protected]> 2026-03-25 13:31 ` Re: BUG #19437: temp_tablespaces doesn't work inside a cursor? Tom Lane <[email protected]> 2026-03-25 13:52 ` BUG #19437: temp_tablespaces doesn't work inside a cursor? David G. Johnston <[email protected]> @ 2026-04-06 01:16 ` Dmitriy Kuzmin <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Dmitriy Kuzmin @ 2026-04-06 01:16 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] <[email protected]> Hello! Any news on these issues? Let me know if the provided information isn't enough to reproduce them Best regards, Dmitry Kuzmin ср, 25 мар. 2026 г. в 23:52, David G. Johnston <[email protected]>: > On Wednesday, March 25, 2026, Tom Lane <[email protected]> wrote: > >> PG Bug reporting form <[email protected]> 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. > > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-04-06 01:16 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-25 10:02 BUG #19437: temp_tablespaces doesn't work inside a cursor? PG Bug reporting form <[email protected]> 2026-03-25 13:31 ` Tom Lane <[email protected]> 2026-03-25 13:52 ` David G. Johnston <[email protected]> 2026-03-25 14:30 ` Dmitriy Kuzmin <[email protected]> 2026-04-06 01:16 ` Dmitriy Kuzmin <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox