Re: BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema

Hi,

On 14/05/25 09:37, PG Bug reporting form wrote:
> I filed an issue to pgbouncer, but I realised the issue is more related to
> postgres_fdw extension: https://github.com/pgbouncer/pgbouncer/issues/1313
> To reproduce this issue, you can use a minimal code sample at
> https://github.com/jackielii/pgbouncer-fdw-issue/tree/main
> Steps to reproduce:
> 1. set up 2 databases: db1 and db2
> 2. setup pgbouncer that connects to both in transaction pooling mode. See
> [pgbouncer.ini](https://github.com/jackielii/pgbouncer-fdw-issue/blob/main/pgbouncer.ini)
> 3. connect to db2 via pgbouncer and creates foreign schema connects to db1
> using postgres_fdw
> 4. Now open a connection to db1 via pgbouncer and run select query without
> schema
> 5. observe an error occurs because wrong default schema pg_catalog is used:
> relation "test1" does not exist
> The issue seems to be related to a connection sets the search_path gets
> reused and search_path is not reverted: [related code in
> postgres_fdw](https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/postgres_fdw.c#L3935-L3938)
> I know this feels like working as intended as postgres_fdw does close and
> reset it at
> https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/postgres_fdw.c#L3946,
> but I think it should revert the `set_config_option` effect, especially for
> search_path
>

I've managed to reproduce the issue.

I don't think that the problem it's on set_transmission_modes() that
you've shared, it seems to me that the issue it's on
configure_remote_session() that is called for every new foreign
connection created by postgres_fdw and it currently sets the seach_path
and other GUCs(timezone, datestyle, and others).

This issue seems a bit tricky to fix because Postgres does not know that
the connection can be reused by PgBouncer. One possible solution for
this is that on postgresEndForeignScan() we could reset all the GUCs set
by the configure_remote_session() and before reusing the connection from
the ConnCacheEntry we configure the remote session using
configure_remote_session(). See attached diff as a proof of concept
(please keep in mind that it's POC and I'm not considering possible
other scenarios and I'm not sure if the reset_remote_session() should be
called at ReleaseConnection()).

One problem with this idea is that it can cause some overhead when
reusing a cached connection and also when ending the foreign scan, so I
think that we could also add a foreign server option to enable this
behaviour. Thoughts?

-- 
Matheus Alcantara