Обсуждение: [MASSMAIL]Crash on UNION with PG 17

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;


If I run using UNION ALL, this works fine:

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


Thanks,
Regina

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] 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] https://www.postgresql.org/message-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