The following bug has been logged on the website:
Bug reference: 18928
Logged by: Jackie Li
Email address: jackie.space@gmail.com
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
