Обсуждение: Create Database using JDBC

On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:

> I wish to create a database if one doesn't exist using JDBC. I tried
> connecting to template1 database and then issuing the "CREATE DATABASE test"
> command but I get the following error :
>
>   ERROR:  CREATE DATABASE: source database "template1"
> is being accessed by other users
>
> How do I resolve this? I'm using PostGreSQL8.0

This should work (we're doing it exactly the same way). But there can
only be one user connected at a time to the template database, it
seems, at least when using the CREATE DATABASE command. Do you have
the one of the PostgreSQL tools open, eg. pgAdmin ?

Tom

Nidhi Srivastava wrote:

> I wish to create a database if one doesn’t exist using JDBC. I tried
> connecting to template1 database and then issuing the “CREATE DATABASE
> test” command but I get the following error :
>
> ERROR:  CREATE DATABASE: source database "template1"
> is being accessed by other users

This is no different for JDBC versus createdb. You can't use a database
as a source for CREATE DATABASE if it has more than one active
connection. I suspect you will find that 'createdb' fails with the same
error..

-O

Hi all,

I'm not too knowledgeable about this matter, but wouldn't it work to
connect to another data base, not template1 ?
And make a policy not to connect to template1.
I guess it doesn't matter which data base are you connected to (except
that it must exist) when you create a new one. So just create a
"standard" data base which always exist, and connect to that one.

Cheers,
Csaba.


