Re: Standard metadata queries

On Apr 24, 2008, at 2:22 PM, Tom Lane wrote:

> Steve Atkins <steve(at)blighty(dot)com> writes:
>> On Apr 24, 2008, at 11:12 AM, Joshua D. Drake wrote:
>>> I believe the information_schema is standard.
>
>> Standard, but woefully incomplete (by design).
>
> Sure, because it's restricted to standardized concepts. Do the
> adapters
> in question need to obtain info about nonstandard things? One would
> hope that they're trying to confine themselves to SQL-standard stuff.

Types and indexes are two things that are commonly needed that
aren't covered well by information_schema.

>> Also, AIUI, it's fairly slow in use, compared to touching the
>> underlying
>> postgresql-specific tables, which would be something that you might
>> not care about in design tools but which might be a problem for use
>> in
>> an ORM or similar.
>
> This is a fair point, and it's unlikely ever to be fixed completely,
> though perhaps we could put a bit more effort into whichever views are
> considered performance-critical.

If it turns out that the sort of information that's needed by APIs
can be answered solely by information_schema queries then it'd
be worth a look (though I suspect that some of the requirements
that the standards put on information_schema rule out some
performance improvements).

> AFAIR, the only times we've heard from adapter authors were when they
> couldn't make something work at all :-(. A review project like you
> propose would be worthwhile. Aside from possibly helping the adapter
> authors, it would give us a better sense of which changes to the
> system
> catalogs to avoid because they'd be likely to break clients.

Good enough reason for me to put some time into it, I think. I'll go see
what current APIs are using and put something up on the wiki.

Cheers,
Steve