Thread: Release 1204 released

Dave, thank you for being a release manager.

Vladimir

Dave Cramer schrieb am 09.10.2015 um 21:32:
>   * fix: filter DatabaseMetaData.getColumns by tables PR #386 (0c95126)

Why was this done?

From my perspective this is a serious bug, because now getColumns() doesn't return column information for views,
materializedviews or foreign tables any more. 

Which in turn means that the SQL client I am maintaining (SQL Workbench/J) is no longer able to display column
informationfor anything else than tables.  

I would assume that this is true for any other JDBC based SQL client. It will also break tools like Liquibase and
probablyHibernate as well as they also rely on getColumns() to return information about views (I am sure about
Liquibase,not 100% sure about Hibernate though) 


Regards
Thomas

All I can say is, that due to the nature of SQL Workbench (being DBMS independent) I do use (or at least test)
many other DBMS and every[1] other JDBC driver does return view columns - including materialized views in Oracle and
DB2.

So apparently all the other vendors think it makes sense to return view (and materialized view) columns.

Thomas

[1] I have tested at least briefly more than 15 different ones.


Dave Cramer schrieb am 15.10.2015 um 22:47:
> So I am looking at the API reference and no where does it say getColumns should return information about views.
>
> That being said the fact that getTables returns the type of the table there may be some inference that it should ?
>
> Lance, do you have some insight here ?
>
> Dave Cramer
>
> On 15 October 2015 at 05:10, Thomas Kellerer <spam_eater@gmx.net
> <mailto:spam_eater@gmx.net>> wrote:
>
> Dave Cramer schrieb am 09.10.2015 um 21:32:
>> * fix: filter DatabaseMetaData.getColumns by tables PR #386
>> (0c95126)
>
> Why was this done?
>
> From my perspective this is a serious bug, because now getColumns()
> doesn't return column information for views, materialized views or
> foreign tables any more.
>
> Which in turn means that the SQL client I am maintaining (SQL
> Workbench/J) is no longer able to display column information for
> anything else than tables.
>
> I would assume that this is true for any other JDBC based SQL client.
> It will also break tools like Liquibase and probably Hibernate as
> well as they also rely on getColumns() to return information about
> views (I am sure about Liquibase, not 100% sure about Hibernate
> though)
>
>
> Regards Thomas

On Thursday, October 15, 2015 4:54 PM, Dave Cramer <pg@fastcrypt.com> wrote:

> I'll revert it. But I think the previous issue was that it was
> also returning indexes as well. I'll just narrow down to tables,
> views, and mat views

... and foreign tables, please.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Wouldn't it make more sense to _exclude_ those types that should
absolutely not appear there?

I don't really see the point of excluding anything there in the first place
to be honest.

This method won't get called "accidently" with some random object name.
If that is an index (or something else), that's what the user requested.

As indexes and tables share the same namespace there will never be
any ambiguity which object is meant.

Currently the driver also returns columns for types "create type foo as ..."
I do rely on that as well.

Regards
Thomas


Dave Cramer schrieb am 15.10.2015 um 23:53:
> That's fine. I'll revert it. But I think the previous issue was that
> it was also returning indexes as well. I'll just narrow down to
> tables, views, and mat views
>
> Dave Cramer
>
> davec@postgresintl.com <mailto:davec@postgresintl.com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 15 October 2015 at 17:39, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>> wrote:
>
>     All I can say is, that due to the nature of SQL Workbench (being DBMS independent) I do use (or at least test)
>     many other DBMS and every[1] other JDBC driver does return view columns - including materialized views in Oracle
andDB2. 
>
>     So apparently all the other vendors think it makes sense to return view (and materialized view) columns.
>
>     Thomas
>
>     [1] I have tested at least briefly more than 15 different ones.

Interesting issue - that is indeed a use-case I didn't think about.

It's too bad that getColumns() doesn't return a "TABLE_TYPE" column that would match the types returned by
getTableTypes()

