Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 6:10 PM, li...@mgreg.com wrote: What do you mean when you say the "catalogs...are database- specific" ? If I'm understanding what you're saying, my whole point is that I don't want to be tied to a database to do any kind of querying about the PG engine itself. Does that make sense? Look at it from the other side; You have a DBMS and you want to store information about what databases and which users are available, who can and can't connect, etc. It makes sense to store that in a database, right? To request that information you need to connect to the database server. Considering that information is stored in a database, having to specify that database to connect to makes sense. Whether that's a named database (with a documented fixed name of course, in this case 'template1' or 'postgres') or an anonymous database doesn't make much difference. You'll still have to specify several other connection parameters (host & port at least), so why not also a valid user (quite desirable from a security point of view) and a database name? It may not make as much sense from a user point of view, but it makes a lot of sense from a database point of view. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49e1ca98129741055947028! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
Sam Mason writes: > As others have said; the design of PG is such that it's built to assume > you're always connected to exactly one database. I'd guess this is an > artifact from a long time ago when PG didn't have multiple databases. It's possible that that was true way back in Berkeley prehistory; there is no one around the project now that would remember (unless maybe Elein does). But the key points here are that critical catalogs like pg_class and pg_proc are per-database, which is a good thing for quite a number of reasons, and PG is sufficiently catalog-driven that it's literally impossible for the engine to do anything useful without having a set of those catalogs available. (Offhand, the only user-visible functionality I can think of that isn't catalog-dependent is the GUC parameters, ie SET/SHOW; and even within that there are some individual parameters that can't meaningfully be set without catalog access.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 1:49 PM, Sam Mason wrote: As others have said; the design of PG is such that it's built to assume you're always connected to exactly one database. I'd guess this is an artifact from a long time ago when PG didn't have multiple databases. -- Sam http://samason.me.uk/ Yes, I believe that this gets at the heart of some of the issue. I think there is some confusion on both sides. Basically, I was under the impression that PG had some overarching database server to which one could connect without needing to specify a database. While, yes, this might be "unremarkable", it is still an extra constraint for no *obvious* reason. I could understand if it was an *option* based on the need for security, etc., but the strict enforcement of this paradigm seems a bit dated. Perhaps the docs make reference as to why, and I simply haven't come across it yet. That said, "psql" provides me with what I need for now. Thanks, Michael
Re: [GENERAL] Connect without specifying a database?
On Sat, Apr 11, 2009 at 01:15:44PM -0400, li...@mgreg.com wrote: > > On Apr 11, 2009, at 12:56 PM, Tom Lane wrote: > > > >There is no such edge case. DROP DATABASE has to be issued while > >connected to some database, and it won't let you drop the DB you're > >connected to. > > > >And CREATE DATABASE has to be issued while connected to some database, > >so createdb still has to have a default database to connect to. There > >really is no state in Postgres corresponding to "connected but not > >connected to any particular database". > > > >It does all hang together. You will need to lose a lot of MySQL > >preconceptions along the way, I fear. > > > > regards, tom lane > > > I think our first issue is semantics and our second is paradigm. > Hopefully I'm simply misunderstanding what you're saying, but what > sense does it make to have to connect to an unrelated DB in order to > query about others? Because most of the time you don't need to do this; the user will have specified the parameters (or they will be known some other way, i.e. defaults) and you'll just connect like you do in every other database by specifying a connection string. > Basically, I have some applications that will simply use PG as a > backend. That application needs to ask the engine manager (whatever > that means in in postgres speak) and see if relevant databases already > exist. If they don't then it needs to create them. This is a bad precedent to set; is somebody accidentally points it at the wrong place it should complain there's nothing there and fail to start. Creating things should normally only be with explicit consent from the user. > So, how does > needing to connect to a database before querying about existing > databases make any sense? MySQL aside, it seems an extra constraint/ > step for naught. Yes it does, but it's rarely a problem in practise. > Perhaps I asked the wrong question in the beginning -- I do > apologize. Maybe I should have asked for an external application that > has the ability to talk to the PG engine. I believe John R. Pierce > provided me with what I need in his last post -- that of listing DBs > via a "psql -l". As Adrian said, all psql -l does is to connect to the "postgres" database and run the following SQL: SELECT d.datname as "Name", r.rolname as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid ORDER BY 1; As others have said; the design of PG is such that it's built to assume you're always connected to exactly one database. I'd guess this is an artifact from a long time ago when PG didn't have multiple databases. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 10:15 AM, li...@mgreg.com wrote: So, how does needing to connect to a database before querying about existing databases make any sense? Well, you have to connect to the database server, no matter what, in order to check on the existence of a database (unless you are doing it by groveling around in the data directories). The fact that you have to supply a database as part of that connection seems unremarkable. Suppose the documentation said something along these lines: "All connections are to a specific database. For operations in which a user-created database would be inappropriate (such as checking for the existence of a user-created database), the database 'root' is created by initdb as a known default for such operations." That would seem to be utterly uncontroversial and normal, yes? OK s/root/template1/, and you're set. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Saturday 11 April 2009 10:15:44 am li...@mgreg.com wrote: > On Apr 11, 2009, at 12:56 PM, Tom Lane wrote: > > There is no such edge case. DROP DATABASE has to be issued while > > connected to some database, and it won't let you drop the DB you're > > connected to. > > > > And CREATE DATABASE has to be issued while connected to some database, > > so createdb still has to have a default database to connect to. There > > really is no state in Postgres corresponding to "connected but not > > connected to any particular database". > > > > It does all hang together. You will need to lose a lot of MySQL > > preconceptions along the way, I fear. > > > > regards, tom lane > > I think our first issue is semantics and our second is paradigm. > Hopefully I'm simply misunderstanding what you're saying, but what > sense does it make to have to connect to an unrelated DB in order to > query about others? > > Basically, I have some applications that will simply use PG as a > backend. That application needs to ask the engine manager (whatever > that means in in postgres speak) and see if relevant databases already > exist. If they don't then it needs to create them. So, how does > needing to connect to a database before querying about existing > databases make any sense? MySQL aside, it seems an extra constraint/ > step for naught. > > Perhaps I asked the wrong question in the beginning -- I do > apologize. Maybe I should have asked for an external application that > has the ability to talk to the PG engine. I believe John R. Pierce > provided me with what I need in his last post -- that of listing DBs > via a "psql -l". I believe that assumes the 'postgres' database is present. The problem is fairly simple no database == no connection. > > > Thanks, > Michael -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 12:56 PM, Tom Lane wrote: There is no such edge case. DROP DATABASE has to be issued while connected to some database, and it won't let you drop the DB you're connected to. And CREATE DATABASE has to be issued while connected to some database, so createdb still has to have a default database to connect to. There really is no state in Postgres corresponding to "connected but not connected to any particular database". It does all hang together. You will need to lose a lot of MySQL preconceptions along the way, I fear. regards, tom lane I think our first issue is semantics and our second is paradigm. Hopefully I'm simply misunderstanding what you're saying, but what sense does it make to have to connect to an unrelated DB in order to query about others? Basically, I have some applications that will simply use PG as a backend. That application needs to ask the engine manager (whatever that means in in postgres speak) and see if relevant databases already exist. If they don't then it needs to create them. So, how does needing to connect to a database before querying about existing databases make any sense? MySQL aside, it seems an extra constraint/ step for naught. Perhaps I asked the wrong question in the beginning -- I do apologize. Maybe I should have asked for an external application that has the ability to talk to the PG engine. I believe John R. Pierce provided me with what I need in his last post -- that of listing DBs via a "psql -l". Thanks, Michael
Re: [GENERAL] Connect without specifying a database?
[ forgot to respond to this bit... ] "li...@mgreg.com" writes: > What if you hit that edge case where there are in fact no databases? There is no such edge case. DROP DATABASE has to be issued while connected to some database, and it won't let you drop the DB you're connected to. > I suppose you could > always run "createdb" just in case before you do anything else, but > that seems counterintuitive. And CREATE DATABASE has to be issued while connected to some database, so createdb still has to have a default database to connect to. There really is no state in Postgres corresponding to "connected but not connected to any particular database". It does all hang together. You will need to lose a lot of MySQL preconceptions along the way, I fear. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
li...@mgreg.com wrote:
I'm sorry, I'm confused by your reply. I may have said "table" when I
meant "database". I believe it absolutely *does* make sense to
connect without specifying a database first. What if you hit that
edge case where there are in fact no databases? I suppose you could
always run "createdb" just in case before you do anything else, but
that seems counterintuitive.
that 'edge case' is one in which postgres isn't running. you have to
run initdb to initialize the database cluster before you can start the
postmaster. initdb creates template0, template1, and, in 8.x and newer,
postgres databases
the postgres database administrator account can do...
$ psql -l
to list all databases, or...
$ psql
postgres=# \l
or, in sql, while connected to {template1 | template0 | postgres | ... }
select datname,... from pg_catalog.pg_database;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
"li...@mgreg.com" writes: > What do you mean when you say the "catalogs...are database- > specific" ? If I'm understanding what you're saying, my whole point > is that I don't want to be tied to a database to do any kind of > querying about the PG engine itself. Does that make sense? No, it does not. This is perhaps an implementation quirk of Postgres's, but it does have some advantages and we're not interested in giving them up just because it confuses MySQLers ;-). The main advantage is that having separate catalogs in each database is more robust (no matter how badly database A gets messed up, database B will be okay) and reduces contention for catalog access. There are a few catalogs that are visible in all databases of an installation (pg_database itself being the most obvious one) but they are not sufficient to support an operational backend. So you have to connect to some database even to query those catalogs. We could have a convention that there is some database that you connect to only for the purpose of inspecting pg_database, but there doesn't seem a whole lot of point in trying to enforce that. The standard databases serve well enough. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Saturday 11 April 2009 9:10:33 am li...@mgreg.com wrote: > On Apr 11, 2009, at 11:56 AM, Tom Lane wrote: > > There are, although we don't spell the commands like that. This is > > not > > relevant to the question of whether it makes sense to connect without > > being connected to a specific database. It doesn't, because the > > catalogs that you have to inspect to find out anything are > > database-specific. > > > > regards, tom lane > > I'm sorry, I'm confused by your reply. I may have said "table" when I > meant "database". I believe it absolutely *does* make sense to > connect without specifying a database first. What if you hit that > edge case where there are in fact no databases? I suppose you could > always run "createdb" just in case before you do anything else, but > that seems counterintuitive. > > What do you mean when you say the "catalogs...are database- > specific" ? If I'm understanding what you're saying, my whole point > is that I don't want to be tied to a database to do any kind of > querying about the PG engine itself. Does that make sense? > > Best, > Michael Might try looking at the pg_database file in ~/DATADIR/global. It lists the databases in the cluster. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 11:56 AM, Tom Lane wrote: There are, although we don't spell the commands like that. This is not relevant to the question of whether it makes sense to connect without being connected to a specific database. It doesn't, because the catalogs that you have to inspect to find out anything are database-specific. regards, tom lane I'm sorry, I'm confused by your reply. I may have said "table" when I meant "database". I believe it absolutely *does* make sense to connect without specifying a database first. What if you hit that edge case where there are in fact no databases? I suppose you could always run "createdb" just in case before you do anything else, but that seems counterintuitive. What do you mean when you say the "catalogs...are database- specific" ? If I'm understanding what you're saying, my whole point is that I don't want to be tied to a database to do any kind of querying about the PG engine itself. Does that make sense? Best, Michael
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 10:49 AM, li...@mgreg.com wrote: On Apr 11, 2009, at 11:39 AM, Raymond O'Donnell wrote: The answer is still "no". :-) The usual thing it to connect to the "postgres" database (or to "template1" in older versions of PG), and then you can issue queries to see what's there. You're pretty much guaranteed that one of those databases will exist (they're created by initdb), unless whoever installed the server did something strange. Ray. Hrm...Ok, well, for the record, I'm moving some systems from MySQL to Postgres and am in the process of getting familiar with it. I was hoping there were some higher level management items like "show databases", and "show tables", etc in Postgres as well. I also didn't want to depend on there being a table there that I needed to "get in the door" with, so to speak. I'm guessing no one in the community sees this addition as desirable (or feasible)? PostgreSQL works at some points slightly different, however connecting to a database weather it's template1 or postgres makes sense once you know a bit more about PostgreSQL's internals. Ries Thanks for the replies, Michael regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nl web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196 SIP: +1-747-690-5133
Re: [GENERAL] Connect without specifying a database?
On Sat, Apr 11, 2009 at 11:49:25AM -0400, li...@mgreg.com wrote: > I was > hoping there were some higher level management items like "show > databases", and "show tables", etc in Postgres as well. The standard way would be to use the "information_schema", it's in the SQL standard and supported by more than just one database engine. > I also didn't > want to depend on there being a table there that I needed to "get in > the door" with, so to speak. I'm guessing no one in the community > sees this addition as desirable (or feasible)? Anything can be done, it's just getting people enthusiastic enough to do it! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
"li...@mgreg.com" writes: > Hrm...Ok, well, for the record, I'm moving some systems from MySQL to > Postgres and am in the process of getting familiar with it. I was > hoping there were some higher level management items like "show > databases", and "show tables", etc in Postgres as well. There are, although we don't spell the commands like that. This is not relevant to the question of whether it makes sense to connect without being connected to a specific database. It doesn't, because the catalogs that you have to inspect to find out anything are database-specific. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 11:39 AM, Raymond O'Donnell wrote: The answer is still "no". :-) The usual thing it to connect to the "postgres" database (or to "template1" in older versions of PG), and then you can issue queries to see what's there. You're pretty much guaranteed that one of those databases will exist (they're created by initdb), unless whoever installed the server did something strange. Ray. Hrm...Ok, well, for the record, I'm moving some systems from MySQL to Postgres and am in the process of getting familiar with it. I was hoping there were some higher level management items like "show databases", and "show tables", etc in Postgres as well. I also didn't want to depend on there being a table there that I needed to "get in the door" with, so to speak. I'm guessing no one in the community sees this addition as desirable (or feasible)? Thanks for the replies, Michael
Re: [GENERAL] Connect without specifying a database?
On 11/04/2009 16:30, li...@mgreg.com wrote: > Quick question: Does PGSQL provide a mechanism by which to connect to > the server without specifying a database? I saw a thread back in 2005 > that said "no" > (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php), > but I was curious as to whether that's changed. Basically I need to be > able to dynamically determine what databases exist etc before performing > certain actions in my application. The answer is still "no". :-) The usual thing it to connect to the "postgres" database (or to "template1" in older versions of PG), and then you can issue queries to see what's there. You're pretty much guaranteed that one of those databases will exist (they're created by initdb), unless whoever installed the server did something strange. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Sat, Apr 11, 2009 at 11:30:00AM -0400, li...@mgreg.com wrote: > Quick question: Does PGSQL provide a mechanism by which to connect to > the server without specifying a database? I saw a thread back in 2005 > that said "no" > (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php ), > but I was curious as to whether that's changed. Basically I need to be > able to dynamically determine what databases exist etc before performing > certain actions in my application. What's wrong with "template1" as in the above thread? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connect without specifying a database?
Hi All, Quick question: Does PGSQL provide a mechanism by which to connect to the server without specifying a database? I saw a thread back in 2005 that said "no" (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php ), but I was curious as to whether that's changed. Basically I need to be able to dynamically determine what databases exist etc before performing certain actions in my application. Thanks, Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
