On Tue, Oct 14, 2014 at 12:21 AM, Eric Ridge <e_ri...@tcdi.com> wrote:
> pg_get_indexdef() and pg_dump don't quote the reloption values, making a
> restore (or cut-n-paste of the pg_get_indexdef() output) impossible if the
> reloption value contains non-alphanumerics.
>
> For example, the statement:
>
> # CREATE INDEX idxfoo ON table USING myindex (col) WITH (option =
> 'some complex string');
>
> cannot be restored as it gets rewritten as:
>
> CREATE INDEX idxfoo ON table USING myindex (col) WITH (option = some
> complex string);
> (note the lack of quotes around the option value)
>
> Looks like (at least) ruleutils.c:flatten_reloptions() needs to be smarter.
The limitation is not directly related to ruleutils.c, but to the way
reloptions are stored for a relation: no quotes are being used
because, well, they are not necessary. All the custom parameters that
can be used by tables or indexes are either on/off switches or
integers. For example:
=# CREATE TABLE test_trgm (t text);
CREATE TABLE
=# CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops)
WITH (fastupdate = off);
CREATE INDEX
=# CREATE INDEX trgm_idx_gist ON test_trgm USING gist (t
gist_trgm_ops) WITH (buffering = on);
CREATE INDEX
=# CREATE TABLE aa (a int) WITH (fillfactor = 40);
CREATE TABLE
=# SELECT relname, reloptions FROM pg_class where relname in
('trgm_idx_gin','trgm_idx_gist','aa');
relname | reloptions
---------------+------------------
trgm_idx_gin | {fastupdate=off}
trgm_idx_gist | {buffering=on}
aa | {fillfactor=40}
(3 rows)
Now, this problem has been discussed a couple of weeks ago when
arguing about adding unit support for storage parameters. Here is
where the feature has been discussed:
http://www.postgresql.org/message-id/flat/CAHGQGwEanQ_e8WLHL25=bm_8z5zkyzw0k0yir+kdmv2hgne...@mail.gmail.com#CAHGQGwEanQ_e8WLHL25=bm_8z5zkyzw0k0yir+kdmv2hgne...@mail.gmail.com
And the thread where the limitation has been actually found:
http://www.postgresql.org/message-id/cab7npqsevwnhk-ta-gjbdgea-1zlt8wfywsp_63ut2ia8w9...@mail.gmail.com
Your need is an argument to make reloptions smarter with quotes. Not
sure that's on the top of the TODO list of people here though.
Regards,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
