Re: pg16: XX000: could not find pathkey item to sort

On Mon, Sep 18, 2023 at 10:02 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> This fails since 1349d2790b
>
> commit 1349d2790bf48a4de072931c722f39337e72055e
> Author: David Rowley <drowley(at)postgresql(dot)org>
> Date: Tue Aug 2 23:11:45 2022 +1200
>
> Improve performance of ORDER BY / DISTINCT aggregates
>
> ts=# CREATE TABLE t (a int, b text) PARTITION BY RANGE (a);
> ts=# CREATE TABLE td PARTITION OF t DEFAULT;
> ts=# INSERT INTO t SELECT 1 AS a, '' AS b;
> ts=# SET enable_partitionwise_aggregate=on;
> ts=# explain SELECT a, COUNT(DISTINCT b) FROM t GROUP BY a;
> ERROR: XX000: could not find pathkey item to sort
> LOCATION: prepare_sort_from_pathkeys, createplan.c:6235

Thanks for the report! I've looked at it a little bit. In function
adjust_group_pathkeys_for_groupagg we add the pathkeys in ordered
aggregates to root->group_pathkeys. But if the new added pathkeys do
not have EC members that match the targetlist or can be computed from
the targetlist, prepare_sort_from_pathkeys would have problem computing
sort column info for the new added pathkeys. In the given example, the
pathkey representing 'b' can not match or be computed from the current
targetlist, so prepare_sort_from_pathkeys emits the error.

My first thought about the fix is that we artificially add resjunk
target entries to parse->targetList for the ordered aggregates'
arguments that are ORDER BY expressions, as attached. While this can
fix the given query, it would cause Assert failure for the query in
sql/triggers.sql.

-- inserts only
insert into my_table values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
update set b = my_table.b || ':' || excluded.b;

I haven't looked into how that happens.

Any thoughts?

Thanks
Richard