public.powa_take_snapshot(9): function public.powa_qualstats_snapshot failed: bigint out of range

[lguduk]

Hello,

On my powa repository server below rpms are installed.

powa-archivist_17-5.0.1-1PGDG.rhel9.x86_64
powa_17-5.0.1-1PGDG.rhel9.x86_64
powa-web-5.0.1-1PGDG.rhel9.noarch
powa-collector-1.3.0-2PGDG.rhel9.noarch

Database version: 17.2
Os version rhel 9.5

Below error is taken from one of monitored servers. Can you give me advice for below error?

SELECT public.powa_take_snapshot(9);

-- function execution log
public.powa_take_snapshot(9): function public.powa_qualstats_snapshot failed:
state : 22003
message: bigint out of range
detail :
hint :
context: SQL statement "WITH capture AS (
SELECT *
FROM public.powa_qualstats_src(_srvid) q
WHERE EXISTS (SELECT 1
FROM public.powa_statements s
WHERE s.srvid = _srvid
AND q.queryid = s.queryid
AND q.dbid = s.dbid
AND q.userid = s.userid)
),
missing_quals AS (
INSERT INTO public.powa_qualstats_quals (srvid, qualid, queryid, dbid, userid, quals)
SELECT DISTINCT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
array_agg(DISTINCT q::public.qual_type)
FROM capture qs,
LATERAL (SELECT (unnest(quals)).) as q
WHERE NOT EXISTS (
SELECT 1
FROM public.powa_qualstats_quals nh
WHERE nh.srvid = _srvid
AND nh.qualid = qs.qualnodeid
AND nh.queryid = qs.queryid
AND nh.dbid = qs.dbid
AND nh.userid = qs.userid
)
GROUP BY srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual AS (
INSERT INTO public.powa_qualstats_quals_history_current (srvid, qualid, queryid,
dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num)
SELECT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
ts, sum(occurences), sum(execution_count), sum(nbfiltered),
avg(mean_err_estimate_ratio), avg(mean_err_estimate_num)
FROM capture as qs
GROUP BY srvid, ts, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual_with_const AS (
INSERT INTO public.powa_qualstats_constvalues_history_current(srvid, qualid,
queryid, dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num, constvalues)
SELECT _srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid, ts,
occurences, execution_count, nbfiltered, mean_err_estimate_ratio,
mean_err_estimate_num, constvalues
FROM capture as qs
)
SELECT COUNT() FROM capture"
PL/pgSQL function public.powa_qualstats_snapshot(integer) line 13 at SQL statement
SQL statement "SELECT public.powa_qualstats_snapshot(9)"
PL/pgSQL function public.powa_take_snapshot(integer) line 79 at EXECUTE
public.powa_take_snapshot(9): function public.powa_qualstats_snapshot failed:
state : 22003
message: bigint out of range
detail :
hint :
context: SQL statement "WITH capture AS (
SELECT *
FROM public.powa_qualstats_src(_srvid) q
WHERE EXISTS (SELECT 1
FROM public.powa_statements s
WHERE s.srvid = _srvid
AND q.queryid = s.queryid
AND q.dbid = s.dbid
AND q.userid = s.userid)
),
missing_quals AS (
INSERT INTO public.powa_qualstats_quals (srvid, qualid, queryid, dbid, userid, quals)
SELECT DISTINCT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
array_agg(DISTINCT q::public.qual_type)
FROM capture qs,
LATERAL (SELECT (unnest(quals)).) as q
WHERE NOT EXISTS (
SELECT 1
FROM public.powa_qualstats_quals nh
WHERE nh.srvid = _srvid
AND nh.qualid = qs.qualnodeid
AND nh.queryid = qs.queryid
AND nh.dbid = qs.dbid
AND nh.userid = qs.userid
)
GROUP BY srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual AS (
INSERT INTO public.powa_qualstats_quals_history_current (srvid, qualid, queryid,
dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num)
SELECT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
ts, sum(occurences), sum(execution_count), sum(nbfiltered),
avg(mean_err_estimate_ratio), avg(mean_err_estimate_num)
FROM capture as qs
GROUP BY srvid, ts, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual_with_const AS (
INSERT INTO public.powa_qualstats_constvalues_history_current(srvid, qualid,
queryid, dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num, constvalues)
SELECT _srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid, ts,
occurences, execution_count, nbfiltered, mean_err_estimate_ratio,
mean_err_estimate_num, constvalues
FROM capture as qs
)
SELECT COUNT() FROM capture"
PL/pgSQL function public.powa_qualstats_snapshot(integer) line 13 at SQL statement
SQL statement "SELECT public.powa_qualstats_snapshot(9)"
PL/pgSQL function public.powa_take_snapshot(integer) line 79 at EXECUTE
public.powa_take_snapshot(9): function public.powa_qualstats_snapshot failed:
state : 22003
message: bigint out of range
detail :
hint :
context: SQL statement "WITH capture AS (
SELECT *
FROM public.powa_qualstats_src(_srvid) q
WHERE EXISTS (SELECT 1
FROM public.powa_statements s
WHERE s.srvid = _srvid
AND q.queryid = s.queryid
AND q.dbid = s.dbid
AND q.userid = s.userid)
),
missing_quals AS (
INSERT INTO public.powa_qualstats_quals (srvid, qualid, queryid, dbid, userid, quals)
SELECT DISTINCT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
array_agg(DISTINCT q::public.qual_type)
FROM capture qs,
LATERAL (SELECT (unnest(quals)).) as q
WHERE NOT EXISTS (
SELECT 1
FROM public.powa_qualstats_quals nh
WHERE nh.srvid = _srvid
AND nh.qualid = qs.qualnodeid
AND nh.queryid = qs.queryid
AND nh.dbid = qs.dbid
AND nh.userid = qs.userid
)
GROUP BY srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual AS (
INSERT INTO public.powa_qualstats_quals_history_current (srvid, qualid, queryid,
dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num)
SELECT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
ts, sum(occurences), sum(execution_count), sum(nbfiltered),
avg(mean_err_estimate_ratio), avg(mean_err_estimate_num)
FROM capture as qs
GROUP BY srvid, ts, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual_with_const AS (
INSERT INTO public.powa_qualstats_constvalues_history_current(srvid, qualid,
queryid, dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num, constvalues)
SELECT _srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid, ts,
occurences, execution_count, nbfiltered, mean_err_estimate_ratio,
mean_err_estimate_num, constvalues
FROM capture as qs
)
SELECT COUNT() FROM capture"
PL/pgSQL function public.powa_qualstats_snapshot(integer) line 13 at SQL statement
SQL statement "SELECT public.powa_qualstats_snapshot(9)"
PL/pgSQL function public.powa_take_snapshot(integer) line 79 at EXECUTE

-- db logs

2025-03-12 10:51:08.037 +03 pid=301990 client=localhost db=powa user=powa appname=PoWA - powa_kcache_snapshot(9) SELECTCONTEXT: PL/pgSQL function public.powa_take_snapshot(integer) line 89 at RAISE
2025-03-12 10:56:08.062 +03 pid=305020 client=localhost db=powa user=powa appname=PoWA - powa_qualstats_snapshot(9) SELECTLOG: temporary file: path "base/pgsql_tmp/pgsql_tmp305020.1", size 2560142
2025-03-12 10:56:08.062 +03 pid=305020 client=localhost db=powa user=powa appname=PoWA - powa_qualstats_snapshot(9) SELECTCONTEXT: PL/pgSQL function public.powa_take_snapshot(integer) line 79 during exception cleanup
2025-03-12 10:56:08.062 +03 pid=305020 client=localhost db=powa user=powa appname=PoWA - powa_qualstats_snapshot(9) SELECTSTATEMENT: SELECT public.powa_take_snapshot(9)

[rjuju]

Hi,

I'm not sure what happened here. Unfortunately postgres doesn't say which field is responsible for the error so I will need to compare them all with the query to see which one it could be. One easy explanation could be sum(occurences), sum(execution_count), sum(nbfiltered), although it would be very surprising that you would reach bigint limit with that.