Thread: Support for DatabaseMetadata: getCatalogName, getTableName, getColumnLabel

Greetings,

Using the the driver supplied with PG 8.2 I am seeing the following with
DatabaseMetadata:

a) getCatalogName(int): always empty

b) getTableName(int) always empty

c) getColumnLabel(int) always equal to getColumnName(i), i.e.:
`SELECT foo AS bar from tbl`,
both getColumnName and getColumnLabel return 'bar'. I believe (cant
recall for sure though) that getColumnLabel is behaving okay, but
getColumnName should instead return 'foo'

I did not check the String argument equivalent to of these.

Is there a configuration option in the driver or PG that I can change to
correct this, or are these just not fully implemented yet?

Kind regards,

Ken

Ken,

The backend does not provide us with this information, so it is not
trivial to implement( or even possible in some cases) .

Dave
On 29-Dec-06, at 1:10 AM, Ken Johanson wrote:

> Greetings,
>
> Using the the driver supplied with PG 8.2 I am seeing the following
> with DatabaseMetadata:
>
> a) getCatalogName(int): always empty
>
> b) getTableName(int) always empty
>
> c) getColumnLabel(int) always equal to getColumnName(i), i.e.:
> `SELECT foo AS bar from tbl`,
> both getColumnName and getColumnLabel return 'bar'. I believe (cant
> recall for sure though) that getColumnLabel is behaving okay, but
> getColumnName should instead return 'foo'
>
> I did not check the String argument equivalent to of these.
>
> Is there a configuration option in the driver or PG that I can
> change to correct this, or are these just not fully implemented yet?
>
> Kind regards,
>
> Ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

Dave Cramer <pg@fastcrypt.com> writes:
> The backend does not provide us with this information, so it is not
> trivial to implement( or even possible in some cases) .

Huh?  We added table oid and column number to the RowDescription message
specifically to support those JDBC methods.  You would have to incur an
additional round trip to the server to get names from these numbers,
but it's certainly implementable ... and I thought it'd been implemented
long ago, so I'm a bit surprised by Ken's report.

As for getCatalogName, I think just returning the database name (which
you should still have from the connection request) would meet the spec.

            regards, tom lane

I stand corrected. I'll try to find some time this week to work on
some of the outstanding issues.

Tom, as of which version has this been implemented ?

Dave
On 29-Dec-06, at 10:47 AM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>> The backend does not provide us with this information, so it is not
>> trivial to implement( or even possible in some cases) .
>
> Huh?  We added table oid and column number to the RowDescription
> message
> specifically to support those JDBC methods.  You would have to
> incur an
> additional round trip to the server to get names from these numbers,
> but it's certainly implementable ... and I thought it'd been
> implemented
> long ago, so I'm a bit surprised by Ken's report.
>
> As for getCatalogName, I think just returning the database name (which
> you should still have from the connection request) would meet the
> spec.
>
>             regards, tom lane
>

Dave Cramer <pg@fastcrypt.com> writes:
> I stand corrected. I'll try to find some time this week to work on
> some of the outstanding issues.

> Tom, as of which version has this been implemented ?

IIRC it was implemented in the V3 protocol, so whenever that came in.

            regards, tom lane

On Fri, 29 Dec 2006, Dave Cramer wrote:

> The backend does not provide us with this information, so it is not trivial
> to implement( or even possible in some cases) .

Actually we did implement it and then backed it out following the
discussion starting here and moved it into PGResultSetMetaData methods
like getBaseColumnName.

http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008

We should certainly be able to implement getCatalogName though.

Kris Jurka

Kris Jurka wrote:
>
>
> On Fri, 29 Dec 2006, Dave Cramer wrote:
>
>> The backend does not provide us with this information, so it is not
>> trivial to implement( or even possible in some cases) .
>
> Actually we did implement it and then backed it out following the
> discussion starting here and moved it into PGResultSetMetaData methods
> like getBaseColumnName.
>
> http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008
>


Also, does getTableName look like it will be feasible in near future?

