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

I see, thanks a lot for the insight.

It sounds to me that my best option is to have a separate connection pool
for the foreign connections. I.e. create a different pool in pgbouncer (
https://github.com/jackielii/pgbouncer-fdw-issue/blob/separate-server/pgbouncer.ini#L4)
that can be used by the create server statement right?

On Wed, 14 May 2025 at 16:07, Matheus Alcantara <matheusssilv97@gmail.com>
wrote:

> 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
>