diff options
Diffstat (limited to 'contrib')
21 files changed, 595 insertions, 138 deletions
diff --git a/contrib/btree_gist/btree_gist--1.7--1.8.sql b/contrib/btree_gist/btree_gist--1.7--1.8.sql index 4ff9c43a8eb..8f79365a461 100644 --- a/contrib/btree_gist/btree_gist--1.7--1.8.sql +++ b/contrib/btree_gist/btree_gist--1.7--1.8.sql @@ -3,85 +3,85 @@ -- complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.8'" to load this file. \quit -CREATE FUNCTION gist_stratnum_btree(int) +CREATE FUNCTION gist_translate_cmptype_btree(int) RETURNS smallint AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE PARALLEL SAFE STRICT; ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_time_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_date_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_macaddr_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_text_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_timetz_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_uuid_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_macaddr8_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_enum_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; ALTER OPERATOR FAMILY gist_bool_ops USING gist ADD - FUNCTION 12 ("any", "any") gist_stratnum_btree (int) ; + FUNCTION 12 ("any", "any") gist_translate_cmptype_btree (int) ; diff --git a/contrib/btree_gist/btree_gist.c b/contrib/btree_gist/btree_gist.c index 280ce808456..39fcbdad334 100644 --- a/contrib/btree_gist/btree_gist.c +++ b/contrib/btree_gist/btree_gist.c @@ -15,7 +15,7 @@ PG_MODULE_MAGIC_EXT( PG_FUNCTION_INFO_V1(gbt_decompress); PG_FUNCTION_INFO_V1(gbtreekey_in); PG_FUNCTION_INFO_V1(gbtreekey_out); -PG_FUNCTION_INFO_V1(gist_stratnum_btree); +PG_FUNCTION_INFO_V1(gist_translate_cmptype_btree); /************************************************** * In/Out for keys @@ -62,7 +62,7 @@ gbt_decompress(PG_FUNCTION_ARGS) * Returns the btree number for supported operators, otherwise invalid. */ Datum -gist_stratnum_btree(PG_FUNCTION_ARGS) +gist_translate_cmptype_btree(PG_FUNCTION_ARGS) { CompareType cmptype = PG_GETARG_INT32(0); diff --git a/contrib/btree_gist/expected/stratnum.out b/contrib/btree_gist/expected/stratnum.out index dd0edaf4a20..8222b661538 100644 --- a/contrib/btree_gist/expected/stratnum.out +++ b/contrib/btree_gist/expected/stratnum.out @@ -1,13 +1,13 @@ --- test stratnum support func -SELECT gist_stratnum_btree(7); - gist_stratnum_btree ---------------------- - 0 +-- test stratnum translation support func +SELECT gist_translate_cmptype_btree(7); + gist_translate_cmptype_btree +------------------------------ + 0 (1 row) -SELECT gist_stratnum_btree(3); - gist_stratnum_btree ---------------------- - 3 +SELECT gist_translate_cmptype_btree(3); + gist_translate_cmptype_btree +------------------------------ + 3 (1 row) diff --git a/contrib/btree_gist/sql/stratnum.sql b/contrib/btree_gist/sql/stratnum.sql index 75adddad849..da8bbf883b0 100644 --- a/contrib/btree_gist/sql/stratnum.sql +++ b/contrib/btree_gist/sql/stratnum.sql @@ -1,3 +1,3 @@ --- test stratnum support func -SELECT gist_stratnum_btree(7); -SELECT gist_stratnum_btree(3); +-- test stratnum translation support func +SELECT gist_translate_cmptype_btree(7); +SELECT gist_translate_cmptype_btree(3); diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c index 98d4e3d7dac..8a0b112a7ff 100644 --- a/contrib/dblink/dblink.c +++ b/contrib/dblink/dblink.c @@ -105,7 +105,7 @@ static PGresult *storeQueryResult(volatile storeInfo *sinfo, PGconn *conn, const static void storeRow(volatile storeInfo *sinfo, PGresult *res, bool first); static remoteConn *getConnectionByName(const char *name); static HTAB *createConnHash(void); -static void createNewConnection(const char *name, remoteConn *rconn); +static remoteConn *createNewConnection(const char *name); static void deleteConnection(const char *name); static char **get_pkey_attnames(Relation rel, int16 *indnkeyatts); static char **get_text_array_contents(ArrayType *array, int *numitems); @@ -119,7 +119,8 @@ static Relation get_rel_from_relname(text *relname_text, LOCKMODE lockmode, AclM static char *generate_relation_name(Relation rel); static void dblink_connstr_check(const char *connstr); static bool dblink_connstr_has_pw(const char *connstr); -static void dblink_security_check(PGconn *conn, remoteConn *rconn, const char *connstr); +static void dblink_security_check(PGconn *conn, const char *connname, + const char *connstr); static void dblink_res_error(PGconn *conn, const char *conname, PGresult *res, bool fail, const char *fmt,...) pg_attribute_printf(5, 6); static char *get_connect_string(const char *servername); @@ -147,16 +148,22 @@ static uint32 dblink_we_get_conn = 0; static uint32 dblink_we_get_result = 0; /* - * Following is list that holds multiple remote connections. + * Following is hash that holds multiple remote connections. * Calling convention of each dblink function changes to accept - * connection name as the first parameter. The connection list is + * connection name as the first parameter. The connection hash is * much like ecpg e.g. a mapping between a name and a PGconn object. + * + * To avoid potentially leaking a PGconn object in case of out-of-memory + * errors, we first create the hash entry, then open the PGconn. + * Hence, a hash entry whose rconn.conn pointer is NULL must be + * understood as a leftover from a failed create; it should be ignored + * by lookup operations, and silently replaced by create operations. */ typedef struct remoteConnHashEnt { char name[NAMEDATALEN]; - remoteConn *rconn; + remoteConn rconn; } remoteConnHashEnt; /* initial number of connection hashes */ @@ -233,7 +240,7 @@ dblink_get_conn(char *conname_or_str, errmsg("could not establish connection"), errdetail_internal("%s", msg))); } - dblink_security_check(conn, rconn, connstr); + dblink_security_check(conn, NULL, connstr); if (PQclientEncoding(conn) != GetDatabaseEncoding()) PQsetClientEncoding(conn, GetDatabaseEncodingName()); freeconn = true; @@ -296,15 +303,6 @@ dblink_connect(PG_FUNCTION_ARGS) else if (PG_NARGS() == 1) conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0)); - if (connname) - { - rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext, - sizeof(remoteConn)); - rconn->conn = NULL; - rconn->openCursorCount = 0; - rconn->newXactForCursor = false; - } - /* first check for valid foreign data server */ connstr = get_connect_string(conname_or_str); if (connstr == NULL) @@ -317,6 +315,13 @@ dblink_connect(PG_FUNCTION_ARGS) if (dblink_we_connect == 0) dblink_we_connect = WaitEventExtensionNew("DblinkConnect"); + /* if we need a hashtable entry, make that first, since it might fail */ + if (connname) + { + rconn = createNewConnection(connname); + Assert(rconn->conn == NULL); + } + /* OK to make connection */ conn = libpqsrv_connect(connstr, dblink_we_connect); @@ -324,8 +329,8 @@ dblink_connect(PG_FUNCTION_ARGS) { msg = pchomp(PQerrorMessage(conn)); libpqsrv_disconnect(conn); - if (rconn) - pfree(rconn); + if (connname) + deleteConnection(connname); ereport(ERROR, (errcode(ERRCODE_SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION), @@ -334,16 +339,16 @@ dblink_connect(PG_FUNCTION_ARGS) } /* check password actually used if not superuser */ - dblink_security_check(conn, rconn, connstr); + dblink_security_check(conn, connname, connstr); /* attempt to set client encoding to match server encoding, if needed */ if (PQclientEncoding(conn) != GetDatabaseEncoding()) PQsetClientEncoding(conn, GetDatabaseEncodingName()); + /* all OK, save away the conn */ if (connname) { rconn->conn = conn; - createNewConnection(connname, rconn); } else { @@ -383,10 +388,7 @@ dblink_disconnect(PG_FUNCTION_ARGS) libpqsrv_disconnect(conn); if (rconn) - { deleteConnection(conname); - pfree(rconn); - } else pconn->conn = NULL; @@ -1304,6 +1306,9 @@ dblink_get_connections(PG_FUNCTION_ARGS) hash_seq_init(&status, remoteConnHash); while ((hentry = (remoteConnHashEnt *) hash_seq_search(&status)) != NULL) { + /* ignore it if it's not an open connection */ + if (hentry->rconn.conn == NULL) + continue; /* stash away current value */ astate = accumArrayResult(astate, CStringGetTextDatum(hentry->name), @@ -2539,8 +2544,8 @@ getConnectionByName(const char *name) hentry = (remoteConnHashEnt *) hash_search(remoteConnHash, key, HASH_FIND, NULL); - if (hentry) - return hentry->rconn; + if (hentry && hentry->rconn.conn != NULL) + return &hentry->rconn; return NULL; } @@ -2557,8 +2562,8 @@ createConnHash(void) HASH_ELEM | HASH_STRINGS); } -static void -createNewConnection(const char *name, remoteConn *rconn) +static remoteConn * +createNewConnection(const char *name) { remoteConnHashEnt *hentry; bool found; @@ -2572,17 +2577,15 @@ createNewConnection(const char *name, remoteConn *rconn) hentry = (remoteConnHashEnt *) hash_search(remoteConnHash, key, HASH_ENTER, &found); - if (found) - { - libpqsrv_disconnect(rconn->conn); - pfree(rconn); - + if (found && hentry->rconn.conn != NULL) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("duplicate connection name"))); - } - hentry->rconn = rconn; + /* New, or reusable, so initialize the rconn struct to zeroes */ + memset(&hentry->rconn, 0, sizeof(remoteConn)); + + return &hentry->rconn; } static void @@ -2671,9 +2674,12 @@ dblink_connstr_has_required_scram_options(const char *connstr) * We need to make sure that the connection made used credentials * which were provided by the user, so check what credentials were * used to connect and then make sure that they came from the user. + * + * On failure, we close "conn" and also delete the hashtable entry + * identified by "connname" (if that's not NULL). */ static void -dblink_security_check(PGconn *conn, remoteConn *rconn, const char *connstr) +dblink_security_check(PGconn *conn, const char *connname, const char *connstr) { /* Superuser bypasses security check */ if (superuser()) @@ -2703,8 +2709,8 @@ dblink_security_check(PGconn *conn, remoteConn *rconn, const char *connstr) /* Otherwise, fail out */ libpqsrv_disconnect(conn); - if (rconn) - pfree(rconn); + if (connname) + deleteConnection(connname); ereport(ERROR, (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED), diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out index df8d43b3749..246e3d3e566 100644 --- a/contrib/file_fdw/expected/file_fdw.out +++ b/contrib/file_fdw/expected/file_fdw.out @@ -48,6 +48,10 @@ SET ROLE regress_file_fdw_superuser; CREATE USER MAPPING FOR regress_file_fdw_superuser SERVER file_server; CREATE USER MAPPING FOR regress_no_priv_user SERVER file_server; -- validator tests +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (foo 'bar'); -- ERROR +ERROR: invalid option "foo" +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS ("a=b" 'true'); -- ERROR +ERROR: invalid option name "a=b": must not contain "=" CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'xml'); -- ERROR ERROR: COPY format "xml" not recognized CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':'); -- ERROR diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql index 2cdbe7a8a4c..1a397ad4bd1 100644 --- a/contrib/file_fdw/sql/file_fdw.sql +++ b/contrib/file_fdw/sql/file_fdw.sql @@ -55,6 +55,8 @@ CREATE USER MAPPING FOR regress_file_fdw_superuser SERVER file_server; CREATE USER MAPPING FOR regress_no_priv_user SERVER file_server; -- validator tests +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (foo 'bar'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS ("a=b" 'true'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'xml'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':'); -- ERROR diff --git a/contrib/pg_prewarm/Makefile b/contrib/pg_prewarm/Makefile index 9cfde8c4e4f..617ac8e09b2 100644 --- a/contrib/pg_prewarm/Makefile +++ b/contrib/pg_prewarm/Makefile @@ -10,6 +10,8 @@ EXTENSION = pg_prewarm DATA = pg_prewarm--1.1--1.2.sql pg_prewarm--1.1.sql pg_prewarm--1.0--1.1.sql PGFILEDESC = "pg_prewarm - preload relation data into system buffer cache" +REGRESS = pg_prewarm + TAP_TESTS = 1 ifdef USE_PGXS diff --git a/contrib/pg_prewarm/expected/pg_prewarm.out b/contrib/pg_prewarm/expected/pg_prewarm.out new file mode 100644 index 00000000000..94e4fa1a9d2 --- /dev/null +++ b/contrib/pg_prewarm/expected/pg_prewarm.out @@ -0,0 +1,10 @@ +-- Test pg_prewarm extension +CREATE EXTENSION pg_prewarm; +-- pg_prewarm() should fail if the target relation has no storage. +CREATE TABLE test (c1 int) PARTITION BY RANGE (c1); +SELECT pg_prewarm('test', 'buffer'); +ERROR: relation "test" does not have storage +DETAIL: This operation is not supported for partitioned tables. +-- Cleanup +DROP TABLE test; +DROP EXTENSION pg_prewarm; diff --git a/contrib/pg_prewarm/meson.build b/contrib/pg_prewarm/meson.build index 82b9851303c..f24c47ef6a5 100644 --- a/contrib/pg_prewarm/meson.build +++ b/contrib/pg_prewarm/meson.build @@ -29,6 +29,11 @@ tests += { 'name': 'pg_prewarm', 'sd': meson.current_source_dir(), 'bd': meson.current_build_dir(), + 'regress': { + 'sql': [ + 'pg_prewarm', + ], + }, 'tap': { 'tests': [ 't/001_basic.pl', diff --git a/contrib/pg_prewarm/pg_prewarm.c b/contrib/pg_prewarm/pg_prewarm.c index 50808569bd7..b968933ea8b 100644 --- a/contrib/pg_prewarm/pg_prewarm.c +++ b/contrib/pg_prewarm/pg_prewarm.c @@ -112,6 +112,14 @@ pg_prewarm(PG_FUNCTION_ARGS) if (aclresult != ACLCHECK_OK) aclcheck_error(aclresult, get_relkind_objtype(rel->rd_rel->relkind), get_rel_name(relOid)); + /* Check that the relation has storage. */ + if (!RELKIND_HAS_STORAGE(rel->rd_rel->relkind)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("relation \"%s\" does not have storage", + RelationGetRelationName(rel)), + errdetail_relkind_not_supported(rel->rd_rel->relkind))); + /* Check that the fork exists. */ if (!smgrexists(RelationGetSmgr(rel), forkNumber)) ereport(ERROR, diff --git a/contrib/pg_prewarm/sql/pg_prewarm.sql b/contrib/pg_prewarm/sql/pg_prewarm.sql new file mode 100644 index 00000000000..c76f2c79164 --- /dev/null +++ b/contrib/pg_prewarm/sql/pg_prewarm.sql @@ -0,0 +1,10 @@ +-- Test pg_prewarm extension +CREATE EXTENSION pg_prewarm; + +-- pg_prewarm() should fail if the target relation has no storage. +CREATE TABLE test (c1 int) PARTITION BY RANGE (c1); +SELECT pg_prewarm('test', 'buffer'); + +-- Cleanup +DROP TABLE test; +DROP EXTENSION pg_prewarm; diff --git a/contrib/pg_stat_statements/expected/extended.out b/contrib/pg_stat_statements/expected/extended.out index 04a05943372..7da308ba84f 100644 --- a/contrib/pg_stat_statements/expected/extended.out +++ b/contrib/pg_stat_statements/expected/extended.out @@ -68,3 +68,61 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (4 rows) +-- Various parameter numbering patterns +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- Unique query IDs with parameter numbers switched. +SELECT WHERE ($1::int, 7) IN ((8, $2::int), ($3::int, 9)) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE ($2::int, 10) IN ((11, $3::int), ($1::int, 12)) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE $1::int IN ($2::int, $3::int) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE $2::int IN ($3::int, $1::int) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE $3::int IN ($1::int, $2::int) \bind '1' '2' '3' \g +-- +(0 rows) + +-- Two groups of two queries with the same query ID. +SELECT WHERE '1'::int IN ($1::int, '2'::int) \bind '1' \g +-- +(1 row) + +SELECT WHERE '4'::int IN ($1::int, '5'::int) \bind '2' \g +-- +(0 rows) + +SELECT WHERE $2::int IN ($1::int, '1'::int) \bind '1' '2' \g +-- +(0 rows) + +SELECT WHERE $2::int IN ($1::int, '2'::int) \bind '3' '4' \g +-- +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT WHERE $1::int IN ($2::int, $3::int) | 1 + SELECT WHERE $2::int IN ($1::int, $3::int) | 2 + SELECT WHERE $2::int IN ($1::int, $3::int) | 2 + SELECT WHERE $2::int IN ($3::int, $1::int) | 1 + SELECT WHERE $3::int IN ($1::int, $2::int) | 1 + SELECT WHERE ($1::int, $4) IN (($5, $2::int), ($3::int, $6)) | 1 + SELECT WHERE ($2::int, $4) IN (($5, $3::int), ($1::int, $6)) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(8 rows) + diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 09476a7b699..038ae110364 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -238,6 +238,35 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; t (1 row) +-- normalization of constants and parameters, with constant locations +-- recorded one or more times. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE '1' IN ('1'::int, '3'::int::text); +-- +(1 row) + +SELECT WHERE (1, 2) IN ((1, 2), (2, 3)); +-- +(1 row) + +SELECT WHERE (3, 4) IN ((5, 6), (8, 7)); +-- +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT WHERE $1 IN ($2::int, $3::int::text) | 1 + SELECT WHERE ($1, $2) IN (($3, $4), ($5, $6)) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 +(4 rows) + -- -- queries with locking clauses -- diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index d8fdf42df79..129001c70c8 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -144,7 +144,7 @@ typedef struct pgssHashKey { Oid userid; /* user OID */ Oid dbid; /* database OID */ - uint64 queryid; /* query identifier */ + int64 queryid; /* query identifier */ bool toplevel; /* query executed at top level */ } pgssHashKey; @@ -346,7 +346,7 @@ static void pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment *queryEnv, DestReceiver *dest, QueryCompletion *qc); -static void pgss_store(const char *query, uint64 queryId, +static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, pgssStoreKind kind, double total_time, uint64 rows, @@ -370,7 +370,7 @@ static char *qtext_fetch(Size query_offset, int query_len, char *buffer, Size buffer_size); static bool need_gc_qtexts(void); static void gc_qtexts(void); -static TimestampTz entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only); +static TimestampTz entry_reset(Oid userid, Oid dbid, int64 queryid, bool minmax_only); static char *generate_normalized_query(JumbleState *jstate, const char *query, int query_loc, int *query_len_p); static void fill_in_constant_lengths(JumbleState *jstate, const char *query, @@ -852,7 +852,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate) { if (pgss_track_utility && IsA(query->utilityStmt, ExecuteStmt)) { - query->queryId = UINT64CONST(0); + query->queryId = INT64CONST(0); return; } } @@ -899,7 +899,7 @@ pgss_planner(Query *parse, */ if (pgss_enabled(nesting_level) && pgss_track_planning && query_string - && parse->queryId != UINT64CONST(0)) + && parse->queryId != INT64CONST(0)) { instr_time start; instr_time duration; @@ -1002,7 +1002,7 @@ pgss_ExecutorStart(QueryDesc *queryDesc, int eflags) * counting of optimizable statements that are directly contained in * utility statements. */ - if (pgss_enabled(nesting_level) && queryDesc->plannedstmt->queryId != UINT64CONST(0)) + if (pgss_enabled(nesting_level) && queryDesc->plannedstmt->queryId != INT64CONST(0)) { /* * Set up to track total elapsed time in ExecutorRun. Make sure the @@ -1068,9 +1068,9 @@ pgss_ExecutorFinish(QueryDesc *queryDesc) static void pgss_ExecutorEnd(QueryDesc *queryDesc) { - uint64 queryId = queryDesc->plannedstmt->queryId; + int64 queryId = queryDesc->plannedstmt->queryId; - if (queryId != UINT64CONST(0) && queryDesc->totaltime && + if (queryId != INT64CONST(0) && queryDesc->totaltime && pgss_enabled(nesting_level)) { /* @@ -1111,7 +1111,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, DestReceiver *dest, QueryCompletion *qc) { Node *parsetree = pstmt->utilityStmt; - uint64 saved_queryId = pstmt->queryId; + int64 saved_queryId = pstmt->queryId; int saved_stmt_location = pstmt->stmt_location; int saved_stmt_len = pstmt->stmt_len; bool enabled = pgss_track_utility && pgss_enabled(nesting_level); @@ -1131,7 +1131,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, * only. */ if (enabled) - pstmt->queryId = UINT64CONST(0); + pstmt->queryId = INT64CONST(0); /* * If it's an EXECUTE statement, we don't track it and don't increment the @@ -1278,7 +1278,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, * for the arrays in the Counters field. */ static void -pgss_store(const char *query, uint64 queryId, +pgss_store(const char *query, int64 queryId, int query_location, int query_len, pgssStoreKind kind, double total_time, uint64 rows, @@ -1304,7 +1304,7 @@ pgss_store(const char *query, uint64 queryId, * Nothing to do if compute_query_id isn't enabled and no other module * computed a query identifier. */ - if (queryId == UINT64CONST(0)) + if (queryId == INT64CONST(0)) return; /* @@ -1514,11 +1514,11 @@ pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS) { Oid userid; Oid dbid; - uint64 queryid; + int64 queryid; userid = PG_GETARG_OID(0); dbid = PG_GETARG_OID(1); - queryid = (uint64) PG_GETARG_INT64(2); + queryid = PG_GETARG_INT64(2); entry_reset(userid, dbid, queryid, false); @@ -1530,12 +1530,12 @@ pg_stat_statements_reset_1_11(PG_FUNCTION_ARGS) { Oid userid; Oid dbid; - uint64 queryid; + int64 queryid; bool minmax_only; userid = PG_GETARG_OID(0); dbid = PG_GETARG_OID(1); - queryid = (uint64) PG_GETARG_INT64(2); + queryid = PG_GETARG_INT64(2); minmax_only = PG_GETARG_BOOL(3); PG_RETURN_TIMESTAMPTZ(entry_reset(userid, dbid, queryid, minmax_only)); @@ -2671,7 +2671,7 @@ if (e) { \ * Reset entries corresponding to parameters passed. */ static TimestampTz -entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only) +entry_reset(Oid userid, Oid dbid, int64 queryid, bool minmax_only) { HASH_SEQ_STATUS hash_seq; pgssEntry *entry; @@ -2691,7 +2691,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only) stats_reset = GetCurrentTimestamp(); - if (userid != 0 && dbid != 0 && queryid != UINT64CONST(0)) + if (userid != 0 && dbid != 0 && queryid != INT64CONST(0)) { /* If all the parameters are available, use the fast path. */ memset(&key, 0, sizeof(pgssHashKey)); @@ -2714,7 +2714,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only) SINGLE_ENTRY_RESET(entry); } - else if (userid != 0 || dbid != 0 || queryid != UINT64CONST(0)) + else if (userid != 0 || dbid != 0 || queryid != INT64CONST(0)) { /* Reset entries corresponding to valid parameters. */ hash_seq_init(&hash_seq, pgss_hash); @@ -2818,9 +2818,7 @@ generate_normalized_query(JumbleState *jstate, const char *query, last_off = 0, /* Offset from start for previous tok */ last_tok_len = 0; /* Length (in bytes) of that tok */ bool in_squashed = false; /* in a run of squashed consts? */ - int skipped_constants = 0; /* Position adjustment of later - * constants after squashed ones */ - + int num_constants_replaced = 0; /* * Get constants' lengths (core system only gives us locations). Note @@ -2878,7 +2876,7 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* ... and then a param symbol replacing the constant itself */ n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d", - i + 1 + jstate->highest_extern_param_id - skipped_constants); + num_constants_replaced++ + 1 + jstate->highest_extern_param_id); /* In case previous constants were merged away, stop doing that */ in_squashed = false; @@ -2902,12 +2900,10 @@ generate_normalized_query(JumbleState *jstate, const char *query, /* ... and then start a run of squashed constants */ n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d /*, ... */", - i + 1 + jstate->highest_extern_param_id - skipped_constants); + num_constants_replaced++ + 1 + jstate->highest_extern_param_id); /* The next location will match the block below, to end the run */ in_squashed = true; - - skipped_constants++; } else { diff --git a/contrib/pg_stat_statements/sql/extended.sql b/contrib/pg_stat_statements/sql/extended.sql index 1af0711020c..a366658a53a 100644 --- a/contrib/pg_stat_statements/sql/extended.sql +++ b/contrib/pg_stat_statements/sql/extended.sql @@ -19,3 +19,19 @@ SELECT $1 \bind 'unnamed_val1' \g \bind_named stmt1 'stmt1_val1' \g SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- Various parameter numbering patterns +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +-- Unique query IDs with parameter numbers switched. +SELECT WHERE ($1::int, 7) IN ((8, $2::int), ($3::int, 9)) \bind '1' '2' '3' \g +SELECT WHERE ($2::int, 10) IN ((11, $3::int), ($1::int, 12)) \bind '1' '2' '3' \g +SELECT WHERE $1::int IN ($2::int, $3::int) \bind '1' '2' '3' \g +SELECT WHERE $2::int IN ($3::int, $1::int) \bind '1' '2' '3' \g +SELECT WHERE $3::int IN ($1::int, $2::int) \bind '1' '2' '3' \g +-- Two groups of two queries with the same query ID. +SELECT WHERE '1'::int IN ($1::int, '2'::int) \bind '1' \g +SELECT WHERE '4'::int IN ($1::int, '5'::int) \bind '2' \g +SELECT WHERE $2::int IN ($1::int, '1'::int) \bind '1' '2' \g +SELECT WHERE $2::int IN ($1::int, '2'::int) \bind '3' '4' \g + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql index c5e0b84ee5b..189d405512f 100644 --- a/contrib/pg_stat_statements/sql/select.sql +++ b/contrib/pg_stat_statements/sql/select.sql @@ -79,6 +79,14 @@ DEALLOCATE pgss_test; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT pg_stat_statements_reset() IS NOT NULL AS t; +-- normalization of constants and parameters, with constant locations +-- recorded one or more times. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT WHERE '1' IN ('1'::int, '3'::int::text); +SELECT WHERE (1, 2) IN ((1, 2), (2, 3)); +SELECT WHERE (3, 4) IN ((5, 6), (8, 7)); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- -- queries with locking clauses -- diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c index 304f3c20f83..caf14462696 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -58,6 +58,7 @@ typedef struct ConnCacheEntry /* Remaining fields are invalid when conn is NULL: */ int xact_depth; /* 0 = no xact open, 1 = main xact open, 2 = * one level of subxact open, etc */ + bool xact_read_only; /* xact r/o state */ bool have_prep_stmt; /* have we prepared any stmts in this xact? */ bool have_error; /* have any subxacts aborted in this xact? */ bool changing_xact_state; /* xact state change in process */ @@ -84,6 +85,12 @@ static unsigned int prep_stmt_number = 0; /* tracks whether any work is needed in callback functions */ static bool xact_got_connection = false; +/* + * tracks the nesting level of the topmost read-only transaction determined + * by GetTopReadOnlyTransactionNestLevel() + */ +static int top_read_only_level = 0; + /* custom wait event values, retrieved from shared memory */ static uint32 pgfdw_we_cleanup_result = 0; static uint32 pgfdw_we_connect = 0; @@ -372,6 +379,7 @@ make_new_connection(ConnCacheEntry *entry, UserMapping *user) /* Reset all transient state fields, to be sure all are clean */ entry->xact_depth = 0; + entry->xact_read_only = false; entry->have_prep_stmt = false; entry->have_error = false; entry->changing_xact_state = false; @@ -843,29 +851,81 @@ do_sql_command_end(PGconn *conn, const char *sql, bool consume_input) * those scans. A disadvantage is that we can't provide sane emulation of * READ COMMITTED behavior --- it would be nice if we had some other way to * control which remote queries share a snapshot. + * + * Note also that we always start the remote transaction with the same + * read/write and deferrable properties as the local transaction, and start + * the remote subtransaction with the same read/write property as the local + * subtransaction. */ static void begin_remote_xact(ConnCacheEntry *entry) { int curlevel = GetCurrentTransactionNestLevel(); - /* Start main transaction if we haven't yet */ + /* + * Set the nesting level of the topmost read-only transaction if the + * current transaction is read-only and we haven't yet. Once it's set, + * it's retained until that transaction is committed/aborted, and then + * reset (see pgfdw_xact_callback and pgfdw_subxact_callback). + */ + if (XactReadOnly) + { + if (top_read_only_level == 0) + top_read_only_level = GetTopReadOnlyTransactionNestLevel(); + Assert(top_read_only_level > 0); + } + else + Assert(top_read_only_level == 0); + + /* + * Start main transaction if we haven't yet; otherwise, change the + * already-started remote transaction/subtransaction to read-only if the + * local transaction/subtransaction have been done so after starting them + * and we haven't yet. + */ if (entry->xact_depth <= 0) { - const char *sql; + StringInfoData sql; + bool ro = (top_read_only_level == 1); elog(DEBUG3, "starting remote transaction on connection %p", entry->conn); + initStringInfo(&sql); + appendStringInfoString(&sql, "START TRANSACTION ISOLATION LEVEL "); if (IsolationIsSerializable()) - sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE"; + appendStringInfoString(&sql, "SERIALIZABLE"); else - sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ"; + appendStringInfoString(&sql, "REPEATABLE READ"); + if (ro) + appendStringInfoString(&sql, " READ ONLY"); + if (XactDeferrable) + appendStringInfoString(&sql, " DEFERRABLE"); entry->changing_xact_state = true; - do_sql_command(entry->conn, sql); + do_sql_command(entry->conn, sql.data); entry->xact_depth = 1; + if (ro) + { + Assert(!entry->xact_read_only); + entry->xact_read_only = true; + } entry->changing_xact_state = false; } + else if (!entry->xact_read_only) + { + Assert(top_read_only_level == 0 || + entry->xact_depth <= top_read_only_level); + if (entry->xact_depth == top_read_only_level) + { + entry->changing_xact_state = true; + do_sql_command(entry->conn, "SET transaction_read_only = on"); + entry->xact_read_only = true; + entry->changing_xact_state = false; + } + } + else + Assert(top_read_only_level > 0 && + entry->xact_depth >= top_read_only_level); /* * If we're in a subtransaction, stack up savepoints to match our level. @@ -874,12 +934,21 @@ begin_remote_xact(ConnCacheEntry *entry) */ while (entry->xact_depth < curlevel) { - char sql[64]; + StringInfoData sql; + bool ro = (entry->xact_depth + 1 == top_read_only_level); - snprintf(sql, sizeof(sql), "SAVEPOINT s%d", entry->xact_depth + 1); + initStringInfo(&sql); + appendStringInfo(&sql, "SAVEPOINT s%d", entry->xact_depth + 1); + if (ro) + appendStringInfoString(&sql, "; SET transaction_read_only = on"); entry->changing_xact_state = true; - do_sql_command(entry->conn, sql); + do_sql_command(entry->conn, sql.data); entry->xact_depth++; + if (ro) + { + Assert(!entry->xact_read_only); + entry->xact_read_only = true; + } entry->changing_xact_state = false; } } @@ -1174,6 +1243,9 @@ pgfdw_xact_callback(XactEvent event, void *arg) /* Also reset cursor numbering for next transaction */ cursor_number = 0; + + /* Likewise for top_read_only_level */ + top_read_only_level = 0; } /* @@ -1272,6 +1344,10 @@ pgfdw_subxact_callback(SubXactEvent event, SubTransactionId mySubid, false); } } + + /* If in the topmost read-only transaction, reset top_read_only_level */ + if (curlevel == top_read_only_level) + top_read_only_level = 0; } /* @@ -1374,6 +1450,9 @@ pgfdw_reset_xact_state(ConnCacheEntry *entry, bool toplevel) /* Reset state to show we're out of a transaction */ entry->xact_depth = 0; + /* Reset xact r/o state */ + entry->xact_read_only = false; + /* * If the connection isn't in a good idle state, it is marked as * invalid or keep_connections option of its server is disabled, then @@ -1394,6 +1473,10 @@ pgfdw_reset_xact_state(ConnCacheEntry *entry, bool toplevel) { /* Reset state to show we're out of a subtransaction */ entry->xact_depth--; + + /* If in the topmost read-only transaction, reset xact r/o state */ + if (entry->xact_depth + 1 == top_read_only_level) + entry->xact_read_only = false; } } diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 2185b42bb4f..eb4716bed81 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -12384,6 +12384,140 @@ SELECT count(*) FROM remote_application_name DROP FOREIGN TABLE remote_application_name; DROP VIEW my_application_name; -- =================================================================== +-- test read-only and/or deferrable transactions +-- =================================================================== +CREATE TABLE loct (f1 int, f2 text); +CREATE FUNCTION locf() RETURNS SETOF loct LANGUAGE SQL AS + 'UPDATE public.loct SET f2 = f2 || f2 RETURNING *'; +CREATE VIEW locv AS SELECT t.* FROM locf() t; +CREATE FOREIGN TABLE remt (f1 int, f2 text) + SERVER loopback OPTIONS (table_name 'locv'); +CREATE FOREIGN TABLE remt2 (f1 int, f2 text) + SERVER loopback2 OPTIONS (table_name 'locv'); +INSERT INTO loct VALUES (1, 'foo'), (2, 'bar'); +START TRANSACTION READ ONLY; +SAVEPOINT s; +SELECT * FROM remt; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SELECT * FROM remt; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK; +START TRANSACTION; +SAVEPOINT s; +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK; +START TRANSACTION; +SAVEPOINT s; +SELECT * FROM remt; -- should work + f1 | f2 +----+-------- + 1 | foofoo + 2 | barbar +(2 rows) + +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SELECT * FROM remt; -- should work + f1 | f2 +----+-------- + 1 | foofoo + 2 | barbar +(2 rows) + +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK; +START TRANSACTION; +SAVEPOINT s; +SELECT * FROM remt; -- should work + f1 | f2 +----+-------- + 1 | foofoo + 2 | barbar +(2 rows) + +SET transaction_read_only = on; +SELECT * FROM remt2; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SELECT * FROM remt; -- should work + f1 | f2 +----+-------- + 1 | foofoo + 2 | barbar +(2 rows) + +SET transaction_read_only = on; +SELECT * FROM remt2; -- should fail +ERROR: cannot execute UPDATE in a read-only transaction +CONTEXT: SQL function "locf" statement 1 +remote SQL command: SELECT f1, f2 FROM public.locv +ROLLBACK; +DROP FOREIGN TABLE remt; +CREATE FOREIGN TABLE remt (f1 int, f2 text) + SERVER loopback OPTIONS (table_name 'loct'); +START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY; +SELECT * FROM remt; + f1 | f2 +----+----- + 1 | foo + 2 | bar +(2 rows) + +COMMIT; +START TRANSACTION ISOLATION LEVEL SERIALIZABLE DEFERRABLE; +SELECT * FROM remt; + f1 | f2 +----+----- + 1 | foo + 2 | bar +(2 rows) + +COMMIT; +START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; +SELECT * FROM remt; + f1 | f2 +----+----- + 1 | foo + 2 | bar +(2 rows) + +COMMIT; +-- Clean up +DROP FOREIGN TABLE remt; +DROP FOREIGN TABLE remt2; +DROP VIEW locv; +DROP FUNCTION locf(); +DROP TABLE loct; +-- =================================================================== -- test parallel commit and parallel abort -- =================================================================== ALTER SERVER loopback OPTIONS (ADD parallel_commit 'true'); diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 331f3fc088d..4283ce9f962 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -240,6 +240,7 @@ typedef struct PgFdwDirectModifyState PGresult *result; /* result for query */ int num_tuples; /* # of result tuples */ int next_tuple; /* index of next one to return */ + MemoryContextCallback result_cb; /* ensures result will get freed */ Relation resultRel; /* relcache entry for the target relation */ AttrNumber *attnoMap; /* array of attnums of input user columns */ AttrNumber ctidAttno; /* attnum of input ctid column */ @@ -2671,6 +2672,17 @@ postgresBeginDirectModify(ForeignScanState *node, int eflags) node->fdw_state = dmstate; /* + * We use a memory context callback to ensure that the dmstate's PGresult + * (if any) will be released, even if the query fails somewhere that's + * outside our control. The callback is always armed for the duration of + * the query; this relies on PQclear(NULL) being a no-op. + */ + dmstate->result_cb.func = (MemoryContextCallbackFunction) PQclear; + dmstate->result_cb.arg = NULL; + MemoryContextRegisterResetCallback(CurrentMemoryContext, + &dmstate->result_cb); + + /* * Identify which user to do the remote access as. This should match what * ExecCheckPermissions() does. */ @@ -2817,7 +2829,13 @@ postgresEndDirectModify(ForeignScanState *node) return; /* Release PGresult */ - PQclear(dmstate->result); + if (dmstate->result) + { + PQclear(dmstate->result); + dmstate->result = NULL; + /* ... and don't forget to disable the callback */ + dmstate->result_cb.arg = NULL; + } /* Release remote connection */ ReleaseConnection(dmstate->conn); @@ -4591,13 +4609,17 @@ execute_dml_stmt(ForeignScanState *node) /* * Get the result, and check for success. * - * We don't use a PG_TRY block here, so be careful not to throw error - * without releasing the PGresult. + * We use a memory context callback to ensure that the PGresult will be + * released, even if the query fails somewhere that's outside our control. + * The callback is already registered, just need to fill in its arg. */ + Assert(dmstate->result == NULL); dmstate->result = pgfdw_get_result(dmstate->conn); + dmstate->result_cb.arg = dmstate->result; + if (PQresultStatus(dmstate->result) != (dmstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK)) - pgfdw_report_error(ERROR, dmstate->result, dmstate->conn, true, + pgfdw_report_error(ERROR, dmstate->result, dmstate->conn, false, dmstate->query); /* Get the number of rows affected. */ @@ -4641,30 +4663,16 @@ get_returning_data(ForeignScanState *node) } else { - /* - * On error, be sure to release the PGresult on the way out. Callers - * do not have PG_TRY blocks to ensure this happens. - */ - PG_TRY(); - { - HeapTuple newtup; - - newtup = make_tuple_from_result_row(dmstate->result, - dmstate->next_tuple, - dmstate->rel, - dmstate->attinmeta, - dmstate->retrieved_attrs, - node, - dmstate->temp_cxt); - ExecStoreHeapTuple(newtup, slot, false); - } - PG_CATCH(); - { - PQclear(dmstate->result); - PG_RE_THROW(); - } - PG_END_TRY(); + HeapTuple newtup; + newtup = make_tuple_from_result_row(dmstate->result, + dmstate->next_tuple, + dmstate->rel, + dmstate->attinmeta, + dmstate->retrieved_attrs, + node, + dmstate->temp_cxt); + ExecStoreHeapTuple(newtup, slot, false); /* Get the updated/deleted tuple. */ if (dmstate->rel) resultSlot = slot; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index e534b40de3c..20a535b99d8 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -4201,6 +4201,84 @@ DROP FOREIGN TABLE remote_application_name; DROP VIEW my_application_name; -- =================================================================== +-- test read-only and/or deferrable transactions +-- =================================================================== +CREATE TABLE loct (f1 int, f2 text); +CREATE FUNCTION locf() RETURNS SETOF loct LANGUAGE SQL AS + 'UPDATE public.loct SET f2 = f2 || f2 RETURNING *'; +CREATE VIEW locv AS SELECT t.* FROM locf() t; +CREATE FOREIGN TABLE remt (f1 int, f2 text) + SERVER loopback OPTIONS (table_name 'locv'); +CREATE FOREIGN TABLE remt2 (f1 int, f2 text) + SERVER loopback2 OPTIONS (table_name 'locv'); +INSERT INTO loct VALUES (1, 'foo'), (2, 'bar'); + +START TRANSACTION READ ONLY; +SAVEPOINT s; +SELECT * FROM remt; -- should fail +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SELECT * FROM remt; -- should fail +ROLLBACK; + +START TRANSACTION; +SAVEPOINT s; +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ROLLBACK; + +START TRANSACTION; +SAVEPOINT s; +SELECT * FROM remt; -- should work +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SELECT * FROM remt; -- should work +SET transaction_read_only = on; +SELECT * FROM remt; -- should fail +ROLLBACK; + +START TRANSACTION; +SAVEPOINT s; +SELECT * FROM remt; -- should work +SET transaction_read_only = on; +SELECT * FROM remt2; -- should fail +ROLLBACK TO s; +RELEASE SAVEPOINT s; +SELECT * FROM remt; -- should work +SET transaction_read_only = on; +SELECT * FROM remt2; -- should fail +ROLLBACK; + +DROP FOREIGN TABLE remt; +CREATE FOREIGN TABLE remt (f1 int, f2 text) + SERVER loopback OPTIONS (table_name 'loct'); + +START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY; +SELECT * FROM remt; +COMMIT; + +START TRANSACTION ISOLATION LEVEL SERIALIZABLE DEFERRABLE; +SELECT * FROM remt; +COMMIT; + +START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; +SELECT * FROM remt; +COMMIT; + +-- Clean up +DROP FOREIGN TABLE remt; +DROP FOREIGN TABLE remt2; +DROP VIEW locv; +DROP FUNCTION locf(); +DROP TABLE loct; + +-- =================================================================== -- test parallel commit and parallel abort -- =================================================================== ALTER SERVER loopback OPTIONS (ADD parallel_commit 'true'); |