I'm trying to decide how I should proceed on a project, and this is sort
of a limbo for me. If I can do anything to help with this (testing,
etc), please let me know.

Thank you,
ken

On Thu, 4 Jan 2007, Ken Johanson wrote:

> Kris Jurka wrote:
>>
>> Actually we did implement it and then backed it out following the
>> discussion starting here and moved it into PGResultSetMetaData methods like
>> getBaseColumnName.
>>
>> http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008
>>
>
>
> Also, does getTableName look like it will be feasible in near future?
>

Yes, there are also getBaseTableName and getBaseSchemaName methods
available.

http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGResultSetMetaData.html

Kris Jurka

>>
>> Also, does getTableName look like it will be feasible in near future?
>>
>
> Yes, there are also getBaseTableName and getBaseSchemaName methods
> available.
>
> http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGResultSetMetaData.html
>

These will be in the java.sql.ResultSetMetaData interface's
implementation? Or would I need to obtain a PGResultSetMetaData (which
doesn't seem to extend RSMD; does a facade RSMD impl class wrap
PGResultSetMetaData?)?

My apologies for not being able to determine this on my own.

On Thu, 4 Jan 2007, Ken Johanson wrote:

> These will be in the java.sql.ResultSetMetaData interface's implementation?
> Or would I need to obtain a PGResultSetMetaData (which doesn't seem to extend
> RSMD; does a facade RSMD impl class wrap PGResultSetMetaData?)?
>

ResultSet rs = Statement.executeQuery("SELECT a, b FROM tab");
ResultSetMetaData rsmd = rs.getMetaData();
org.postgresql.PGResultSetMetaData pgrsmd =
     (org.postgresql.PGResultSetMetaData)rsmd;
for (int i=1; i<=rsmd.getColumnCount(); i++) {
     System.out.println(pgrsmd.getBaseTableName(i));
}

Kris Jurka

Kris Jurka wrote:
>
>
> On Thu, 4 Jan 2007, Ken Johanson wrote:
>
>> These will be in the java.sql.ResultSetMetaData interface's
>> implementation? Or would I need to obtain a PGResultSetMetaData (which
>> doesn't seem to extend RSMD; does a facade RSMD impl class wrap
>> PGResultSetMetaData?)?
>>
>
> ResultSet rs = Statement.executeQuery("SELECT a, b FROM tab");
> ResultSetMetaData rsmd = rs.getMetaData();
> org.postgresql.PGResultSetMetaData pgrsmd =
>     (org.postgresql.PGResultSetMetaData)rsmd;
> for (int i=1; i<=rsmd.getColumnCount(); i++) {
>     System.out.println(pgrsmd.getBaseTableName(i));
> }
>
> Kris Jurka
>
>


Kris, I'm sorry, I meant, will I be able to get the table name via
ResultSetMetaData.getTableName(int). The app I'm using internally
depends on standard interfaces at its core and unfortunately I don't
have the ability (nor desire really) to add in impl-specific code.

I guess I am not fully understanding why getTableName isn't just being
populated with PGResultSetMetaData.getBaseTableName() in the first
place; if so please feel welcome to hit me over the head with the cold
hard truth ;-)

Thank you again,
Ken

On Thu, 4 Jan 2007, Ken Johanson wrote:

> I guess I am not fully understanding why getTableName isn't just being
> populated with PGResultSetMetaData.getBaseTableName() in the first place; if
> so please feel welcome to hit me over the head with the cold hard truth ;-)
>

The previous discussion I cited was referring to getColumnName and how it
really should be referring to the alias of a result, not the column
itself.  That is for "SELECT a AS b FROM c AS d" should return "b" for
getColumnName(1).  If we accept that as true then it follows that
getTableName(1) should return "d", not "c".  Right now we don't have the
information to return "d" and so we must always return the empty string.
Getting the base column and table names seems more useful but does not
appear to be what the javadoc and spec imply as the behavior of the
standard API methods.

Kris Jurka

Kris Jurka wrote:

