Does this bother anyone else:
CREATE INDEX uses an amoptions parser specific for the index type and, at least for btree, rejects relation options
fromthe "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
MATERIALIZEDVIEW 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
aVIEW_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
