* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost <sfr...@snowman.net> 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 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
signature.asc
Description: Digital signature
