Error while calling public.powa_wait_sampling_src: invalid memory alloc request size 1173399096

[tarkhil]

master: Error while calling public.powa_wait_sampling_src: invalid memory alloc request size 1173399096 
CONTEXT: SQL statement 
"SELECT now(), COALESCE(pgss.dbid, 0) AS dbid, s.event_type, s.event, s.queryid, sum(s.count) as count FROM pg_wait_sampling_profile s 
-- pg_wait_sampling doesn't offer a per (userid, dbid, queryid) view, 
-- only per pid, but pid can be reused for different databases or users 
-- so we cannot deduce db or user from it. However, queryid should be 
-- unique across differet databases, so we retrieve the dbid this way. 
-- Note that the same queryid can exists for multiple entries if 
-- multiple users execute the query, so it's critical to retrieve a 
-- single row from pg_stat_statements per (dbid, queryid) 
LEFT JOIN (SELECT DISTINCT s2.dbid, s2.queryid FROM pg_stat_statements(false) s2 ) pgss 
ON pgss.queryid = s.queryid 
WHERE s.event_type IS NOT NULL AND s.event IS NOT NULL AND COALESCE(pgss.dbid, 0) NOT IN (
SELECT oid FROM powa_databases WHERE dropped IS NOT NULL) GROUP BY pgss.dbid, s.event_type, s.event, s.queryid" PL/pgSQL function powa_wait_sampling_src(integer) line 4 at RETURN QUERY

No idea on the reason.

[marco44]

Looks like this query wants to retrieve 1GB of data. Did you set pg_wait_sampling.profile_pid to off? If not the produced view can be quite huge (and return data we don't care about in powa)

[tarkhil]

Okay, did it; should I cleanup something to get rid of the error?

[marco44]

I suppose calling pg_wait_sampling_reset_profile() should be enough

[tarkhil]

Yes, it works!

[tarkhil]

No error, no data. Should something be restarted/reconfigured?

[rjuju]

It shouldn't be needed.

Do you see any row in the powa_wait_sampling_history_current table of the powa repository server? If not, do you see rows in the powa_wait_sampling_src_tmp table on the same repository server?

[tarkhil]

powa=# select count(*) from powa_wait_sampling_history_current ;
 count
-------
 21745
(1 row)

and still nothing in web interface

[rjuju]

Looking at the code I don't see any reason why it wouldn't be displayed. Is it the same on all pages (per instance, per database, per query)?

Are you using the remote mode or local mode? If remote, how many remote servers do you have? Is the configuration page shows that pg_wait sampling is sampled and find the correct version? (assuming the UI is available on 127.0.0.1:8888 and the server has the id 42, url http://127.0.0.1:8888/config/42)

[tarkhil]

Right after your comment, everything reappeared.

(actually, I went from the Home button and now it works)

[rjuju]

Ah, good news. Maybe it's due to a somewhat large snapshot interval. In general data only appear after 3 snapshots, since we compute diff between snapshot (so 2 values to get a single counter), and then needs a 3rd snapshot to start drawing lines.