Crash on UNION with PG 17

On Wed, Mar 27, 2024 at 11:33:55AM -0400, Regina Obe wrote:

Our PostGIS bot that follows master branch has been crashing for past couple
of days on one of our tests

https://trac.osgeo.org/postgis/ticket/5701

I traced the issue down to this commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=66c0185a3d14b
bbf51d0fc9d267093ffec735231

The issue can be exercised without postgis installed as follows:

CREATE TABLE edge_data AS
SELECT i AS edge_id, i + 1 AS start_node, i + 2 As end_node
FROM generate_series(1,10) AS i;

WITH edge AS (
SELECT start_node, end_node
FROM edge_data
WHERE edge_id = 1
)
SELECT start_node id FROM edge UNION
SELECT end_node FROM edge;

I can confirm the crash in git master.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, 28 Mar 2024 at 04:34, Regina Obe <lr@pcorp.us> wrote:

The issue can be exercised without postgis installed as follows:

CREATE TABLE edge_data AS
SELECT i AS edge_id, i + 1 AS start_node, i + 2 As end_node
FROM generate_series(1,10) AS i;

WITH edge AS (
SELECT start_node, end_node
FROM edge_data
WHERE edge_id = 1
)
SELECT start_node id FROM edge UNION
SELECT end_node FROM edge;

Thanks for the report.

There's some discussion about this in [1]/messages/by-id/242fc7c6-a8aa-2daf-ac4c-0a231e2619c1@gmail.com along with a proposed way to
fix it. The proposed fix does alter the function signature of an
important and externally visible planner function, so will be waiting
for some feedback on that before moving ahead with fixing.

[1]: /messages/by-id/242fc7c6-a8aa-2daf-ac4c-0a231e2619c1@gmail.com

David

On Thu, 28 Mar 2024 at 04:34, Regina Obe <lr@pcorp.us> wrote:

CREATE TABLE edge_data AS
SELECT i AS edge_id, i + 1 AS start_node, i + 2 As end_node
FROM generate_series(1,10) AS i;

WITH edge AS (
SELECT start_node, end_node
FROM edge_data
WHERE edge_id = 1
)
SELECT start_node id FROM edge UNION
SELECT end_node FROM edge;

As of d5d2205c8, this query should work as expected.

Thank you for letting us know about this.

David

On Thu, 28 Mar 2024 at 04:34, Regina Obe <lr@pcorp.us> wrote:

CREATE TABLE edge_data AS
SELECT i AS edge_id, i + 1 AS start_node, i + 2 As end_node FROM
generate_series(1,10) AS i;

WITH edge AS (
SELECT start_node, end_node
FROM edge_data
WHERE edge_id = 1
)
SELECT start_node id FROM edge UNION
SELECT end_node FROM edge;

As of d5d2205c8, this query should work as expected.

Thank you for letting us know about this.

David

Thanks for the fix. I confirm it works now and our bots are green again.

Regina