Re: Template1 is locked when pgAdminIII is open

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 31 January 2005 16:40
> To: Dave Page
> Cc: Mehul Doshi-A20614; pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] Template1 is locked when pgAdminIII is open
>
> "Dave Page" <dpage(at)vale-housing(dot)co(dot)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".

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(at)vale-housing(dot)co(dot)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[(at)]dcc(dot)uchile(dot)cl>)
"La Primavera ha venido. Nadie sabe como ha sido" (A. Machado)

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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(at)vale-housing(dot)co(dot)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