BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function - Mailing list pgsql-bugs

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.