However, information_schema.columns does return columns from views, so those should at least be included
(although not getting columns for materialized views or types would mean I had to adjust my SQL Workbench for that)

Thomas


Dave Cramer schrieb am 16.10.2015 um 00:49:
> This was the original issue
>
> https://github.com/pgjdbc/pgjdbc/issues/374
>
> I don't think returning test_table_pkey is correct, hence the change
>
> Dave Cramer
>
> davec@postgresintl.com <mailto:davec@postgresintl.com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 15 October 2015 at 18:19, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>> wrote:
>
>     Wouldn't it make more sense to _exclude_ those types that should
>     absolutely not appear there?
>
>     I don't really see the point of excluding anything there in the first place
>     to be honest.
>
>     This method won't get called "accidently" with some random object name.
>     If that is an index (or something else), that's what the user requested.
>
>     As indexes and tables share the same namespace there will never be
>     any ambiguity which object is meant.
>
>     Currently the driver also returns columns for types "create type foo as ..."
>     I do rely on that as well.
>
>     Regards
>     Thomas
>       Dave Cramer schrieb am 15.10.2015 um 23:53:
>
>         That's fine. I'll revert it. But I think the previous issue was that
>         it was also returning indexes as well. I'll just narrow down to
>         tables, views, and mat views
>
>         Dave Cramer
>
>         davec@postgresintl.com <mailto:davec@postgresintl.com> <mailto:davec@postgresintl.com
<mailto:davec@postgresintl.com>>
>         www.postgresintl.com <http://www.postgresintl.com> <http://www.postgresintl.com>
>
>         On 15 October 2015 at 17:39, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>
<mailto:spam_eater@gmx.net<mailto:spam_eater@gmx.net>>> wrote: 
>
>             All I can say is, that due to the nature of SQL Workbench (being DBMS independent) I do use (or at least
test)
>             many other DBMS and every[1] other JDBC driver does return view columns - including materialized views in
Oracleand DB2. 
>
>             So apparently all the other vendors think it makes sense to return view (and materialized view) columns.
>
>             Thomas
>
>             [1] I have tested at least briefly more than 15 different ones.
>
>
>
>
>
>     --
>     Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org <mailto:pgsql-jdbc@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

From a SQL perspective, a view is indistinguishable from a table, so JDBC
expects you to handle them the same. This should also be clear from the
description of getTables, especially this column of the getTables
resultSet: "TABLE_TYPE String => table type. Typical types are "TABLE",
"VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
"SYNONYM"".

In other words, JDBC (and - IIRC - the SQL standard for that matter),
consider a view a table type (or more correctly: a relation), therefor
getColumns should return the columns of a view.

You can easily replace an existing view with a table (or vice versa), and
your application should be able to continue working as is.

Mark

On Thu, 15 Oct 2015 16:47:34 -0400, Dave Cramer <pg@fastcrypt.com> wrote:
> So I am looking at the API reference and no where does it say getColumns
> should return information about views.
>
> That being said the fact that getTables returns the type of the table
there
> may be some inference that it should ?
>
> Lance, do you have some insight here ?
>
> Dave Cramer
>
> davec@postgresintl.com
> www.postgresintl.com
>
> On 15 October 2015 at 07:10, Dave Cramer <pg@fastcrypt.com> wrote:
>
>> Yes, this looks like it needs to be reverted....
>>
>> Dave Cramer
>>
>> davec@postgresintl.com
>> www.postgresintl.com
>>
>> On 15 October 2015 at 05:10, Thomas Kellerer <spam_eater@gmx.net>
wrote:
>>
>>> Dave Cramer schrieb am 09.10.2015 um 21:32:
>>> >   * fix: filter DatabaseMetaData.getColumns by tables PR #386
>>> >   (0c95126)
>>>
>>> Why was this done?
>>>
>>> From my perspective this is a serious bug, because now getColumns()
>>> doesn't return column information for views, materialized views or
>>> foreign
>>> tables any more.
>>>
>>> Which in turn means that the SQL client I am maintaining (SQL
>>> Workbench/J) is no longer able to display column information for
>>> anything
>>> else than tables.
>>>
>>> I would assume that this is true for any other JDBC based SQL client.
It
>>> will also break tools like Liquibase and probably Hibernate as well as
>>> they
>>> also rely on getColumns() to return information about views (I am sure
>>> about Liquibase, not 100% sure about Hibernate though)
>>>
>>>
>>> Regards
>>> Thomas
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>
>>

