Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > Latest patch. > Names and scopes are as per discussion. New files for code and > regression test. Docs included. Pushed with (mostly) cosmetic adjustments. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > Latest patch. > Names and scopes are as per discussion. New files for code and > regression test. Docs included. I'm starting to review this now. If anyone has objections to the property names/behaviors shown in Andrew's previous message,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Latest patch. Names and scopes are as per discussion. New files for code and regression test. Docs included. -- Andrew (irc:RhodiumToad) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7830334..4552a74 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: Tom> But we need to be clear in the documentation about what this Tom> property actually means. My objection to having it answer at the Tom> index or column level is basically that that encourages confusion Tom> as to what it means. OK.

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > "Tom" == Tom Lane writes: > Tom> +1 mostly, but I'm a bit bemused by can_order and can_backward > Tom> having different scopes --- how come? > That's where they were in the previous list, a couple of messages up in > the

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> This table shows what properties are exposed at the AM-wide level, >> the per-index level and the per-column level. Tom> +1 mostly, but I'm a bit bemused by can_order and can_backward Tom> having different scopes --- how come? That's

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> distance_orderable now returns true/false depending on the opclass, >> not just on the amcanorderbyop field. In order to do this, I've >> added an optional amproperty function to the AM api, which if it >> exists, gets first dibs on all

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > This table shows what properties are exposed at the AM-wide level, the > per-index level and the per-column level. +1 mostly, but I'm a bit bemused by can_order and can_backward having different scopes --- how come? Also, not sure about

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

