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