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

Thanks for the response. You can checkout the pgbouncer's config at my
minimal reproducing example:
https://github.com/jackielii/pgbouncer-fdw-issue/blob/separate-server/pgbouncer.ini

I can't say the config is wrong or not, but our devs require the pool_mode
to be "transaction" for their respective db.

At this point I feel we shouldn't fix this: postgres shouldn't care how the
connection is reused. If anything pgbouncer should make sure the connection
is reset for reuse. And they kind of do: use a separate pool.

In conclusion, my workaround at
https://github.com/jackielii/pgbouncer-fdw-issue/tree/separate-server which
creates another pool for db1 is the right solution.

Thanks & Kind regards

On Wed, 14 May 2025 at 16:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> ISTM the problem described here is a failure on PgBouncer's part: it is
> failing to maintain the illusion of a separate connection per user.
> I don't know that much about PgBouncer, but perhaps you just have
> it configured wrong?
>
> regards, tom lane
>