Re: Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?

> Here (Ireland) we sometimes say "common-or-garden variety".... It means a normal, everyday variety. :-)

I'm afraid that my Irish dialect is limited to "sláinte" only ;-) In
any case, thanks for taking the time to help with this issue. I'm
still investigating, but I think that calling the "ghostbusters" is
moving up the list now lol.

On Sat, Nov 23, 2024 at 7:09 AM Ray O'Donnell <ray(at)rodonnell(dot)ie> wrote:
>
> On 23/11/2024 13:06, Steeve Boulanger wrote:
>
>
> > The above is some garden variety select?
>
> Not 100% sure what the expression "garden variety select" means lol, but I'll take a guess that it means an "select from an in-house application" .. and yes it is.
>
>
> Here (Ireland) we sometimes say "common-or-garden variety".... It means a normal, everyday variety. :-)
>
> Ray.
>
>
>
>
> -Steeve
>
> On Fri, Nov 22, 2024 at 11:18 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>
>> On 11/21/24 15:50, Steeve Boulanger wrote:
>> > > 1) Do the 77 share some trait the other 80 don't.
>> >
>> > No pattern found yet .. but still verifying a few things
>> >
>> > > 2) Do the OS system logs reveal anything?
>> >
>> > Nothing found in syslog
>> >
>> > > 3) What was happening in the databases just prior to the time the stats
>> > reset?
>> >
>> > Here's an example (log extracts) for a stats reset occurrence:
>> >
>> > select datname, stats_reset, now()-stats_reset as since_reset
>> > from pg_stat_database
>> > where ( now()-stats_reset ) < interval '1 day'
>> > order by 3 limit 1;
>> >
>> > datname | stats_reset | since_reset
>> > ----------------+-------------------------------+-----------------
>> > MyDB | *2024-11-21 13:48:34.332*785+00 | 00:00:22.266304
>> >
>> > <--LOGS-->
>> > 2024-11-21 13:48:34.324 UTC pid=[322035][2] db=[MyDB] usr=[user1]
>> > client=[host1] app=[[unknown]]LOG: connection authorized: user=user1
>> > database=MyDB applicatio
>> > n_name=app1 <..>
>>
>> What is the [2] referring to?
>>
>> >
>> > <.. no calls at "2024-11-21 13:48:34.332" - WHY?? ..>
>>
>> My guess is the difference in time it takes to log the action and set
>> the log timestamp. Whereas the stats_reset value is the timestamp when
>> the stats system actually did the reset.
>>
>> >
>> > 2024-11-21 13:48:34.336 UTC pid=[322035][3] db=[MyDB] usr=[user1]
>> > client=[host1] app=[app1]LOG: duration: 1.071 ms parse <unnamed>:
>> > SELECT <..>
>>
>> The above is some garden variety select?
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> ray(at)rodonnell(dot)ie