So I'm tidying up and doing docs for the next version of this patch, but here for comment is the current functionality: select cap, pg_indexam_has_property(a.oid, cap) as "AM", pg_index_has_property('onek_hundred'::regclass, cap) as "Index",

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Robert Haas writes: > On Thu, Aug 11, 2016 at 11:35 AM, Tom Lane wrote: >> Well, if it's unqueryable they won't be able to query it no matter how >> hard they try ;-). > Sure, but as this thread already demonstrates, they may also complain > forcefully

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Kevin Grittner writes: > On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane wrote: >> In short, I do not see a good reason to expose ampredlocks at the SQL >> level, and I think there needs to be a darn good reason to expose any of >> this stuff, not just "maybe

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 08/11/2016 10:46 AM, Kevin Grittner wrote: On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane wrote: Kevin Grittner writes: But a DBA who has a problem doesn't care what the truth will be in a year or two -- the interest is in *right now* on one particular

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane wrote: > Kevin Grittner writes: >> Where [whether the AM supports predicate locking at a >> granularity finer than the provided default of index relation] >> would be interesting to know is [ ... ] (when there is

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Thu, Aug 11, 2016 at 5:24 PM, Robert Haas wrote: > You argue against > these things on the grounds that they might change later, but the > overwhelming evidence from posts on this list is that people would > prefer to have access to APIs that might not be stable rather

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Thu, Aug 11, 2016 at 11:35 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 10, 2016 at 6:14 PM, Tom Lane wrote: >>> In short, I do not see a good reason to expose ampredlocks at the SQL >>> level, and I think there needs

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Robert Haas writes: > On Wed, Aug 10, 2016 at 6:14 PM, Tom Lane wrote: >> In short, I do not see a good reason to expose ampredlocks at the SQL >> level, and I think there needs to be a darn good reason to expose any of >> this stuff, not just "maybe

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Wed, Aug 10, 2016 at 6:14 PM, Tom Lane wrote: > Kevin Grittner writes: >> That one seems like it should either be at the AM level or not >> included at all. Where it would be interesting to know is if you >> are a hacker looking for an AM to enhance

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > So these properties (I've changed all the names here, suggestions > welcome for better ones) I think should be testable on the AM, each with > an example of why: > can_order > can_unique > can_multi_col > can_exclude Check, flags

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Kevin Grittner writes: > That one seems like it should either be at the AM level or not > included at all. Where it would be interesting to know is if you > are a hacker looking for an AM to enhance with support, or (when > there is more than just btree supported, so it is not

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Wed, Aug 10, 2016 at 12:31 PM, Andrew Gierth wrote: > These could be limited to being testable only on a specified index, and > not AM-wide: > predicate_locks (? maybe) That one seems like it should either be at the AM level or not included at all. Where it

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> - this still has everything in amapi.c rather than creating any new >> files. Also, the regression tests are in create_index.sql for lack >> of any obviously better place. Tom> This more than doubles the size of amapi.c, so it has a

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > - this still has everything in amapi.c rather than creating any new > files. Also, the regression tests are in create_index.sql for lack > of any obviously better place. This more than doubles the size of amapi.c, so it has a

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Updated patch. Changes: - returns NULL rather than "cache lookup failed" - added pg_index_column_has_property (incl. docs) - added regression tests Not changed / need consideration: - this still has everything in amapi.c rather than creating any new files. Also, the regression

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Alvaro" == Alvaro Herrera writes: >> One idea is utils/adt/misc.c. Or we could make a new file under >> utils/adt/ though I'm not very sure what to name it. amaccess.c? >> catutils.c? If there's only ever likely to be one or two functions >> of this ilk,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Tom Lane wrote: > Andrew Gierth writes: > > Where'd be a good place to put that function? ruleutils? catalog/index.c ? > > > (ruleutils is way too big already) > > Agreed. catalog/index.c is not a place that implements SQL-visible > functions, so I don't like that

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > Where'd be a good place to put that function? ruleutils? catalog/index.c ? > (ruleutils is way too big already) Agreed. catalog/index.c is not a place that implements SQL-visible functions, so I don't like that either. One idea is

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Kevin" == Kevin Grittner writes: >>> Building on the has-property approach Andrew suggested, I wonder if >>> we need something like pg_index_column_has_property(indexoid, colno, >>> propertyname) with properties like "sortable", "desc", "nulls first". >> >> This

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sun, Aug 7, 2016 at 11:53 PM, Alvaro Herrera wrote: >> Building on the has-property approach Andrew suggested, I wonder if >> we need something like pg_index_column_has_property(indexoid, colno, >> propertyname) with properties like "sortable", "desc", "nulls first".

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

I wrote: > Having said all that, it is unfortunate that 9.6 is going to go out > without any good solution to this need. But as Robert already pointed > out, trying to fix it now would force delaying 9.6rc1 by several weeks > (and that's assuming that it doesn't take very long to get consensus >

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Vik Fearing writes: > On 08/08/16 15:38, Tom Lane wrote: >> Well, the next step is to fill in the blanks: what properties need to be >> queryable? > That's less urgent; adding missing properties does not require a > catversion bump. If the complaint is "I can do X before

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 08/08/16 15:38, Tom Lane wrote: > Alvaro Herrera writes: >> Tom Lane wrote: >>> Building on the has-property approach Andrew suggested, I wonder if >>> we need something like pg_index_column_has_property(indexoid, colno, >>> propertyname) with properties like

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Alvaro Herrera writes: > Tom Lane wrote: >> Building on the has-property approach Andrew suggested, I wonder if >> we need something like pg_index_column_has_property(indexoid, colno, >> propertyname) with properties like "sortable", "desc", "nulls first". > This seems

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 8 August 2016 at 03:49, Vladimir Sitnikov wrote: > > > Tom Lane : > >> FWIW, this thread started on 25-July, less than two weeks ago. > > > Technically speaking, there was a pgsql-jdbc thread started on May 14: >

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Tom Lane : > FWIW, this thread started on 25-July, less than two weeks ago. Technically speaking, there was a pgsql-jdbc thread started on May 14: https://www.postgresql.org/message-id/nh72v6%24582%241%40ger.gmane.org 9.6beta1 was released on May 12 The fact that it wasn't

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Tom Lane wrote: > Building on the has-property approach Andrew suggested, I wonder if > we need something like pg_index_column_has_property(indexoid, colno, > propertyname) with properties like "sortable", "desc", "nulls first". This seems simple enough, on the surface. Why not run with this

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sun, Aug 07, 2016 at 07:19:39PM -0400, Tom Lane wrote: > Had the complaint been raised sooner, maybe there would've been time > to get a well-thought-out API into 9.6. The fact that it wasn't raised > till more than 6 months after we committed the pg_am changes, and more > than 2 months after

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Robert Haas writes: > On Sat, Aug 6, 2016 at 8:00 AM, Andrew Gierth > wrote: >> Anyway, what I haven't seen in this thread is any implementable >> counter-proposal other than the "just hardcode the name 'btree'" >> response that was given in

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sun, Aug 7, 2016 at 4:09 PM, Greg Sabino Mullane wrote: > Robert Haas wrote: >> But I'm neither willing to commit a patch to fix the day before rc1 >> nor to argue that the whole release cycle should be put back by >> several weeks on account of this issue. > > Seriously?

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Robert Haas wrote: > But I'm neither willing to commit a patch to fix the day before rc1 > nor to argue that the whole release cycle should be put back by > several weeks on account of this issue. Seriously? First, not sure why this would

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sat, Aug 6, 2016 at 8:00 AM, Andrew Gierth wrote: > Anyway, what I haven't seen in this thread is any implementable > counter-proposal other than the "just hardcode the name 'btree'" > response that was given in the JDBC thread, which I don't consider > acceptable

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 8/6/16 10:13 AM, Bruce Momjian wrote: On Sat, Aug 6, 2016 at 04:04:41PM +0100, Andrew Gierth wrote: "Bruce" == Bruce Momjian writes: Bruce> Would it be helpful to output an array of strings representing Bruce> the index definition? >> Why would that help, if the

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sat, Aug 6, 2016 at 04:04:41PM +0100, Andrew Gierth wrote: > > "Bruce" == Bruce Momjian writes: > > Bruce> Would it be helpful to output an array of strings representing > Bruce> the index definition? > > >> Why would that help, if the point is to enable

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Bruce" == Bruce Momjian writes: Bruce> Would it be helpful to output an array of strings representing Bruce> the index definition? >> Why would that help, if the point is to enable programmatic access >> to information? Bruce> I was thinking an array of strings

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sat, Aug 6, 2016 at 01:00:15PM +0100, Andrew Gierth wrote: > > "Bruce" == Bruce Momjian writes: > > >> As far as I understood Andrew's use case, he was specifically *not* > >> interested in a complete representation of an index definition, but > >> rather about

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Bruce" == Bruce Momjian writes: >> As far as I understood Andrew's use case, he was specifically *not* >> interested in a complete representation of an index definition, but >> rather about whether it had certain properties that would be of >> interest to

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Mon, Aug 1, 2016 at 10:19:43AM -0400, Tom Lane wrote: > As far as I understood Andrew's use case, he was specifically *not* > interested in a complete representation of an index definition, but > rather about whether it had certain properties that would be of > interest to query-constructing

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Mon, Aug 1, 2016 at 10:19 AM, Tom Lane wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> Building on the has-property approach Andrew suggested, I wonder if > >> we need something like pg_index_column_has_property(indexoid,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> Building on the has-property approach Andrew suggested, I wonder if > >> we need something like pg_index_column_has_property(indexoid, colno, > >> propertyname) with

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Building on the has-property approach Andrew suggested, I wonder if >> we need something like pg_index_column_has_property(indexoid, colno, >> propertyname) with properties like "sortable", "desc", "nulls

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> Yeah. I'm not very impressed by the underlying assumption that it's > >> okay for client-side code to hard-wire knowledge about what indoption > >> bits mean,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Yeah. I'm not very impressed by the underlying assumption that it's >> okay for client-side code to hard-wire knowledge about what indoption >> bits mean, but not okay for it to hard-wire knowledge about which

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Peter Eisentraut writes: > > On 7/25/16 3:26 PM, Andrew Gierth wrote: > >> The issue I ran into was the exact same one as in the JDBC thread I > >> linked to earlier: correctly interpreting pg_index.indoption (to get the >

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Peter Eisentraut writes: > On 7/25/16 3:26 PM, Andrew Gierth wrote: >> The issue I ran into was the exact same one as in the JDBC thread I >> linked to earlier: correctly interpreting pg_index.indoption (to get the >> ASC / DESC and NULLS FIRST/LAST settings),

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 7/25/16 3:26 PM, Andrew Gierth wrote: > The issue I ran into was the exact same one as in the JDBC thread I > linked to earlier: correctly interpreting pg_index.indoption (to get the > ASC / DESC and NULLS FIRST/LAST settings), which requires knowing > whether amcanorder is true to determine

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

And a doc patch to go with it: -- Andrew (irc:RhodiumToad) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0689cc9..3e13e38 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -577,6 +577,89 @@ + +Capability information

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 25/07/16 21:20, Andrew Gierth wrote: > Here is my proposed code (first cut; obviously it needs docs too). > Opinions? I support the future of this patch, for 9.6. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

* Tom Lane (t...@sss.pgh.pa.us) wrote: > "Joshua D. Drake" writes: > > On 07/25/2016 12:19 PM, Tom Lane wrote: > >> Andrew still hasn't shown a concrete > >> example of what he needs to do and why. > > > I think that Andrew and other people who have commented on this

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

"Joshua D. Drake" writes: > On 07/25/2016 12:19 PM, Tom Lane wrote: >> Andrew still hasn't shown a concrete >> example of what he needs to do and why. > I think that Andrew and other people who have commented on this thread > made it pretty obvious why it is useful.

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 07/25/2016 12:19 PM, Tom Lane wrote: Robert Haas writes: On Mon, Jul 25, 2016 at 10:35 AM, Tom Lane wrote: You missed the "compelling argument why it's needed" part. What is the need for this? It's self-evident that this thread wouldn't exist

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: Tom> Andrew still hasn't shown a concrete example of what he needs to Tom> do and why. The issue I ran into was the exact same one as in the JDBC thread I linked to earlier: correctly interpreting pg_index.indoption (to get the ASC / DESC

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Here is my proposed code (first cut; obviously it needs docs too). Opinions? -- Andrew (irc:RhodiumToad) diff --git a/src/backend/access/index/amapi.c b/src/backend/access/index/amapi.c index d347ebc..3e7e084 100644 --- a/src/backend/access/index/amapi.c +++ b/src/backend/access/index/amapi.c

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Robert Haas writes: > On Mon, Jul 25, 2016 at 10:35 AM, Tom Lane wrote: >> You missed the "compelling argument why it's needed" part. What is the >> need for this? > It's self-evident that this thread wouldn't exist if it were not the > case that

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Mon, Jul 25, 2016 at 10:35 AM, Tom Lane wrote: > You missed the "compelling argument why it's needed" part. What is the > need for this? It's self-evident that this thread wouldn't exist if it were not the case that people had queries that no longer work because of these

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> It could easily be exposed as a function interface of the form >> index_has_capability(oid,name) or indexam_has_capability(oid,name) >> without any initdb worries. Tom> You missed the "compelling argument why it's needed" part. What

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > "Tom" == Tom Lane writes: > Tom> We could in theory expose a view to show the data --- but since a > Tom> large part of the point of that change was to not need initdb for > Tom> AM API changes, and to not be constrained

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

* Andrew Gierth (and...@tao11.riddles.org.uk) wrote: > > "Tom" == Tom Lane writes: > > >> With the gutting of pg_am in 9.6, there seems to be no longer any > >> way for a query of the system catalogs to discover any of the index > >> capabilities that were formerly

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> With the gutting of pg_am in 9.6, there seems to be no longer any >> way for a query of the system catalogs to discover any of the index >> capabilities that were formerly columns in pg_am (notably >> amcanorder, amcanorderbyop,

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > With the gutting of pg_am in 9.6, there seems to be no longer any way > for a query of the system catalogs to discover any of the index > capabilities that were formerly columns in pg_am (notably amcanorder, > amcanorderbyop, amclusterable,

[HACKERS] No longer possible to query catalogs for index capabilities?

With the gutting of pg_am in 9.6, there seems to be no longer any way for a query of the system catalogs to discover any of the index capabilities that were formerly columns in pg_am (notably amcanorder, amcanorderbyop, amclusterable, amsearcharray, amsearchnulls). Am I missing something or is