Обсуждение: BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function

The following bug has been logged on the website:

Bug reference:      13317
Logged by:          Caleb P. Burns
Email address:      cpburnz@gmail.com
PostgreSQL version: 9.3.6
Operating system:   Ubuntu 12.04.5
Description:

If I define a SQL function as:

CREATE FUNCTION sql_test() RETURNS TABLE (a INTEGER, b INTEGER) AS $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
$$ LANGUAGE sql IMMUTABLE ROWS 2;

I can select the values from both columns:

postgres=# SELECT (sql_test()).*;
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

I can also do the same for a PL/pgSQL function:

CREATE FUNCTION plpgsql_test() RETURNS TABLE (a INTEGER, b INTEGER) AS $$
    BEGIN
        RETURN QUERY SELECT 1, 2 UNION ALL SELECT 3, 4;
    END
$$ LANGUAGE plpgsql IMMUTABLE ROWS 2;

postgres=# SELECT (plpgsql_test()).*;
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

If I try to do the same for a PL/Python (3u) function, the query will run
for more than 5 or 10 minutes and never finish:

CREATE FUNCTION plpython_yield_test() RETURNS TABLE (a INTEGER, b INTEGER)
AS $$
    yield (1, 2)
    yield (3, 4)
$$ LANGUAGE plpython3u IMMUTABLE ROWS 2;

postgres=# SELECT (plpython_yield_test()).*;
^CCancel request sent
Cancel request sent
ERROR:  canceling statement due to user request

CREATE FUNCTION plpython_return_test() RETURNS TABLE (a INTEGER, b INTEGER)
AS $$
    return [(1, 2), (3, 4)]
$$ LANGUAGE plpython3u IMMUTABLE ROWS 2;

postgres=# SELECT (plpython_return_test()).*;
^CCancel request sent
Cancel request sent
ERROR:  canceling statement due to user request

However, selecting only a single column works.

postgres=# SELECT (plpython_yield_test()).a;
 a
---
 1
 3
(2 rows)

postgres=# SELECT (plpython_yield_test()).b;
 b
---
 2
 4
(2 rows)

postgres=# SELECT (plpython_return_test()).a;
 a
---
 1
 3
(2 rows)

postgres=# SELECT (plpython_return_test()).b;
 b
---
 2
 4
(2 rows)

Or if only one row is returned, then the query finishes:

CREATE FUNCTION plpython_return_test2() RETURNS TABLE (a INTEGER, b INTEGER)
AS $$
    return [(5, 6)]
$$ LANGUAGE plpython3u IMMUTABLE ROWS 1;

SELECT (plpython_return_test2()).*;
 a | b
---+---
 5 | 6
(1 row)

Running EXPLAIN does not reveal anything:

postgres=# EXPLAIN SELECT (plpython_return_test()).a;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.27 rows=2 width=0)
(1 row)

postgres=# EXPLAIN SELECT (plpython_return_test()).*;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.52 rows=2 width=0)
(1 row)

However, EXPLAIN ANALYZE will not finish for the multiple columns and rows:

postgres=# EXPLAIN ANALYZE SELECT (plpython_return_test()).a;
                                     QUERY PLAN

------------------------------------------------------------------------------------
 Result  (cost=0.00..0.27 rows=2 width=0) (actual time=0.056..0.063 rows=2
loops=1)
 Total runtime: 0.076 ms
(2 rows)

postgres=# EXPLAIN ANALYZE SELECT (plpython_return_test()).*;
^CCancel request sent
Cancel request sent
ERROR:  canceling statement due to user request

postgres=# EXPLAIN ANALYZE SELECT (plpython_return_test2()).*;
                                     QUERY PLAN

------------------------------------------------------------------------------------
 Result  (cost=0.00..0.51 rows=1 width=0) (actual time=0.089..0.106 rows=1
loops=1)
 Total runtime: 0.119 ms
(2 rows)

This appears to be a bug that selecting from multiple columns returned from
a PL/Python function returning multiple rows does not work (never
finishes).

NOTE: This issue is also present on a Windows machine running PostgreSQL
9.1.0.

On Tuesday, May 19, 2015, <cpburnz@gmail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13317
> Logged by:          Caleb P. Burns
> Email address:      cpburnz@gmail.com <javascript:;>
> PostgreSQL version: 9.3.6
> Operating system:   Ubuntu 12.04.5
> Description:
>
> If I define a SQL function as:
>
> CREATE FUNCTION sql_test() RETURNS TABLE (a INTEGER, b INTEGER) AS $$
>         SELECT 1, 2
>         UNION ALL
>         SELECT 3, 4
> $$ LANGUAGE sql IMMUTABLE ROWS 2;
>
> I can select the values from both columns:
>
> postgres=# SELECT (sql_test()).*;
>  a | b
> ---+---
>  1 | 2
>  3 | 4
> (2 rows)
>
> I can also do the same for a PL/pgSQL function:
>
> CREATE FUNCTION plpgsql_test() RETURNS TABLE (a INTEGER, b INTEGER) AS $$
>         BEGIN
>                 RETURN QUERY SELECT 1, 2 UNION ALL SELECT 3, 4;
>         END
> $$ LANGUAGE plpgsql IMMUTABLE ROWS 2;
>
> postgres=# SELECT (plpgsql_test()).*;
>  a | b
> ---+---
>  1 | 2
>  3 | 4
> (2 rows)
>
> If I try to do the same for a PL/Python (3u) function, the query will run
> for more than 5 or 10 minutes and never finish:
>
> CREATE FUNCTION plpython_yield_test() RETURNS TABLE (a INTEGER, b INTEGER)
> AS $$
>         yield (1, 2)
>         yield (3, 4)
> $$ LANGUAGE plpython3u IMMUTABLE ROWS 2;
>
> postgres=# SELECT (plpython_yield_test()).*;
> ^CCancel request sent
> Cancel request sent
> ERROR:  canceling statement due to user request
>
>
> This appears to be a bug that selecting from multiple columns returned from
> a PL/Python function returning multiple rows does not work (never
> finishes).
>
>
Then don't do that.   Seriously, don't do that.  Ever.  Even when it
"works" it isn't actually working.  So, just don't do that.

If you can use LATERAL you should do so.  Otherwise use the form:

with func_call (res) as (
Select func(...)
)
Select (func_call.res).* from func_call;

Put a raise notice in the pl/pgsql version of the function to see why.
Basically the function is evaluated once for each column being asked for.
Python is having issues with the function being invoked repeatedly probably
resulting in some kind of infinite recursion.  This is likely a bug that
could be fixed but the "workaround" is the correct way to handle set
returning functions in the select.  You must return the overall type first
and then explode the type.  You should not explode the function itself.

David J.