minor gripe about lax reloptions parsing for views
Started by Mark Dilgerover 4 years ago7 messages
Does this bother anyone else:
CREATE INDEX uses an amoptions parser specific for the index type and, at least for btree, rejects relation options from the "toast" namespace:
+-- Bad reloption for index draws an error
+CREATE INDEX idx ON test_tbl USING btree (i) WITH (toast.nonsense=insanity);
+ERROR: unrecognized parameter namespace "toast"No so for CREATE VIEW, which shares logic with CREATE TABLE:
+-- But not for views, where "toast" namespace relopts are ignored
+CREATE VIEW nonsense_1 WITH (toast.nonsense=insanity, toast.foo="bar baz")
+ AS SELECT * FROM test_tbl;
+SELECT relname, reloptions FROM pg_class WHERE relname = 'nonsense_1';
+ relname | reloptions
+------------+------------
+ nonsense_1 |
+(1 row)
+
+-- Well-formed but irrelevant toast options are also silently ignored
+CREATE VIEW vac_opts_1 WITH (toast.autovacuum_enabled=false)
+ AS SELECT * FROM test_tbl;
+SELECT relname, reloptions FROM pg_class WHERE relname = 'vac_opts_1';
+ relname | reloptions
+------------+------------
+ vac_opts_1 |
+(1 row)So far as I can see, this does no harm other than to annoy me. It might confuse new users, though, as changing to a MATERIALIZED VIEW makes the toast options relevant, but the user feedback for the command is no different:
+-- But if we upgrade to a materialized view, they are not ignored, but
+-- they attach to the toast table, not the view, so users might not notice
+-- the difference
+CREATE MATERIALIZED VIEW vac_opts_2 WITH (toast.autovacuum_enabled=false)
+ AS SELECT * FROM test_tbl;
+SELECT relname, reloptions FROM pg_class WHERE relname = 'vac_opts_2';
+ relname | reloptions
+------------+------------
+ vac_opts_2 |
+(1 row)
+
+-- They can find the difference if they know where to look
+SELECT rel.relname, toast.relname, toast.reloptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname IN ('nonsense_1', 'vac_opts_1', 'vac_opts_2');
+ relname | relname | reloptions
+------------+----------------+----------------------------
+ nonsense_1 | |
+ vac_opts_1 | |
+ vac_opts_2 | pg_toast_19615 | {autovacuum_enabled=false}
+(3 rows)The solution is simple enough: stop using HEAP_RELOPT_NAMESPACES when parsing reloptions for views and instead create a VIEW_RELOPT_NAMESPACES array which does not include "toast".
I've already fixed this, mixed into some other work. I'll pull it out as its own patch if there is any interest.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: minor gripe about lax reloptions parsing for views
On 2021-Sep-30, Mark Dilger wrote:
The solution is simple enough: stop using HEAP_RELOPT_NAMESPACES when
parsing reloptions for views and instead create a
VIEW_RELOPT_NAMESPACES array which does not include "toast".
It seems a reasonable (non-backpatchable) change to me.
I've already fixed this, mixed into some other work. I'll pull it out
as its own patch if there is any interest.
Yeah.
I suppose you'll need a new routine that returns the namespace array to
use based on relkind.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)
Re: minor gripe about lax reloptions parsing for views
On Oct 1, 2021, at 6:15 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2021-Sep-30, Mark Dilger wrote:
The solution is simple enough: stop using HEAP_RELOPT_NAMESPACES when
parsing reloptions for views and instead create a
VIEW_RELOPT_NAMESPACES array which does not include "toast".It seems a reasonable (non-backpatchable) change to me.
I agree. It's neither important enough to be back-patched nor completely non-breaking. Somebody could be passing bogus reloptions and relying on the parser to ignore them.
I've already fixed this, mixed into some other work. I'll pull it out
as its own patch if there is any interest.Yeah.
I suppose you'll need a new routine that returns the namespace array to
use based on relkind.
The patch does it this way. The new routine can just return NULL for relkinds that don't accept "toast" as an option namespace. We don't need to create the VIEW_RELOPT_NAMESPACES array mentioned upthread.
The patch changes the docs for index storage option "fillfactor". The existing documentation imply that all index methods support this parameter, but in truth built-in methods brin and gin do not, and we should not imply anything about what non-built-in methods do.
The changes to create_view.sql demonstrate what the patch has fixed.
The other changes to regression tests provide previously missing test coverage of storage options for which the behavior is unchanged. I prefer to have coverage, but if the committer who picks this up disagrees, those changes could just be ignored. I'd also be happy to remove them and repost if the committer prefers.
Attachments:
v1-0001-Reject-storage-options-in-toast-namespace-in-view.patchapplication/octet-stream; name=v1-0001-Reject-storage-options-in-toast-namespace-in-view.patch; x-unix-mode=0644Download
From acb427cf73b5e56bfa7594993ca41dff8088471e Mon Sep 17 00:00:00 2001
From: Mark Dilger <mark.dilger@enterprisedb.com>
Date: Fri, 1 Oct 2021 11:37:38 -0700
Subject: [PATCH v1] Reject storage options in toast namespace in views
CREATE VEW was silently ignoring storage options in the toast
namespace. It seems better to reject them. Do that.
---
doc/src/sgml/ref/create_index.sgml | 8 ++-
src/backend/access/common/reloptions.c | 30 +++++++++-
src/backend/commands/createas.c | 3 +-
src/backend/commands/tablecmds.c | 15 ++---
src/backend/tcop/utility.c | 3 +-
src/include/access/reloptions.h | 3 +-
src/test/regress/expected/brin.out | 30 ++++++++++
src/test/regress/expected/btree_index.out | 28 ++++++++++
src/test/regress/expected/create_view.out | 6 ++
src/test/regress/expected/gin.out | 30 ++++++++++
src/test/regress/expected/gist.out | 30 ++++++++++
src/test/regress/expected/hash_index.out | 30 ++++++++++
src/test/regress/expected/matview.out | 68 +++++++++++++++++++++++
src/test/regress/expected/spgist.out | 30 ++++++++++
src/test/regress/sql/brin.sql | 20 +++++++
src/test/regress/sql/btree_index.sql | 17 ++++++
src/test/regress/sql/create_view.sql | 4 ++
src/test/regress/sql/gin.sql | 20 +++++++
src/test/regress/sql/gist.sql | 20 +++++++
src/test/regress/sql/hash_index.sql | 18 ++++++
src/test/regress/sql/matview.sql | 56 +++++++++++++++++++
src/test/regress/sql/spgist.sql | 18 ++++++
22 files changed, 472 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index cc484d5b39..1a31ea4cb5 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -417,8 +417,12 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
updates or inserts will cause a sudden flood of page splits.
</para>
<para>
- The other index methods use fillfactor in different but roughly
- analogous ways; the default fillfactor varies between methods.
+ Other built-in index methods which use fillfactor use it in different but
+ roughly analogous ways; the default fillfactor varies between methods.
+ </para>
+ <para>
+ The built-in <acronym>BRIN</acronym> and <acronym>GIN</acronym> index
+ methods do not use fillfactor.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index b5602f5323..0f8d12332f 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -543,6 +543,8 @@ static relopt_string stringRelOpts[] =
{{NULL}}
};
+static const char *heapNsps[] = HEAP_RELOPT_NAMESPACES;
+
static relopt_gen **relOpts = NULL;
static bits32 last_assigned_kind = RELOPT_KIND_LAST_DEFAULT;
@@ -1147,7 +1149,7 @@ add_local_string_reloption(local_relopts *relopts, const char *name,
*/
Datum
transformRelOptions(Datum oldOptions, List *defList, const char *namspace,
- char *validnsps[], bool acceptOidsOff, bool isReset)
+ const char *validnsps[], bool acceptOidsOff, bool isReset)
{
Datum result;
ArrayBuildState *astate;
@@ -1316,6 +1318,32 @@ transformRelOptions(Datum oldOptions, List *defList, const char *namspace,
return result;
}
+/*
+ * Get the default list of reloption namespaces accepted for the given relkind,
+ * or NULL if the relkind accepts no namespace qualified reloptions.
+ */
+const char **
+reloptNsps(char relkind)
+{
+ switch (relkind)
+ {
+ case RELKIND_MATVIEW:
+ case RELKIND_PARTITIONED_TABLE:
+ case RELKIND_RELATION:
+ case RELKIND_TOASTVALUE:
+ return heapNsps;
+ case RELKIND_COMPOSITE_TYPE:
+ case RELKIND_FOREIGN_TABLE:
+ case RELKIND_INDEX:
+ case RELKIND_PARTITIONED_INDEX:
+ case RELKIND_SEQUENCE:
+ case RELKIND_VIEW:
+ return NULL;
+ default:
+ Assert(false);
+ }
+ return NULL; /* keep compiler quiet */
+}
/*
* Convert the text-array format of reloptions into a List of DefElem.
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 0982851715..5450948357 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -88,7 +88,6 @@ create_ctas_internal(List *attrList, IntoClause *into)
bool is_matview;
char relkind;
Datum toast_options;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
ObjectAddress intoRelationAddr;
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
@@ -127,7 +126,7 @@ create_ctas_internal(List *attrList, IntoClause *into)
toast_options = transformRelOptions((Datum) 0,
create->options,
"toast",
- validnsps,
+ reloptNsps(RELKIND_RELATION),
true, false);
(void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ff97b618e6..9a8bd3b7cb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -641,7 +641,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
ListCell *listptr;
AttrNumber attnum;
bool partitioned;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
Oid ofTypeId;
ObjectAddress address;
LOCKMODE parentLockmode;
@@ -789,8 +788,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
/*
* Parse and validate reloptions, if any.
*/
- reloptions = transformRelOptions((Datum) 0, stmt->options, NULL, validnsps,
- true, false);
+ reloptions = transformRelOptions((Datum) 0, stmt->options, NULL,
+ reloptNsps(relkind), true, false);
switch (relkind)
{
@@ -13704,7 +13703,6 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
Datum repl_val[Natts_pg_class];
bool repl_null[Natts_pg_class];
bool repl_repl[Natts_pg_class];
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
if (defList == NIL && operation != AT_ReplaceRelOptions)
return; /* nothing to do */
@@ -13735,8 +13733,9 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
/* Generate new proposed reloptions (text array) */
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
- defList, NULL, validnsps, false,
- operation == AT_ResetRelOptions);
+ defList, NULL,
+ reloptNsps(rel->rd_rel->relkind),
+ false, operation == AT_ResetRelOptions);
/* Validate */
switch (rel->rd_rel->relkind)
@@ -13854,7 +13853,9 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
}
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
- defList, "toast", validnsps, false,
+ defList, "toast",
+ reloptNsps(RELKIND_TOASTVALUE),
+ false,
operation == AT_ResetRelOptions);
(void) heap_reloptions(RELKIND_TOASTVALUE, newOptions, true);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index bf085aa93b..49cf637c4b 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1154,7 +1154,6 @@ ProcessUtilitySlow(ParseState *pstate,
{
CreateStmt *cstmt = (CreateStmt *) stmt;
Datum toast_options;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
/* Remember transformed RangeVar for LIKE */
table_rv = cstmt->relation;
@@ -1181,7 +1180,7 @@ ProcessUtilitySlow(ParseState *pstate,
toast_options = transformRelOptions((Datum) 0,
cstmt->options,
"toast",
- validnsps,
+ reloptNsps(RELKIND_TOASTVALUE),
true,
false);
(void) heap_reloptions(RELKIND_TOASTVALUE,
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index 7c5fbeb517..88d3a8f093 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -220,8 +220,9 @@ extern void add_local_string_reloption(local_relopts *opts, const char *name,
fill_string_relopt filler, int offset);
extern Datum transformRelOptions(Datum oldOptions, List *defList,
- const char *namspace, char *validnsps[],
+ const char *namspace, const char *validnsps[],
bool acceptOidsOff, bool isReset);
+extern const char **reloptNsps(char relkind);
extern List *untransformRelOptions(Datum options);
extern bytea *extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
amoptions_function amoptions);
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index e53d6e4885..143b05f10a 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -565,5 +565,35 @@ SELECT * FROM brintest_3 WHERE b < '0';
---+---+---+---
(0 rows)
+-- Test supported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (autosummarize=true,pages_per_range=40);
+-- Test unsupported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fillfactor=30); -- from gist
+ERROR: unrecognized parameter "fillfactor"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (nonsense);
+ERROR: unrecognized parameter "nonsense"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
DROP TABLE brintest_3;
RESET enable_seqscan;
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out
index bc113a70b4..5a51037395 100644
--- a/src/test/regress/expected/btree_index.out
+++ b/src/test/regress/expected/btree_index.out
@@ -329,3 +329,31 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
ERROR: operator class int4_ops has no options
+-- Test unsupported storage parameters
+create index on btree_tall_tbl (id) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on btree_tall_tbl (id) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on btree_tall_tbl (id) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on btree_tall_tbl (id) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on btree_tall_tbl (id) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on btree_tall_tbl (id) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on btree_tall_tbl (id) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on btree_tall_tbl (id) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on btree_tall_tbl (id) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on btree_tall_tbl (id) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on btree_tall_tbl (id) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on btree_tall_tbl (id) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on btree_tall_tbl (id) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f50ef76685..ab7b0d98d8 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -267,6 +267,12 @@ ERROR: invalid value for boolean option "security_barrier": 100
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
ERROR: unrecognized parameter "invalid_option"
+CREATE VIEW mysecview7 WITH (toast.vacuum_index_cleanup=OFF) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+ERROR: unrecognized parameter namespace "toast"
+CREATE VIEW mysecview8 WITH (nonsense.instanity) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+ERROR: unrecognized parameter namespace "nonsense"
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
diff --git a/src/test/regress/expected/gin.out b/src/test/regress/expected/gin.out
index 6402e89c7f..c95ac0a151 100644
--- a/src/test/regress/expected/gin.out
+++ b/src/test/regress/expected/gin.out
@@ -287,4 +287,34 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[];
reset enable_seqscan;
reset enable_bitmapscan;
+-- Test supported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (fastupdate=on, gin_pending_list_limit=1000);
+-- Test unsupported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on t_gin_test_tbl using gin(i, j) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on t_gin_test_tbl using gin(i, j) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on t_gin_test_tbl using gin(i, j) with (fillfactor=30); -- from gist
+ERROR: unrecognized parameter "fillfactor"
+create index on t_gin_test_tbl using gin(i, j) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on t_gin_test_tbl using gin(i, j) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on t_gin_test_tbl using gin(i, j) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on t_gin_test_tbl using gin(i, j) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on t_gin_test_tbl using gin(i, j) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on t_gin_test_tbl using gin(i, j) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on t_gin_test_tbl using gin(i, j) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on t_gin_test_tbl using gin(i, j) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
drop table t_gin_test_tbl;
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index 90edb4061d..3f4ad53819 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -316,4 +316,34 @@ drop index gist_tbl_multi_index;
reset enable_seqscan;
reset enable_bitmapscan;
reset enable_indexonlyscan;
+-- Test supported storage parameters
+create index on gist_tbl using gist (p, c) with (fillfactor=30, buffering=on);
+-- Test unsupported storage parameters
+create index on gist_tbl using gist (p, c) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on gist_tbl using gist (p, c) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on gist_tbl using gist (p, c) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on gist_tbl using gist (p, c) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on gist_tbl using gist (p, c) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on gist_tbl using gist (p, c) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on gist_tbl using gist (p, c) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on gist_tbl using gist (p, c) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on gist_tbl using gist (p, c) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on gist_tbl using gist (p, c) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on gist_tbl using gist (p, c) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on gist_tbl using gist (p, c) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on gist_tbl using gist (p, c) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
drop table gist_tbl;
diff --git a/src/test/regress/expected/hash_index.out b/src/test/regress/expected/hash_index.out
index e23de21b41..b1c2ca0d6f 100644
--- a/src/test/regress/expected/hash_index.out
+++ b/src/test/regress/expected/hash_index.out
@@ -241,3 +241,33 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=101);
ERROR: value 101 out of bounds for option "fillfactor"
DETAIL: Valid values are between "10" and "100".
+-- Test unsupported storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (nonsense);
+ERROR: unrecognized parameter "nonsense"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 313c72a268..6782c606d5 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -676,3 +676,71 @@ NOTICE: relation "matview_ine_tab" already exists, skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+-- Test supported storage parameters
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+-- Test supported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_opts WITH (toast.vacuum_index_cleanup=auto)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+-- Test unsupported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.no_such_option)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "no_such_option"
+-- Test the interaction of supported storage parameters with delayed population
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs
+ WITH NO DATA;
+REFRESH MATERIALIZED VIEW matview_opts;
+SELECT rel.reloptions, toast.reloptions AS toastoptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname = 'matview_opts';
+ reloptions | toastoptions
+------------------------------------+-----------------------------
+ {fillfactor=30,parallel_workers=5} | {vacuum_index_cleanup=auto}
+(1 row)
+
+DROP MATERIALIZED VIEW matview_opts;
+-- Test unsupported storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (pages_per_range=40) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "pages_per_range"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (autosummarize=true) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "autosummarize"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (deduplicate_items=true) -- from btree
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (fastupdate=on) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "fastupdate"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (gin_pending_list_limit=1000) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (buffering=on) -- from gist
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "buffering"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (seq_page_cost=1.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (random_page_cost=4.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "random_page_cost"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (effective_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (maintenance_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+-- Test nonsense storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "nonsense"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "nonsense"
diff --git a/src/test/regress/expected/spgist.out b/src/test/regress/expected/spgist.out
index 9364b88bc2..698aa0a493 100644
--- a/src/test/regress/expected/spgist.out
+++ b/src/test/regress/expected/spgist.out
@@ -65,3 +65,33 @@ DETAIL: Valid values are between "10" and "100".
-- Modify fillfactor in existing index
alter index spgist_point_idx set (fillfactor = 90);
reindex index spgist_point_idx;
+-- Test unsupported storage parameters
+create index on spgist_point_tbl using spgist(p) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on spgist_point_tbl using spgist(p) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on spgist_point_tbl using spgist(p) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on spgist_point_tbl using spgist(p) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on spgist_point_tbl using spgist(p) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on spgist_point_tbl using spgist(p) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on spgist_point_tbl using spgist(p) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on spgist_point_tbl using spgist(p) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on spgist_point_tbl using spgist(p) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on spgist_point_tbl using spgist(p) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on spgist_point_tbl using spgist(p) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on spgist_point_tbl using spgist(p) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on spgist_point_tbl using spgist(p) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on spgist_point_tbl using spgist(p) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index 3bd866d947..0b713eba95 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -507,5 +507,25 @@ SELECT * FROM brintest_3 WHERE b < '0';
SELECT * FROM brintest_3 WHERE b < '0';
+-- Test supported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (autosummarize=true,pages_per_range=40);
+
+-- Test unsupported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (deduplicate_items=on); -- from btree
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fastupdate=on); -- from gin
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (gin_pending_list_limit=1000); -- from gin
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fillfactor=30); -- from gist
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (buffering=on); -- from gist
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (parallel_workers=5); -- from heap
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (seq_page_cost=1.0); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (random_page_cost=4.0); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (effective_io_concurrency=5); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (maintenance_io_concurrency=5); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (nonsense);
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.nonsense);
+
DROP TABLE brintest_3;
RESET enable_seqscan;
diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql
index c60312db2d..700f1f1e32 100644
--- a/src/test/regress/sql/btree_index.sql
+++ b/src/test/regress/sql/btree_index.sql
@@ -172,3 +172,20 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
+
+-- Test unsupported storage parameters
+create index on btree_tall_tbl (id) with (pages_per_range=40); -- from brin
+create index on btree_tall_tbl (id) with (autosummarize=true); -- from brin
+create index on btree_tall_tbl (id) with (fastupdate=on); -- from gin
+create index on btree_tall_tbl (id) with (gin_pending_list_limit=1000); -- from gin
+create index on btree_tall_tbl (id) with (buffering=on); -- from gist
+create index on btree_tall_tbl (id) with (parallel_workers=5); -- from heap
+create index on btree_tall_tbl (id) with (seq_page_cost=1.0); -- from tablespace
+create index on btree_tall_tbl (id) with (random_page_cost=4.0); -- from tablespace
+create index on btree_tall_tbl (id) with (effective_io_concurrency=5); -- from tablespace
+create index on btree_tall_tbl (id) with (maintenance_io_concurrency=5); -- from tablespace
+create index on btree_tall_tbl (id) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on btree_tall_tbl (id) with (nonsense);
+create index on btree_tall_tbl (id) with (toast.nonsense);
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index bdda56e8de..3ac2ec091a 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -218,6 +218,10 @@ CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
AS SELECT * FROM tbl1 WHERE a > 100;
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview7 WITH (toast.vacuum_index_cleanup=OFF) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (nonsense.instanity) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
diff --git a/src/test/regress/sql/gin.sql b/src/test/regress/sql/gin.sql
index 5194afcc1f..18ece42a36 100644
--- a/src/test/regress/sql/gin.sql
+++ b/src/test/regress/sql/gin.sql
@@ -170,4 +170,24 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[];
reset enable_seqscan;
reset enable_bitmapscan;
+-- Test supported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (fastupdate=on, gin_pending_list_limit=1000);
+
+-- Test unsupported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (pages_per_range=40); -- from brin
+create index on t_gin_test_tbl using gin(i, j) with (autosummarize=true); -- from brin
+create index on t_gin_test_tbl using gin(i, j) with (deduplicate_items=on); -- from btree
+create index on t_gin_test_tbl using gin(i, j) with (fillfactor=30); -- from gist
+create index on t_gin_test_tbl using gin(i, j) with (buffering=on); -- from gist
+create index on t_gin_test_tbl using gin(i, j) with (parallel_workers=5); -- from heap
+create index on t_gin_test_tbl using gin(i, j) with (seq_page_cost=1.0); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (random_page_cost=4.0); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (effective_io_concurrency=5); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (maintenance_io_concurrency=5); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (nonsense);
+create index on t_gin_test_tbl using gin(i, j) with (toast.nonsense);
+
drop table t_gin_test_tbl;
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index b9d398ea94..3973221126 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -147,4 +147,24 @@ reset enable_seqscan;
reset enable_bitmapscan;
reset enable_indexonlyscan;
+-- Test supported storage parameters
+create index on gist_tbl using gist (p, c) with (fillfactor=30, buffering=on);
+
+-- Test unsupported storage parameters
+create index on gist_tbl using gist (p, c) with (pages_per_range=40); -- from brin
+create index on gist_tbl using gist (p, c) with (autosummarize=true); -- from brin
+create index on gist_tbl using gist (p, c) with (deduplicate_items=on); -- from btree
+create index on gist_tbl using gist (p, c) with (fastupdate=on); -- from gin
+create index on gist_tbl using gist (p, c) with (gin_pending_list_limit=1000); -- from gin
+create index on gist_tbl using gist (p, c) with (parallel_workers=5); -- from heap
+create index on gist_tbl using gist (p, c) with (seq_page_cost=1.0); -- from tablespace
+create index on gist_tbl using gist (p, c) with (random_page_cost=4.0); -- from tablespace
+create index on gist_tbl using gist (p, c) with (effective_io_concurrency=5); -- from tablespace
+create index on gist_tbl using gist (p, c) with (maintenance_io_concurrency=5); -- from tablespace
+create index on gist_tbl using gist (p, c) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on gist_tbl using gist (p, c) with (nonsense);
+create index on gist_tbl using gist (p, c) with (toast.nonsense);
+
drop table gist_tbl;
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 4d1aa020a9..b7fcb8b58c 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -202,3 +202,21 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=9);
CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=101);
+
+-- Test unsupported storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (autosummarize=true); -- from brin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (pages_per_range=40); -- from brin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (deduplicate_items=on); -- from btree
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (fastupdate=on); -- from gin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (gin_pending_list_limit=1000); -- from gin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (buffering=on); -- from gist
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (parallel_workers=5); -- from heap
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (seq_page_cost=1.0); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (random_page_cost=4.0); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (effective_io_concurrency=5); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (maintenance_io_concurrency=5); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (nonsense);
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.nonsense);
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd45..21f023e684 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -295,3 +295,59 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
+
+-- Test supported storage parameters
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test supported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_opts WITH (toast.vacuum_index_cleanup=auto)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test unsupported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.no_such_option)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+
+-- Test the interaction of supported storage parameters with delayed population
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs
+ WITH NO DATA;
+REFRESH MATERIALIZED VIEW matview_opts;
+SELECT rel.reloptions, toast.reloptions AS toastoptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname = 'matview_opts';
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test unsupported storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (pages_per_range=40) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (autosummarize=true) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (deduplicate_items=true) -- from btree
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (fastupdate=on) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (gin_pending_list_limit=1000) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (buffering=on) -- from gist
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (seq_page_cost=1.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (random_page_cost=4.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (effective_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (maintenance_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+
+-- Test nonsense storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
diff --git a/src/test/regress/sql/spgist.sql b/src/test/regress/sql/spgist.sql
index c72cf42a33..1dbadad014 100644
--- a/src/test/regress/sql/spgist.sql
+++ b/src/test/regress/sql/spgist.sql
@@ -71,3 +71,21 @@ create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfac
-- Modify fillfactor in existing index
alter index spgist_point_idx set (fillfactor = 90);
reindex index spgist_point_idx;
+
+-- Test unsupported storage parameters
+create index on spgist_point_tbl using spgist(p) with (pages_per_range=40); -- from brin
+create index on spgist_point_tbl using spgist(p) with (autosummarize=true); -- from brin
+create index on spgist_point_tbl using spgist(p) with (deduplicate_items=on); -- from btree
+create index on spgist_point_tbl using spgist(p) with (fastupdate=on); -- from gin
+create index on spgist_point_tbl using spgist(p) with (gin_pending_list_limit=1000); -- from gin
+create index on spgist_point_tbl using spgist(p) with (buffering=on); -- from gist
+create index on spgist_point_tbl using spgist(p) with (parallel_workers=5); -- from heap
+create index on spgist_point_tbl using spgist(p) with (seq_page_cost=1.0); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (random_page_cost=4.0); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (effective_io_concurrency=5); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (maintenance_io_concurrency=5); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on spgist_point_tbl using spgist(p) with (nonsense);
+create index on spgist_point_tbl using spgist(p) with (toast.nonsense);
--
2.21.1 (Apple Git-122.3)
Re: minor gripe about lax reloptions parsing for views
On Oct 1, 2021, at 12:34 PM, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
The patch does it this way.
A rebased patch is attached.
Attachments:
v2-0001-Reject-storage-options-in-toast-namespace-in-view.patchapplication/octet-stream; name=v2-0001-Reject-storage-options-in-toast-namespace-in-view.patch; x-unix-mode=0644Download
From acecc051d22548fcc252859b20102ff1e79f0809 Mon Sep 17 00:00:00 2001
From: Mark Dilger <mark.dilger@enterprisedb.com>
Date: Wed, 3 Nov 2021 16:04:49 -0700
Subject: [PATCH v2] Reject storage options in toast namespace in views
CREATE VEW was silently ignoring storage options in the toast
namespace. It seems better to reject them. Do that. While at it,
add test coverage of storage options used in other CREATE statments.
---
doc/src/sgml/ref/create_index.sgml | 8 ++-
src/backend/access/common/reloptions.c | 30 +++++++++-
src/backend/commands/createas.c | 3 +-
src/backend/commands/tablecmds.c | 15 ++---
src/backend/tcop/utility.c | 3 +-
src/include/access/reloptions.h | 3 +-
src/test/regress/expected/brin.out | 30 ++++++++++
src/test/regress/expected/btree_index.out | 28 ++++++++++
src/test/regress/expected/create_view.out | 6 ++
src/test/regress/expected/gin.out | 30 ++++++++++
src/test/regress/expected/gist.out | 30 ++++++++++
src/test/regress/expected/hash_index.out | 30 ++++++++++
src/test/regress/expected/matview.out | 68 +++++++++++++++++++++++
src/test/regress/expected/spgist.out | 30 ++++++++++
src/test/regress/sql/brin.sql | 20 +++++++
src/test/regress/sql/btree_index.sql | 17 ++++++
src/test/regress/sql/create_view.sql | 4 ++
src/test/regress/sql/gin.sql | 20 +++++++
src/test/regress/sql/gist.sql | 20 +++++++
src/test/regress/sql/hash_index.sql | 18 ++++++
src/test/regress/sql/matview.sql | 56 +++++++++++++++++++
src/test/regress/sql/spgist.sql | 18 ++++++
22 files changed, 472 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 89a4d746f6..17f7332115 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -420,8 +420,12 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
updates or inserts will cause a sudden flood of page splits.
</para>
<para>
- The other index methods use fillfactor in different but roughly
- analogous ways; the default fillfactor varies between methods.
+ Other built-in index methods which use fillfactor use it in different but
+ roughly analogous ways; the default fillfactor varies between methods.
+ </para>
+ <para>
+ The built-in <acronym>BRIN</acronym> and <acronym>GIN</acronym> index
+ methods do not use fillfactor.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index b5602f5323..0f8d12332f 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -543,6 +543,8 @@ static relopt_string stringRelOpts[] =
{{NULL}}
};
+static const char *heapNsps[] = HEAP_RELOPT_NAMESPACES;
+
static relopt_gen **relOpts = NULL;
static bits32 last_assigned_kind = RELOPT_KIND_LAST_DEFAULT;
@@ -1147,7 +1149,7 @@ add_local_string_reloption(local_relopts *relopts, const char *name,
*/
Datum
transformRelOptions(Datum oldOptions, List *defList, const char *namspace,
- char *validnsps[], bool acceptOidsOff, bool isReset)
+ const char *validnsps[], bool acceptOidsOff, bool isReset)
{
Datum result;
ArrayBuildState *astate;
@@ -1316,6 +1318,32 @@ transformRelOptions(Datum oldOptions, List *defList, const char *namspace,
return result;
}
+/*
+ * Get the default list of reloption namespaces accepted for the given relkind,
+ * or NULL if the relkind accepts no namespace qualified reloptions.
+ */
+const char **
+reloptNsps(char relkind)
+{
+ switch (relkind)
+ {
+ case RELKIND_MATVIEW:
+ case RELKIND_PARTITIONED_TABLE:
+ case RELKIND_RELATION:
+ case RELKIND_TOASTVALUE:
+ return heapNsps;
+ case RELKIND_COMPOSITE_TYPE:
+ case RELKIND_FOREIGN_TABLE:
+ case RELKIND_INDEX:
+ case RELKIND_PARTITIONED_INDEX:
+ case RELKIND_SEQUENCE:
+ case RELKIND_VIEW:
+ return NULL;
+ default:
+ Assert(false);
+ }
+ return NULL; /* keep compiler quiet */
+}
/*
* Convert the text-array format of reloptions into a List of DefElem.
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 0982851715..5450948357 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -88,7 +88,6 @@ create_ctas_internal(List *attrList, IntoClause *into)
bool is_matview;
char relkind;
Datum toast_options;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
ObjectAddress intoRelationAddr;
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
@@ -127,7 +126,7 @@ create_ctas_internal(List *attrList, IntoClause *into)
toast_options = transformRelOptions((Datum) 0,
create->options,
"toast",
- validnsps,
+ reloptNsps(RELKIND_RELATION),
true, false);
(void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 857cc5ce6e..4bf52d2cdb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -641,7 +641,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
ListCell *listptr;
AttrNumber attnum;
bool partitioned;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
Oid ofTypeId;
ObjectAddress address;
LOCKMODE parentLockmode;
@@ -789,8 +788,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
/*
* Parse and validate reloptions, if any.
*/
- reloptions = transformRelOptions((Datum) 0, stmt->options, NULL, validnsps,
- true, false);
+ reloptions = transformRelOptions((Datum) 0, stmt->options, NULL,
+ reloptNsps(relkind), true, false);
switch (relkind)
{
@@ -13739,7 +13738,6 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
Datum repl_val[Natts_pg_class];
bool repl_null[Natts_pg_class];
bool repl_repl[Natts_pg_class];
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
if (defList == NIL && operation != AT_ReplaceRelOptions)
return; /* nothing to do */
@@ -13770,8 +13768,9 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
/* Generate new proposed reloptions (text array) */
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
- defList, NULL, validnsps, false,
- operation == AT_ResetRelOptions);
+ defList, NULL,
+ reloptNsps(rel->rd_rel->relkind),
+ false, operation == AT_ResetRelOptions);
/* Validate */
switch (rel->rd_rel->relkind)
@@ -13889,7 +13888,9 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
}
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
- defList, "toast", validnsps, false,
+ defList, "toast",
+ reloptNsps(RELKIND_TOASTVALUE),
+ false,
operation == AT_ResetRelOptions);
(void) heap_reloptions(RELKIND_TOASTVALUE, newOptions, true);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index bf085aa93b..49cf637c4b 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1154,7 +1154,6 @@ ProcessUtilitySlow(ParseState *pstate,
{
CreateStmt *cstmt = (CreateStmt *) stmt;
Datum toast_options;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
/* Remember transformed RangeVar for LIKE */
table_rv = cstmt->relation;
@@ -1181,7 +1180,7 @@ ProcessUtilitySlow(ParseState *pstate,
toast_options = transformRelOptions((Datum) 0,
cstmt->options,
"toast",
- validnsps,
+ reloptNsps(RELKIND_TOASTVALUE),
true,
false);
(void) heap_reloptions(RELKIND_TOASTVALUE,
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index 7c5fbeb517..88d3a8f093 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -220,8 +220,9 @@ extern void add_local_string_reloption(local_relopts *opts, const char *name,
fill_string_relopt filler, int offset);
extern Datum transformRelOptions(Datum oldOptions, List *defList,
- const char *namspace, char *validnsps[],
+ const char *namspace, const char *validnsps[],
bool acceptOidsOff, bool isReset);
+extern const char **reloptNsps(char relkind);
extern List *untransformRelOptions(Datum options);
extern bytea *extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
amoptions_function amoptions);
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index e53d6e4885..143b05f10a 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -565,5 +565,35 @@ SELECT * FROM brintest_3 WHERE b < '0';
---+---+---+---
(0 rows)
+-- Test supported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (autosummarize=true,pages_per_range=40);
+-- Test unsupported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fillfactor=30); -- from gist
+ERROR: unrecognized parameter "fillfactor"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (nonsense);
+ERROR: unrecognized parameter "nonsense"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
DROP TABLE brintest_3;
RESET enable_seqscan;
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out
index c43e023716..7720515a6a 100644
--- a/src/test/regress/expected/btree_index.out
+++ b/src/test/regress/expected/btree_index.out
@@ -329,6 +329,34 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
ERROR: operator class int4_ops has no options
+-- Test unsupported storage parameters
+create index on btree_tall_tbl (id) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on btree_tall_tbl (id) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on btree_tall_tbl (id) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on btree_tall_tbl (id) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on btree_tall_tbl (id) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on btree_tall_tbl (id) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on btree_tall_tbl (id) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on btree_tall_tbl (id) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on btree_tall_tbl (id) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on btree_tall_tbl (id) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on btree_tall_tbl (id) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on btree_tall_tbl (id) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on btree_tall_tbl (id) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
-- Test case of ALTER INDEX with abuse of column names for indexes.
-- This grammar is not officially supported, but the parser allows it.
CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id);
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f50ef76685..ab7b0d98d8 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -267,6 +267,12 @@ ERROR: invalid value for boolean option "security_barrier": 100
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
ERROR: unrecognized parameter "invalid_option"
+CREATE VIEW mysecview7 WITH (toast.vacuum_index_cleanup=OFF) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+ERROR: unrecognized parameter namespace "toast"
+CREATE VIEW mysecview8 WITH (nonsense.instanity) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+ERROR: unrecognized parameter namespace "nonsense"
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
diff --git a/src/test/regress/expected/gin.out b/src/test/regress/expected/gin.out
index 6402e89c7f..c95ac0a151 100644
--- a/src/test/regress/expected/gin.out
+++ b/src/test/regress/expected/gin.out
@@ -287,4 +287,34 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[];
reset enable_seqscan;
reset enable_bitmapscan;
+-- Test supported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (fastupdate=on, gin_pending_list_limit=1000);
+-- Test unsupported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on t_gin_test_tbl using gin(i, j) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on t_gin_test_tbl using gin(i, j) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on t_gin_test_tbl using gin(i, j) with (fillfactor=30); -- from gist
+ERROR: unrecognized parameter "fillfactor"
+create index on t_gin_test_tbl using gin(i, j) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on t_gin_test_tbl using gin(i, j) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on t_gin_test_tbl using gin(i, j) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on t_gin_test_tbl using gin(i, j) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on t_gin_test_tbl using gin(i, j) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on t_gin_test_tbl using gin(i, j) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on t_gin_test_tbl using gin(i, j) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on t_gin_test_tbl using gin(i, j) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
drop table t_gin_test_tbl;
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index 90edb4061d..3f4ad53819 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -316,4 +316,34 @@ drop index gist_tbl_multi_index;
reset enable_seqscan;
reset enable_bitmapscan;
reset enable_indexonlyscan;
+-- Test supported storage parameters
+create index on gist_tbl using gist (p, c) with (fillfactor=30, buffering=on);
+-- Test unsupported storage parameters
+create index on gist_tbl using gist (p, c) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on gist_tbl using gist (p, c) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on gist_tbl using gist (p, c) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on gist_tbl using gist (p, c) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on gist_tbl using gist (p, c) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on gist_tbl using gist (p, c) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on gist_tbl using gist (p, c) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on gist_tbl using gist (p, c) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on gist_tbl using gist (p, c) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on gist_tbl using gist (p, c) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on gist_tbl using gist (p, c) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on gist_tbl using gist (p, c) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on gist_tbl using gist (p, c) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
drop table gist_tbl;
diff --git a/src/test/regress/expected/hash_index.out b/src/test/regress/expected/hash_index.out
index e23de21b41..b1c2ca0d6f 100644
--- a/src/test/regress/expected/hash_index.out
+++ b/src/test/regress/expected/hash_index.out
@@ -241,3 +241,33 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=101);
ERROR: value 101 out of bounds for option "fillfactor"
DETAIL: Valid values are between "10" and "100".
+-- Test unsupported storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (nonsense);
+ERROR: unrecognized parameter "nonsense"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 313c72a268..6782c606d5 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -676,3 +676,71 @@ NOTICE: relation "matview_ine_tab" already exists, skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+-- Test supported storage parameters
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+-- Test supported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_opts WITH (toast.vacuum_index_cleanup=auto)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+-- Test unsupported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.no_such_option)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "no_such_option"
+-- Test the interaction of supported storage parameters with delayed population
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs
+ WITH NO DATA;
+REFRESH MATERIALIZED VIEW matview_opts;
+SELECT rel.reloptions, toast.reloptions AS toastoptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname = 'matview_opts';
+ reloptions | toastoptions
+------------------------------------+-----------------------------
+ {fillfactor=30,parallel_workers=5} | {vacuum_index_cleanup=auto}
+(1 row)
+
+DROP MATERIALIZED VIEW matview_opts;
+-- Test unsupported storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (pages_per_range=40) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "pages_per_range"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (autosummarize=true) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "autosummarize"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (deduplicate_items=true) -- from btree
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (fastupdate=on) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "fastupdate"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (gin_pending_list_limit=1000) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (buffering=on) -- from gist
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "buffering"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (seq_page_cost=1.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (random_page_cost=4.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "random_page_cost"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (effective_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (maintenance_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+-- Test nonsense storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "nonsense"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "nonsense"
diff --git a/src/test/regress/expected/spgist.out b/src/test/regress/expected/spgist.out
index 9364b88bc2..698aa0a493 100644
--- a/src/test/regress/expected/spgist.out
+++ b/src/test/regress/expected/spgist.out
@@ -65,3 +65,33 @@ DETAIL: Valid values are between "10" and "100".
-- Modify fillfactor in existing index
alter index spgist_point_idx set (fillfactor = 90);
reindex index spgist_point_idx;
+-- Test unsupported storage parameters
+create index on spgist_point_tbl using spgist(p) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on spgist_point_tbl using spgist(p) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on spgist_point_tbl using spgist(p) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on spgist_point_tbl using spgist(p) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on spgist_point_tbl using spgist(p) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on spgist_point_tbl using spgist(p) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on spgist_point_tbl using spgist(p) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on spgist_point_tbl using spgist(p) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on spgist_point_tbl using spgist(p) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on spgist_point_tbl using spgist(p) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on spgist_point_tbl using spgist(p) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on spgist_point_tbl using spgist(p) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on spgist_point_tbl using spgist(p) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on spgist_point_tbl using spgist(p) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index 3bd866d947..0b713eba95 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -507,5 +507,25 @@ SELECT * FROM brintest_3 WHERE b < '0';
SELECT * FROM brintest_3 WHERE b < '0';
+-- Test supported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (autosummarize=true,pages_per_range=40);
+
+-- Test unsupported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (deduplicate_items=on); -- from btree
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fastupdate=on); -- from gin
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (gin_pending_list_limit=1000); -- from gin
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fillfactor=30); -- from gist
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (buffering=on); -- from gist
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (parallel_workers=5); -- from heap
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (seq_page_cost=1.0); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (random_page_cost=4.0); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (effective_io_concurrency=5); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (maintenance_io_concurrency=5); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (nonsense);
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.nonsense);
+
DROP TABLE brintest_3;
RESET enable_seqscan;
diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql
index c34502249f..0aad5464f6 100644
--- a/src/test/regress/sql/btree_index.sql
+++ b/src/test/regress/sql/btree_index.sql
@@ -173,6 +173,23 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
+-- Test unsupported storage parameters
+create index on btree_tall_tbl (id) with (pages_per_range=40); -- from brin
+create index on btree_tall_tbl (id) with (autosummarize=true); -- from brin
+create index on btree_tall_tbl (id) with (fastupdate=on); -- from gin
+create index on btree_tall_tbl (id) with (gin_pending_list_limit=1000); -- from gin
+create index on btree_tall_tbl (id) with (buffering=on); -- from gist
+create index on btree_tall_tbl (id) with (parallel_workers=5); -- from heap
+create index on btree_tall_tbl (id) with (seq_page_cost=1.0); -- from tablespace
+create index on btree_tall_tbl (id) with (random_page_cost=4.0); -- from tablespace
+create index on btree_tall_tbl (id) with (effective_io_concurrency=5); -- from tablespace
+create index on btree_tall_tbl (id) with (maintenance_io_concurrency=5); -- from tablespace
+create index on btree_tall_tbl (id) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on btree_tall_tbl (id) with (nonsense);
+create index on btree_tall_tbl (id) with (toast.nonsense);
+
-- Test case of ALTER INDEX with abuse of column names for indexes.
-- This grammar is not officially supported, but the parser allows it.
CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id);
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index bdda56e8de..3ac2ec091a 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -218,6 +218,10 @@ CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
AS SELECT * FROM tbl1 WHERE a > 100;
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview7 WITH (toast.vacuum_index_cleanup=OFF) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (nonsense.instanity) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
diff --git a/src/test/regress/sql/gin.sql b/src/test/regress/sql/gin.sql
index 5194afcc1f..18ece42a36 100644
--- a/src/test/regress/sql/gin.sql
+++ b/src/test/regress/sql/gin.sql
@@ -170,4 +170,24 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[];
reset enable_seqscan;
reset enable_bitmapscan;
+-- Test supported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (fastupdate=on, gin_pending_list_limit=1000);
+
+-- Test unsupported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (pages_per_range=40); -- from brin
+create index on t_gin_test_tbl using gin(i, j) with (autosummarize=true); -- from brin
+create index on t_gin_test_tbl using gin(i, j) with (deduplicate_items=on); -- from btree
+create index on t_gin_test_tbl using gin(i, j) with (fillfactor=30); -- from gist
+create index on t_gin_test_tbl using gin(i, j) with (buffering=on); -- from gist
+create index on t_gin_test_tbl using gin(i, j) with (parallel_workers=5); -- from heap
+create index on t_gin_test_tbl using gin(i, j) with (seq_page_cost=1.0); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (random_page_cost=4.0); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (effective_io_concurrency=5); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (maintenance_io_concurrency=5); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (nonsense);
+create index on t_gin_test_tbl using gin(i, j) with (toast.nonsense);
+
drop table t_gin_test_tbl;
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index b9d398ea94..3973221126 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -147,4 +147,24 @@ reset enable_seqscan;
reset enable_bitmapscan;
reset enable_indexonlyscan;
+-- Test supported storage parameters
+create index on gist_tbl using gist (p, c) with (fillfactor=30, buffering=on);
+
+-- Test unsupported storage parameters
+create index on gist_tbl using gist (p, c) with (pages_per_range=40); -- from brin
+create index on gist_tbl using gist (p, c) with (autosummarize=true); -- from brin
+create index on gist_tbl using gist (p, c) with (deduplicate_items=on); -- from btree
+create index on gist_tbl using gist (p, c) with (fastupdate=on); -- from gin
+create index on gist_tbl using gist (p, c) with (gin_pending_list_limit=1000); -- from gin
+create index on gist_tbl using gist (p, c) with (parallel_workers=5); -- from heap
+create index on gist_tbl using gist (p, c) with (seq_page_cost=1.0); -- from tablespace
+create index on gist_tbl using gist (p, c) with (random_page_cost=4.0); -- from tablespace
+create index on gist_tbl using gist (p, c) with (effective_io_concurrency=5); -- from tablespace
+create index on gist_tbl using gist (p, c) with (maintenance_io_concurrency=5); -- from tablespace
+create index on gist_tbl using gist (p, c) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on gist_tbl using gist (p, c) with (nonsense);
+create index on gist_tbl using gist (p, c) with (toast.nonsense);
+
drop table gist_tbl;
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 4d1aa020a9..b7fcb8b58c 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -202,3 +202,21 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=9);
CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=101);
+
+-- Test unsupported storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (autosummarize=true); -- from brin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (pages_per_range=40); -- from brin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (deduplicate_items=on); -- from btree
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (fastupdate=on); -- from gin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (gin_pending_list_limit=1000); -- from gin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (buffering=on); -- from gist
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (parallel_workers=5); -- from heap
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (seq_page_cost=1.0); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (random_page_cost=4.0); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (effective_io_concurrency=5); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (maintenance_io_concurrency=5); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (nonsense);
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.nonsense);
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd45..21f023e684 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -295,3 +295,59 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
+
+-- Test supported storage parameters
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test supported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_opts WITH (toast.vacuum_index_cleanup=auto)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test unsupported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.no_such_option)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+
+-- Test the interaction of supported storage parameters with delayed population
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs
+ WITH NO DATA;
+REFRESH MATERIALIZED VIEW matview_opts;
+SELECT rel.reloptions, toast.reloptions AS toastoptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname = 'matview_opts';
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test unsupported storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (pages_per_range=40) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (autosummarize=true) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (deduplicate_items=true) -- from btree
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (fastupdate=on) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (gin_pending_list_limit=1000) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (buffering=on) -- from gist
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (seq_page_cost=1.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (random_page_cost=4.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (effective_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (maintenance_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+
+-- Test nonsense storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
diff --git a/src/test/regress/sql/spgist.sql b/src/test/regress/sql/spgist.sql
index c72cf42a33..1dbadad014 100644
--- a/src/test/regress/sql/spgist.sql
+++ b/src/test/regress/sql/spgist.sql
@@ -71,3 +71,21 @@ create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfac
-- Modify fillfactor in existing index
alter index spgist_point_idx set (fillfactor = 90);
reindex index spgist_point_idx;
+
+-- Test unsupported storage parameters
+create index on spgist_point_tbl using spgist(p) with (pages_per_range=40); -- from brin
+create index on spgist_point_tbl using spgist(p) with (autosummarize=true); -- from brin
+create index on spgist_point_tbl using spgist(p) with (deduplicate_items=on); -- from btree
+create index on spgist_point_tbl using spgist(p) with (fastupdate=on); -- from gin
+create index on spgist_point_tbl using spgist(p) with (gin_pending_list_limit=1000); -- from gin
+create index on spgist_point_tbl using spgist(p) with (buffering=on); -- from gist
+create index on spgist_point_tbl using spgist(p) with (parallel_workers=5); -- from heap
+create index on spgist_point_tbl using spgist(p) with (seq_page_cost=1.0); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (random_page_cost=4.0); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (effective_io_concurrency=5); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (maintenance_io_concurrency=5); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on spgist_point_tbl using spgist(p) with (nonsense);
+create index on spgist_point_tbl using spgist(p) with (toast.nonsense);
--
2.21.1 (Apple Git-122.3)
Re: minor gripe about lax reloptions parsing for views
Rebased patch attached:
Attachments:
v3-0001-Reject-storage-options-in-toast-namespace-in-view.patchapplication/octet-stream; name=v3-0001-Reject-storage-options-in-toast-namespace-in-view.patch; x-unix-mode=0644Download
From 92da8666b6fa248a40b5835de9a7f3b320e46d7a Mon Sep 17 00:00:00 2001
From: Mark Dilger <mark.dilger@enterprisedb.com>
Date: Tue, 21 Dec 2021 11:07:26 -0800
Subject: [PATCH v3] Reject storage options in toast namespace in views
CREATE VEW was silently ignoring storage options in the toast
namespace. It seems better to reject them. Do that. While at it,
add test coverage of storage options used in other CREATE statments.
---
doc/src/sgml/ref/create_index.sgml | 8 ++-
src/backend/access/common/reloptions.c | 30 +++++++++-
src/backend/commands/createas.c | 3 +-
src/backend/commands/tablecmds.c | 15 ++---
src/backend/tcop/utility.c | 3 +-
src/include/access/reloptions.h | 3 +-
src/test/regress/expected/brin.out | 30 ++++++++++
src/test/regress/expected/btree_index.out | 28 ++++++++++
src/test/regress/expected/create_view.out | 6 ++
src/test/regress/expected/gin.out | 30 ++++++++++
src/test/regress/expected/gist.out | 30 ++++++++++
src/test/regress/expected/hash_index.out | 30 ++++++++++
src/test/regress/expected/matview.out | 68 +++++++++++++++++++++++
src/test/regress/expected/spgist.out | 30 ++++++++++
src/test/regress/sql/brin.sql | 20 +++++++
src/test/regress/sql/btree_index.sql | 17 ++++++
src/test/regress/sql/create_view.sql | 4 ++
src/test/regress/sql/gin.sql | 20 +++++++
src/test/regress/sql/gist.sql | 20 +++++++
src/test/regress/sql/hash_index.sql | 18 ++++++
src/test/regress/sql/matview.sql | 56 +++++++++++++++++++
src/test/regress/sql/spgist.sql | 18 ++++++
22 files changed, 472 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 89a4d746f6..17f7332115 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -420,8 +420,12 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
updates or inserts will cause a sudden flood of page splits.
</para>
<para>
- The other index methods use fillfactor in different but roughly
- analogous ways; the default fillfactor varies between methods.
+ Other built-in index methods which use fillfactor use it in different but
+ roughly analogous ways; the default fillfactor varies between methods.
+ </para>
+ <para>
+ The built-in <acronym>BRIN</acronym> and <acronym>GIN</acronym> index
+ methods do not use fillfactor.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index b5602f5323..0f8d12332f 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -543,6 +543,8 @@ static relopt_string stringRelOpts[] =
{{NULL}}
};
+static const char *heapNsps[] = HEAP_RELOPT_NAMESPACES;
+
static relopt_gen **relOpts = NULL;
static bits32 last_assigned_kind = RELOPT_KIND_LAST_DEFAULT;
@@ -1147,7 +1149,7 @@ add_local_string_reloption(local_relopts *relopts, const char *name,
*/
Datum
transformRelOptions(Datum oldOptions, List *defList, const char *namspace,
- char *validnsps[], bool acceptOidsOff, bool isReset)
+ const char *validnsps[], bool acceptOidsOff, bool isReset)
{
Datum result;
ArrayBuildState *astate;
@@ -1316,6 +1318,32 @@ transformRelOptions(Datum oldOptions, List *defList, const char *namspace,
return result;
}
+/*
+ * Get the default list of reloption namespaces accepted for the given relkind,
+ * or NULL if the relkind accepts no namespace qualified reloptions.
+ */
+const char **
+reloptNsps(char relkind)
+{
+ switch (relkind)
+ {
+ case RELKIND_MATVIEW:
+ case RELKIND_PARTITIONED_TABLE:
+ case RELKIND_RELATION:
+ case RELKIND_TOASTVALUE:
+ return heapNsps;
+ case RELKIND_COMPOSITE_TYPE:
+ case RELKIND_FOREIGN_TABLE:
+ case RELKIND_INDEX:
+ case RELKIND_PARTITIONED_INDEX:
+ case RELKIND_SEQUENCE:
+ case RELKIND_VIEW:
+ return NULL;
+ default:
+ Assert(false);
+ }
+ return NULL; /* keep compiler quiet */
+}
/*
* Convert the text-array format of reloptions into a List of DefElem.
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 0982851715..5450948357 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -88,7 +88,6 @@ create_ctas_internal(List *attrList, IntoClause *into)
bool is_matview;
char relkind;
Datum toast_options;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
ObjectAddress intoRelationAddr;
/* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
@@ -127,7 +126,7 @@ create_ctas_internal(List *attrList, IntoClause *into)
toast_options = transformRelOptions((Datum) 0,
create->options,
"toast",
- validnsps,
+ reloptNsps(RELKIND_RELATION),
true, false);
(void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index bf42587e38..75fe2e4a8c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -646,7 +646,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
ListCell *listptr;
AttrNumber attnum;
bool partitioned;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
Oid ofTypeId;
ObjectAddress address;
LOCKMODE parentLockmode;
@@ -794,8 +793,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
/*
* Parse and validate reloptions, if any.
*/
- reloptions = transformRelOptions((Datum) 0, stmt->options, NULL, validnsps,
- true, false);
+ reloptions = transformRelOptions((Datum) 0, stmt->options, NULL,
+ reloptNsps(relkind), true, false);
switch (relkind)
{
@@ -13836,7 +13835,6 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
Datum repl_val[Natts_pg_class];
bool repl_null[Natts_pg_class];
bool repl_repl[Natts_pg_class];
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
if (defList == NIL && operation != AT_ReplaceRelOptions)
return; /* nothing to do */
@@ -13867,8 +13865,9 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
/* Generate new proposed reloptions (text array) */
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
- defList, NULL, validnsps, false,
- operation == AT_ResetRelOptions);
+ defList, NULL,
+ reloptNsps(rel->rd_rel->relkind),
+ false, operation == AT_ResetRelOptions);
/* Validate */
switch (rel->rd_rel->relkind)
@@ -13986,7 +13985,9 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
}
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
- defList, "toast", validnsps, false,
+ defList, "toast",
+ reloptNsps(RELKIND_TOASTVALUE),
+ false,
operation == AT_ResetRelOptions);
(void) heap_reloptions(RELKIND_TOASTVALUE, newOptions, true);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 1fbc387d47..3a58b29959 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1155,7 +1155,6 @@ ProcessUtilitySlow(ParseState *pstate,
{
CreateStmt *cstmt = (CreateStmt *) stmt;
Datum toast_options;
- static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
/* Remember transformed RangeVar for LIKE */
table_rv = cstmt->relation;
@@ -1182,7 +1181,7 @@ ProcessUtilitySlow(ParseState *pstate,
toast_options = transformRelOptions((Datum) 0,
cstmt->options,
"toast",
- validnsps,
+ reloptNsps(RELKIND_TOASTVALUE),
true,
false);
(void) heap_reloptions(RELKIND_TOASTVALUE,
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index 7c5fbeb517..88d3a8f093 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -220,8 +220,9 @@ extern void add_local_string_reloption(local_relopts *opts, const char *name,
fill_string_relopt filler, int offset);
extern Datum transformRelOptions(Datum oldOptions, List *defList,
- const char *namspace, char *validnsps[],
+ const char *namspace, const char *validnsps[],
bool acceptOidsOff, bool isReset);
+extern const char **reloptNsps(char relkind);
extern List *untransformRelOptions(Datum options);
extern bytea *extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
amoptions_function amoptions);
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 2d03d8e134..f46590d1f0 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -565,6 +565,36 @@ SELECT * FROM brintest_3 WHERE b < '0';
---+---+---+---
(0 rows)
+-- Test supported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (autosummarize=true,pages_per_range=40);
+-- Test unsupported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fillfactor=30); -- from gist
+ERROR: unrecognized parameter "fillfactor"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (nonsense);
+ERROR: unrecognized parameter "nonsense"
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
DROP TABLE brintest_3;
RESET enable_seqscan;
-- Test handling of index predicates - updating attributes in predicates
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out
index c43e023716..7720515a6a 100644
--- a/src/test/regress/expected/btree_index.out
+++ b/src/test/regress/expected/btree_index.out
@@ -329,6 +329,34 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
ERROR: operator class int4_ops has no options
+-- Test unsupported storage parameters
+create index on btree_tall_tbl (id) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on btree_tall_tbl (id) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on btree_tall_tbl (id) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on btree_tall_tbl (id) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on btree_tall_tbl (id) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on btree_tall_tbl (id) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on btree_tall_tbl (id) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on btree_tall_tbl (id) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on btree_tall_tbl (id) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on btree_tall_tbl (id) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on btree_tall_tbl (id) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on btree_tall_tbl (id) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on btree_tall_tbl (id) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
-- Test case of ALTER INDEX with abuse of column names for indexes.
-- This grammar is not officially supported, but the parser allows it.
CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id);
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f50ef76685..ab7b0d98d8 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -267,6 +267,12 @@ ERROR: invalid value for boolean option "security_barrier": 100
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
ERROR: unrecognized parameter "invalid_option"
+CREATE VIEW mysecview7 WITH (toast.vacuum_index_cleanup=OFF) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+ERROR: unrecognized parameter namespace "toast"
+CREATE VIEW mysecview8 WITH (nonsense.instanity) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+ERROR: unrecognized parameter namespace "nonsense"
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
diff --git a/src/test/regress/expected/gin.out b/src/test/regress/expected/gin.out
index 6402e89c7f..c95ac0a151 100644
--- a/src/test/regress/expected/gin.out
+++ b/src/test/regress/expected/gin.out
@@ -287,4 +287,34 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[];
reset enable_seqscan;
reset enable_bitmapscan;
+-- Test supported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (fastupdate=on, gin_pending_list_limit=1000);
+-- Test unsupported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on t_gin_test_tbl using gin(i, j) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on t_gin_test_tbl using gin(i, j) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on t_gin_test_tbl using gin(i, j) with (fillfactor=30); -- from gist
+ERROR: unrecognized parameter "fillfactor"
+create index on t_gin_test_tbl using gin(i, j) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on t_gin_test_tbl using gin(i, j) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on t_gin_test_tbl using gin(i, j) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on t_gin_test_tbl using gin(i, j) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on t_gin_test_tbl using gin(i, j) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on t_gin_test_tbl using gin(i, j) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on t_gin_test_tbl using gin(i, j) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on t_gin_test_tbl using gin(i, j) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
drop table t_gin_test_tbl;
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index 90edb4061d..3f4ad53819 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -316,4 +316,34 @@ drop index gist_tbl_multi_index;
reset enable_seqscan;
reset enable_bitmapscan;
reset enable_indexonlyscan;
+-- Test supported storage parameters
+create index on gist_tbl using gist (p, c) with (fillfactor=30, buffering=on);
+-- Test unsupported storage parameters
+create index on gist_tbl using gist (p, c) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on gist_tbl using gist (p, c) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on gist_tbl using gist (p, c) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on gist_tbl using gist (p, c) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on gist_tbl using gist (p, c) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on gist_tbl using gist (p, c) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on gist_tbl using gist (p, c) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on gist_tbl using gist (p, c) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on gist_tbl using gist (p, c) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on gist_tbl using gist (p, c) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on gist_tbl using gist (p, c) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on gist_tbl using gist (p, c) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on gist_tbl using gist (p, c) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
drop table gist_tbl;
diff --git a/src/test/regress/expected/hash_index.out b/src/test/regress/expected/hash_index.out
index e23de21b41..b1c2ca0d6f 100644
--- a/src/test/regress/expected/hash_index.out
+++ b/src/test/regress/expected/hash_index.out
@@ -241,3 +241,33 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=101);
ERROR: value 101 out of bounds for option "fillfactor"
DETAIL: Valid values are between "10" and "100".
+-- Test unsupported storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (nonsense);
+ERROR: unrecognized parameter "nonsense"
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 313c72a268..6782c606d5 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -676,3 +676,71 @@ NOTICE: relation "matview_ine_tab" already exists, skipping
(0 rows)
DROP MATERIALIZED VIEW matview_ine_tab;
+-- Test supported storage parameters
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+-- Test supported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_opts WITH (toast.vacuum_index_cleanup=auto)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+-- Test unsupported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.no_such_option)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "no_such_option"
+-- Test the interaction of supported storage parameters with delayed population
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs
+ WITH NO DATA;
+REFRESH MATERIALIZED VIEW matview_opts;
+SELECT rel.reloptions, toast.reloptions AS toastoptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname = 'matview_opts';
+ reloptions | toastoptions
+------------------------------------+-----------------------------
+ {fillfactor=30,parallel_workers=5} | {vacuum_index_cleanup=auto}
+(1 row)
+
+DROP MATERIALIZED VIEW matview_opts;
+-- Test unsupported storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (pages_per_range=40) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "pages_per_range"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (autosummarize=true) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "autosummarize"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (deduplicate_items=true) -- from btree
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "deduplicate_items"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (fastupdate=on) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "fastupdate"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (gin_pending_list_limit=1000) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "gin_pending_list_limit"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (buffering=on) -- from gist
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "buffering"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (seq_page_cost=1.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "seq_page_cost"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (random_page_cost=4.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "random_page_cost"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (effective_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "effective_io_concurrency"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (maintenance_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+-- Test nonsense storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "nonsense"
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+ERROR: unrecognized parameter "nonsense"
diff --git a/src/test/regress/expected/spgist.out b/src/test/regress/expected/spgist.out
index 1688e0e0a3..e40ec7c5ce 100644
--- a/src/test/regress/expected/spgist.out
+++ b/src/test/regress/expected/spgist.out
@@ -86,3 +86,33 @@ select * from spgist_domain_tbl where f1 = 'fo';
fo
(1 row)
+-- Test unsupported storage parameters
+create index on spgist_point_tbl using spgist(p) with (pages_per_range=40); -- from brin
+ERROR: unrecognized parameter "pages_per_range"
+create index on spgist_point_tbl using spgist(p) with (autosummarize=true); -- from brin
+ERROR: unrecognized parameter "autosummarize"
+create index on spgist_point_tbl using spgist(p) with (deduplicate_items=on); -- from btree
+ERROR: unrecognized parameter "deduplicate_items"
+create index on spgist_point_tbl using spgist(p) with (fastupdate=on); -- from gin
+ERROR: unrecognized parameter "fastupdate"
+create index on spgist_point_tbl using spgist(p) with (gin_pending_list_limit=1000); -- from gin
+ERROR: unrecognized parameter "gin_pending_list_limit"
+create index on spgist_point_tbl using spgist(p) with (buffering=on); -- from gist
+ERROR: unrecognized parameter "buffering"
+create index on spgist_point_tbl using spgist(p) with (parallel_workers=5); -- from heap
+ERROR: unrecognized parameter "parallel_workers"
+create index on spgist_point_tbl using spgist(p) with (seq_page_cost=1.0); -- from tablespace
+ERROR: unrecognized parameter "seq_page_cost"
+create index on spgist_point_tbl using spgist(p) with (random_page_cost=4.0); -- from tablespace
+ERROR: unrecognized parameter "random_page_cost"
+create index on spgist_point_tbl using spgist(p) with (effective_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "effective_io_concurrency"
+create index on spgist_point_tbl using spgist(p) with (maintenance_io_concurrency=5); -- from tablespace
+ERROR: unrecognized parameter "maintenance_io_concurrency"
+create index on spgist_point_tbl using spgist(p) with (toast.vacuum_index_cleanup=auto); -- from toast
+ERROR: unrecognized parameter namespace "toast"
+-- Test nonsense storage parameters
+create index on spgist_point_tbl using spgist(p) with (nonsense);
+ERROR: unrecognized parameter "nonsense"
+create index on spgist_point_tbl using spgist(p) with (toast.nonsense);
+ERROR: unrecognized parameter namespace "toast"
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index e12f3a0df9..a6acd61e32 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -507,6 +507,26 @@ SELECT * FROM brintest_3 WHERE b < '0';
SELECT * FROM brintest_3 WHERE b < '0';
+-- Test supported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (autosummarize=true,pages_per_range=40);
+
+-- Test unsupported storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (deduplicate_items=on); -- from btree
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fastupdate=on); -- from gin
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (gin_pending_list_limit=1000); -- from gin
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (fillfactor=30); -- from gist
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (buffering=on); -- from gist
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (parallel_workers=5); -- from heap
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (seq_page_cost=1.0); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (random_page_cost=4.0); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (effective_io_concurrency=5); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (maintenance_io_concurrency=5); -- from tablespace
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (nonsense);
+CREATE INDEX ON brintest_3 USING brin (b, c) WITH (toast.nonsense);
+
DROP TABLE brintest_3;
RESET enable_seqscan;
diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql
index c34502249f..0aad5464f6 100644
--- a/src/test/regress/sql/btree_index.sql
+++ b/src/test/regress/sql/btree_index.sql
@@ -173,6 +173,23 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
+-- Test unsupported storage parameters
+create index on btree_tall_tbl (id) with (pages_per_range=40); -- from brin
+create index on btree_tall_tbl (id) with (autosummarize=true); -- from brin
+create index on btree_tall_tbl (id) with (fastupdate=on); -- from gin
+create index on btree_tall_tbl (id) with (gin_pending_list_limit=1000); -- from gin
+create index on btree_tall_tbl (id) with (buffering=on); -- from gist
+create index on btree_tall_tbl (id) with (parallel_workers=5); -- from heap
+create index on btree_tall_tbl (id) with (seq_page_cost=1.0); -- from tablespace
+create index on btree_tall_tbl (id) with (random_page_cost=4.0); -- from tablespace
+create index on btree_tall_tbl (id) with (effective_io_concurrency=5); -- from tablespace
+create index on btree_tall_tbl (id) with (maintenance_io_concurrency=5); -- from tablespace
+create index on btree_tall_tbl (id) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on btree_tall_tbl (id) with (nonsense);
+create index on btree_tall_tbl (id) with (toast.nonsense);
+
-- Test case of ALTER INDEX with abuse of column names for indexes.
-- This grammar is not officially supported, but the parser allows it.
CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id);
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index bdda56e8de..3ac2ec091a 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -218,6 +218,10 @@ CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
AS SELECT * FROM tbl1 WHERE a > 100;
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview7 WITH (toast.vacuum_index_cleanup=OFF) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
+CREATE VIEW mysecview8 WITH (nonsense.instanity) -- Error
+ AS SELECT * FROM tbl1 WHERE a < 100;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
diff --git a/src/test/regress/sql/gin.sql b/src/test/regress/sql/gin.sql
index 5194afcc1f..18ece42a36 100644
--- a/src/test/regress/sql/gin.sql
+++ b/src/test/regress/sql/gin.sql
@@ -170,4 +170,24 @@ select count(*) from t_gin_test_tbl where j @> '{}'::int[];
reset enable_seqscan;
reset enable_bitmapscan;
+-- Test supported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (fastupdate=on, gin_pending_list_limit=1000);
+
+-- Test unsupported storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (pages_per_range=40); -- from brin
+create index on t_gin_test_tbl using gin(i, j) with (autosummarize=true); -- from brin
+create index on t_gin_test_tbl using gin(i, j) with (deduplicate_items=on); -- from btree
+create index on t_gin_test_tbl using gin(i, j) with (fillfactor=30); -- from gist
+create index on t_gin_test_tbl using gin(i, j) with (buffering=on); -- from gist
+create index on t_gin_test_tbl using gin(i, j) with (parallel_workers=5); -- from heap
+create index on t_gin_test_tbl using gin(i, j) with (seq_page_cost=1.0); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (random_page_cost=4.0); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (effective_io_concurrency=5); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (maintenance_io_concurrency=5); -- from tablespace
+create index on t_gin_test_tbl using gin(i, j) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on t_gin_test_tbl using gin(i, j) with (nonsense);
+create index on t_gin_test_tbl using gin(i, j) with (toast.nonsense);
+
drop table t_gin_test_tbl;
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index b9d398ea94..3973221126 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -147,4 +147,24 @@ reset enable_seqscan;
reset enable_bitmapscan;
reset enable_indexonlyscan;
+-- Test supported storage parameters
+create index on gist_tbl using gist (p, c) with (fillfactor=30, buffering=on);
+
+-- Test unsupported storage parameters
+create index on gist_tbl using gist (p, c) with (pages_per_range=40); -- from brin
+create index on gist_tbl using gist (p, c) with (autosummarize=true); -- from brin
+create index on gist_tbl using gist (p, c) with (deduplicate_items=on); -- from btree
+create index on gist_tbl using gist (p, c) with (fastupdate=on); -- from gin
+create index on gist_tbl using gist (p, c) with (gin_pending_list_limit=1000); -- from gin
+create index on gist_tbl using gist (p, c) with (parallel_workers=5); -- from heap
+create index on gist_tbl using gist (p, c) with (seq_page_cost=1.0); -- from tablespace
+create index on gist_tbl using gist (p, c) with (random_page_cost=4.0); -- from tablespace
+create index on gist_tbl using gist (p, c) with (effective_io_concurrency=5); -- from tablespace
+create index on gist_tbl using gist (p, c) with (maintenance_io_concurrency=5); -- from tablespace
+create index on gist_tbl using gist (p, c) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on gist_tbl using gist (p, c) with (nonsense);
+create index on gist_tbl using gist (p, c) with (toast.nonsense);
+
drop table gist_tbl;
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 4d1aa020a9..b7fcb8b58c 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -202,3 +202,21 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=9);
CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
WITH (fillfactor=101);
+
+-- Test unsupported storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (autosummarize=true); -- from brin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (pages_per_range=40); -- from brin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (deduplicate_items=on); -- from btree
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (fastupdate=on); -- from gin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (gin_pending_list_limit=1000); -- from gin
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (buffering=on); -- from gist
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (parallel_workers=5); -- from heap
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (seq_page_cost=1.0); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (random_page_cost=4.0); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (effective_io_concurrency=5); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (maintenance_io_concurrency=5); -- from tablespace
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (nonsense);
+CREATE INDEX ON hash_f8_heap USING hash (random) WITH (toast.nonsense);
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd45..21f023e684 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -295,3 +295,59 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
+
+-- Test supported storage parameters
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test supported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_opts WITH (toast.vacuum_index_cleanup=auto)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test unsupported toast storage parameters with materialzed views lacking
+-- toastable columns
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.no_such_option)
+ AS SELECT gs AS i FROM generate_series(1,100) gs;
+
+-- Test the interaction of supported storage parameters with delayed population
+CREATE MATERIALIZED VIEW matview_opts
+ WITH (toast.vacuum_index_cleanup=auto, fillfactor=30, parallel_workers=5)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs
+ WITH NO DATA;
+REFRESH MATERIALIZED VIEW matview_opts;
+SELECT rel.reloptions, toast.reloptions AS toastoptions
+ FROM pg_class rel LEFT JOIN pg_class toast ON rel.reltoastrelid = toast.oid
+ WHERE rel.relname = 'matview_opts';
+DROP MATERIALIZED VIEW matview_opts;
+
+-- Test unsupported storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (pages_per_range=40) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (autosummarize=true) -- from brin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (deduplicate_items=true) -- from btree
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (fastupdate=on) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (gin_pending_list_limit=1000) -- from gin
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (buffering=on) -- from gist
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (seq_page_cost=1.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (random_page_cost=4.0) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (effective_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (maintenance_io_concurrency=5) -- from tablespace
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+
+-- Test nonsense storage parameters
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
+CREATE MATERIALIZED VIEW matview_uns_opts WITH (toast.nonsense)
+ AS SELECT gs, gs::text AS i FROM generate_series(1,100) gs;
diff --git a/src/test/regress/sql/spgist.sql b/src/test/regress/sql/spgist.sql
index 7644f344a9..e8f1a65138 100644
--- a/src/test/regress/sql/spgist.sql
+++ b/src/test/regress/sql/spgist.sql
@@ -80,3 +80,21 @@ insert into spgist_domain_tbl values('fee'), ('fi'), ('fo'), ('fum');
explain (costs off)
select * from spgist_domain_tbl where f1 = 'fo';
select * from spgist_domain_tbl where f1 = 'fo';
+
+-- Test unsupported storage parameters
+create index on spgist_point_tbl using spgist(p) with (pages_per_range=40); -- from brin
+create index on spgist_point_tbl using spgist(p) with (autosummarize=true); -- from brin
+create index on spgist_point_tbl using spgist(p) with (deduplicate_items=on); -- from btree
+create index on spgist_point_tbl using spgist(p) with (fastupdate=on); -- from gin
+create index on spgist_point_tbl using spgist(p) with (gin_pending_list_limit=1000); -- from gin
+create index on spgist_point_tbl using spgist(p) with (buffering=on); -- from gist
+create index on spgist_point_tbl using spgist(p) with (parallel_workers=5); -- from heap
+create index on spgist_point_tbl using spgist(p) with (seq_page_cost=1.0); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (random_page_cost=4.0); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (effective_io_concurrency=5); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (maintenance_io_concurrency=5); -- from tablespace
+create index on spgist_point_tbl using spgist(p) with (toast.vacuum_index_cleanup=auto); -- from toast
+
+-- Test nonsense storage parameters
+create index on spgist_point_tbl using spgist(p) with (nonsense);
+create index on spgist_point_tbl using spgist(p) with (toast.nonsense);
--
2.21.1 (Apple Git-122.3)
Re: minor gripe about lax reloptions parsing for views
On 2021-Dec-21, Mark Dilger wrote:
Rebased patch attached:
These tests are boringly repetitive. Can't we have something like a
nested loop, with AMs on one and reloptions on the other, where each
reloption is tried on each AM and an exception block to report the
failure or success for each case? Maybe have the list of AMs queried
from pg_am with hardcoded additions if needed?
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)
Re: minor gripe about lax reloptions parsing for views
The patch is currently not applying.
And it looks like there hasn't been any discussion since Alvaro's
comments last december. I'm marking the patch Returned with Feedback.
On Fri, 24 Dec 2021 at 16:49, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2021-Dec-21, Mark Dilger wrote:
Rebased patch attached:
These tests are boringly repetitive. Can't we have something like a
nested loop, with AMs on one and reloptions on the other, where each
reloption is tried on each AM and an exception block to report the
failure or success for each case? Maybe have the list of AMs queried
from pg_am with hardcoded additions if needed?--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)
--
greg