Re: [HACKERS] Problem with "create database ... with template "
On Wed, 9 May 2007, Tom Lane wrote: Oleg Bartunov <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Problem with "create database ... with template "
Oleg Bartunov <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with "create database ... with template "
On Wed, 9 May 2007, Tom Lane wrote: Oleg Bartunov <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Problem with "create database ... with template "
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Problem with "create database ... with template "
Oleg Bartunov <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
