Problem with "create database ... with template "
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't connect 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
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
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
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
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
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