Re: [HACKERS] pg_get_indexdef() doesn't quote string reloptions
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 reloptions that 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 around the 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_get_indexdef() doesn't quote string reloptions
On Mon, Oct 13, 2014 at 11:21 AM, Eric Ridge e_ri...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_get_indexdef() doesn't quote string reloptions
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
Re: [HACKERS] pg_get_INDEXdef - opclass
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 12 June 2006 18:32 To: Dave Page Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_get_INDEXdef - opclass Dave Page dpage@vale-housing.co.uk writes: Following a pgAdmin bug report, I noticed that pg_get_viewdef doesn't return the opclass when called for a specific column (in 8.1 at least) - Bah, I mean pg_get_indexdef of course :-) This is intentional --- whoever asked for the per-column variant of the indexdef function wanted it that way. It seems reasonable to me: you can extract the opclass name with a simple join against pg_index.indclass[N], when you need it, whereas if the function sometimes included an opclass name that would tend to break apps that weren't expecting it. OTOH, getting the expression for an expression column would be seriously painful if there were no function to do it. Right, but how can I conditionally join with pg_opclass based on whether or not the opclass specified for the column is the default for that type? For a base type index column I can probably do that with some SQL, but what about cases where the index column is an expression? Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_get_INDEXdef - opclass
Dave Page dpage@vale-housing.co.uk writes: Right, but how can I conditionally join with pg_opclass based on whether or not the opclass specified for the column is the default for that type? Check pg_opclass.opcdefault and compare pg_opclass.opcintype to the index column's datatype (which you'd get from its pg_attribute row ... whether it's an expression is irrelevant). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_get_INDEXdef - opclass
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 13 June 2006 14:42 To: Dave Page Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_get_INDEXdef - opclass Dave Page dpage@vale-housing.co.uk writes: Right, but how can I conditionally join with pg_opclass based on whether or not the opclass specified for the column is the default for that type? Check pg_opclass.opcdefault and compare pg_opclass.opcintype to the index column's datatype (which you'd get from its pg_attribute row ... whether it's an expression is irrelevant). Ahh right - thanks. I got it into my head that there was no pg_attribute row for an expression. Must be the sun... Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_get_INDEXdef - opclass
Bah, I mean pg_get_indexdef of course :-) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Page Sent: 12 June 2006 12:12 To: pgsql-hackers@postgresql.org Subject: [HACKERS] pg_get_viewdef - opclass Following a pgAdmin bug report, I noticed that pg_get_viewdef doesn't return the opclass when called for a specific column (in 8.1 at least) - for example, for the index: CREATE UNIQUE INDEX bar_pattern_idx2 ON foofoo USING btree (lower((bar)::text) bpchar_pattern_ops, bar2) A query on column 1 returns: lower((bar)::text) It seems to me that the opclass should be included as well. Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_get_INDEXdef - opclass
Dave Page dpage@vale-housing.co.uk writes: Following a pgAdmin bug report, I noticed that pg_get_viewdef doesn't return the opclass when called for a specific column (in 8.1 at least) - Bah, I mean pg_get_indexdef of course :-) This is intentional --- whoever asked for the per-column variant of the indexdef function wanted it that way. It seems reasonable to me: you can extract the opclass name with a simple join against pg_index.indclass[N], when you need it, whereas if the function sometimes included an opclass name that would tend to break apps that weren't expecting it. OTOH, getting the expression for an expression column would be seriously painful if there were no function to do it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] pg_get_indexdef
Should pg_get_indexdef return its TABLESPACE clause? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_get_indexdef
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Should pg_get_indexdef return its TABLESPACE clause? Already done. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_get_indexdef
Should pg_get_indexdef return its TABLESPACE clause? Already done. Cool. I'd considered it before when I was coding psql stuff, but then I forgot to bring it up again on the list... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
