diff options
Diffstat (limited to 'contrib')
| -rw-r--r-- | contrib/btree_gist/btree_gist--1.7--1.8.sql | 54 | ||||
| -rw-r--r-- | contrib/btree_gist/btree_gist.c | 4 | ||||
| -rw-r--r-- | contrib/btree_gist/expected/stratnum.out | 18 | ||||
| -rw-r--r-- | contrib/btree_gist/sql/stratnum.sql | 6 | ||||
| -rw-r--r-- | contrib/file_fdw/expected/file_fdw.out | 4 | ||||
| -rw-r--r-- | contrib/file_fdw/sql/file_fdw.sql | 2 | ||||
| -rw-r--r-- | contrib/pg_prewarm/autoprewarm.c | 11 | ||||
| -rw-r--r-- | contrib/postgres_fdw/connection.c | 99 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 134 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 78 |
10 files changed, 359 insertions, 51 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/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/autoprewarm.c b/contrib/pg_prewarm/autoprewarm.c index c52f4d4dc9e..c01b9c7e6a4 100644 --- a/contrib/pg_prewarm/autoprewarm.c +++ b/contrib/pg_prewarm/autoprewarm.c @@ -693,8 +693,15 @@ apw_dump_now(bool is_bgworker, bool dump_unlogged) return 0; } - block_info_array = - (BlockInfoRecord *) palloc(sizeof(BlockInfoRecord) * NBuffers); + /* + * With sufficiently large shared_buffers, allocation will exceed 1GB, so + * allow for a huge allocation to prevent outright failure. + * + * (In the future, it might be a good idea to redesign this to use a more + * memory-efficient data structure.) + */ + block_info_array = (BlockInfoRecord *) + palloc_extended((sizeof(BlockInfoRecord) * NBuffers), MCXT_ALLOC_HUGE); for (num_blocks = 0, i = 0; i < NBuffers; i++) { 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/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'); |