On Fri, 2005-07-01 at 12:26, Thomas Dudziak wrote:
> On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
> > I wish to create a database if one doesn't exist using JDBC. I tried
> > connecting to template1 database and then issuing the "CREATE DATABASE test"
> > command but I get the following error :
> >
> >   ERROR:  CREATE DATABASE: source database "template1"
> > is being accessed by other users
> >
> > How do I resolve this? I'm using PostGreSQL8.0
>
> This should work (we're doing it exactly the same way). But there can
> only be one user connected at a time to the template database, it
> seems, at least when using the CREATE DATABASE command. Do you have
> the one of the PostgreSQL tools open, eg. pgAdmin ?
>
> Tom
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> Yes I have the pgAdmin open. Is there any work around to check if more
> than one users are connected to the server and resolve this?

You could close pgAdmin, or at least disconnect it from the database ?!

Tom

Yes I have the pgAdmin open. Is there any work around to check if more
than one users are connected to the server and resolve this?

Or is there any way I can create a DB at the time of Postgres
installation itself. I'm using postgresql8.0 on windows.

If you can't see the bright side of life, polish the dull side.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
Sent: Friday, July 01, 2005 3:56 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC

On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:

> I wish to create a database if one doesn't exist using JDBC. I tried
> connecting to template1 database and then issuing the "CREATE DATABASE
test"
> command but I get the following error :
>
>   ERROR:  CREATE DATABASE: source database "template1"
> is being accessed by other users
>
> How do I resolve this? I'm using PostGreSQL8.0

This should work (we're doing it exactly the same way). But there can
only be one user connected at a time to the template database, it
seems, at least when using the CREATE DATABASE command. Do you have
the one of the PostgreSQL tools open, eg. pgAdmin ?

Tom

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Even when I'm not accessing templte1 but any other scratch database
through pgAdmin and try to create a new DB using JDBC I get the same
ERROR:  CREATE DATABASE: source database "template1" is being accessed
by other users. Is there a way to check if a DB exists and create it if
it doesn't using JDBC. How can I ensure that pgAdmin is not open on any
of the client machines and connected to the Postgre server on my
machine....becuase if even a single pgAdmin client is open on any of the
client machines it doesn't allow me to create a new DB throwing the
above error.

Thanks & Regards
Nidhi

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nidhi Srivastava
Sent: Friday, July 01, 2005 4:04 PM
To: Thomas Dudziak; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC

Yes I have the pgAdmin open. Is there any work around to check if more
than one users are connected to the server and resolve this?

Or is there any way I can create a DB at the time of Postgres
installation itself. I'm using postgresql8.0 on windows.

If you can't see the bright side of life, polish the dull side.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
Sent: Friday, July 01, 2005 3:56 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC

On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:

> I wish to create a database if one doesn't exist using JDBC. I tried
> connecting to template1 database and then issuing the "CREATE DATABASE
test"
> command but I get the following error :
>
>   ERROR:  CREATE DATABASE: source database "template1"
> is being accessed by other users
>
> How do I resolve this? I'm using PostGreSQL8.0

This should work (we're doing it exactly the same way). But there can
only be one user connected at a time to the template database, it
seems, at least when using the CREATE DATABASE command. Do you have
the one of the PostgreSQL tools open, eg. pgAdmin ?

Tom

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

On Mon, 4 Jul 2005, Nidhi Srivastava wrote:

> Even when I'm not accessing templte1 but any other scratch database
> through pgAdmin and try to create a new DB using JDBC I get the same
> ERROR:  CREATE DATABASE: source database "template1" is being accessed
> by other users. Is there a way to check if a DB exists and create it if
> it doesn't using JDBC. How can I ensure that pgAdmin is not open on any
> of the client machines and connected to the Postgre server on my
> machine....becuase if even a single pgAdmin client is open on any of the
> client machines it doesn't allow me to create a new DB throwing the
> above error.
>

I would suggest using an alernate template database to create new
databases with.  You could even mark it to refuse connections to ensure no
one else will connect to it and block your operation.

Kris Jurka

On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
> But even that alternate template database would need to be created the
> first time after server installation by connceting to the template1 DB??
> Right??
>
> All I want is to automate database creation the first time after  server
> installation eliminate user inputs

But why then not requiring that no one is connected to the template1
database ? That shouldn't be too much of a requirement. Myself, I
develop with postgres on a daily basis (using JDBC), and even I have
pgAmin only open when necessary eg. for checking that some data was
inserted in the db or testing a query.

Tom

On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> Problem is if any client application is connected to another database on
> my server anywhere, I shall not able to create my database using
> template1. How can I determine which other clients are connected to my
> server?

Have you tried that this doesn't work ? If none of the clients is
connected to template1 but only to their own databases, then AFAIK it
should work (pgAdmin might be different in this matter, perhaps it is
always connected to the template databases ?).
You could write a small JDBC app to verify that it does/doesn't work, eg.

* open a connection to template1
* create database test
* close connection to template1
* open connection to test and keep it open
* open connection to template1
* create database test2
* close connection to template1
* close connection to test

if this doesn't work, then you probably should file a bug and attach
this sample app.

Tom

But even that alternate template database would need to be created the
first time after server installation by connceting to the template1 DB??
Right??

All I want is to automate database creation the first time after  server
installation eliminate user inputs

-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Monday, July 04, 2005 3:01 PM
To: Nidhi Srivastava
Cc: Thomas Dudziak; pgsql-jdbc@postgresql.org; John R Pierce
Subject: Re: [JDBC] Create Database using JDBC



On Mon, 4 Jul 2005, Nidhi Srivastava wrote:

> Even when I'm not accessing templte1 but any other scratch database
> through pgAdmin and try to create a new DB using JDBC I get the same
> ERROR:  CREATE DATABASE: source database "template1" is being accessed
> by other users. Is there a way to check if a DB exists and create it
if
> it doesn't using JDBC. How can I ensure that pgAdmin is not open on
any
> of the client machines and connected to the Postgre server on my
> machine....becuase if even a single pgAdmin client is open on any of
the
> client machines it doesn't allow me to create a new DB throwing the
> above error.
>

I would suggest using an alernate template database to create new
databases with.  You could even mark it to refuse connections to ensure
no
one else will connect to it and block your operation.

Kris Jurka

Problem is if any client application is connected to another database on
my server anywhere, I shall not able to create my database using
template1. How can I determine which other clients are connected to my
server?


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
Sent: Monday, July 04, 2005 4:39 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC

On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
> But even that alternate template database would need to be created the
> first time after server installation by connceting to the template1
DB??
> Right??
>
> All I want is to automate database creation the first time after
server
> installation eliminate user inputs

But why then not requiring that no one is connected to the template1
database ? That shouldn't be too much of a requirement. Myself, I
develop with postgres on a daily basis (using JDBC), and even I have
pgAmin only open when necessary eg. for checking that some data was
inserted in the db or testing a query.

Tom

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

select * from pg_stat_activity

Dave
On 4-Jul-05, at 9:08 AM, Nidhi Srivastava wrote:

> Problem is if any client application is connected to another
> database on
> my server anywhere, I shall not able to create my database using
> template1. How can I determine which other clients are connected to my
> server?
>
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
> Sent: Monday, July 04, 2005 4:39 PM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Create Database using JDBC
>
> On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
>>
>> But even that alternate template database would need to be created
>> the
>> first time after server installation by connceting to the template1
>>
> DB??
>
>> Right??
>>
>> All I want is to automate database creation the first time after
>>
> server
>
>> installation eliminate user inputs
>>
>
> But why then not requiring that no one is connected to the template1
> database ? That shouldn't be too much of a requirement. Myself, I
> develop with postgres on a daily basis (using JDBC), and even I have
> pgAmin only open when necessary eg. for checking that some data was
> inserted in the db or testing a query.
>
> Tom
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>



Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )

Nidhi Srivastava wrote:
> Problem is if any client application is connected to another database on
> my server anywhere, I shall not able to create my database using
> template1. How can I determine which other clients are connected to my
> server?

It's not "another database", it is specifically the database that you
are using as the template for CREATE DATABASE that must be idle. See the
CREATE DATABASE docs for details.

Usually template1 is not used for anything else so there's no problem. I
suspect that pgAdmin is connecting to template1 *as well* as to the
database you request -- so just the act of running it means that noone
else can CREATE DATABASE from template1.

Whatever database you end up using as a template, you will need to
ensure that there's noone else connected to it. If having random users
connect to the template DB is a problem, then consider using pg_hba.conf
to enforce this -- i.e. don't let people connect to template1 in the
first place unless they are a particular user who is allowed to CREATE
DATABASE..

This is not JDBC-specific though. I suggest you take the discussion to
pgsql-general where there's a wider audience and you may get more help.

-O

Hi, Nidhi,

Nidhi Srivastava schrieb:

> I wish to create a database if one doesn’t exist using JDBC. I tried
> connecting to template1 database and then issuing the “CREATE DATABASE
> test” command but I get the following error :
>
> ERROR:  CREATE DATABASE: source database "template1"
>
> is being accessed by other users
>
> How do I resolve this? I’m using PostGreSQL8.0

This is a PostgreSQL limitation, in that you cannot use a database as
template if anyone else except the one who issued the create database
has a connection to it.

You can either use "template0" as template (if you did not apply any
modifications to template1), or create your own templateblah database
which you use as template.

Btw, the Debian PostgreSQL packagers seem to add an empty database
called "postgresql" to each cluster, and then all tools (e. G. pgadmin)
are told to use this database to connect against, so the template
database is kept reserved for real templating issues. Maybe this could
be adopted upstream.

HTH,
Markus

Hi, Thomas,

Thomas Dudziak schrieb:

> But why then not requiring that no one is connected to the template1
> database ? That shouldn't be too much of a requirement. Myself, I
> develop with postgres on a daily basis (using JDBC), and even I have
> pgAmin only open when necessary eg. for checking that some data was
> inserted in the db or testing a query.

This will not be possible on installations with a large userbase, you
have a bunch of users, admins and developers which are using all kinds
of programs that (partially implicitly) connect to template1.

Maybe the debian way (add an additional postgresql database for this
purposes) should be adopted?

Markus

Hi, Nidhi,

Nidhi Srivastava schrieb:
> But even that alternate template database would need to be created the
> first time after server installation by connceting to the template1 DB??
> Right??

No, you can specify any database that has no active connections (exept
possibly your own single one) as template on database creation,
including template0 (which is usually set up to refuse connections).

So connect to template1, and create your new template database with
using template0 as template.

HTH,
Markus

On Sat, Dec 17, 2005 at 02:19:00PM +0100, Markus Schaber wrote:
> Btw, the Debian PostgreSQL packagers seem to add an empty database
> called "postgresql" to each cluster, and then all tools (e. G. pgadmin)
> are told to use this database to connect against, so the template
> database is kept reserved for real templating issues. Maybe this could
> be adopted upstream.

PostgreSQL adds that database automatically on initdb starting with
version 8.1.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Hi, Jim,

Jim C. Nasby schrieb:

> PostgreSQL adds that database automatically on initdb starting with
> version 8.1.

Oh, great, then this is not debian specific. :-)

Markus