Thread: Weird "template1" errors on CREATE DATABASE

Hi,

I'm getting some weird errors when I run some Perl code that does a
CREATE DATABASE query.  Sometimes it dies with the error 'ERROR:
CREATE DATABASE: source database "template1" is being accessed by other
users'.  Other times it works fine.

The thing is, this server is running on my laptop, and no other code is
accessing the database except the code I'm running right now.
Sometimes it happens the very first time I connect to the database
(using "template1" as advised in the docs) and try to create a
database.  So there couldn't *possibly* be anyone else accessing the
"template1" database.

Any way to track this down further?  It's frustrating that it's an
intermittent problem, but that seems to be the reality of the situation.

I'm running postgres 7.3.2 and DBD::Pg 1.22 with DBI 1.35, all on OS X
10.2.5.

  -Ken

Ken Williams <ken@mathforum.org> writes:
> I'm getting some weird errors when I run some Perl code that does a
> CREATE DATABASE query.  Sometimes it dies with the error 'ERROR:
> CREATE DATABASE: source database "template1" is being accessed by other
> users'.  Other times it works fine.

> The thing is, this server is running on my laptop, and no other code is
> accessing the database except the code I'm running right now.
> Sometimes it happens the very first time I connect to the database
> (using "template1" as advised in the docs) and try to create a
> database.  So there couldn't *possibly* be anyone else accessing the
> "template1" database.

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.  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.

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.

            regards, tom lane

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