Обсуждение: Template1 is locked when pgAdminIII is open
In the PostgreSQL RC-1, whenever we have the pgAdminIII open and try to create a database, it says that template1 is lockedup and hence the database cannot be created. I believe it shouldn't lock it up and allow the database to be created. If this bug has already been raised and fixed in subsequent versions, please let me know. Thanks & Regards, Mehul
=20 > -----Original Message----- > From: pgsql-bugs-owner@postgresql.org=20 > [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Mehul=20 > Doshi-A20614 > Sent: 31 January 2005 12:58 > To: 'pgsql-bugs@postgresql.org' > Subject: [BUGS] Template1 is locked when pgAdminIII is open >=20 > In the PostgreSQL RC-1, whenever we have the pgAdminIII open=20 > and try to create a database, it says that template1 is=20 > locked up and hence the database cannot be created. I believe=20 > it shouldn't lock it up and allow the database to be created. >=20 > If this bug has already been raised and fixed in subsequent=20 > versions, please let me know.=20 pgAdmin uses template1 by default as it is the only accessible database that we can be sure will exist. There are 2 solutions to your problem: 1) Change your pgAdmin connection to use a different default database. 2) Select a different database to use as the template when creating a new one. Regards, Dave.
"Dave Page" <dpage@vale-housing.co.uk> writes:
> pgAdmin uses template1 by default as it is the only accessible database
> that we can be sure will exist. There are 2 solutions to your problem:
> 1) Change your pgAdmin connection to use a different default database.
> 2) Select a different database to use as the template when creating a
> new one.
He shouldn't really be getting that error though, as CREATE DATABASE
only complains if there is another connection *besides your own* to
the template database.
I think the real answer is something more along the lines of "don't run
two copies of pgAdmin at once".
regards, tom lane
Tom Lane wrote: > "Dave Page" <dpage@vale-housing.co.uk> writes: > >>pgAdmin uses template1 by default as it is the only accessible database >>that we can be sure will exist. There are 2 solutions to your problem: > > >>1) Change your pgAdmin connection to use a different default database. > > >>2) Select a different database to use as the template when creating a >>new one. > > > He shouldn't really be getting that error though, as CREATE DATABASE > only complains if there is another connection *besides your own* to > the template database. > > I think the real answer is something more along the lines of "don't run > two copies of pgAdmin at once". Which is still not the whole truth. pgAdmin may open more connections to the "initial database", e.g. to show the server status. So the advice is "don't use the template1 database for pgadmin connections if you're creating databases frequently" Regards, Andreas
=20 > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 > Sent: 31 January 2005 16:40 > To: Dave Page > Cc: Mehul Doshi-A20614; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] Template1 is locked when pgAdminIII is open=20 >=20 > "Dave Page" <dpage@vale-housing.co.uk> writes: > > pgAdmin uses template1 by default as it is the only=20 > accessible database > > that we can be sure will exist. There are 2 solutions to=20 > your problem: >=20 > > 1) Change your pgAdmin connection to use a different=20 > default database. >=20 > > 2) Select a different database to use as the template when=20 > creating a > > new one. >=20 > He shouldn't really be getting that error though, as CREATE DATABASE > only complains if there is another connection *besides your own* to > the template database. >=20 > I think the real answer is something more along the lines of=20 > "don't run > two copies of pgAdmin at once". He might not be. pgAdmin uses a master connection (normally to template1) and one connection to each database browsed (minus the master connection which is reused). However, each SQL Query window opened will also open a new connection to the currently selected database, which is what might have happened in this case. Of course, the easy answer is to close any SQL windows in template1 as well... Regards, Dave.
"Dave Page" <dpage@vale-housing.co.uk> writes:
> Tom Lane wrote:
>> I think the real answer is something more along the lines of
>> "don't run two copies of pgAdmin at once".
> He might not be. pgAdmin uses a master connection (normally to
> template1) and one connection to each database browsed (minus the master
> connection which is reused). However, each SQL Query window opened will
> also open a new connection to the currently selected database, which is
> what might have happened in this case. Of course, the easy answer is to
> close any SQL windows in template1 as well...
Hmm. Would it be possible to teach pgAdmin to close extra connections
to template1 whenever it's doing CREATE DATABASE?
Of course, sooner or later we should fix the underlying locking
mechanism so we don't have to have this constraint, but I'm not sure
when that will happen.
regards, tom lane
On Mon, Jan 31, 2005 at 12:29:12PM -0500, Tom Lane wrote: > Of course, sooner or later we should fix the underlying locking > mechanism so we don't have to have this constraint, but I'm not sure > when that will happen. Hm, can we use LockSharedObject? It's on my shared dependency patch. What needs to be locked? Maybe we need to take a AccessShareLock on the pg_database row for schema changes, and AccessExclusiveLock on same for database creation. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La Primavera ha venido. Nadie sabe como ha sido" (A. Machado)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Mon, Jan 31, 2005 at 12:29:12PM -0500, Tom Lane wrote:
>> Of course, sooner or later we should fix the underlying locking
>> mechanism so we don't have to have this constraint, but I'm not sure
>> when that will happen.
> What needs to be locked?
Somehow we'd need to prevent any changes to the template database while
it's being copied. AFAICS this would require something on the order of
taking out a lock at the database level for every query, which sounds
like a mighty expensive solution compared to the seriousness of the
problem.
regards, tom lane
Tom Lane wrote: > "Dave Page" <dpage@vale-housing.co.uk> writes: > >>Tom Lane wrote: >> >>>I think the real answer is something more along the lines of >>>"don't run two copies of pgAdmin at once". > > >>He might not be. pgAdmin uses a master connection (normally to >>template1) and one connection to each database browsed (minus the master >>connection which is reused). However, each SQL Query window opened will >>also open a new connection to the currently selected database, which is >>what might have happened in this case. Of course, the easy answer is to >>close any SQL windows in template1 as well... > > > Hmm. Would it be possible to teach pgAdmin to close extra connections > to template1 whenever it's doing CREATE DATABASE? If those connections were guaranteed to be idle, we wouldn't need them. They are independent windows, so we can't kill the conn. Regards, Andreas
=20 > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 > Sent: 31 January 2005 17:29 > To: Dave Page > Cc: Mehul Doshi-A20614; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] Template1 is locked when pgAdminIII is open=20 >=20 > Hmm. Would it be possible to teach pgAdmin to close extra connections > to template1 whenever it's doing CREATE DATABASE? Hmm, it's possible but not particularly friendly - it would stop any ongoing user queries (not that there are likely to be many of those in template1), and the server status window (which periodically selects from pg_stat_activity among other things). I think it would be nicer for pgAdmin to give a more helpful error message (in most cases it simply passes on such messages as they come from the server). Regards, Dave.
Hi,
I amn't running two copies of pgAdmin at same time. The problem is
The postgres server is running on a separate Windows XP system. I cannot be sure if pgAdmin is open or not on that
system.I use the console way to create a database on that system.
createdb mehultest -h pcdsk506 -U postgres -W postgres
Password:
createdb: database creation failed: ERROR: source database "template1" is being
accessed by other users
The admin of that system might be cleaning up databases or viewing or modifying using pgAdminIII. In such a case, I am
blockedtill pgAdminIII is closed by the system admin using the remote system.
I think it might be a good idea for pgAdminIII to use some shared lock which can be used with createdb thereby allowing
theremote user to create the database. After that pgAdminIII can just refresh it's view to see the new db, similar to
theway it refreshes when you drop a db.
Thanks & Regards,
Mehul
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 31, 2005 10:10 PM
To: Dave Page
Cc: Mehul Doshi-A20614; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Template1 is locked when pgAdminIII is open
"Dave Page" <dpage@vale-housing.co.uk> writes:
> pgAdmin uses template1 by default as it is the only accessible
> database that we can be sure will exist. There are 2 solutions to your
> problem:
> 1) Change your pgAdmin connection to use a different default database.
> 2) Select a different database to use as the template when creating a
> new one.
He shouldn't really be getting that error though, as CREATE DATABASE only complains if there is another connection
*besidesyour own* to the template database.
I think the real answer is something more along the lines of "don't run two copies of pgAdmin at once".
regards, tom lane