Accidentally parallel unsafe functions
Hi,
I am currently looking into adding the correct parallel options to all
functions in the extensions and I noticed that some built-in functions
seems to have been marked as unsafe by accident. The main culprit is
system_views.sql which redefines these functions and removes the
parallel safe flag.
I think this counts as a 9.6 bug unlike my work on adding the flags to
all extensions which is for 9.7.
I have attached a patch which marks them and all conversion functions as
parallel safe. I also added the flag to ts_debug() when I was already
editing system_views.sql, feel free to ignore that one if you like.
Affected functions:
- json_populate_record()
- json_populate_recordset()
- jsonb_insert()
- jsonb_set()
- make_interval()
- parse_ident()
- Loads of conversion functions
Andreas
Attachments:
parallel-unsafe-functions.patchtext/x-patch; name=parallel-unsafe-functions.patchDownload
commit 9afcc5f1ed22be18d69dc0b70a0f057a023cc5ec
Author: Andreas Karlsson <andreas@proxel.se>
Date: Fri Apr 29 23:29:42 2016 +0200
Mark functions as parallel safe
- Conversion fucntions
- Functions which are redfined in system_views.sql
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index d3cc848..e08bc67 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -890,7 +890,7 @@ FROM pg_catalog.ts_parse(
) AS tt
WHERE tt.tokid = parse.tokid
$$
-LANGUAGE SQL STRICT STABLE;
+LANGUAGE SQL STRICT STABLE PARALLEL SAFE;
COMMENT ON FUNCTION ts_debug(regconfig,text) IS
'debug function for text search configuration';
@@ -906,7 +906,7 @@ RETURNS SETOF record AS
$$
SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
$$
-LANGUAGE SQL STRICT STABLE;
+LANGUAGE SQL STRICT STABLE PARALLEL SAFE;
COMMENT ON FUNCTION ts_debug(text) IS
'debug function for current text search configuration';
@@ -922,17 +922,17 @@ COMMENT ON FUNCTION ts_debug(text) IS
CREATE OR REPLACE FUNCTION
pg_start_backup(label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true)
- RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
+ RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup' PARALLEL SAFE;
-- legacy definition for compatibility with 9.3
CREATE OR REPLACE FUNCTION
json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record';
+ RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE;
-- legacy definition for compatibility with 9.3
CREATE OR REPLACE FUNCTION
json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
- RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset';
+ RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE;
CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
@@ -980,7 +980,7 @@ CREATE OR REPLACE FUNCTION
secs double precision DEFAULT 0.0)
RETURNS interval
LANGUAGE INTERNAL
-STRICT IMMUTABLE
+STRICT IMMUTABLE PARALLEL SAFE
AS 'make_interval';
CREATE OR REPLACE FUNCTION
@@ -988,14 +988,14 @@ CREATE OR REPLACE FUNCTION
create_if_missing boolean DEFAULT true)
RETURNS jsonb
LANGUAGE INTERNAL
-STRICT IMMUTABLE
+STRICT IMMUTABLE PARALLEL SAFE
AS 'jsonb_set';
CREATE OR REPLACE FUNCTION
parse_ident(str text, strict boolean DEFAULT true)
RETURNS text[]
LANGUAGE INTERNAL
-STRICT IMMUTABLE
+STRICT IMMUTABLE PARALLEL SAFE
AS 'parse_ident';
CREATE OR REPLACE FUNCTION
@@ -1003,7 +1003,7 @@ CREATE OR REPLACE FUNCTION
insert_after boolean DEFAULT false)
RETURNS jsonb
LANGUAGE INTERNAL
-STRICT IMMUTABLE
+STRICT IMMUTABLE PARALLEL SAFE
AS 'jsonb_insert';
-- The default permissions for functions mean that anyone can execute them.
diff --git a/src/backend/utils/mb/conversion_procs/Makefile b/src/backend/utils/mb/conversion_procs/Makefile
index 8b97803..879467e 100644
--- a/src/backend/utils/mb/conversion_procs/Makefile
+++ b/src/backend/utils/mb/conversion_procs/Makefile
@@ -173,7 +173,7 @@ $(SQLSCRIPT): Makefile
func=$$1; shift; \
obj=$$1; shift; \
echo "-- $$se --> $$de"; \
- echo "CREATE OR REPLACE FUNCTION $$func (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$$"libdir"/$$obj', '$$func' LANGUAGE C STRICT;"; \
+ echo "CREATE OR REPLACE FUNCTION $$func (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$$"libdir"/$$obj', '$$func' LANGUAGE C STRICT PARALLEL SAFE;"; \
echo "COMMENT ON FUNCTION $$func(INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) IS 'internal conversion function for $$se to $$de';"; \
echo "DROP CONVERSION pg_catalog.$$name;"; \
echo "CREATE DEFAULT CONVERSION pg_catalog.$$name FOR '$$se' TO '$$de' FROM $$func;"; \
Andreas Karlsson wrote:
Hi,
I am currently looking into adding the correct parallel options to all
functions in the extensions and I noticed that some built-in functions seems
to have been marked as unsafe by accident. The main culprit is
system_views.sql which redefines these functions and removes the parallel
safe flag.
Surely CREATE OR REPLACE should keep whatever the flag was, rather than
ovewrite it with a bogus value if not specified? In other words IMO the
CREATE OR REPLACE code needs changing, not system_views.sql.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Andreas Karlsson wrote:
I am currently looking into adding the correct parallel options to all
functions in the extensions and I noticed that some built-in functions seems
to have been marked as unsafe by accident. The main culprit is
system_views.sql which redefines these functions and removes the parallel
safe flag.
Surely CREATE OR REPLACE should keep whatever the flag was, rather than
ovewrite it with a bogus value if not specified? In other words IMO the
CREATE OR REPLACE code needs changing, not system_views.sql.
Absolutely not! The definition of CREATE OR REPLACE is that at the end,
the state of the object is predictable from only what the command says.
This is not open for renegotiation.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Apr 29, 2016 at 4:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Andreas Karlsson wrote:
I am currently looking into adding the correct parallel options to all
functions in the extensions and I noticed that some built-in functionsseems
to have been marked as unsafe by accident. The main culprit is
system_views.sql which redefines these functions and removes theparallel
safe flag.
Surely CREATE OR REPLACE should keep whatever the flag was, rather than
ovewrite it with a bogus value if not specified? In other words IMO the
CREATE OR REPLACE code needs changing, not system_views.sql.Absolutely not! The definition of CREATE OR REPLACE is that at the end,
the state of the object is predictable from only what the command says.
This is not open for renegotiation.
To whit:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
"""
When CREATE OR REPLACE FUNCTION is used to replace an existing function,
the ownership and permissions of the function do not change. All other
function properties are assigned the values specified or implied in the
command. You must own the function to replace it (this includes being a
member of the owning role).
"""
I'd interpret "specified or implied in the command" to mean exactly what
Tom said - and it applies to "all [other] function properties".
The ownership bit is a nice side-effect since you can use superuser to
replace existing functions that are already owned by another user. Those
are the only implied dynamic of function creation that exists within the
CREATE FUNCTION command - everything else is contained within the CREATE
FUNCTION DDL.
David J.
On 04/30/2016 01:19 AM, Tom Lane wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Surely CREATE OR REPLACE should keep whatever the flag was, rather than
ovewrite it with a bogus value if not specified? In other words IMO the
CREATE OR REPLACE code needs changing, not system_views.sql.Absolutely not! The definition of CREATE OR REPLACE is that at the end,
the state of the object is predictable from only what the command says.
This is not open for renegotiation.
An example to support Tom is that it already works like the for other
options.
postgres=# CREATE FUNCTION f() RETURNS int LANGUAGE sql AS $$ SELECT 1
$$ SECURITY DEFINER;
CREATE FUNCTION
postgres=# SELECT pg_get_functiondef('f'::regproc);
pg_get_functiondef
---------------------------------------
CREATE OR REPLACE FUNCTION public.f()+
RETURNS integer +
LANGUAGE sql +
SECURITY DEFINER +
AS $function$ SELECT 1 $function$ +
(1 row)
postgres=# CREATE OR REPLACE FUNCTION f() RETURNS int LANGUAGE sql AS $$
SELECT 1 $$;
CREATE FUNCTION
postgres=# SELECT pg_get_functiondef('f'::regproc);
pg_get_functiondef
---------------------------------------
CREATE OR REPLACE FUNCTION public.f()+
RETURNS integer +
LANGUAGE sql +
AS $function$ SELECT 1 $function$ +
(1 row)
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Apr 29, 2016 at 6:06 PM, Andreas Karlsson <andreas@proxel.se> wrote:
I am currently looking into adding the correct parallel options to all
functions in the extensions and I noticed that some built-in functions seems
to have been marked as unsafe by accident. The main culprit is
system_views.sql which redefines these functions and removes the parallel
safe flag.I think this counts as a 9.6 bug unlike my work on adding the flags to all
extensions which is for 9.7.I have attached a patch which marks them and all conversion functions as
parallel safe. I also added the flag to ts_debug() when I was already
editing system_views.sql, feel free to ignore that one if you like.Affected functions:
- json_populate_record()
- json_populate_recordset()
- jsonb_insert()
- jsonb_set()
- make_interval()
- parse_ident()
- Loads of conversion functions
Hmm. The new pg_start_backup() is not parallel-safe. It's
parallel-restricted, because it relies on backend-private state. I'll
go fix that.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Apr 29, 2016 at 6:06 PM, Andreas Karlsson <andreas@proxel.se> wrote:
I am currently looking into adding the correct parallel options to all
functions in the extensions and I noticed that some built-in functions seems
to have been marked as unsafe by accident. The main culprit is
system_views.sql which redefines these functions and removes the parallel
safe flag.I think this counts as a 9.6 bug unlike my work on adding the flags to all
extensions which is for 9.7.I have attached a patch which marks them and all conversion functions as
parallel safe. I also added the flag to ts_debug() when I was already
editing system_views.sql, feel free to ignore that one if you like.Affected functions:
- json_populate_record()
- json_populate_recordset()
- jsonb_insert()
- jsonb_set()
- make_interval()
- parse_ident()
- Loads of conversion functions
Committed all of this except for the bit about pg_start_backup, for
which I committed a separate fix.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 05/03/2016 08:45 PM, Robert Haas wrote:
Committed all of this except for the bit about pg_start_backup, for
which I committed a separate fix.
Thanks, and really good that you spotted the pg_start_backup() issue.
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers