Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with "create database ... with template "

Problem with "create database ... with template "

From
Oleg Bartunov
Date:
Hi there,

I'm investigating a problem my client experienced with 
"create database xxx with template=yyy" - they often get error
"ERROR:  source database "yyy" is being accessed by other users",

I modelled situation in clean environment with very simple perl script
( FreeBSD 6.2-STABLE, postgresql-8.2.3 from ports ) and was able to
reproduce the problem. There were no other connections ( I checked log ).
I tried shell script and got the same problem. Is this a known problem ?


perl testcreatedb.pl
Repeat #:1
Repeat #:2
Repeat #:3
Repeat #:4
Repeat #:5
Repeat #:6
Repeat #:7
Repeat #:8
Repeat #:9
Repeat #:10
Repeat #:11
Repeat #:12
Repeat #:13
Repeat #:14
Repeat #:15
Repeat #:16
DBD::Pg::db do failed: ERROR:  source database "yyy" is being accessed by other users
DBD::Pg::db do failed: ERROR:  source database "yyy" is being accessed by other users



----------- cut here -----------
#!/usr/bin/perl

use strict;
use DBI;

my $dbh;
my $rv = undef;

foreach  my $i ( 1..100) {

$dbh = DBI->connect('dbi:Pg:database=yyy;host=msk2.socionet.ru;port=5432','db_user','',{ RaiseError => 1}) or      die
"Can'tconnect to database !\n";
 

print STDERR "Repeat #:$i\n";

$rv = $dbh->do('drop database xxx');
$rv = $dbh->do('create database xxx with  TEMPLATE=yyy');

print STDERR "error creating database: $rv\n" if ( $rv ne '0E0' );

$dbh->disconnect;
--------------- cut here -------------

    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Re: Problem with "create database ... with template "

From
Tom Lane
Date:
Oleg Bartunov <oleg@sai.msu.su> writes:
> I'm investigating a problem my client experienced with 
> "create database xxx with template=yyy" - they often get error
> "ERROR:  source database "yyy" is being accessed by other users",

Your test case simply shows that it takes a finite amount of time for
the previous backend to exit; depending on scheduler priorities it's
possible for the next instance to connect and try to CREATE DATABASE
before the previous one is gone.  My suggestion would be to connect
to some other database besides the one being copied.
        regards, tom lane

Re: Problem with "create database ... with template "

From
Andrew Dunstan
Date:

Tom Lane wrote:
> Your test case simply shows that it takes a finite amount of time for
> the previous backend to exit; depending on scheduler priorities it's
> possible for the next instance to connect and try to CREATE DATABASE
> before the previous one is gone.  My suggestion would be to connect
> to some other database besides the one being copied.
>
>     
>   

Didn't we invent the "postgres" database for just this reason?

cheers

andrew

Re: Problem with "create database ... with template "

From
Oleg Bartunov
Date:
On Wed, 9 May 2007, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
>> I'm investigating a problem my client experienced with
>> "create database xxx with template=yyy" - they often get error
>> "ERROR:  source database "yyy" is being accessed by other users",
>
> Your test case simply shows that it takes a finite amount of time for
> the previous backend to exit; depending on scheduler priorities it's
> possible for the next instance to connect and try to CREATE DATABASE
> before the previous one is gone.  My suggestion would be to connect
> to some other database besides the one being copied.

I tried that connecting to template1, but it doesn't helped. The problem
occurs even if I repeat 'drop;create' in one connection (the same backend).

connect
while true do drop database create database with template
end
disconnect

    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Re: Problem with "create database ... with template "

From
Tom Lane
Date:
Oleg Bartunov <oleg@sai.msu.su> writes:
> I tried that connecting to template1, but it doesn't helped. The problem
> occurs even if I repeat 'drop;create' in one connection (the same backend).

Hmm.  Do you have autovacuum turned on?  8.2 isn't real smart about
dealing with autovac running in the template DB ...

FWIW, I could repeat it here easily with the given test case (new
connections being made each time) but not with a single persistent
connection.
        regards, tom lane

Re: Problem with "create database ... with template "

From
Oleg Bartunov
Date:
On Wed, 9 May 2007, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
>> I tried that connecting to template1, but it doesn't helped. The problem
>> occurs even if I repeat 'drop;create' in one connection (the same backend).
>
> Hmm.  Do you have autovacuum turned on?  8.2 isn't real smart about
> dealing with autovac running in the template DB ...

yes, it turned on. With autovac off the problem seems gone away.

>
> FWIW, I could repeat it here easily with the given test case (new
> connections being made each time) but not with a single persistent
> connection.

If I play with toy database (empty) I don't see error message. 
Real database is about 800Mb, so it takes about 22 seconds to create
database. Looks like there is a time lag between completion 
'create database ...' command and actual releasing of template db.

    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83