Skip site navigation (1) Skip section navigation (2)

Re: Creating database problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "M(dot)A(dot) Oude Kotte" <marc(at)solcon(dot)nl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Creating database problem
Date: 2004-10-29 15:26:54
Message-ID: 16422.1099063614@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"M.A. Oude Kotte" <marc(at)solcon(dot)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

In response to

pgsql-general by date

Next:From: C GDate: 2004-10-29 15:42:45
Subject: adding missing FROM-clause
Previous:From: Marc G. FournierDate: 2004-10-29 15:03:19
Subject: Re: Question regarding the mailing list.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group