Re: Standard metadata queries

On Apr 24, 2008, at 11:12 AM, Joshua D. Drake wrote:

> On Thu, 24 Apr 2008 11:01:13 -0700
> Steve Atkins <steve(at)blighty(dot)com> wrote:
>
>> I've been chatting with the Trolltech folks about the implementation
>> of the Qt wrapper around libpq, and the issue of how to properly do
>> metadata queries came up. That is things like "What are the column
>> names and types of the primary key of this table, and what index
>> enforces it?" or "What are the names and types of each field of this
>> table?".
>>
>> These seem like queries that'll be used by a lot of people, hidden
>> down in ORMs and access libraries, and which are hard to get right,
>> let alone efficient, and which will generally be written by one
>> person (developing the ORM or library) and likely not touched again.
>>
>> Is there a standard set of well-crafted implementations of these
>> anywhere that could be used by all the interface and ORM developers?
>> If not, would it make sense to put some together and document or
>> wiki them? Both as example code and as a set of good, solid queries
>> that library developers can cut and paste.
>>
>> (The implementation I'm looking at right now has, amongst other
>> things, hardwired OID-to-type mappings, and there's got to be a
>> cleaner way than that).
>
> I believe the information_schema is standard.

Standard, but woefully incomplete (by design).

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.

Something like newsysviews might be an appropriate answer, but if
it's not included in a core distribution then none of the APIs or ORMs
can rely on it.

Given that, I think that using common queries for DBD::Pg, JDBC, Qt,
etc, etc would probably benefit an awful lot of users and reduce the
amount of duplicated effort across the various APIs. Ripping the
existing queries out of one or more of those and just having a few
people who understand pg_* sanity check them seems like it might
be a decent place to start, if nobody has already done something
similar.

Cheers,
Steve