No longer possible to query catalogs for index capabilities?

Andrew Gierth <andrew@tao11.riddles.org.uk> 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, amsearcharray, amsearchnulls).

Am I missing something or is this a significant oversight?

It's absolutely not an oversight. We asked when 65c5fcd35 went in
whether there was still any need for that information to be available at
the SQL level, and nobody appeared to care. We could in theory expose
a view to show the data --- but since a large part of the point of that
change was to not need initdb for AM API changes, and to not be
constrained to exactly SQL-compatible representations within that API,
I'm disinclined to do so without a fairly compelling argument why it's
needed.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> 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, amsearcharray,
amsearchnulls).

Am I missing something or is this a significant oversight?

Tom> It's absolutely not an oversight. We asked when 65c5fcd35 went in
Tom> whether there was still any need for that information to be
Tom> available at the SQL level, and nobody appeared to care.

Perhaps you were asking the wrong people?

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 to exactly
Tom> SQL-compatible representations within that API, I'm disinclined to
Tom> do so without a fairly compelling argument why it's needed.

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.

That would surely be better than the present condition of being
completely unable to get this information from SQL.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> 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, amsearcharray,
amsearchnulls).

Am I missing something or is this a significant oversight?

Tom> It's absolutely not an oversight. We asked when 65c5fcd35 went in
Tom> whether there was still any need for that information to be
Tom> available at the SQL level, and nobody appeared to care.

Perhaps you were asking the wrong people?

The capabilities strike me as useful to expose, they're pretty useful to
know. I believe we were right to hide the APIs/functions and don't see
any need to expose those to the SQL level.

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 to exactly
Tom> SQL-compatible representations within that API, I'm disinclined to
Tom> do so without a fairly compelling argument why it's needed.

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.

Hmm, that seems pretty reasonable.

That would surely be better than the present condition of being
completely unable to get this information from SQL.

Agreed.

Thanks!

Stephen

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> 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 to exactly
Tom> SQL-compatible representations within that API, I'm disinclined to
Tom> do so without a fairly compelling argument why it's needed.

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.

You missed the "compelling argument why it's needed" part. What is the
need for this? I'm not going to be persuaded by "it was there before".
We've gotten along fine without such inspection functions for FDWs and
tablesample methods, so I doubt that we really need them for index AMs.
Nobody's writing applications that make decisions about which AM to use
based on what they see in pg_am. And anyone who's concerned whether their
AM is reporting the right info is going to be much better served by gdb
than by some functions that can present only a subset of what's in the
info struct.

Moreover, I think you are missing the point about initdb. The issue there
is that anytime in future that we make a change to the AM API, we'd need
to have a debate about whether and how to expose such a change for SQL
inspection. Defining the exposure mechanism as a new function rather than
a new view column changes neither the need for a debate, nor the need for
an initdb unless we decide that we don't need to expose anything. But if
your proposal is merely that we freeze the set of information available
as some subset of what used to be available from pg_am, then it sounds
an awful lot like a backwards-compatibility hack rather than an honest
attempt to describe AM capabilities.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> 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
Tom> is the need for this? I'm not going to be persuaded by "it was
Tom> there before".

How about "it was there before, and people did use it"?

In fact I notice you participated in a discussion of this a couple of
months back on the JDBC list, in which your solution was to suggest
hardcoding the name 'btree' into the query:

/messages/by-id/24504.1463237368@sss.pgh.pa.us

Doesn't that strike you as an indication that something is wrong?

Tom> We've gotten along fine without such inspection functions for FDWs
Tom> and tablesample methods,

which are new and not especially interesting to code doing introspection

Tom> so I doubt that we really need them for index AMs.

People write catalog queries for indexes a whole lot more than they do
for FDWs or tablesample methods.

This whole discussion started because I wrote a catalog query for
someone on IRC, and found I couldn't do it on 9.6 because amcanorder was
gone.

Tom> Nobody's writing applications that make decisions about which AM
Tom> to use based on what they see in pg_am.

That's not the issue. The issue is finding information about _existing_
indexes that is not otherwise exposed.

Tom> Moreover, I think you are missing the point about initdb. The
Tom> issue there is that anytime in future that we make a change to the
Tom> AM API, we'd need to have a debate about whether and how to expose
Tom> such a change for SQL inspection. Defining the exposure mechanism
Tom> as a new function rather than a new view column changes neither
Tom> the need for a debate, nor the need for an initdb unless we decide
Tom> that we don't need to expose anything.

