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

From Jackie Li
Subject Re: BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema
Date
Msg-id CADRwNDaB7THeT9qT=BM0GiROrJsQrDtdto7CRwiGT1J0vjC6Gg@mail.gmail.com
Whole thread Raw
In response to Re: BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
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@sss.pgh.pa.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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #18928: postgres_fdw search_path not reset causing pgbouncer pool_mode=transaction using the wrong schema
Next
From: Daniel Gustafsson
Date:
Subject: Re: BUG #18925: Heap-buffer-overflow: pglz_compress with pglz_stategy_always