From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dmitriy Kuzmin <kuzmin.db4@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19437: temp_tablespaces doesn't work inside a cursor?
In-reply-to: <CAHLDt=-HP4V8rfxEWE6-xC93YTN2X=KNeyYZsygVqnb-adRe6g@mail.gmail.com>
References: <19437-0a65fb52d0f13a0d@postgresql.org> <233968.1774445501@sss.pgh.pa.us> <CAKFQuwbtK=HfDsUo7p7zqFUb-h=ZqUS93K4+9nbOPW+bKnQuaA@mail.gmail.com> <CAHLDt=-HP4V8rfxEWE6-xC93YTN2X=KNeyYZsygVqnb-adRe6g@mail.gmail.com>
Comments: In-reply-to Dmitriy Kuzmin <kuzmin.db4@gmail.com>
	message dated "Mon, 06 Apr 2026 11:16:19 +1000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1196654.1775443600.1@sss.pgh.pa.us>
Date: Sun, 05 Apr 2026 22:46:40 -0400
Message-ID: <1196655.1775443600@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1196655.1775443600%40sss.pgh.pa.us>
Precedence: bulk

Dmitriy Kuzmin <kuzmin.db4@gmail.com> writes:
> Any news on these issues?

I don't see any bug here.  You're expecting the ALTER SYSTEM SET
change to propagate instantaneously after pg_reload_conf(), but
it doesn't.  I cannot replicate your claim that the setting
changes once it has been received, either.

The fact that DECLARE CURSOR isn't using a temp tablespace is
intentional, according to the comments in OpenTemporaryFile:

     * If some temp tablespace(s) have been given to us, try to use the next
     * one.  If a given tablespace can't be found, we silently fall back to
     * the database's default tablespace.
     *
     * BUT: if the temp file is slated to outlive the current transaction,
     * force it into the database's default tablespace, so that it will not
     * pose a threat to possible tablespace drop attempts.

The reason it thinks the temp file may need to outlive the current
transaction is that we're putting the FETCH results into the current
Portal's holdStore, which is specifically intended to possibly outlive
the transaction.  Since you didn't say WITH HOLD in the DECLARE
CURSOR, maybe there's an optimization possibility there.  But I'm
not sure --- I think the details of the wire protocol may foreclose
throwing the Portal's results away until it's explicitly closed.

I didn't go looking to see how well these details are documented.

			regards, tom lane