I'm not proposing a new function for each capability. I'm proposing ONE
function (or two, one starting from the index rather than the AM, for
convenience). Adding more capability names would not require an initdb.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 25, 2016 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> 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 new
changes. You can hold your breath and pretend that every single one
of those queries is probably misdesigned, but I do not think anyone
else will find that argument convincing.

--
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

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Jul 25, 2016 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> 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 new
changes. You can hold your breath and pretend that every single one
of those queries is probably misdesigned, but I do not think anyone
else will find that argument convincing.

We've already broken existing queries against pg_am, simply because the
columns are not there anymore; and that decision is not getting undone
at this point. I'm willing to consider putting back some substitute
capability, but I'd like to see as much evidence for adding that as we'd
expect for any other new feature. Andrew still hasn't shown a concrete
example of what he needs to do and why.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Here is my proposed code (first cut; obviously it needs docs too).
Opinions?

--
Andrew (irc:RhodiumToad)

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> 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 and NULLS FIRST/LAST settings), which requires knowing
whether amcanorder is true to determine whether to look at the bits at
all. The guy I was helping was using an earlier pg version, so it didn't
affect him (yet); I hit it when trying to test the query on 9.6.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07/25/2016 12:19 PM, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Jul 25, 2016 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> 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 new
changes. You can hold your breath and pretend that every single one
of those queries is probably misdesigned, but I do not think anyone
else will find that argument convincing.

We've already broken existing queries against pg_am, simply because the
columns are not there anymore; and that decision is not getting undone
at this point. I'm willing to consider putting back some substitute
capability, but I'd like to see as much evidence for adding that as we'd
expect for any other new feature. 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.

JD

regards, tom lane

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Joshua D. Drake" <jd@commandprompt.com> 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.

Both Andrew and Robert have asserted without proof that it'd be useful
to be able to get at some of that data. Given the lack of any supporting
evidence, it's impossible to know which data needs to be exposed, and
that's why I find their statements insufficient. "Emulate 9.5's pg_am
exactly" is not in the cards, and short of that I'd like to see some
concrete reasons why we do or do not need to expose particular bits of
data.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

"Joshua D. Drake" <jd@commandprompt.com> 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.

Both Andrew and Robert have asserted without proof that it'd be useful
to be able to get at some of that data. Given the lack of any supporting
evidence, it's impossible to know which data needs to be exposed, and
that's why I find their statements insufficient. "Emulate 9.5's pg_am
exactly" is not in the cards, and short of that I'd like to see some
concrete reasons why we do or do not need to expose particular bits of
data.

I believe the response to "what" is the patch which Andrew provided, and
the use-case is illustrated by the query which he wrote that used those
columns in much the same way that the JDBC driver used them (and which
was also broken by their removal). This isn't just academic "gee, I
wish we hadn't removed those columns", there are clearly cases where
they were useful and were used.

I do not believe hard-coding the name of index types as a definitive
list of which indexes support what capabilities is an appropriate
approach (as was suggested, and evidently done, for the JDBC driver).

Additional use-cases include query analysis, by which one might want to
see what capabilities an index has to understand why it may or may not
be useful for a given query. I would also suggest that relying on
pg_get_indexdef() is a poor solution and we should be considering how to
expose the necessary information for pg_dump through the catalog instead
of asking users who are interested to use a function that returns the
result as an SQL DDL statement. We don't do that for table definitions
and have argued time and time again why we shouldn't.

Thanks!

Stephen

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, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

And a doc patch to go with it:

--
Andrew (irc:RhodiumToad)

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 whether to look at the bits at
all.

Maybe we should provide a facility to decode those bits then?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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), which requires knowing
whether amcanorder is true to determine whether to look at the bits at
all.

Maybe we should provide a facility to decode those bits then?

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 index
AMs use which indoption bits. There's something fundamentally wrong
in that. We don't let psql or pg_dump look directly at indoption, so
why would we think that third-party client-side code should do so?

Andrew complained upthread that pg_get_indexdef() was too heavyweight
for his purposes, but it's not clear to me what he wants instead.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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), which requires knowing
whether amcanorder is true to determine whether to look at the bits at
all.

Maybe we should provide a facility to decode those bits then?

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 index
AMs use which indoption bits. There's something fundamentally wrong
in that. We don't let psql or pg_dump look directly at indoption, so
why would we think that third-party client-side code should do so?

