Thread: Standard metadata queries
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). Cheers, Steve
On Thu, 24 Apr 2008 11:01:13 -0700 Steve Atkins <steve@blighty.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. Joshua D. Drake > > Cheers, > Steve > > -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On Apr 24, 2008, at 11:12 AM, Joshua D. Drake wrote: > On Thu, 24 Apr 2008 11:01:13 -0700 > Steve Atkins <steve@blighty.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
Steve Atkins <steve@blighty.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.
> 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.
> 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.
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.
regards, tom lane
On Apr 24, 2008, at 2:22 PM, Tom Lane wrote: > Steve Atkins <steve@blighty.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
On Apr 24, 2008, at 1:01 PM, Steve Atkins 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). Take a look at http://pgfoundry.org/projects/newsysviews/ -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828