Thread: BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function
BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function
From
cpburnz@gmail.com
Date:
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.
Re: BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function
From
"David G. Johnston"
Date:
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.