From 20eb2731b7775f3381939d2667b6aa8ba62ab2c5 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 17 Jun 2016 15:09:57 -0400 Subject: Update dblink extension for parallel query. Almost all functions provided by this extension are PARALLEL RESTRICTED. Mostly, that's because the leader's TCP connections won't be shared with the workers, but in some cases like dblink_get_pkey it's because they obtain locks which might be released early if taken within a parallel worker. dblink_fdw_validator probably can't be used in a query anyway, but there would be no problem from the point of view of parallel query if it were, so it's PARALLEL SAFE. Andreas Karlsson --- contrib/dblink/Makefile | 3 +- contrib/dblink/dblink--1.1--1.2.sql | 46 +++++++ contrib/dblink/dblink--1.1.sql | 235 ------------------------------------ contrib/dblink/dblink--1.2.sql | 235 ++++++++++++++++++++++++++++++++++++ contrib/dblink/dblink.control | 2 +- 5 files changed, 284 insertions(+), 237 deletions(-) create mode 100644 contrib/dblink/dblink--1.1--1.2.sql delete mode 100644 contrib/dblink/dblink--1.1.sql create mode 100644 contrib/dblink/dblink--1.2.sql diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile index b8d515716d0..5189758dabf 100644 --- a/contrib/dblink/Makefile +++ b/contrib/dblink/Makefile @@ -6,7 +6,8 @@ PG_CPPFLAGS = -I$(libpq_srcdir) SHLIB_LINK = $(libpq) EXTENSION = dblink -DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql +DATA = dblink--1.2.sql dblink--1.1--1.2.sql dblink--1.0--1.1.sql \ + dblink--unpackaged--1.0.sql PGFILEDESC = "dblink - connect to other PostgreSQL databases" REGRESS = paths dblink diff --git a/contrib/dblink/dblink--1.1--1.2.sql b/contrib/dblink/dblink--1.1--1.2.sql new file mode 100644 index 00000000000..ecbc49ef94c --- /dev/null +++ b/contrib/dblink/dblink--1.1--1.2.sql @@ -0,0 +1,46 @@ +/* contrib/dblink/dblink--1.1--1.2.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION dblink UPDATE TO '1.2'" to load this file. \quit + +ALTER FUNCTION dblink_connect(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_connect(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_connect_u(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_connect_u(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_disconnect() PARALLEL RESTRICTED; +ALTER FUNCTION dblink_disconnect(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_open(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_open(text, text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_open(text, text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_open(text, text, text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_fetch(text, int) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_fetch(text, int, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_fetch(text, text, int) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_fetch(text, text, int, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_close(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_close(text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_close(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_close(text, text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink(text, text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink(text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_exec(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_exec(text, text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_exec(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_exec(text, boolean) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_get_pkey(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_build_sql_insert(text, int2vector, int, _text, _text) PARALLEL SAFE; +ALTER FUNCTION dblink_build_sql_delete(text, int2vector, int, _text) PARALLEL SAFE; +ALTER FUNCTION dblink_build_sql_update(text, int2vector, int, _text, _text) PARALLEL SAFE; +ALTER FUNCTION dblink_current_query() PARALLEL RESTRICTED; +ALTER FUNCTION dblink_send_query(text, text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_is_busy(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_get_result(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_get_result(text, bool) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_get_connections() PARALLEL RESTRICTED; +ALTER FUNCTION dblink_cancel_query(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_error_message(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_get_notify() PARALLEL RESTRICTED; +ALTER FUNCTION dblink_get_notify(text) PARALLEL RESTRICTED; +ALTER FUNCTION dblink_fdw_validator(text[], oid) PARALLEL SAFE; diff --git a/contrib/dblink/dblink--1.1.sql b/contrib/dblink/dblink--1.1.sql deleted file mode 100644 index 873355382ce..00000000000 --- a/contrib/dblink/dblink--1.1.sql +++ /dev/null @@ -1,235 +0,0 @@ -/* contrib/dblink/dblink--1.1.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION dblink" to load this file. \quit - --- dblink_connect now restricts non-superusers to password --- authenticated connections -CREATE FUNCTION dblink_connect (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_connect (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT; - --- dblink_connect_u allows non-superusers to use --- non-password authenticated connections, but initially --- privileges are revoked from public -CREATE FUNCTION dblink_connect_u (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT SECURITY DEFINER; - -CREATE FUNCTION dblink_connect_u (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT SECURITY DEFINER; - -REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public; -REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public; - -CREATE FUNCTION dblink_disconnect () -RETURNS text -AS 'MODULE_PATHNAME','dblink_disconnect' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_disconnect (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_disconnect' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, int) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, int, boolean) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, text, int) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, text, int, boolean) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink (text, text) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_record' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink (text, text, boolean) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_record' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink (text) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_record' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink (text, boolean) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_record' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_exec (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_exec' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_exec (text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_exec' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_exec (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_exec' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_exec (text,boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_exec' -LANGUAGE C STRICT; - -CREATE TYPE dblink_pkey_results AS (position int, colname text); - -CREATE FUNCTION dblink_get_pkey (text) -RETURNS setof dblink_pkey_results -AS 'MODULE_PATHNAME','dblink_get_pkey' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_build_sql_insert (text, int2vector, int, _text, _text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_build_sql_insert' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_build_sql_delete (text, int2vector, int, _text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_build_sql_delete' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_build_sql_update (text, int2vector, int, _text, _text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_build_sql_update' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_current_query () -RETURNS text -AS 'MODULE_PATHNAME','dblink_current_query' -LANGUAGE C; - -CREATE FUNCTION dblink_send_query(text, text) -RETURNS int4 -AS 'MODULE_PATHNAME', 'dblink_send_query' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_is_busy(text) -RETURNS int4 -AS 'MODULE_PATHNAME', 'dblink_is_busy' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_get_result(text) -RETURNS SETOF record -AS 'MODULE_PATHNAME', 'dblink_get_result' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_get_result(text, bool) -RETURNS SETOF record -AS 'MODULE_PATHNAME', 'dblink_get_result' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_get_connections() -RETURNS text[] -AS 'MODULE_PATHNAME', 'dblink_get_connections' -LANGUAGE C; - -CREATE FUNCTION dblink_cancel_query(text) -RETURNS text -AS 'MODULE_PATHNAME', 'dblink_cancel_query' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_error_message(text) -RETURNS text -AS 'MODULE_PATHNAME', 'dblink_error_message' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_get_notify( - OUT notify_name TEXT, - OUT be_pid INT4, - OUT extra TEXT -) -RETURNS setof record -AS 'MODULE_PATHNAME', 'dblink_get_notify' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_get_notify( - conname TEXT, - OUT notify_name TEXT, - OUT be_pid INT4, - OUT extra TEXT -) -RETURNS setof record -AS 'MODULE_PATHNAME', 'dblink_get_notify' -LANGUAGE C STRICT; - -/* New stuff in 1.1 begins here */ - -CREATE FUNCTION dblink_fdw_validator( - options text[], - catalog oid -) -RETURNS void -AS 'MODULE_PATHNAME', 'dblink_fdw_validator' -LANGUAGE C STRICT; - -CREATE FOREIGN DATA WRAPPER dblink_fdw VALIDATOR dblink_fdw_validator; diff --git a/contrib/dblink/dblink--1.2.sql b/contrib/dblink/dblink--1.2.sql new file mode 100644 index 00000000000..248943b0d28 --- /dev/null +++ b/contrib/dblink/dblink--1.2.sql @@ -0,0 +1,235 @@ +/* contrib/dblink/dblink--1.2.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION dblink" to load this file. \quit + +-- dblink_connect now restricts non-superusers to password +-- authenticated connections +CREATE FUNCTION dblink_connect (text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_connect' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_connect (text, text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_connect' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +-- dblink_connect_u allows non-superusers to use +-- non-password authenticated connections, but initially +-- privileges are revoked from public +CREATE FUNCTION dblink_connect_u (text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_connect' +LANGUAGE C STRICT PARALLEL RESTRICTED SECURITY DEFINER; + +CREATE FUNCTION dblink_connect_u (text, text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_connect' +LANGUAGE C STRICT PARALLEL RESTRICTED SECURITY DEFINER; + +REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public; +REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public; + +CREATE FUNCTION dblink_disconnect () +RETURNS text +AS 'MODULE_PATHNAME','dblink_disconnect' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_disconnect (text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_disconnect' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_open (text, text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_open' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_open (text, text, boolean) +RETURNS text +AS 'MODULE_PATHNAME','dblink_open' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_open (text, text, text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_open' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_open (text, text, text, boolean) +RETURNS text +AS 'MODULE_PATHNAME','dblink_open' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_fetch (text, int) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_fetch' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_fetch (text, int, boolean) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_fetch' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_fetch (text, text, int) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_fetch' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_fetch (text, text, int, boolean) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_fetch' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_close (text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_close' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_close (text, boolean) +RETURNS text +AS 'MODULE_PATHNAME','dblink_close' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_close (text, text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_close' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_close (text, text, boolean) +RETURNS text +AS 'MODULE_PATHNAME','dblink_close' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink (text, text) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_record' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink (text, text, boolean) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_record' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink (text) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_record' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink (text, boolean) +RETURNS setof record +AS 'MODULE_PATHNAME','dblink_record' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_exec (text, text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_exec' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_exec (text, text, boolean) +RETURNS text +AS 'MODULE_PATHNAME','dblink_exec' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_exec (text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_exec' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_exec (text,boolean) +RETURNS text +AS 'MODULE_PATHNAME','dblink_exec' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE TYPE dblink_pkey_results AS (position int, colname text); + +CREATE FUNCTION dblink_get_pkey (text) +RETURNS setof dblink_pkey_results +AS 'MODULE_PATHNAME','dblink_get_pkey' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_build_sql_insert (text, int2vector, int, _text, _text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_build_sql_insert' +LANGUAGE C STRICT PARALLEL SAFE; + +CREATE FUNCTION dblink_build_sql_delete (text, int2vector, int, _text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_build_sql_delete' +LANGUAGE C STRICT PARALLEL SAFE; + +CREATE FUNCTION dblink_build_sql_update (text, int2vector, int, _text, _text) +RETURNS text +AS 'MODULE_PATHNAME','dblink_build_sql_update' +LANGUAGE C STRICT PARALLEL SAFE; + +CREATE FUNCTION dblink_current_query () +RETURNS text +AS 'MODULE_PATHNAME','dblink_current_query' +LANGUAGE C PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_send_query(text, text) +RETURNS int4 +AS 'MODULE_PATHNAME', 'dblink_send_query' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_is_busy(text) +RETURNS int4 +AS 'MODULE_PATHNAME', 'dblink_is_busy' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_get_result(text) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'dblink_get_result' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_get_result(text, bool) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'dblink_get_result' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_get_connections() +RETURNS text[] +AS 'MODULE_PATHNAME', 'dblink_get_connections' +LANGUAGE C PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_cancel_query(text) +RETURNS text +AS 'MODULE_PATHNAME', 'dblink_cancel_query' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_error_message(text) +RETURNS text +AS 'MODULE_PATHNAME', 'dblink_error_message' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_get_notify( + OUT notify_name TEXT, + OUT be_pid INT4, + OUT extra TEXT +) +RETURNS setof record +AS 'MODULE_PATHNAME', 'dblink_get_notify' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION dblink_get_notify( + conname TEXT, + OUT notify_name TEXT, + OUT be_pid INT4, + OUT extra TEXT +) +RETURNS setof record +AS 'MODULE_PATHNAME', 'dblink_get_notify' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +/* New stuff in 1.1 begins here */ + +CREATE FUNCTION dblink_fdw_validator( + options text[], + catalog oid +) +RETURNS void +AS 'MODULE_PATHNAME', 'dblink_fdw_validator' +LANGUAGE C STRICT PARALLEL SAFE; + +CREATE FOREIGN DATA WRAPPER dblink_fdw VALIDATOR dblink_fdw_validator; diff --git a/contrib/dblink/dblink.control b/contrib/dblink/dblink.control index 39f439affcf..bdd17d28a4b 100644 --- a/contrib/dblink/dblink.control +++ b/contrib/dblink/dblink.control @@ -1,5 +1,5 @@ # dblink extension comment = 'connect to other PostgreSQL databases from within a database' -default_version = '1.1' +default_version = '1.2' module_pathname = '$libdir/dblink' relocatable = true -- cgit v1.2.3