Re: pg_get_indexdef() doesn't quote string reloptions

On Tue, Oct 14, 2014 at 12:21 AM, Eric Ridge <e_ridge@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+kdMV2HgnE9FQ@mail.gmail.com#CAHGQGwEanQ_e8WLHL25=bm_8Z5zkyZw0K0yiR+kdMV2HgnE9FQ@mail.gmail.com
And the thread where the limitation has been actually found:
http://www.postgresql.org/message-id/CAB7nPqSeVWnhk-TA-GJBDgea-1ZLT8WFYwSp_63ut2ia8W9wrQ@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