Re: Weird "template1" errors on CREATE DATABASE

Hi Tom,

Thanks for your help.

On Friday, April 18, 2003, at 09:40 AM, Tom Lane wrote:
>
> I have seen reports like this before, and AFAICT the issue is simply
> that your previous connection to template1 hasn't finished cleaning
> itself up yet. It takes a finite amount of time for a backend to shut
> down.

Hmm - it's not enough to wait for the $dbh->disconnect() method to
finish? I would happily block there if I could be assured that the
connection was indeed disconnected afterwards. It makes it difficult
to know when it's safe to proceed.

> Also, it's not unheard-of for client-side code to be holding a
> connection open for longer than the programmer realizes, especially
> when the connection closure is triggered indirectly through garbage
> collection. Perl is one language where that applies.

All the disconnecting should be happening through $dbh->disconnect() in
this case, not garbage collection.

> Throwing a fractional-second delay in before you try to do the CREATE
> DATABASE will probably make the problem go away. Alternatively, fix
> your client-side code so that it's not opening a fresh connection for
> each SQL command, as I surmise it's doing now.

It's only doing that in order to create or drop a database, because (as
far as I know) there's no way to switch an existent connection to/from
the "template1" database. So an outline of the code is something like
this:

$dbh = DBI->connect('dbi:Pg:dbname=template1', 'ken', '', {RaiseError
=> 1});
$dbh->do("CREATE DATABASE test_alzabo_pg");
$dbh->disconnect;

$dbh2 = DBI->connect('dbi:Pg:dbname=test_alzabo_pg', 'ken',
{RaiseError => 1});
...do stuff...
$dbh2->disconnect;

$dbh = DBI->connect('dbi:Pg:dbname=template1', 'ken', '', {RaiseError
=> 1});
$dbh->do("DROP DATABASE test_alzabo_pg");
$dbh->disconnect;

In the test code I'm running, creating & dropping databases happens
several times.

-Ken