Thread: Creating database problem

Hi All,

I'm unsure if this is the correct mailinglist for my question. I have a
problem with CREATE DATABASE, but as the docs state that officially this
is not an SQL statement, I was unsure if I should subscribe to pgsql-sql
or pgsql-general.

NOTE: I've searched on google, postgresql.org, FAQ's and the
static/dynamic documentation, but I couldn't find a solution to my problem.

When I try to execute "CREATE DATABASE somename" while connected to
database template1 as a non-superuser with createdb-rights, I get the
error message "source database "template1" is being accessed by other
users". This is correct, as I'm connected to the same server using
pgAdmin III on a different PC. But my question is, why this error
message? I understand that I need to be using at least _some_ database
in other to connect. But why can I only create a new database (which, as
far as I know, has nothing to do with template1 itself) if I'm the only
connected user? Or am I missing something? I do need to be able to
create databases while other users are connected.

I've used MySQL in the past, which allows a user to create databases
whenever he likes, no matter how many people are connected. I expected
Postgres to do the same, but maybe I'm doing something wrong... :)

Thanks in advance for any help!

Marc

> When I try to execute "CREATE DATABASE somename" while connected to
> database template1 as a non-superuser with createdb-rights, I get the
> error message "source database "template1" is being accessed by other
> users". This is correct, as I'm connected to the same server using
> pgAdmin III on a different PC. But my question is, why this error
> message? I understand that I need to be using at least _some_ database
> in other to connect. But why can I only create a new database (which, as
> far as I know, has nothing to do with template1 itself)
"Create database" does have to do with template1. Template1 is
used as the template for the new database ;-)

> if I'm the only
> connected user? Or am I missing something? I do need to be able to
> create databases while other users are connected.
Sure, but not while they are connected to template1. There is
no real reason that I know of why ordinary users should be
connected to template1 for any significant amount of time.

HTH,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

"M.A. Oude Kotte" <marc@solcon.nl> writes:
> When I try to execute "CREATE DATABASE somename" while connected to
> database template1 as a non-superuser with createdb-rights, I get the
> error message "source database "template1" is being accessed by other
> users". This is correct, as I'm connected to the same server using
> pgAdmin III on a different PC. But my question is, why this error
> message?

It's a locking issue: if the other guy is changing the database while
you copy it, you'd get an inconsistent copy.

The test is not bulletproof, since in theory someone could connect to
the source database after we make the check and alter it underneath us
while the copy is still proceeding.  But it's better than no defense at all.

One possible way to work around the issue is to specify TEMPLATE template0
in CREATE DATABASE; there shouldn't be anybody connected there.

In retrospect it was probably a misfeature that template1 is both the
default database-to-copy and the default database-to-connect-to, but
this usage is so deeply entrenched that no one really wants to change it.

            regards, tom lane