Thread: search_path not reloaded via unix socket connections

Kong Man <kong_mansatiansin@hotmail.com> writes:
> Can anybody explain why the search_path setting, with several config reloads, would not change via local connections?
We struggled with our production settings on Postgres 9.3 today, only to realize, after a while, that the search_path
changeactually took effect via TCP/IP, but not unix socket, connections ever since the first reload. 

That's, um, pretty hard to believe.  Less magical interpretations would
involve something like a per-user or per-database setting overriding
what's in the config file in some sessions but not others.  But I really
really doubt that TCP vs unix socket is the determining factor.

            regards, tom lane

Can anybody explain why the search_path setting, with several config reloads, would not change via local connections?  We struggled with our production settings on Postgres 9.3 today, only to realize, after a while, that the search_path change actually took effect via TCP/IP, but not unix socket, connections ever since the first reload.

On Thursday, September 17, 2015, Kong Man <kong_mansatiansin@hotmail.com> wrote:

Can anybody explain why the search_path setting, with several config reloads, would not change via local connections?  We struggled with our production settings on Postgres 9.3 today, only to realize, after a while, that the search_path change actually took effect via TCP/IP, but not unix socket, connections ever since the first reload.

What Tom said...

Alternatively maybe your Unix socket sessions are long lived but your TCP/IP ones are not - potentially going through a pooler that refreshes sessions.  You need to describe more or provide repeatable evidence if we are to conclude that this is anything other than operator error.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Or not, since it does appear that the reload signal is propagated to active
> sessions and take effect after the most recent command finishes.

Yeah.  I had been wondering about long-lived open transactions, but AFAICS
from the code, backends should re-read the config file at the next client
command submission, whether inside a transaction block or not.  So the
thing to look for is what might be overriding the config file's value.

In interactive sessions, examining the pg_settings view would be a
promising way to debug that.  I suspect though that the OP is guessing
about what's happening inside application-driven sessions, where it would
be hard to do that kind of debugging :-(

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Or not, since it does appear that the reload signal is propagated to active
> sessions and take effect after the most recent command finishes.

Yeah.  I had been wondering about long-lived open transactions, but AFAICS
from the code, backends should re-read the config file at the next client
command submission, whether inside a transaction block or not.  So the
thing to look for is what might be overriding the config file's value.

In interactive sessions, examining the pg_settings view would be a
promising way to debug that.  I suspect though that the OP is guessing
about what's happening inside application-driven sessions, where it would
be hard to do that kind of debugging :-(

                        regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> As an aside the alter user/database commands do end up requiring the user
> to disconnect and reconnect.  Is there a hard limitation why an
> administrator can't send some kind of signal to cause a re-read of those by
> an active session?

If we wanted to redefine their behavior, we could probably make that
happen, though it's not clear we want to.  For example, if you've done
SET ROLE, should a reload of ALTER USER values pertaining to your
original role affect you?

Another interesting point is that the config file re-read behavior will
currently happen at next command receipt, even if you are inside an
aborted transaction.  We cannot read the system catalogs if the current
transaction is aborted, so the timing would have to be subtly different
in any case.

            regards, tom lane

On 09/18/2015 08:19 AM, Kong Man wrote:
> Tom,
> I have made sure that the per-user and per-database settings are reset.
>   The old search_path setting still persists whenever I reconnect via
> local connections no matter what user I log in as.

So exactly how are you connecting and from what?

In other words the connection string and what client are you using?

>
>  > From: tgl@sss.pgh.pa.us
>  > To: kong_mansatiansin@hotmail.com
>  > CC: pgsql-general@postgresql.org
>  > Subject: Re: [GENERAL] search_path not reloaded via unix socket
> connections
>  > Date: Thu, 17 Sep 2015 22:29:04 -0400
>  >
>  > Kong Man <kong_mansatiansin@hotmail.com> writes:
>  > > Can anybody explain why the search_path setting, with several
> config reloads, would not change via local connections? We struggled
> with our production settings on Postgres 9.3 today, only to realize,
> after a while, that the search_path change actually took effect via
> TCP/IP, but not unix socket, connections ever since the first reload.
>  >
>  > That's, um, pretty hard to believe. Less magical interpretations would
>  > involve something like a per-user or per-database setting overriding
>  > what's in the config file in some sessions but not others. But I really
>  > really doubt that TCP vs unix socket is the determining factor.
>  >
>  > regards, tom lane


--
Adrian Klaver
adrian.klaver@aklaver.com