Re: Protect syscache from bloating with negative cache entries

At Fri, 25 Jan 2019 07:26:46 +0000, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote in <0A3221C70F24FB45833433255569204D1FB70E6B(at)G01JPEXMBYT05>
> From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
> > On Thu, Jan 24, 2019 at 10:02 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > I will argue hard that we should not do it at all, ever.
> > >
> > > There is already a mechanism for broadcasting global GUC changes:
> > > apply them to postgresql.conf (or use ALTER SYSTEM) and SIGHUP.
> > > I do not think we need something that can remotely change a GUC's
> > > value in just one session. The potential for bugs, misuse, and
> > > just plain confusion is enormous, and the advantage seems minimal.
> >
> > I think there might be some merit in being able to activate debugging
> > or tracing facilities for a particular session remotely, but designing
> > something that will do that sort of thing well seems like a very
> > complex problem that certainly should not be sandwiched into another
> > patch that is mostly about something else. And if we ever get such a
> > thing I suspect it should be entirely separate from the GUC system.

It means that we have a lesser copy of the GUC system but can be
set remotely, then some features explicitly register their own
knob on the new system, with the name that I suspenct it should
be the same to the related GUC (for users' convenient).

> +1 for a separate patch for remote session configuration.

It sounds reasnable for me. As I said there should be some such
variables.

> ALTER SYSTEM + SIGHUP targeted at a particular backend would do
> if the DBA can log into the database server (so, it can't be
> used for DBaaS.) It would be useful to have
> pg_reload_conf(pid).

I don't think it is reasonable. ALTER SYSTEM alters a *system*
configuration which is assumed to be the same on all sessions and
other processes. All sessions start the syscache tracking if
another ALTER SYSTEM for another variable then pg_reload_conf()
come after doing the above. I think the change should persist no
longer than the session-lifetime.

I think that a consensus on backend-targetted remote tuning is
made here:)

A. Let GUC variables settable by a remote session.

A-1. Variables are changed at a busy time (my first patch).
(transaction-awareness of GUC makes this complex)

A-2. Variables are changed when the session is idle (or outside
a transaction).

B. Override some variables via values laid on shared memory. (my
second or the last patch).

Very specific to a target feature. I think it consumes a bit
too large memory.

C. Provide session-specific GUC variable (that overides the global one)

- Add new configuration file "postgresql.conf.<PID>" and
pg_reload_conf() let the session with the PID loads it as if
it is the last included file. All such files are removed at
startup or at the end of the coressponding session.

- Add a new syntax like this:
ALTER SESSION WITH (pid=xxxx)
SET configuration_parameter {TO | =} {value | 'value' | DEFAULT}
RESET configuration_parameter
RESET ALL

- Target variables are marked with GUC_REMOTE.

I'll consider the last choice and will come up with a patch.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center