Thread: Connection.setCatalog()
Why isn't setCatalog() implemented? This is similar to the DatabaseMetaData.getCatalogs() which I asked about recently. TheMySQL driver supports it, no reason why we shouldn't. Jason Davies jason@netspade.com
Hi Dave, "catalogs" are the same as "databases" I think. Basically, typing \l at the psql prompt will list them. The MySQL driverlists all the databases when getCatalogs() is called (by the way, Peter E has implemented it in DatabaseMetaData.getCatalogs()when I requested it - see CVS). I think maybe the name is just confusing? Methods like Connection.getCatalog() should return the database which the driver is currently connected to. Connection.setCatalog(Stringdatabase) should connect to the specified database. The DatabaseMetaData.supportsCatalogsInXXX()may need to be modified. I'm not sure about the stuff in DatabaseMetaData.getTables()for example - at the moment specifying null gets all the tables in the database which the driveris currently connected to. I think this is fine - but different database name patterns might be specified and theymay have to be implemented? The MySQL driver implements get/setCatalog() as well as getCatalogs(). Supporting catalogs is necessary for my database toolto list the databases available etc. Thanks, Jason Davies jason@netspade.com [[[ Original Message from Dave Cramer <dave@fastcrypt.com> ]]] > Jason, > > Catalogs are foreign to postgres, can you briefly explain what they are, > and how you expect them to work > > Dave > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of jason@netspade.com > Sent: July 13, 2001 8:14 AM > Subject: [JDBC] Connection.setCatalog() > > > Why isn't setCatalog() implemented? This is similar to the > DatabaseMetaData.getCatalogs() which I asked about recently. The MySQL > driver supports it, no reason why we shouldn't. > > Jason Davies > jason@netspade.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
jason@netspade.com writes: > Connection.setCatalog(String database) should connect to the specified database. The JDBC spec says: : Sets a catalog name in order to select a subspace of this Connection's : database in which to work. If the driver does not support catalogs, it : will silently ignore this request. Note the part about "subspace". In PostgreSQL, the database/catalog is fixed when the connection is established. On other systems you can probably change the database/catalog while keeping the connection. But you cannot establish a new connection if the spec says that this method should make a selection among the objects available in the current connection. > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. These methods are all implemented correctly. > I'm not sure about the stuff in DatabaseMetaData.getTables() for > example - at the moment specifying null gets all the tables in the > database which the driver is currently connected to. I think this is > fine - but different database name patterns might be specified and > they may have to be implemented? Yup. We'll just throw an SQLException in that case. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
[[[ Original Message from Peter Eisentraut <peter_e@gmx.net> ]]] > jason@netspade.com writes: > > > Connection.setCatalog(String database) should connect to the specified database. > > The JDBC spec says: > > : Sets a catalog name in order to select a subspace of this Connection's > : database in which to work. If the driver does not support catalogs, it > : will silently ignore this request. > > Note the part about "subspace". In PostgreSQL, the database/catalog is > fixed when the connection is established. On other systems you can > probably change the database/catalog while keeping the connection. But > you cannot establish a new connection if the spec says that this method > should make a selection among the objects available in the current > connection. What do they mean by database though? It is vague because the definitions are not clear, but I interpret it like this: A"catalog" equals "PostgreSQL database" which is a subset of the whole "database" assuming "database" in this context tomean the whole server. I realise that the "catalog" is fixed but this is also the case for MySQL. Note it doesn't say "incurrent connection" so no reason why you can't create a new one. The bottom line is that supporting them would make lifeeasier for people like me writing a database admin tool. By the way, you've already applied my suggested changes to DatabaseMetaData.getCatalogs() - supporting that would imply supportingDriver.set/getCatalog(). You may want to check what other similar drivers do, but as far as I can see the only reason for not implementing catalogsupport is if you have a database (e.g. Interbase) where you only connect to one "catalog" which is a file location- you can't actually list other available catalogs. I hope that clarifies the way I see it. > > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. > > These methods are all implemented correctly. > > > I'm not sure about the stuff in DatabaseMetaData.getTables() for > > example - at the moment specifying null gets all the tables in the > > database which the driver is currently connected to. I think this is > > fine - but different database name patterns might be specified and > > they may have to be implemented? > > Yup. We'll just throw an SQLException in that case. Yes, that isn't really a problem. But _if_ you want to support catalogs maybe you have to go the whole way? Thanks, Jason Davies jason@netspade.com
jason@netspade.com writes: > What do they mean by database though? It is vague because the > definitions are not clear, but I interpret it like this: A "catalog" > equals "PostgreSQL database" which is a subset of the whole "database" > assuming "database" in this context to mean the whole server. I > realise that the "catalog" is fixed but this is also the case for > MySQL. Note it doesn't say "in current connection" so no reason why > you can't create a new one. The bottom line is that supporting them > would make life easier for people like me writing a database admin > tool. This seems like a reasonable interpretation. Given that the alternative would be to not implement it at all, I would agree if you'd implement it as suggested. > > > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. > > > > These methods are all implemented correctly. > > > > > I'm not sure about the stuff in DatabaseMetaData.getTables() for > > > example - at the moment specifying null gets all the tables in the > > > database which the driver is currently connected to. I think this is > > > fine - but different database name patterns might be specified and > > > they may have to be implemented? > > > > Yup. We'll just throw an SQLException in that case. > > Yes, that isn't really a problem. But _if_ you want to support catalogs maybe you have to go the whole way? Do you mean that DatabaseMetaData.getTables() in a catalog other than the current one should reset the connection to the new catalog? That sounds a bit too revolutionary to me, but then again, as above, the alternative is to not allow it at all. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
[[[ Original Message from Peter Eisentraut <peter_e@gmx.net> ]]] > jason@netspade.com writes: > > > What do they mean by database though? It is vague because the > > definitions are not clear, but I interpret it like this: A "catalog" > > equals "PostgreSQL database" which is a subset of the whole "database" > > assuming "database" in this context to mean the whole server. I > > realise that the "catalog" is fixed but this is also the case for > > MySQL. Note it doesn't say "in current connection" so no reason why > > you can't create a new one. The bottom line is that supporting them > > would make life easier for people like me writing a database admin > > tool. > > This seems like a reasonable interpretation. Given that the alternative > would be to not implement it at all, I would agree if you'd implement it > as suggested. Great, thanks. Here is a context diff against current CVS for org.postgresql.Connection.get/setCatalog() - is this the correctplace to implement the methods? - the no-op methods were replicated in both jdbc1 and jdbc2 subclasses. I've madethe diff remove the no-op methods in the jdbc1 and jdbc2 subclasses. > > > > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. > > > > > > These methods are all implemented correctly. > > > > > > > I'm not sure about the stuff in DatabaseMetaData.getTables() for > > > > example - at the moment specifying null gets all the tables in the > > > > database which the driver is currently connected to. I think this is > > > > fine - but different database name patterns might be specified and > > > > they may have to be implemented? > > > > > > Yup. We'll just throw an SQLException in that case. > > > > Yes, that isn't really a problem. But _if_ you want to support catalogs maybe > you have to go the whole way? > > Do you mean that DatabaseMetaData.getTables() in a catalog other than the > current one should reset the connection to the new catalog? That sounds a > bit too revolutionary to me, but then again, as above, the alternative is > to not allow it at all. Yes. I agree that it's a bit pointless - it would make life easier for everyone if it was not allowed at all. Do you knowwhether PostgreSQL will support the catalogname.tablename syntax in SQL queries in the future? At the moment it seemsas if catalogs are kinda half supported - you can list all catalogs on the system but you can't manipulate between themeasily. For now at least I suggest we leave these methods alone. Thanks, Jason > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter >
Attachment
jason@netspade.com writes:
> Do you know whether PostgreSQL will support the catalogname.tablename
> syntax in SQL queries in the future?
Schema support is on the to-do list. It's probably too late to imagine
that it will get done for 7.2, but maybe for 7.3.
Although the details haven't been argued out yet, I suspect that the
existing concept of independent databases within an installation will
remain in place (for backwards compatibility if nothing else).
SQL-style schemas and catalogs will exist as new naming levels
*within* what we now call a database.
Peter E. has previously commented that Postgres databases correspond
most closely to the SQL concept of "catalog cluster", not "catalog".
This agrees with my reading of SQL92 4.13:
A cluster is an implementation-defined collection of catalogs.
Exactly one cluster is associated with an SQL-session and it
defines the totality of the SQL-data that is available to that
SQL-session.
Schemas and catalogs will exist within a database, and there will
still be no possibility of cross-database accesses (but, hopefully,
much less need for it either).
While I haven't followed this discussion closely, it appears to me that
you are trying to make setCatalog reconnect to a new database --- ie, a
new cluster in SQL terminology. I think this is a bad idea, as it will
create a backwards compatibility problem once we have actual catalogs
for setCatalog to work with. I'd suggest that the right short-term
thing is for setCatalog to either do nothing or throw an error.
regards, tom lane
[[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]] > jason@netspade.com writes: > > Do you know whether PostgreSQL will support the catalogname.tablename > > syntax in SQL queries in the future? > > Schema support is on the to-do list. It's probably too late to imagine > that it will get done for 7.2, but maybe for 7.3. > > Although the details haven't been argued out yet, I suspect that the > existing concept of independent databases within an installation will > remain in place (for backwards compatibility if nothing else). > SQL-style schemas and catalogs will exist as new naming levels > *within* what we now call a database. I see - I didn't realise this. As this is the case, there is no need for my database tool to list available databases sincedatabases in postgresql are clusters. Thanks for clarifying that. > Peter E. has previously commented that Postgres databases correspond > most closely to the SQL concept of "catalog cluster", not "catalog". > This agrees with my reading of SQL92 4.13: > > A cluster is an implementation-defined collection of catalogs. > Exactly one cluster is associated with an SQL-session and it > defines the totality of the SQL-data that is available to that > SQL-session. > > Schemas and catalogs will exist within a database, and there will > still be no possibility of cross-database accesses (but, hopefully, > much less need for it either). Good. As long as everything conforms to some kind of standard we're okay :) > While I haven't followed this discussion closely, it appears to me that > you are trying to make setCatalog reconnect to a new database --- ie, a > new cluster in SQL terminology. I think this is a bad idea, as it will > create a backwards compatibility problem once we have actual catalogs > for setCatalog to work with. I'd suggest that the right short-term > thing is for setCatalog to either do nothing or throw an error. I agree. In which case the update Peter E did to DatabaseMetaData.getCatalogs() should be backed out. At least I've learnthow to use CVS and how to make context diffs :) > > regards, tom lane Jason Davies jason@netspade.com
Tom Lane writes:
> Peter E. has previously commented that Postgres databases correspond
> most closely to the SQL concept of "catalog cluster", not "catalog".
I most certainly did not. According to my interpretation:
schema = schema
catalog = database
cluster = thing you get from initdb
This is also how we currently document it and it tends to be the practice
in other products as well.
> This agrees with my reading of SQL92 4.13:
>
> A cluster is an implementation-defined collection of catalogs.
> Exactly one cluster is associated with an SQL-session and it
> defines the totality of the SQL-data that is available to that
> SQL-session.
Yes, the stuff served by a single postmaster is the totality of the
SQL data available to that SQL session. But note:
The method of creation and destruction of
catalogs is implementation-defined. The set of catalogs that
can be referenced in any SQL-statement, during any particular
SQL-transaction, or during the course of an SQL-session is also
implementation-defined.
(just above your stuff)
which serves us just fine.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Peter E. has previously commented that Postgres databases correspond
>> most closely to the SQL concept of "catalog cluster", not "catalog".
> I most certainly did not. According to my interpretation:
I sit corrected. If you want to define catalog == database, okay with
me.
regards, tom lane
[[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]] > Peter Eisentraut <peter_e@gmx.net> writes: > > Tom Lane writes: > >> Peter E. has previously commented that Postgres databases correspond > >> most closely to the SQL concept of "catalog cluster", not "catalog". > > > I most certainly did not. According to my interpretation: > > I sit corrected. If you want to define catalog == database, okay with > me. > > regards, tom lane Great, here is a context diff of CVS for implementing the get/setCatalog methods in Connection - note: I've updated setCatalog(Stringcatalog) from my previous diff so it checks whether it is already connected to the specified catalog. Thanks, Jason Davies jason@netspade.com
Attachment
OK, seems like this is the final one to be applied.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
> [[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]]
>
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > Tom Lane writes:
> > >> Peter E. has previously commented that Postgres databases correspond
> > >> most closely to the SQL concept of "catalog cluster", not "catalog".
> >
> > > I most certainly did not. According to my interpretation:
> >
> > I sit corrected. If you want to define catalog == database, okay with
> > me.
> >
> > regards, tom lane
>
> Great, here is a context diff of CVS for implementing the get/setCatalog methods in Connection - note: I've updated
setCatalog(Stringcatalog) from my previous diff so it checks whether it is already connected to the specified catalog.
>
> Thanks,
>
> Jason Davies
>
> jason@netspade.com
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Thanks. > [[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]] > > > Peter Eisentraut <peter_e@gmx.net> writes: > > > Tom Lane writes: > > >> Peter E. has previously commented that Postgres databases correspond > > >> most closely to the SQL concept of "catalog cluster", not "catalog". > > > > > I most certainly did not. According to my interpretation: > > > > I sit corrected. If you want to define catalog == database, okay with > > me. > > > > regards, tom lane > > Great, here is a context diff of CVS for implementing the get/setCatalog methods in Connection - note: I've updated setCatalog(Stringcatalog) from my previous diff so it checks whether it is already connected to the specified catalog. > > Thanks, > > Jason Davies > > jason@netspade.com [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
[[[ Original Message from Barry Lind <blind@xythos.com> ]]] > > I don't like this patch. Nothing wrong with the code, but with what is > being done. I wouldn't expect the setCatalog method to end my current > transaction and quietly reconect me to another database. In this new > connection my auto comit status will be reset and there could be other > side effects I haven't yet thought of. What happens if the current user > doesn't have permission to connect to the new database? It's the only way at the moment until catalogs are sorted out properly. If the current user doesn't have permission to connectto the new database an SQLException will be thrown. I don't have any experience with auto commit stuff but if youdon't want it to be reset then simply don't use setCatalog. > Also are there any security issues with having a client being able to > discover all databases and then going and listing all tables/objects in > them? Well, that doesn't depend on the driver implementation. > If we can't come up with a secure way of querying this information > without reconnecting the connection, then I think we should not > implement setCatalog. Basically it just imitates the \c command in the pgsql command line interface. Ideally there should be a way to continueusing the current connection, like the USE command in MySQL. BTW, this is how the mm.mysql driver implements setCatalog()- it just calls "USE catalogname". > BTW Does anyone know what the Oracle driver does with catalogs, since > they also don't have such a concept natively. I am especially > interested in if they support Connection.setCatalog(). AFAIK Oracle doesn't support any of the catalogs stuff at all and Connection.setCatalog() is ignored. Jason Davies jason@netspade.com > --Barry > > jason@netspade.com wrote: > > > Date: Thu, 19 Jul 2001 14:48:45 -0500 > > From: jason@netspade.com > > To: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net> > > Subject: Re: Connection.setCatalog() > > > > [[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]] > > > >> Peter Eisentraut <peter_e@gmx.net> writes: > >> > Tom Lane writes: > >> >> Peter E. has previously commented that Postgres databases correspond > >> >> most closely to the SQL concept of "catalog cluster", not "catalog". > >> > >> > I most certainly did not. According to my interpretation: > >> > >> I sit corrected. If you want to define catalog == database, okay with > >> me. > >> > >> regards, tom lane > > > > Great, here is a context diff of CVS for implementing the > > get/setCatalog methods in Connection - note: I've updated > > setCatalog(String catalog) from my previous diff so it checks whether > > it is already connected to the specified catalog. > > > > Thanks, > > > > Jason Davies > > > > jason@netspade.com > > [Connection.diff] > > [attachment omitted] > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html >
I don't like this patch. Nothing wrong with the code, but with what is being done. I wouldn't expect the setCatalog method to end my current transaction and quietly reconect me to another database. In this new connection my auto comit status will be reset and there could be other side effects I haven't yet thought of. What happens if the current user doesn't have permission to connect to the new database? Also are there any security issues with having a client being able to discover all databases and then going and listing all tables/objects in them? If we can't come up with a secure way of querying this information without reconnecting the connection, then I think we should not implement setCatalog. BTW Does anyone know what the Oracle driver does with catalogs, since they also don't have such a concept natively. I am especially interested in if they support Connection.setCatalog(). --Barry jason@netspade.com wrote: > Date: Thu, 19 Jul 2001 14:48:45 -0500 > From: jason@netspade.com > To: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net> > Subject: Re: Connection.setCatalog() > > [[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]] > >> Peter Eisentraut <peter_e@gmx.net> writes: >> > Tom Lane writes: >> >> Peter E. has previously commented that Postgres databases correspond >> >> most closely to the SQL concept of "catalog cluster", not "catalog". >> >> > I most certainly did not. According to my interpretation: >> >> I sit corrected. If you want to define catalog == database, okay with >> me. >> >> regards, tom lane > > Great, here is a context diff of CVS for implementing the > get/setCatalog methods in Connection - note: I've updated > setCatalog(String catalog) from my previous diff so it checks whether > it is already connected to the specified catalog. > > Thanks, > > Jason Davies > > jason@netspade.com > [Connection.diff] > [attachment omitted] > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
I am just seeing this message now. Does this patch need to be backed out? > > I don't like this patch. Nothing wrong with the code, but with what is > being done. I wouldn't expect the setCatalog method to end my current > transaction and quietly reconect me to another database. In this new > connection my auto comit status will be reset and there could be other > side effects I haven't yet thought of. What happens if the current user > doesn't have permission to connect to the new database? > > Also are there any security issues with having a client being able to > discover all databases and then going and listing all tables/objects in > them? > > If we can't come up with a secure way of querying this information > without reconnecting the connection, then I think we should not > implement setCatalog. > > BTW Does anyone know what the Oracle driver does with catalogs, since > they also don't have such a concept natively. I am especially > interested in if they support Connection.setCatalog(). > --Barry > > jason@netspade.com wrote: > > > Date: Thu, 19 Jul 2001 14:48:45 -0500 > > From: jason@netspade.com > > To: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net> > > Subject: Re: Connection.setCatalog() > > > > [[[ Original Message from Tom Lane <tgl@sss.pgh.pa.us> ]]] > > > >> Peter Eisentraut <peter_e@gmx.net> writes: > >> > Tom Lane writes: > >> >> Peter E. has previously commented that Postgres databases correspond > >> >> most closely to the SQL concept of "catalog cluster", not "catalog". > >> > >> > I most certainly did not. According to my interpretation: > >> > >> I sit corrected. If you want to define catalog == database, okay with > >> me. > >> > >> regards, tom lane > > > > Great, here is a context diff of CVS for implementing the > > get/setCatalog methods in Connection - note: I've updated > > setCatalog(String catalog) from my previous diff so it checks whether > > it is already connected to the specified catalog. > > > > Thanks, > > > > Jason Davies > > > > jason@netspade.com > > [Connection.diff] > > [attachment omitted] > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026