PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15721
PG Version11.2
OSScientific Linux release 7.4
Opened2019-03-28 17:28:09+00
Reported byChris Nestrud
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15721
Logged by:          Chris Nestrud
Email address:      (redacted)
PostgreSQL version: 11.2
Operating system:   Scientific Linux release 7.4
Description:        

I have a Postgres server running version 11.2 which occasionally fails to
run a query with an error similar to:

dsa_allocate could not find 97 free pages

This seems to happen when there are several long-running queries using the
same table which contains around 60,000,000 rows and 30 partitions.

This instance constantly receives data in staging tables, inserts from
staging to partitions, finds statistics which are sent elsewhere, and drops
the partitions when they are no longer needed.

This instance is temporary and is located on a ram disk.

Configuration values changed from defaults are:

shared_buffers = 32GB
work_mem = 32MB
maintenance_work_mem = 512MB
wal_level = minimal
synchronous_commit = off
full_page_writes = off
max_wal_size = 8GB
max_wal_senders = 0
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
effective_cache_size = 128GB

This is an infrequent error. The server will run for several days,
constantly running queries such as those shown below, and only occasionally
show this error.

Queries are of the form:

select a_a, count(*) as a_count
from t
where c_time >= 'yy-mm-dd hh:mm:ss'
and c_time < 'yy-mm-dd hh:mm:ss'
group by a_a
order by a_count desc;

and

select f, sum(f_count) as f_count
        from (
        select a_a as f, count(*) as f_count
        from t
        where c_time>= 'yyyy-mm-dd hh:mm:ss'
and c_time < 'yyyy-mm-dd hh:mm:ss'
        and function_with_indexed_result(a_a)
        group by a_a
        union all
        select b_b as f, count(*) as f_count
        from t
        where c_time>= 'yyyy-mm-dd hh:mm:ss'
and c_time < 'yyyy-mm-dd hh:mm:ss'
        and function_with_indexed_result(b_b)
        group by b_b
        ) t1
        group by f
        order by sum(f_count) desc;

Column a_a is inet
Column b_b is inet
Column c_time is timestamp with time zone

There are usually 3 or 4 queries of this type running simultaneously, along
with other queries.

Messages

DateAuthorSubject
2019-03-28 17:28:09+00PG Bug reporting formBUG #15721: FATAL: dsa_allocate could not find 97 free pages
2019-03-28 18:47:20+00Sergei KornilovRe: BUG #15721: FATAL: dsa_allocate could not find 97 free pages