Re: Connection.setCatalog method - Mailing list pgsql-jdbc

That patch wasn't applied because I vetoed it.  I am unhappy with the
logic as it appeared in that patch, but due to the limitations of the
server I don't know of anyway to implement setCatalog().

There are two problems I have with the patch as it was originally submitted:

1) Transaction problems.  Lets say I am in the middle of a transaction
when I call setCatalog(), since the implementation simply closes the
current connection and opens a new one, my in process transaction is
aborted.  Which is certainly against the spec.  In general, I don't like
the patch because is under the covers replaces the existing connection
with a new one that can have a whole slew of side effects that are not
addressed (what happens to existing statement objects? existing result
sets? existing database meta data objects?).

2) The patch makes the invalid assumption that just because the current
username/password lets me connect to database A, it will also let me
connect to database B.  Thus calling setCatalog() if you have security
setup will leave your connection hosed.

Finally while it may seem on the surface that postgresql databases are
the same thing as jdbc catalogs, there are many differences.  Enough
that in my opinion they should not be considered the same thing.  For
example:

DatabaseMetaData.isCatalogAtStart() says:
* Retrieves whether a catalog appears at the start of a fully qualified
* table name.  If not, the catalog appears at the end.
Thus the jdbc concept of catalog also is a namespace concept (ala
database.schema.table which postgres doesn't support).

DatabaseMetaData.get*() methods that take a catalog parameter:
All of these methods assume that you can get information from any
catalog/database.  But in postgres that isn't true.  You can only get
meta information for the database you are currently connected to.  And
because of the issue #2 above there really isn't any way to get
information about other databases objects.


Now I am certainly not against enhanceing postgres to support the jdbc
view of catalogs, but I think it is much more work and likely will need
to be done very differently than the below mentioned patch does things.

thanks,
--Barry



Fernando Nasser wrote:
> Yes, setCatalog() should switch the connection to another database (it
> can only be ignored if the driver does not support catalogs, which is
> not the case).
>
> I have no idea why that patch was not applied.  Could not find anything
> after the thread you've pointed to.
>
> Regards,
> Fernando
>
>
> Philip A. Chapman wrote:
>
>> Everyone,
>>
>> I am working on yet another database administration application.  I have
>> run into some issues because the JDBC implementation of
>> Connection.setCatalog()
>> (org.postgresq.jdbc1.AbstractJdbc1Connection.java) does nothing when
>> setCatalog() is called.  My understanding is that doing nothing is
>> allowed by the JDBC specification, but this is somewhat confusing as
>> getCatalog() returns a value and DatabaseMetaData.getCatalogs() returns
>> a list of values.
>>
>> While searching though this list's archives, I found a thread from July
>> of 2001 that deals with this subject.  Toward the end of the thread, it
>> appears as though it was decided that the setCatalog() functionallity
>> should be implemented and that a patch was to be accepted to make this
>> happen:
>>
>> http://archives.postgresql.org/pgsql-jdbc/2001-07/msg00150.php
>>
>> What has become of this?  Can someone shed some light on why
>> Connection.getCatalog and DatabaseMetaData.getCatalogs() work, but
>> Connection.setCatalog does not?
>
>
>