Re: BUG #17494: High demand for displacement sort

On Tue, May 24, 2022, 8:37 AM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17494
> Logged by: Владимир Пашутин
> Email address: pashutin(at)gmail(dot)com
> PostgreSQL version: 11.14
> Operating system: any
> Description:
>
> When working with sorting large lists, we often encounter an error:
> SQL state [XX000]; error code [0]; ERROR: could not resize shared memory
> segment \"/PostgreSQL.932873081\" to 100868096 bytes: Interrupted system
> call; nested exception is org.postgresql.util.PSQLException: ERROR: could
> not resize shared memory segment \"/PostgreSQL.932873081\" to 100868096
> bytes: Interrupted system call
> But we almost never need the whole list. Requests are always limited with
> LIMIT and OFFSET.
> However, from the analysis of the query plan, it turns out that first there
> is a complete sorting of the entire result and only then the selection of
> the required part of the rows.
>

This is not much of a bug report. PostgreSQL does have top-N sorts. Why
it is not used for some particular query is impossible to say without
seeing the query and/or query plan.

Even if not using top-N, it should still spill to disk instead of erroring
out like that, assuming your memory/parallel settings and load are
reasonable for your server. Again, with this amount of detail there is no
way to know.

Cheers,

Jeff