Hi all, The main goal of this patch is enable to an user the capability to store options (relations and attributes) related to extensions by using a fixed prefix called 'ext' in the defined name. It's cant be useful for replication solutions.
So, with this patch we can do that: ALTER TABLE foo SET (ext.somext.do_replicate=true); When 'ext' is the fixed prefix, 'somext' is the extension name, 'do_replicate' is the extension option and 'true' is the value. Also we can use this form to define storage options to indexes and per-attribute options. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index d210077..bf4e196 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -82,6 +82,15 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESE <xref linkend="SQL-REINDEX"> to get the desired effects. </para> + <note> + <para> + A special prefix called '<replaceable class="PARAMETER">ext.</>' can be + used to define storage parameter. The storage parameters with this prefix + will be used by 'extensions'. Storage option nomenclature: ext.name.option=value + (ext=fixed prefix, name=extension name, option=option name and value=option value). + See example bellow. + </para> + </note> </listitem> </varlistentry> @@ -202,6 +211,17 @@ ALTER INDEX distributors SET (fillfactor = 75); REINDEX INDEX distributors; </programlisting></para> + <para> + To set a storage parameter to be used by extensions: +<programlisting> +ALTER INDEX distributors + SET (ext.somext.do_replicate=true); +</programlisting> + (ext=fixed prefix, somext=extension name, do_replicate=option name and + true=option value) +</para> + + </refsect1> <refsect1> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 89649a2..4756a58 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -213,6 +213,17 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> of statistics by the <productname>PostgreSQL</productname> query planner, refer to <xref linkend="planner-stats">. </para> + + <note> + <para> + A special prefix called '<replaceable class="PARAMETER">ext.</>' can be used to + define per-attribute options. The attribute options with this prefix will be + used by 'extensions'. The attribute option nomenclature: ext.name.option=value + (ext=fixed prefix, name=extension name, option=option name and value=option value). + See the example bellow. + </para> + </note> + </listitem> </varlistentry> @@ -476,6 +487,11 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> <command>ALTER TABLE</> does not treat <literal>OIDS</> as a storage parameter. Instead use the <literal>SET WITH OIDS</> and <literal>SET WITHOUT OIDS</> forms to change OID status. + A special prefix called '<replaceable class="PARAMETER">ext.</>' can be + used to define storage parameter. The storage parameters with this prefix + will be used by 'extensions'. Storage option nomenclature: ext.name.option=value + (ext=fixed prefix, name=extension name, option=option name and value=option value). + See example bellow. </para> </note> </listitem> @@ -1112,6 +1128,26 @@ ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx; </programlisting></para> + <para> + To set a per-attribute option to be used by extensions: +<programlisting> +ALTER TABLE distributors + ALTER COLUMN dist_id SET (ext.somext.do_replicate=true); +</programlisting> + (ext=fixed prefix, somext=extension name, do_replicate=option name and + true=option value) +</para> + + <para> + To set a storage parameter to be used by extensions: +<programlisting> +ALTER TABLE distributors + SET (ext.somext.do_replicate=true); +</programlisting> + (ext=fixed prefix, somext=extension name, do_replicate=option name and + true=option value) +</para> + </refsect1> <refsect1> diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index b5fd30a..06c2b3a 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -275,6 +275,8 @@ static void initialize_reloptions(void); static void parse_one_reloption(relopt_value *option, char *text_str, int text_len, bool validate); +static bool is_extension_namespace(char *namespace); + /* * initialize_reloptions * initialization routine, must be called before parsing @@ -602,13 +604,15 @@ transformRelOptions(Datum oldOptions, List *defList, char *namspace, /* Search for a match in defList */ foreach(cell, defList) { - DefElem *def = (DefElem *) lfirst(cell); + DefElem *def = (DefElem *) lfirst(cell); int kw_len; + char *text_compare; /* ignore if not in the same namespace */ if (namspace == NULL) { - if (def->defnamespace != NULL) + if (def->defnamespace != NULL && + !is_extension_namespace(def->defnamespace)) continue; } else if (def->defnamespace == NULL) @@ -617,8 +621,17 @@ transformRelOptions(Datum oldOptions, List *defList, char *namspace, continue; kw_len = strlen(def->defname); + if (is_extension_namespace(def->defnamespace)) + kw_len += strlen(def->defnamespace) + 1; + + text_compare = (char *) palloc(kw_len + 1); + if (is_extension_namespace(def->defnamespace)) + sprintf(text_compare, "%s.%s", def->defnamespace, def->defname); + else + sprintf(text_compare, "%s", def->defname); + if (text_len > kw_len && text_str[kw_len] == '=' && - pg_strncasecmp(text_str, def->defname, kw_len) == 0) + pg_strncasecmp(text_str, text_compare, kw_len) == 0) break; } if (!cell) @@ -675,7 +688,7 @@ transformRelOptions(Datum oldOptions, List *defList, char *namspace, } } - if (!valid) + if (!valid && !is_extension_namespace(def->defnamespace)) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("unrecognized parameter namespace \"%s\"", @@ -688,7 +701,8 @@ transformRelOptions(Datum oldOptions, List *defList, char *namspace, /* ignore if not in the same namespace */ if (namspace == NULL) { - if (def->defnamespace != NULL) + if (def->defnamespace != NULL && + !is_extension_namespace(def->defnamespace)) continue; } else if (def->defnamespace == NULL) @@ -706,10 +720,18 @@ transformRelOptions(Datum oldOptions, List *defList, char *namspace, else value = "true"; len = VARHDRSZ + strlen(def->defname) + 1 + strlen(value); + + if (is_extension_namespace(def->defnamespace)) + len += strlen(def->defnamespace) + 1; + /* +1 leaves room for sprintf's trailing null */ t = (text *) palloc(len + 1); SET_VARSIZE(t, len); - sprintf(VARDATA(t), "%s=%s", def->defname, value); + + if (is_extension_namespace(def->defnamespace)) + sprintf(VARDATA(t), "%s.%s=%s", def->defnamespace, def->defname, value); + else + sprintf(VARDATA(t), "%s=%s", def->defname, value); astate = accumArrayResult(astate, PointerGetDatum(t), false, TEXTOID, @@ -896,6 +918,9 @@ parseRelOptions(Datum options, bool validate, relopt_kind kind, int text_len = VARSIZE(optiontext) - VARHDRSZ; int j; + if (is_extension_namespace(text_str)) + continue; + /* Search for a match in reloptions */ for (j = 0; j < numoptions; j++) { @@ -1316,3 +1341,12 @@ tablespace_reloptions(Datum reloptions, bool validate) return (bytea *) tsopts; } + +bool +is_extension_namespace(char *namespace) +{ + if (namespace == NULL) + return false; + + return (pg_strncasecmp(namespace, "ext.", 4) == 0); +} diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 11f6291..74f0015 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2283,10 +2283,26 @@ reloption_elem: $$ = makeDefElemExtended($1, $3, (Node *) $5, DEFELEM_UNSPEC); } + | ColLabel '.' ColLabel '.' ColLabel '=' def_arg + { + if (strcmp($1, "ext") != 0) + parser_yyerror("syntax error"); + + $$ = makeDefElemExtended(psprintf("%s.%s", $1, $3), $5, + (Node *) $7, DEFELEM_UNSPEC); + } | ColLabel '.' ColLabel { $$ = makeDefElemExtended($1, $3, NULL, DEFELEM_UNSPEC); } + | ColLabel '.' ColLabel '.' ColLabel + { + if (strcmp($1, "ext") != 0) + parser_yyerror("syntax error"); + + $$ = makeDefElemExtended(psprintf("%s.%s", $1, $3), $5, + NULL, DEFELEM_UNSPEC); + } ; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 7366392..7fe62b9 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2337,3 +2337,127 @@ FROM ( 0 | t (1 row) +-- +-- Extension Option Test +-- +CREATE TABLE extension_reloption_test(id SERIAL PRIMARY KEY); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+------------ + extension_reloption_test | + extension_reloption_test.id | + extension_reloption_test_pkey | +(3 rows) + +ALTER TABLE extension_reloption_test SET (fillfactor=70); +ALTER INDEX extension_reloption_test_pkey SET (fillfactor=80); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (n_distinct=100); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+------------------ + extension_reloption_test | {fillfactor=70} + extension_reloption_test.id | {n_distinct=100} + extension_reloption_test_pkey | {fillfactor=80} +(3 rows) + +ALTER TABLE extension_reloption_test SET (xx.bdr.do_replicate=true); +ERROR: syntax error at or near "true" +LINE 1: ...BLE extension_reloption_test SET (xx.bdr.do_replicate=true); + ^ +ALTER INDEX extension_reloption_test_pkey SET (xx.bdr.do_replicate=true); +ERROR: syntax error at or near "true" +LINE 1: ...xtension_reloption_test_pkey SET (xx.bdr.do_replicate=true); + ^ +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (xx.bdr.do_replicate=true); +ERROR: syntax error at or near "true" +LINE 1: ...loption_test ALTER COLUMN id SET (xx.bdr.do_replicate=true); + ^ +ALTER TABLE extension_reloption_test SET (xx.bdr=true); +ERROR: unrecognized parameter namespace "xx" +ALTER INDEX extension_reloption_test_pkey SET (xx.bdr=true); +ERROR: unrecognized parameter namespace "xx" +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (xx.bdr=true); +ERROR: unrecognized parameter namespace "xx" +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+------------------ + extension_reloption_test | {fillfactor=70} + extension_reloption_test.id | {n_distinct=100} + extension_reloption_test_pkey | {fillfactor=80} +(3 rows) + +-- test ext.* namespace +ALTER TABLE extension_reloption_test SET (ext.bdr.do_replicate=true); +ALTER INDEX extension_reloption_test_pkey SET (ext.bdr.do_replicate=true); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (ext.bdr.do_replicate=true); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+-------------------------------------------- + extension_reloption_test | {fillfactor=70,ext.bdr.do_replicate=true} + extension_reloption_test.id | {n_distinct=100,ext.bdr.do_replicate=true} + extension_reloption_test_pkey | {fillfactor=80,ext.bdr.do_replicate=true} +(3 rows) + +ALTER TABLE extension_reloption_test SET (ext.foo.bar.bar=1234); +ERROR: syntax error at or near "." +LINE 1: ...ER TABLE extension_reloption_test SET (ext.foo.bar.bar=1234)... + ^ +ALTER INDEX extension_reloption_test_pkey SET (ext.foo.bar.bar=1234); +ERROR: syntax error at or near "." +LINE 1: ...DEX extension_reloption_test_pkey SET (ext.foo.bar.bar=1234)... + ^ +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (ext.foo.bar.bar=1234); +ERROR: syntax error at or near "." +LINE 1: ...on_reloption_test ALTER COLUMN id SET (ext.foo.bar.bar=1234)... + ^ +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+-------------------------------------------- + extension_reloption_test | {fillfactor=70,ext.bdr.do_replicate=true} + extension_reloption_test.id | {n_distinct=100,ext.bdr.do_replicate=true} + extension_reloption_test_pkey | {fillfactor=80,ext.bdr.do_replicate=true} +(3 rows) + +ALTER TABLE extension_reloption_test SET (ext.foo.bar=1234); +ALTER INDEX extension_reloption_test_pkey SET (ext.foo.bar=1234); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (ext.foo.bar=1234); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+------------------------------------------------------------- + extension_reloption_test | {fillfactor=70,ext.bdr.do_replicate=true,ext.foo.bar=1234} + extension_reloption_test.id | {n_distinct=100,ext.bdr.do_replicate=true,ext.foo.bar=1234} + extension_reloption_test_pkey | {fillfactor=80,ext.bdr.do_replicate=true,ext.foo.bar=1234} +(3 rows) + +ALTER TABLE extension_reloption_test RESET (ext.foo.bar); +ALTER INDEX extension_reloption_test_pkey RESET (ext.foo.bar); +ALTER TABLE extension_reloption_test ALTER COLUMN id RESET (ext.foo.bar); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; + relname | reloptions +-------------------------------+-------------------------------------------- + extension_reloption_test | {fillfactor=70,ext.bdr.do_replicate=true} + extension_reloption_test.id | {n_distinct=100,ext.bdr.do_replicate=true} + extension_reloption_test_pkey | {fillfactor=80,ext.bdr.do_replicate=true} +(3 rows) + diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 0d3b79b..b540e89 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1567,3 +1567,59 @@ FROM ( FROM pg_class WHERE relkind IN ('r', 'i', 'S', 't', 'm') ) mapped; + +-- +-- Extension Option Test +-- +CREATE TABLE extension_reloption_test(id SERIAL PRIMARY KEY); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +ALTER TABLE extension_reloption_test SET (fillfactor=70); +ALTER INDEX extension_reloption_test_pkey SET (fillfactor=80); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (n_distinct=100); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +ALTER TABLE extension_reloption_test SET (xx.bdr.do_replicate=true); +ALTER INDEX extension_reloption_test_pkey SET (xx.bdr.do_replicate=true); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (xx.bdr.do_replicate=true); +ALTER TABLE extension_reloption_test SET (xx.bdr=true); +ALTER INDEX extension_reloption_test_pkey SET (xx.bdr=true); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (xx.bdr=true); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +-- test ext.* namespace +ALTER TABLE extension_reloption_test SET (ext.bdr.do_replicate=true); +ALTER INDEX extension_reloption_test_pkey SET (ext.bdr.do_replicate=true); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (ext.bdr.do_replicate=true); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +ALTER TABLE extension_reloption_test SET (ext.foo.bar.bar=1234); +ALTER INDEX extension_reloption_test_pkey SET (ext.foo.bar.bar=1234); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (ext.foo.bar.bar=1234); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +ALTER TABLE extension_reloption_test SET (ext.foo.bar=1234); +ALTER INDEX extension_reloption_test_pkey SET (ext.foo.bar=1234); +ALTER TABLE extension_reloption_test ALTER COLUMN id SET (ext.foo.bar=1234); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +ALTER TABLE extension_reloption_test RESET (ext.foo.bar); +ALTER INDEX extension_reloption_test_pkey RESET (ext.foo.bar); +ALTER TABLE extension_reloption_test ALTER COLUMN id RESET (ext.foo.bar); +SELECT relname, reloptions FROM pg_class WHERE oid IN ('extension_reloption_test'::regclass, 'extension_reloption_test_pkey'::regclass) +UNION ALL +SELECT attrelid::regclass||'.'||attname, attoptions FROM pg_attribute WHERE attrelid = 'extension_reloption_test'::regclass AND attname = 'id' +ORDER BY 1; +
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers