Message-ID: <0f704b53-0535-47de-b600-e11ad0558314@oss.nttdata.com>
Date: Tue, 4 Mar 2025 13:45:21 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Log connection establishment timings
To: Melanie Plageman <melanieplageman@gmail.com>,
 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Andres Freund <andres@anarazel.de>,
 Guillaume Lelarge <guillaume@lelarge.info>,
 Pg Hackers <pgsql-hackers@postgresql.org>,
 Daniel Gustafsson <daniel@yesql.se>, andrey.chudnovskiy@microsoft.com,
 Jelte Fennema-Nio <postgres@jeltef.nl>,
 Jacob Champion <jacob.champion@enterprisedb.com>
References: <Z45lUorFBcmCKxp7@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAAKRu_YrNsA7-v5L9d318XZu+tPqcxp+ctCGy2EGYrSt69ON=A@mail.gmail.com>
 <CAAKRu_aV+B2yLpkcfh06tJV7XQPodjasxQ=XKh+MjD8ixkXwrA@mail.gmail.com>
 <65fe2d68-a214-40f0-80a8-56ca7323ad5c@oss.nttdata.com>
 <Z77Fxf49c+LIW74a@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAAKRu_bWRipJ2HA8cwvxrZOhSpvpPLkvhcBHecXBx8_wS4cjQQ@mail.gmail.com>
 <Z8ALQSr1o0AoGpAo@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAAKRu_Zv7e1GWHiTJ9UOZEoqs8hO8sCezZZNuPv7s5mGYe_QkA@mail.gmail.com>
 <7d3fcftmqsm2vounbkmkqnwbqkovsizgdkow46qo7nunau4q2n@pyavjiltbqnu>
 <CAAKRu_asMtUpxDjts1J60batVqLsSGRodMOq4E9ryg1XdO8EZw@mail.gmail.com>
 <Z8FPpJPDLHWNuV2c@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAAKRu_ZcseVdWMz9vxfb9yavS+JE762cXFJjzWsPLjvEPMckNg@mail.gmail.com>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <CAAKRu_ZcseVdWMz9vxfb9yavS+JE762cXFJjzWsPLjvEPMckNg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0f704b53-0535-47de-b600-e11ad0558314%40oss.nttdata.com>
Precedence: bulk


On 2025/03/01 7:52, Melanie Plageman wrote:
> On Fri, Feb 28, 2025 at 12:54 AM Bertrand Drouvot
> <bertranddrouvot.pg@gmail.com> wrote:
>>
>> On Thu, Feb 27, 2025 at 05:55:19PM -0500, Melanie Plageman wrote:
>>> It still needs polishing (e.g. I need to figure out where to put the new guc hook
>>> functions and enums and such)
>>
>> yeah, I wonder if it would make sense to create new dedicated "connection_logging"
>> file(s).
> 
> I think for now there isn't enough for a new file. I think these are
> probably okay in postmaster.c since this has to do with postmaster
> starting new connections.
> 
>>> I'm worried the list of possible connection log messages could get
>>> unwieldy if we add many more.
>>
>> Thinking out loud, I'm not sure we'd add "many more" but maybe what we could do
>> is to introduce some predefined groups like:
>>
>> 'basic' (that would be equivalent to 'received, + timings introduced in 0002')
>> 'security' (equivalent to 'authenticated,authorized')
>>
>> Not saying we need to create this kind of predefined groups now, just an idea
>> in case we'd add many more: that could "help" the user experience, or are you
>> more concerned about the code maintenance?
> 
> I was more worried about the user having to provide very long lists.
> But groupings could be a sensible solution in the future.
> 
>> Just did a quick test, (did not look closely at the code), and it looks like
>> that:
>>
>> 'all': does not report the "received" (but does report authenticated and authorized)
>> 'received': does not work?
>> 'authenticated': works
>> 'authorized': works
> 
> Thanks for testing! Ouch, there were many bugs in that prototype. My
> enums were broken and a few other things.
> 
> I found some bugs in 0002 as well.  Attached v8 fixes the issues I found so far.
> 
> We have to be really careful about when log_connections is actually
> set before we use it -- I fixed some issues with that.

When log_connection is empty string in postgresql.conf and I ran
"psql -d "options=-clog_connections=all"", I got the following log message.
You can see the total duration in this message is unexpected.
It looks like this happens because creation_time wasn’t collected,
as log_connections was empty before the fork.

     LOG:  connection establishment times (ms): total: 1148052469, fork: 0, authentication: 0

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION