invalid memory alloc request size

[pchovelon]

Got this error log in PostgreSQL : ERROR: XX000: invalid memory alloc request size 1107200064

The query is the following one :

 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

Any idea ?

Thank you :pray:

[rjuju]

Hi,

1107200064 is just a bit more than 1GB, which is the highest amount of memory postgres allows in a single allocation. It probably just means that you have a lot of wait events, which lead to this problem.

If you haven't done so already, I would suggest to set pg_wait_sampling.profile_pid to false globally, as recommended in https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_wait_sampling.html#configuration. If this parameter is set to true, it will greatly increase the amount of data stored by pg_wait_sampling, so disabling it should greatly reduce the amount of memory needed for that query.

[pchovelon]

Merci beaucoup :smile: :elephant:

[rjuju]

De rien :)

I'm closing this issue, feel free to reopen it if the mentioned configuration change isn't enough to fix the problem.