Andrew complained upthread that pg_get_indexdef() was too heavyweight
for his purposes, but it's not clear to me what he wants instead.

I guess I'm missing something because it seems quite clear to me. He
wants to know if the index was built with ASC or DESC, and if it was
built with NULLS FIRST or NULLS LAST, just like the JDBC driver.

pg_get_indexdef() will return that information, but as an SQL statement
with a lot of other information that isn't relevant and is difficult to
deal with when all you're trying to do is write an SQL query (no, I
don't believe the solution here is to use pg_get_indexef() ~ 'DESC').

For my 2c, I'd like to see pg_dump able to use the catalog tables to
derive the index definition, just as they manage to figure out table
definitions without (for the most part) using functions. More
generally, I believe we should be working to reach a point where we can
reconstruct all objects in the database using just the catalog, without
any SQL bits being provided from special functions which access
information that isn't available at the SQL level.

I don't see any problem with what Andrew has proposed as the information
returned informs the creation of the DDL statement, but does not provide
a textual "drop-in"/black-box component to include in the statement to
recreate the object, the way pg_get_indexdef() does.

Thanks!

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@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 index
AMs use which indoption bits. There's something fundamentally wrong
in that. We don't let psql or pg_dump look directly at indoption, so
why would we think that third-party client-side code should do so?

For my 2c, I'd like to see pg_dump able to use the catalog tables to
derive the index definition, just as they manage to figure out table
definitions without (for the most part) using functions. More
generally, I believe we should be working to reach a point where we can
reconstruct all objects in the database using just the catalog, without
any SQL bits being provided from special functions which access
information that isn't available at the SQL level.

No, I reject that entirely. It would be insane for example to expect that
random client-side code should be able to interpret the node trees stored
in places like pg_index.indexprs. It's barely possible that we could
maintain such logic in pg_dump, though having to maintain a different
version for each supported server branch would be a giant PITA. But do
you also want to maintain translated-into-Java copies of each of those
libraries for the benefit of JDBC? Or any other language that client
code might be written in?

Now, obviously knowing which bit in pg_index.indoption does what would be
a few orders of magnitude less of a maintenance hazard than knowing what
expression node trees contain. But that doesn't make it a good
future-proof thing for clients to be doing. If the answer to the question
"why do you need access to pg_am.amcanorder?" is "so I can interpret the
bits in pg_index.indoption", I think it's clear that we've got an
abstraction failure that is not going to be fixed by just exposing
something equivalent to the old pg_am definition.

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".

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@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 index
AMs use which indoption bits. There's something fundamentally wrong
in that. We don't let psql or pg_dump look directly at indoption, so
why would we think that third-party client-side code should do so?

For my 2c, I'd like to see pg_dump able to use the catalog tables to
derive the index definition, just as they manage to figure out table
definitions without (for the most part) using functions. More
generally, I believe we should be working to reach a point where we can
reconstruct all objects in the database using just the catalog, without
any SQL bits being provided from special functions which access
information that isn't available at the SQL level.

No, I reject that entirely. It would be insane for example to expect that
random client-side code should be able to interpret the node trees stored
in places like pg_index.indexprs. It's barely possible that we could
maintain such logic in pg_dump, though having to maintain a different
version for each supported server branch would be a giant PITA. But do
you also want to maintain translated-into-Java copies of each of those
libraries for the benefit of JDBC? Or any other language that client
code might be written in?

Honestly, I anticipated the focus on the pg_get_expr() and should have
explicitly commented on it. I agree that we shouldn't look to have
pg_dump or client utilities be able to understand node trees and that,
instead, we should continue to provide a way for those to be
reconstructed into SQL expressions.

Now, obviously knowing which bit in pg_index.indoption does what would be
a few orders of magnitude less of a maintenance hazard than knowing what
expression node trees contain. But that doesn't make it a good
future-proof thing for clients to be doing. If the answer to the question
"why do you need access to pg_am.amcanorder?" is "so I can interpret the
bits in pg_index.indoption", I think it's clear that we've got an
abstraction failure that is not going to be fixed by just exposing
something equivalent to the old pg_am definition.

I agree- asking clients to interpret the bits in pg_index.indoption
isn't the right answer either.

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".

Right, this makes sense to me. The point which I was trying to get at
above is that we should be able to replace most of what is provided in
pg_get_indexdef() by using this function to rebuild the CREATE INDEX
command- again, similar to how we build a CREATE TABLE command rather
than simply provide a 'pg_get_tabledef()'.

Thanks!

Stephen