On 11/23/24 05:16, Steeve Boulanger wrote:
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.
One possible scenario:
log_min_messages = info
log_min_error_statement = info
log_statement = 'all'
psql -d test -U postgres -p 5432
CREATE OR REPLACE FUNCTION public.admin_func()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM pg_stat_reset();
RAISE NOTICE 'Reset statistics';
END;
$function$
select datname, stats_reset from pg_stat_database;
datname | stats_reset
--------------+-------------------------------
NULL | NULL
postgres | NULL
template1 | NULL
template0 | NULL
test | 2024-11-23 09:21:49.421552-08
task_manager | NULL
test_psql | NULL
production | NULL
pp_archive | NULL
farm_db | NULL
select admin_func();
NOTICE: Reset statistics
admin_func
------------
(1 row)
select datname, stats_reset from pg_stat_database;
datname | stats_reset
--------------+-------------------------------
NULL | NULL
postgres | NULL
template1 | NULL
template0 | NULL
test | 2024-11-23 09:26:30.749257-08
task_manager | NULL
test_psql | NULL
production | NULL
pp_archive | NULL
farm_db | NULL
2024-11-23 09:26:30.749 PST [14501] postgres@test LOG: statement:
select admin_func();
2024-11-23 09:26:30.749 PST [14501] postgres@test NOTICE: Reset statistics
2024-11-23 09:26:30.749 PST [14501] postgres@test CONTEXT: PL/pgSQL
function admin_func() line 4 at RAISE
2024-11-23 09:26:30.749 PST [14501] postgres@test STATEMENT: select
admin_func();
The issue being that the pg_stat_reset() is buried in a function and
does not show up on its own. The RAISE NOTICE alerts in my logs just so
I could find the function easily. It could be there is a function or
functions in your setup doing something similar.
On Sat, Nov 23, 2024 at 7:09 AM Ray O'Donnell <ray@xxxxxxxxxxxx> 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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
--
Raymond O'Donnell // Galway // Ireland
ray@xxxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
