BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18928
Logged by:          Jackie Li
Email address:      [email protected]
PostgreSQL version: 17.5
Operating system:   Linux, MacOS
Description:

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
Kind Regards,
Jackie