time_bucket could not find pathkey item to sort

[Daennes]

Relevant system information:

• Image: timescale/timescaledb:latest-pg11
• PostgreSQL version: latest-11
• TimescaleDB version: 2.02
• Installation method: Docker

Same issue for timescaleDB 1.7.5 and 2.1.0

Hello All, I get an unexpected error when I use the time_bucket function. Especially when in our case small time ranges are used < 2h. Timerange is here the diff between timestamp A and B. Explanation: I have table A and B. They have a one-to-many relationship.I now join B to A on an ID and the time column.

SELECT time_bucket('1 hour', metricA.time, 'timestampA') "time",
       sum(metricB.costs)
FROM metricA
INNER JOIN metricB ON metricB.name = metricA.name AND metricB.time = metricA.time
WHERE metricA.time BETWEEN 'timestampA' AND 'timestampB'
GROUP BY 1

The error no longer occurs if metricB.time is used in the time_bucket function. time_bucket('1 hour', metricB.time, 'timestampA')

Error:

ERROR: could not find pathkey item to sort
SQL state: XX000

Any ides?

[svenklemm]

Can you provide a minimal sql script to reproduce the issue in a new database? EXPLAIN output for the query would be helpful too.

[emmanuel-kubermatic]

Hello @svenklemm , we have resolved the issue by using a TRUNCATE instead of a DELETE to empty out the tables in our database.

Our approach:

`SELECT table_name
           FROM information_schema.tables
           WHERE table_schema = 'public'`
Then loop through the result and append a `TRUNCATE table CASCADE` for a read heavy hypertable in our test spec whilst using a `DELETE FROM table` for the rest

That seemed to fix the pathkey item issue for now but we're not quite aware why that works

Any notes from your end?

[genedavis]

Hi any update on this issue? Thx

[svenklemm]

There have been many bugfixes that could be a potential cause for this since this has been reported. Can you try on the latest version or provide a self-contained script to reproduce the error.

[konskov]

Hello @Daennes @emmanuel-kubermatic @genedavis Have you been able to check with the latest version and see if the issue is still there?

[github-actions[bot]]

This issue has been automatically marked as stale due to lack of activity. You can remove the stale label or comment. Otherwise, this issue will be closed in 30 days. Thank you!

[github-actions[bot]]

Dear Author,

We are closing this issue due to lack of activity. Feel free to add a comment to this issue if you can provide more information and we will re-open it. Thank you!

[cchenggit]

I have met the same error using time_bucket_gapfill recently. As far as I know, this bug may exist in postgres and timescaledb, and both versions are upgraded after the fixed version. but still NOT WORK for me...

links:

• Fix "could not find pathkey item to sort" planner errors that occur in some situations when the sort key involves an aggregate or window function.
• #2232

db> explain SELECT time_bucket_gapfill('1 h', time) as bucket, count(*) as n FROM "logs"  WHERE
 (time >= '2023-04-24T00:00:00') AND (time < '2023-05-05T00:00:00') AND (event @@ '$.actor.type == "user"') GROUP BY 1

could not find pathkey item to sort
Time: 0.004s
``

[kgyrtkirk]

@cchenggit could you please see if the issue is solved by upgrading to the latest version?

I think the original issue here was specific to time_bucket ; time_bucket_gapfill has a slightly different path ; so in case the problem persists:

• please open a separate ticket
• add explains
• try to simplify - and/or try to remove uses of unrelated features (does this happen if you don't use @@ ? )
• ...and most importantly: provide repro steps so that we could reproduce your issue