> The previous discussion I cited was referring to getColumnName and how
> it really should be referring to the alias of a result, not the column
> itself.  That is for "SELECT a AS b FROM c AS d" should return "b" for
> getColumnName(1).  If we accept that as true then it follows that
> getTableName(1) should return "d", not "c".

Understood now, thanks. I agree/vote this is the best spec
interpretation/behavior (for its lack of a 'getTableLabel'). It also is
in line with a couple other driver's (inc mysqls') current impls.

   Right now we don't have the
> information to return "d" and so we must always return the empty string.
> Getting the base column and table names seems more useful but does not
> appear to be what the javadoc and spec imply as the behavior of the
> standard API methods.
>
> Kris Jurka
>

So to be sure, this (literal and alias name differentiation) actually is
not being provided via the current server V3 protocol or server?

If so, does anyone know off hand if V3 is somehow extensible; i.e can we
add in metadata without breaking apps or major version?

Again, just trying to get an idea of how near or far off this may be. I
think there are several high level object persistence wrappers out there
that depend (right or wrong) on table names/aliases to reconstitute
objects from selects and natural/inner joins.

Ken

On Thu, 4 Jan 2007, Ken Johanson wrote:

>> Right now we don't have the information to return "d" and so we must
>> always return the empty string. Getting the base column and table names
>> seems more useful but does not appear to be what the javadoc and spec
>> imply as the behavior of the standard API methods.
>>
>
> So to be sure, this (literal and alias name differentiation) actually is not
> being provided via the current server V3 protocol or server?

What we get now in the V3 protocol:

1) the column name which may be an alias but we don't know if it came from
an alias or not, consider SELECT a AS a.  If we get "a" we don't know if
it was aliased or not.

2) The underlying table's oid which can be used to lookup it's name and
schema.

3) The underlying column's number in the table which can be used to lookup
its name.

No information on a table alias is available.

> If so, does anyone know off hand if V3 is somehow extensible; i.e can we add
> in metadata without breaking apps or major version?
>

No the RowDescription message is fixed:

http://www.postgresql.org/docs/current/static/protocol-message-formats.html

> Again, just trying to get an idea of how near or far off this may be. I think
> there are several high level object persistence wrappers out there that
> depend (right or wrong) on table names/aliases to reconstitute objects from
> selects and natural/inner joins.
>

It's a ways off and even if it was working you have to think about things
like self joins and calculated results.  What can a tool do with these?

SELECT t1.col, t2.col, 1+3 FROM tab t1, tab t2 WHERE t1.a = t2.b;

And app would need some serious smarts to unravel this sort of thing.

Kris Jurka

>> If so, does anyone know off hand if V3 is somehow extensible; i.e can
>> we add in metadata without breaking apps or major version?
>>
>
> No the RowDescription message is fixed:
>

Oh, man.

>
>> Again, just trying to get an idea of how near or far off this may be.
>> I think there are several high level object persistence wrappers out
>> there that depend (right or wrong) on table names/aliases to
>> reconstitute objects from selects and natural/inner joins.
>>
>
> It's a ways off and even if it was working you have to think about
> things like self joins and calculated results.

Well if the app was sending complex queries yes. But I don't think its
an issue for simple data sweeps:

select * from t1, t2 where t1.pk=t2.fk

With getTableName its possible to build hashtable keys, etc from this
even when t1 and t2 both contains a 'pk' -- which is kind of nice since
one need'nt name the column in the usual tablename_pk format, and
becasue the table name itself implies the tablename_pk format that would
normally be assigned it's column.

while (rs.next())
{
    for (int=1;i<cols;i++)
        hash.put(rs.getTableName(i)+"."+rs.getColumnLabel(i));
}

or to use trees of hashtables (avoiding the StringBuffer)
((Hashtable)tbls->(Hashtable)cols)

Tad off topic on my part, apologies.

ken

