Re: pg16: XX000: could not find pathkey item to sort
| От | Richard Guo |
|---|---|
| Тема | Re: pg16: XX000: could not find pathkey item to sort |
| Дата | |
| Msg-id | CAMbWs48X+cK2pEaZDVVjd+nOJzb1dN1CO1OKz+6D0FX2OTCGbg@mail.gmail.com обсуждение исходный текст |
| Ответ на | pg16: XX000: could not find pathkey item to sort (Justin Pryzby <pryzby@telsasoft.com>) |
| Ответы |
Re: pg16: XX000: could not find pathkey item to sort
|
| Список | pgsql-hackers |
On Mon, Sep 18, 2023 at 10:02 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
This fails since 1349d2790b
commit 1349d2790bf48a4de072931c722f39337e72055e
Author: David Rowley <drowley@postgresql.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
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
Вложения
В списке pgsql-hackers по дате отправления: