From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jackie.space@gmail.com
Subject: BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema
Date: Wed, 14 May 2025 12:37:36 +0000
Message-ID: <18928-b85f8c68121fd98b@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jackie.space@gmail.com
Subject: Re: BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema
In-Reply-To: <18928-b85f8c68121fd98b@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox