Re: ALTER SESSION

Hello.

At Fri, 1 Feb 2019 06:31:40 +0000, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote in <0A3221C70F24FB45833433255569204D1FB927B1(at)G01JPEXMBYT05>
> Thanks for a cool feature with nice UI. Can I expect it to work for background processes? For troubleshooting, I wanted to investigate how autovacuum launcher/worker behaves by having them emit DEBUG messages.
>
> (My comments follow)

I haven't did actually, but it doesn't reject background
workers. But background worker seems to assume that no change in
variablres while working. I should consider that.

> From: Kyotaro HORIGUCHI [mailto:horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp]
> > .auto.conf is already a kind of such.. My first version signals the change
> > via shared memory (in a largely-improvable way) and add the GUC system the
> > complex "nontransactional SET" feature, which lets a change persists beyond
> > transaction end if any. Holding changes until the session becomes idle seems
> > also complex.
> >
> > https://www.postgresql.org/message-id/20181127.193622.252197705.horigu
> > chi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp
> >
> > The most significant reason for passing-by-file is the affinity with the
> > current GUC system.
>
> Regarding the target session specification, do we want to use pid or a session ID like the following?
>
> https://www.postgresql.org/docs/devel/runtime-config-logging.html
> --------------------------------------------------
> The %c escape prints a quasi-unique session identifier, consisting of two 4-byte hexadecimal numbers (without leading zeros) separated by a dot. The numbers are the process start time and the process ID, so %c can also be used as a space saving way of printing those items. For example, to generate the session identifier from pg_stat_activity, use this query:
>
> SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
> to_hex(pid)
> FROM pg_stat_activity;
>
> pid is easier to type. However, the session startup needs to try to delete the leftover file. Is the file system access negligible compared with the total heavy session startup processing?
>
> If we choose session ID, the session startup doesn't have to care about the leftover file. However, the background process crash could leave the file for a long time, since the crash may not lead to postmaster restart. Also, we will get inclined to add sessionid column in pg_stat_activity (the concept of session ID can be useful for other uses.)

Sounds reasonbale.

The attached version happens to add backend startup time in
PGPROC and I added session id as a usable key. (Heavily WIP)

ALTER SESSION WITH (id '5c540141.b7f') SET work_mem to '128kB';

> I'm OK about passing parameter changes via a file. But I'm not sure whether using DSM (DSA) is easier with less code.

Perhaps DSA is not required. Currently it uses rather a large
area but I think we can do the same thing with smaller memory by
sending long strings part by part.

> And considering the multi-threaded server Konstantin is proposing, would it better to take pass-by-memory approach? I imagine that if the server gets multi-threaded, the parameter change would be handled like:
>
> 1. Allocate memory for one parameter change.
> 2. Write the change to that memory.
> 3. Link the memory to a session-specific list.
> 4. The target session removes the entry from the list, applies the change, and frees the memory.
>
> The code modification may be minimal when we migrate to the multi-threaded server -- only memory allocation and free functions.

The attached is a WIP patch that:

- Using the non-transactional SET (for my convenience).

- based on not file, but static shared memory.
Using a new signal

- It adds PGC_S_REMOTE with the same precedence with PGC_S_SESSION.
(This causes arguable behavior..)

- ALTER SESSION syntax. (key can be pid or session id)

(Sorry for the inconsistent name of the patch files..)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center