Re: No longer possible to query catalogs for index capabilities? - Mailing list pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@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 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()'.
>
> Uh, what would be the point?  You're just replacing a call to one backend
> function with calls to N backend functions, and creating version
> dependencies and opportunities for errors of omission on the client side.
> (That is, there's exactly no chance that the set of functions you'd need
> to call to construct a CREATE INDEX command would stay static forever.
> We keep adding new index features.)

We also keep adding table-level options too, and is therefore hardly a
reason to argue that we shouldn't provide the information through the
catalog for a client-side application to rebuild a table, as pg_dump
does.

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

I'm not convinced that the two are actually different.  As we add new
index features, query-constructing applications may be interested in
those new features and therefore we should be exposing that information.
If we were using a capabilities function to build up the CREATE INDEX
command in pg_dump, we never would have ended up in the situation which
we find ourselves now- specifically, that we've removed information that
applications were using.

Consider the RLS case.  If we were using some hypothetical
pg_get_tabledef() in pg_dump, and that function handled everything
about building the table definition, we might not have considered how to
expose the policy information for RLS and could have stored things like
"what command is this policy for?" as an opaque column that clients
wouldn't easily understand.  That would have been unfortunate, as there
are clients which are definitely interested in the policies that have
been defined on tables, for auditing purposes.

In other words, for my 2c, pg_dump provides a great definition of what
we should provide in the way of database introspection and we should try
to minimize the cases where we're providing special server-side
functions that pg_dump needs to perform its role.  That this information
is needed by client applications and we don't provide an easy way to
programatically access it demonstrates how pg_get_indexdef() really went
too far in the direction of the server handing opaque SQL commands for
the client to run to recreate the object, without giving the client any
understanding of the definition of the object.

Thanks!

Stephen