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