Thread: Multiple backend connections
I've written code to connect to and/or create databases in C. To check my work, I'll open up psql in another console. But if I'm connected to the default database (template1), and attempt to connect to that database in my C program, it fails. What gives? Is there a limitation of some kind I'm missing? Paul
Paul M Foster <paulf@quillandmouse.com> writes: > I've written code to connect to and/or create databases in C. To check > my work, I'll open up psql in another console. But if I'm connected to > the default database (template1), and attempt to connect to that > database in my C program, it fails. This shouldn't happen, unless you're at your connection limit for Postgres (32 by default), or running out of kernel file descriptors or something. What is the exact error you're getting in the C program? -Doug
On Wed, 19 Mar 2003, Paul M Foster wrote: > I've written code to connect to and/or create databases in C. To check > my work, I'll open up psql in another console. But if I'm connected to > the default database (template1), and attempt to connect to that > database in my C program, it fails. > > What gives? Is there a limitation of some kind I'm missing? Well possibly but unlikely if that's all you've got going on and you've not tuned the configuration to silly levels. It might help to review the error message that it's giving. > > Paul -- Nigel J. Andrews
On Wed, Mar 19, 2003 at 06:39:06PM -0500, Doug McNaught wrote: > Paul M Foster <paulf@quillandmouse.com> writes: > > > I've written code to connect to and/or create databases in C. To check > > my work, I'll open up psql in another console. But if I'm connected to > > the default database (template1), and attempt to connect to that > > database in my C program, it fails. > > This shouldn't happen, unless you're at your connection limit for > Postgres (32 by default), or running out of kernel file descriptors or > something. > > What is the exact error you're getting in the C program? Well, in a C program, you don't get an error message per se. But I inserted a call to PQresultErrorMessage() and the result is: ERROR: CREATE DATABASE: source database "template1" is being accessed by other users The circumstances are that in one console, I'm connected in psql to the template1 database. In a different console (same username) my C program attempts to connect to a non-existent database. Failing this, it then attempts to connect to template1 in order to create the other database. But it can't connect to template1. This is a Debian 3.0r1 workstation, running PostgreSQL 7.2.1. Paul P.S. BTW, I'm s*bscr*bed, so no need to echo lists posts offlist to me.
Paul M Foster <paulf@quillandmouse.com> writes: > On Wed, Mar 19, 2003 at 06:39:06PM -0500, Doug McNaught wrote: > > > Paul M Foster <paulf@quillandmouse.com> writes: > > > > What is the exact error you're getting in the C program? > > Well, in a C program, you don't get an error message per se. But I > inserted a call to PQresultErrorMessage() and the result is: > > ERROR: CREATE DATABASE: source database "template1" is being accessed by > other users Yeah, I get the same thing if I sit in template1 and try to 'createdb' in another window. I think the answer is "don't do that then." I don't know why it cares, but it obviously does. template1 is copied when a new DB is created, but I'd think as long as there are no transactions open on it, it'd be safe to copy even if someone is connected to it. I'm probably wrong though. Solution: don't use template1 for anything, and avoid connecting to it if you want CREATE DATABASE to succeeed. > P.S. BTW, I'm s*bscr*bed, so no need to echo lists posts offlist to me. Habit, from back when the list used to take hours to deliver messages... -Doug
Paul M Foster <paulf@quillandmouse.com> writes:
>> What is the exact error you're getting in the C program?
> ERROR: CREATE DATABASE: source database "template1" is being accessed by
> other users
Please note this is CREATE DATABASE complaining, *not* connection
establishment. You can't create a database by cloning an open database
(there are not locks that would make this safe). Either don't use
template1 for normal operations, or use template0 as the template for
CREATE DATABASE.
regards, tom lane
On Wed, Mar 19, 2003 at 11:35:53PM -0500, Tom Lane wrote: > Paul M Foster <paulf@quillandmouse.com> writes: > >> What is the exact error you're getting in the C program? > > > ERROR: CREATE DATABASE: source database "template1" is being accessed by > > other users > > Please note this is CREATE DATABASE complaining, *not* connection > establishment. You can't create a database by cloning an open database > (there are not locks that would make this safe). Either don't use > template1 for normal operations, or use template0 as the template for > CREATE DATABASE. Excuse my ignorance here. I'll summarize, and someone can tell me if I'm wrong. 1. So when you create a database, the system uses the open database you're connected to as a "template" unless you specify otherwise with a "WITH TEMPLATE = " clause? 2. Since template0 does not accept connections, can I assume that it exists for the sole purpose of acting as a template for the creation of other databases? 3. Am I correct in assuming that you need to specify a database in order to connect to PostgreSQL, and that, on a "virgin" system, template1 is the database provided for that purpose? If it isn't necessary to specify a database in order to connect, how would you otherwise do it? Paul
Paul M Foster <paulf@quillandmouse.com> writes: > Excuse my ignorance here. > > I'll summarize, and someone can tell me if I'm wrong. > > 1. So when you create a database, the system uses the open database > you're connected to as a "template" unless you specify otherwise with a > "WITH TEMPLATE = " clause? No, it uses template1. > 2. Since template0 does not accept connections, can I assume that it > exists for the sole purpose of acting as a template for the creation of > other databases? template1 is the default template. You can add types, PLs etc to it and they will be copied to any other databases you create. template0 is the 'virgin' template, for use if you mess up template1 or if you want to create a database without all the extra stuff you put in template1. > 3. Am I correct in assuming that you need to specify a database in order > to connect to PostgreSQL, and that, on a "virgin" system, template1 is > the database provided for that purpose? If it isn't necessary to specify > a database in order to connect, how would you otherwise do it? You are correct. The best practice is to stay out of template1 unless you are creating a new database or adding stuff to template1. -Doug
Doug McNaught <doug@mcnaught.org> writes:
> Paul M Foster <paulf@quillandmouse.com> writes:
>> 3. Am I correct in assuming that you need to specify a database in order
>> to connect to PostgreSQL, and that, on a "virgin" system, template1 is
>> the database provided for that purpose? If it isn't necessary to specify
>> a database in order to connect, how would you otherwise do it?
> You are correct. The best practice is to stay out of template1 unless
> you are creating a new database or adding stuff to template1.
It's worth noting that there's a conflict between the use of template1
as the default template for CREATE DATABASE, versus its use as the
default database-to-connect-to for scripts like createuser. You could
get a failure from CREATE DATABASE if someone happens to be transiently
connected to template1 while creating a user or whatever.
This is a historical accident that arises from the fact that template1
is the only certain-to-exist database name (other than template0 which
is a recent innovation). We're probably going to have to change some
of this stuff eventually...
regards, tom lane