The getTables and getColumns methods should be used for objects that can
be used in a FROM clause (getTables) or can be referenced in the select
column list (getColumns). If an index in PostgreSQL doesn't fit in those
categories, then it shouldn't be included (that is what getIndexInfo is
for). Most of the methods in DatabaseMetaData are not primarily for human
consumption, but to discover the structure of a database by a tool that for
example generates queries, etc.

Mark

On Fri, 16 Oct 2015 00:19:04 +0200, Thomas Kellerer <spam_eater@gmx.net>
wrote:
> Wouldn't it make more sense to _exclude_ those types that should
> absolutely not appear there?
>
> I don't really see the point of excluding anything there in the first
place
> to be honest.
>
> This method won't get called "accidently" with some random object name.
> If that is an index (or something else), that's what the user requested.
>
> As indexes and tables share the same namespace there will never be
> any ambiguity which object is meant.
>
> Currently the driver also returns columns for types "create type foo as
> ..."
> I do rely on that as well.
>
> Regards
> Thomas
>
>
> Dave Cramer schrieb am 15.10.2015 um 23:53:
>> That's fine. I'll revert it. But I think the previous issue was that
>> it was also returning indexes as well. I'll just narrow down to
>> tables, views, and mat views
>>
>> Dave Cramer
>>
>> davec@postgresintl.com <mailto:davec@postgresintl.com>
>> www.postgresintl.com <http://www.postgresintl.com>
>>
>> On 15 October 2015 at 17:39, Thomas Kellerer <spam_eater@gmx.net
>> <mailto:spam_eater@gmx.net>> wrote:
>>
>>     All I can say is, that due to the nature of SQL Workbench (being
>>     DBMS independent) I do use (or at least test)
>>     many other DBMS and every[1] other JDBC driver does return view
>>     columns - including materialized views in Oracle and DB2.
>>
>>     So apparently all the other vendors think it makes sense to return
>>     view (and materialized view) columns.
>>
>>     Thomas
>>
>>     [1] I have tested at least briefly more than 15 different ones.

On Fri, 16 Oct 2015 08:50:47 +0200, Thomas Kellerer <spam_eater@gmx.net>
wrote:
> Interesting issue - that is indeed a use-case I didn't think about.
>
> It's too bad that getColumns() doesn't return a "TABLE_TYPE" column that
> would match the types returned by getTableTypes()
>
> However, information_schema.columns does return columns from views, so
> those should at least be included
> (although not getting columns for materialized views or types would mean
I
> had to adjust my SQL Workbench for that)

As I understand it, materialized views are a type of table (or maybe a
special type of view), and therefor should be returned by getTables. A JDBC
driver is free to define additional table types, as long as those types
behave like tables (as in: can be selected). Not sure how that works for
types.

Mark

On 16-10-2015 12:43, Dave Cramer wrote:
> Here is the proposed PR https://github.com/pgjdbc/pgjdbc/pull/403
>
> Please comment

Looks ok to me.

Mark
--
Mark Rotteveel

Dave Cramer schrieb am 16.10.2015 um 12:43:
> Here is the proposed PR https://github.com/pgjdbc/pgjdbc/pull/403
>
> Please comment
>
> Dave Cramer

Looks OK to me although it means I now need to copy the statement
out of the driver in order to retrieve the columns of a type.

But it's still better than having to do that for views, mviews and
foreign tables as well.

Thanks
Thomas