Обсуждение: pg_get_indexdef() doesn't quote string reloptions

Hi all!

I've been working on implementing a custom index using the Index Access Method API and have the need for custom
reloptionsthat are "complex" strings (ie, also contain non-alphaumerics). 

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
aroundthe option value) 

Looks like (at least) ruleutils.c:flatten_reloptions() needs to be smarter.

eric




PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS
The information contained in this communication is intended only for
the use of the addressee. Any other use is strictly prohibited.
Please notify the sender if you have received this message in error.
This communication is protected by applicable legal privileges and is
company confidential.

On Mon, Oct 13, 2014 at 11:21 AM, Eric Ridge <e_ridge@tcdi.com> wrote:
> PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS
> The information contained in this communication is intended only for
> the use of the addressee. Any other use is strictly prohibited.
> Please notify the sender if you have received this message in error.
> This communication is protected by applicable legal privileges and is
> company confidential.

If this communication is in fact intended to be protected by some
legal privilege, or to remain company confidential, you have
definitely sent it to the wrong place.  If it isn't, I think it
shouldn't say that it is.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

> If this communication is in fact intended to be protected by some
> legal privilege, or to remain company confidential, you have
> definitely sent it to the wrong place.

Sadly I don't control my company's email server.  They however don't control my gmail account.  I'll switch to that.

eric