Thread: Parallel SAFE information missing in CREATE OR REPLACE FUNCTION definition
Parallel SAFE information missing in CREATE OR REPLACE FUNCTION definition
From
Ashutosh Sharma
Date:
Hi,
In PGSQL-9.6, if we create a function with PARALLEL clause and try displaying it's definition using "pg_get_functiondef" we see that the PARALLEL keyword used during function creation is missing.
Below are the steps to reproduce:
postgres=# CREATE FUNCTION add(integer, integer) RETURNS integer
postgres-# AS 'select $1 + $2;'
postgres-# LANGUAGE SQL
postgres-# PARALLEL SAFE
postgres-# RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
postgres=# CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
postgres$# BEGIN
postgres$# RETURN i + 1;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql PARALLEL SAFE;
CREATE FUNCTION
postgres=# CREATE FUNCTION square_root(double precision) RETURNS double precision
AS 'dsqrt'
LANGUAGE internal
PARALLEL SAFE;
CREATE FUNCTION
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------+------------------+---------------------+--------
public | add | integer | integer, integer | normal
public | increment | integer | i integer | normal
public | square_root | double precision | double precision | normal
(3 rows)
postgres=# SELECT pg_get_functiondef('add'::regproc);
pg_get_functiondef
---------------------------------------------------------
CREATE OR REPLACE FUNCTION public.add(integer, integer)+
RETURNS integer +
LANGUAGE sql +
STRICT +
AS $function$select $1 + $2;$function$ +
(1 row)
postgres=# SELECT pg_get_functiondef('increment'::regproc);
pg_get_functiondef
--------------------------------------------------------
CREATE OR REPLACE FUNCTION public.increment(i integer)+
RETURNS integer +
LANGUAGE plpgsql +
AS $function$ +
BEGIN +
RETURN i + 1; +
END; +
$function$ +
(1 row)
postgres=# SELECT pg_get_functiondef('square_root'::regproc);
pg_get_functiondef
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.square_root(double precision)+
RETURNS double precision +
LANGUAGE internal +
AS $function$dsqrt$function$ +
(1 row)
RCA: The proparallel information for a function is not considered while preparing its definition inside pg_get_functiondef().
Solution: Add a check for the proparallel flag inside pg_get_functiondef() and based on the value in proparallel flag, store the parallel {safe | unsafe | restricted} info in the buffer that holds the function definition. PFA patch to fix the issue.
With Regards,In PGSQL-9.6, if we create a function with PARALLEL clause and try displaying it's definition using "pg_get_functiondef" we see that the PARALLEL keyword used during function creation is missing.
Below are the steps to reproduce:
postgres=# CREATE FUNCTION add(integer, integer) RETURNS integer
postgres-# AS 'select $1 + $2;'
postgres-# LANGUAGE SQL
postgres-# PARALLEL SAFE
postgres-# RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
postgres=# CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
postgres$# BEGIN
postgres$# RETURN i + 1;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql PARALLEL SAFE;
CREATE FUNCTION
postgres=# CREATE FUNCTION square_root(double precision) RETURNS double precision
AS 'dsqrt'
LANGUAGE internal
PARALLEL SAFE;
CREATE FUNCTION
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------+------------------+---------------------+--------
public | add | integer | integer, integer | normal
public | increment | integer | i integer | normal
public | square_root | double precision | double precision | normal
(3 rows)
postgres=# SELECT pg_get_functiondef('add'::regproc);
pg_get_functiondef
---------------------------------------------------------
CREATE OR REPLACE FUNCTION public.add(integer, integer)+
RETURNS integer +
LANGUAGE sql +
STRICT +
AS $function$select $1 + $2;$function$ +
(1 row)
postgres=# SELECT pg_get_functiondef('increment'::regproc);
pg_get_functiondef
--------------------------------------------------------
CREATE OR REPLACE FUNCTION public.increment(i integer)+
RETURNS integer +
LANGUAGE plpgsql +
AS $function$ +
BEGIN +
RETURN i + 1; +
END; +
$function$ +
(1 row)
postgres=# SELECT pg_get_functiondef('square_root'::regproc);
pg_get_functiondef
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.square_root(double precision)+
RETURNS double precision +
LANGUAGE internal +
AS $function$dsqrt$function$ +
(1 row)
RCA: The proparallel information for a function is not considered while preparing its definition inside pg_get_functiondef().
Solution: Add a check for the proparallel flag inside pg_get_functiondef() and based on the value in proparallel flag, store the parallel {safe | unsafe | restricted} info in the buffer that holds the function definition. PFA patch to fix the issue.
EnterpriseDB: http://www.enterprisedb.com
Attachment
Re: Parallel SAFE information missing in CREATE OR REPLACE FUNCTION definition
From
Robert Haas
Date:
On Tue, Apr 26, 2016 at 1:24 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote: > In PGSQL-9.6, if we create a function with PARALLEL clause and try > displaying it's definition using "pg_get_functiondef" we see that the > PARALLEL keyword used during function creation is missing. > > Below are the steps to reproduce: > > postgres=# CREATE FUNCTION add(integer, integer) RETURNS integer > postgres-# AS 'select $1 + $2;' > postgres-# LANGUAGE SQL > postgres-# PARALLEL SAFE > postgres-# RETURNS NULL ON NULL INPUT; > CREATE FUNCTION > > postgres=# CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer > AS $$ > postgres$# BEGIN > postgres$# RETURN i + 1; > postgres$# END; > postgres$# $$ LANGUAGE plpgsql PARALLEL SAFE; > CREATE FUNCTION > > postgres=# CREATE FUNCTION square_root(double precision) RETURNS double > precision > AS 'dsqrt' > LANGUAGE internal > PARALLEL SAFE; > CREATE FUNCTION > > postgres=# \df > List of functions > Schema | Name | Result data type | Argument data types | Type > --------+-------------+------------------+---------------------+-------- > public | add | integer | integer, integer | normal > public | increment | integer | i integer | normal > public | square_root | double precision | double precision | normal > (3 rows) > > postgres=# SELECT pg_get_functiondef('add'::regproc); > pg_get_functiondef > --------------------------------------------------------- > CREATE OR REPLACE FUNCTION public.add(integer, integer)+ > RETURNS integer + > LANGUAGE sql + > STRICT + > AS $function$select $1 + $2;$function$ + > > (1 row) > > postgres=# SELECT pg_get_functiondef('increment'::regproc); > pg_get_functiondef > -------------------------------------------------------- > CREATE OR REPLACE FUNCTION public.increment(i integer)+ > RETURNS integer + > LANGUAGE plpgsql + > AS $function$ + > BEGIN + > RETURN i + 1; + > END; + > $function$ + > > (1 row) > > postgres=# SELECT pg_get_functiondef('square_root'::regproc); > pg_get_functiondef > ----------------------------------------------------------------- > CREATE OR REPLACE FUNCTION public.square_root(double precision)+ > RETURNS double precision + > LANGUAGE internal + > AS $function$dsqrt$function$ + > > (1 row) > > > RCA: The proparallel information for a function is not considered while > preparing its definition inside pg_get_functiondef(). > > Solution: Add a check for the proparallel flag inside pg_get_functiondef() > and based on the value in proparallel flag, store the parallel {safe | > unsafe | restricted} info in the buffer that holds the function definition. > PFA patch to fix the issue. Thanks, committed. Boy, I feel stupid. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company