Ken Johanson <pg-user@kensystem.com> writes:
> Kris Jurka wrote:
>> The previous discussion I cited was referring to getColumnName and how
>> it really should be referring to the alias of a result, not the column
>> itself.  That is for "SELECT a AS b FROM c AS d" should return "b" for
>> getColumnName(1).  If we accept that as true then it follows that
>> getTableName(1) should return "d", not "c".

> Understood now, thanks. I agree/vote this is the best spec
> interpretation/behavior (for its lack of a 'getTableLabel').

Urm ... I might agree with that, except for its signal lack of usefulness.
What is the point of knowing the table alias?  The
underlying-table-and-column names have some possible usefulness for
issuing updates against the underlying tables, but what the heck are you
going to do with "d" here?  If the application understands the query
(which it itself issued, don't forget) well enough to understand what
"d" conveys, why wouldn't "b" be at least as useful?

I think you're letting a narrow reading of some admittedly-badly-written
spec text get the best of you.

            regards, tom lane

Tom Lane wrote:
>>> The previous discussion I cited was referring to getColumnName and how
>>> it really should be referring to the alias of a result, not the column
>>> itself.  That is for "SELECT a AS b FROM c AS d" should return "b" for
>>> getColumnName(1).  If we accept that as true then it follows that
>>> getTableName(1) should return "d", not "c".
>
>> Understood now, thanks. I agree/vote this is the best spec
>> interpretation/behavior (for its lack of a 'getTableLabel').
>
> Urm ... I might agree with that, except for its signal lack of usefulness.
> What is the point of knowing the table alias?  The
> underlying-table-and-column names have some possible usefulness for
> issuing updates against the underlying tables,

Agreed.

  but what the heck are you
> going to do with "d" here?

you can (as you know) have multiple table aliases on the same table,
where for example 'd' might represent a relation value to c. For example
a manager 'd' over a employee 'c':

select c.firstName, d.firstName from contacts as c, contacts as d where
c.bossId=d.pk and d.role=...

With the table name alias I can tell which rows are bosses or
subordinates (without needing to know/maintain column ordinality, or
otherwise just by doing a `select * from..`). The real world use I have
is similar to above except that I add in another table for one to many
relations (multiple bosses or trainers, etc), and don't really use the
bossId field -- it exemplifies the idea here though.

For low level and efficient data retrieval this this is the very
simplest and most elegant SELECT methodology I've come across - just
select * from [joined tables], and a diver can build hierarchal
hashtables, or instead a flat one with (albeit stringbuffer-built) keys
(tbl.get("boss.firstName"))


   If the application understands the query
> (which it itself issued, don't forget) well enough to understand what
> "d" conveys, why wouldn't "b" be at least as useful?

Yes, but one isn't in the spec as you stated. 'd' seems the more useful
to me. And if it defaults to the real name in absence of an alias, that
seems logical and all-serving also.

Kris Jurka wrote:
>
> What we get now in the V3 protocol:
>
> 1) the column name which may be an alias but we don't know if it came
> from an alias or not, consider SELECT a AS a.  If we get "a" we don't
> know if it was aliased or not.
>
> 2) The underlying table's oid which can be used to lookup it's name and
> schema.
>
> 3) The underlying column's number in the table which can be used to
> lookup its name.
>
> No information on a table alias is available.
>
>> If so, does anyone know off hand if V3 is somehow extensible; i.e can
>> we add in metadata without breaking apps or major version?
>>
>
> No the RowDescription message is fixed:
>
> http://www.postgresql.org/docs/current/static/protocol-message-formats.html
>


Kris, do you know roughly when a v4 protocol could be on the radar?.. is
it months out (or years?), or has it even been discussed yet? I'm trying
to decide if I should vote-in on having this info added into that
version, if there is a discussion/planning thread somewhere...

Thx, ken

Ken Johanson <pg-user@kensystem.com> writes:
> Kris, do you know roughly when a v4 protocol could be on the radar?.. is
> it months out (or years?), or has it even been discussed yet?

The latter.  There are one or two minor things we don't like about the
current protocol, but it's very far from the point where anyone
seriously wants to go through a revision.

            regards, tom lane