Обсуждение: sorting a union over inheritance vs pathkeys

I’ve come across an issue when creating a union over tables which includes inheritance:

CREATE TABLE events (event_id INT NOT NULL);
-- CREATE TABLE
CREATE UNIQUE INDEX events_event_id_key ON events (event_id);
-- CREATE INDEX

CREATE TABLE legacy_events (event_id INT NOT NULL);
-- CREATE TABLE
CREATE UNIQUE INDEX legacy_events_event_id_key ON legacy_events (event_id);
-- CREATE INDEX

CREATE TABLE events_2 () INHERITS (events);
-- CREATE TABLE
-- this index isn't necessary to reproduce the error
CREATE UNIQUE INDEX events_2_event_id_key ON events_2 (event_id);
-- CREATE INDEX

SELECT event_idFROM (SELECT event_id        FROM events      UNION ALL      SELECT event_id        FROM legacy_events)
_ORDERBY event_id; 
-- ERROR:  could not find pathkey item to sort

It’ll work if the indexes are removed. Using PRIMARY KEY in lieu of NOT NULL and UNIQUE indexes still exhibits the
issue.

I’ve seen this in 9.2.8 and 9.3.4. I haven’t tested this in 9.4 or earlier than 9.2.

Any thoughts?

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> I’ve come across an issue when creating a union over tables which includes inheritance:
> CREATE TABLE events (event_id INT NOT NULL);
> -- CREATE TABLE
> CREATE UNIQUE INDEX events_event_id_key ON events (event_id);
> -- CREATE INDEX

> CREATE TABLE legacy_events (event_id INT NOT NULL);
> -- CREATE TABLE
> CREATE UNIQUE INDEX legacy_events_event_id_key ON legacy_events (event_id);
> -- CREATE INDEX

> CREATE TABLE events_2 () INHERITS (events);
> -- CREATE TABLE
> -- this index isn't necessary to reproduce the error
> CREATE UNIQUE INDEX events_2_event_id_key ON events_2 (event_id);
> -- CREATE INDEX

> SELECT event_id
>  FROM (SELECT event_id
>          FROM events
>        UNION ALL
>        SELECT event_id
>          FROM legacy_events) _
>  ORDER BY event_id;
> -- ERROR:  could not find pathkey item to sort

Hm ... I can reproduce that in 9.3 but it seems fine in 9.4 and HEAD.
Don't know what's going on exactly.
        regards, tom lane

I wrote:
> Michael Glaesemann <grzm@seespotcode.net> writes:
>> -- ERROR:  could not find pathkey item to sort

> Hm ... I can reproduce that in 9.3 but it seems fine in 9.4 and HEAD.
> Don't know what's going on exactly.

Interesting --- it appears that commit
a87c729153e372f3731689a7be007bc2b53f1410 is why it works in 9.4.  I had
thought that was just improving plan quality, but it seems to also prevent
this problem.  I guess we'd better back-patch it.

I find that 9.1 through 9.3 fail with this example; it may be that it was
the addition of MergeAppend support that exposed the issue.
        regards, tom lane

On Jun 25, 2014, at 22:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I wrote:
>> Michael Glaesemann <grzm@seespotcode.net> writes:
>>> -- ERROR:  could not find pathkey item to sort
> 
>> Hm ... I can reproduce that in 9.3 but it seems fine in 9.4 and HEAD.
>> Don't know what's going on exactly.
> 
> Interesting --- it appears that commit
> a87c729153e372f3731689a7be007bc2b53f1410 is why it works in 9.4.  I had
> thought that was just improving plan quality, but it seems to also prevent
> this problem.  I guess we'd better back-patch it.

Thanks, Tom!

Michael Glaesemann
grzm seespotcode net