By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

pg_autovacuum causes 'create database' to fail when the first is accessing 'template1'

P: n/a
Hi,

I noticed that when using the single commandline:

drop database <name>; create database <name>;

this sometimes fails due to a pg_autovacuum process running on the background.
When this happens, the error returned is:

db2=# drop database db1; create database db1;
DROP DATABASE
ERROR: source database "template1" is being accessed by other users

The logs shows that pg_autovacuum kicks in right after the drop database, but
right before the create database, causing the latter to fail:
2003-11-08 10:50:15 [1422] LOG: unexpected EOF on client connection
2003-11-08 10:50:46 [1424] LOG: connection received: host=<ip> port=<port>
2003-11-08 10:50:46 [1424] LOG: connection authorized: user=<my_name>
database=db2
2003-11-08 10:50:52 [1424] LOG: query: drop database db1;
2003-11-08 10:50:55 [1426] LOG: connection received: host=[local] port=
2003-11-08 10:50:55 [1426] LOG: connection authorized:
user=<pg_autovacuum_user> database=template1
2003-11-08 10:50:56 [1424] LOG: query: create database db1;
2003-11-08 10:50:56 [1424] ERROR: source database "template1" is being
accessed by other users
Obviously, simply reentering 'create database db1' corrects the problem ;-)
db2=# select version();
version
------------------------------------------------------------------
PostgreSQL 7.4beta3 on i586-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)
pg_autovacuum is started using the options '-D -d2'


This may very well be expected behaviour, but I didn't find anything on it in
pg_autovacuum's README or TODO, so just in case it is a bug.......


--
Best,


Frank van Vugt
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

Good description --- not sure what we can do about it.

---------------------------------------------------------------------------

Frank van Vugt wrote:
Hi,

I noticed that when using the single commandline:

drop database <name>; create database <name>;

this sometimes fails due to a pg_autovacuum process running on the background.
When this happens, the error returned is:

db2=# drop database db1; create database db1;
DROP DATABASE
ERROR: source database "template1" is being accessed by other users

The logs shows that pg_autovacuum kicks in right after the drop database, but
right before the create database, causing the latter to fail:
2003-11-08 10:50:15 [1422] LOG: unexpected EOF on client connection
2003-11-08 10:50:46 [1424] LOG: connection received: host=<ip> port=<port>
2003-11-08 10:50:46 [1424] LOG: connection authorized: user=<my_name>
database=db2
2003-11-08 10:50:52 [1424] LOG: query: drop database db1;
2003-11-08 10:50:55 [1426] LOG: connection received: host=[local] port=
2003-11-08 10:50:55 [1426] LOG: connection authorized:
user=<pg_autovacuum_user> database=template1
2003-11-08 10:50:56 [1424] LOG: query: create database db1;
2003-11-08 10:50:56 [1424] ERROR: source database "template1" is being
accessed by other users
Obviously, simply reentering 'create database db1' corrects the problem ;-)
db2=# select version();
version
------------------------------------------------------------------
PostgreSQL 7.4beta3 on i586-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)
pg_autovacuum is started using the options '-D -d2'


This may very well be expected behaviour, but I didn't find anything on it in
pg_autovacuum's README or TODO, so just in case it is a bug.......


--
Best,


Frank van Vugt
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a
Bruce Momjian <pg***@candle.pha.pa.us> writes:
ERROR: source database "template1" is being accessed by other users
Good description --- not sure what we can do about it.


Sooner or later we'll have to face up to the conflict between using
template1 as the default createdb template and using it as the default
connection target for random scripts like createuser and autovacuum.

The interlock that tries to ensure we have a consistent state of
template1 on disk for createdb to copy is what creates the problem.
And it is not good enough anyway, since there's not any certainty that
someone couldn't connect to template1 and modify it while the 'cp -r'
is in progress.

I suspect ultimately we will have to abandon the use of 'cp -r' for
createdb and do it ourselves, internally. This may be forced by
tablespace disk-organization issues even before we think of a proper
solution to the interlock problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
Hi,

Thanks Bruce ;-)
Tom Lane wrote:
Sooner or later we'll have to face up to the conflict <cut>


Thanks Tom, I understand what's happening now.

Did I miss this future necessity on the TODO-list ('cause I did check...) or
is it simply not on there (yet) ?

I'm just being inquisitive, I understand there's a whole lot of items on the
list that are a tad more important to fix ;-)


--
Best,


Frank van Vugt.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4

P: n/a
Frank van Vugt <ft**********@foxi.nl> writes:
Did I miss this future necessity on the TODO-list ('cause I did check...) or
is it simply not on there (yet) ?


It's not there; it should be. Something like

* Need better (safer, less prone to unnecessary failures) scheme for
ensuring CREATE DATABASE gets a consistent copy of the template database

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

P: n/a
Tom Lane wrote:
Frank van Vugt <ft**********@foxi.nl> writes:
Did I miss this future necessity on the TODO-list ('cause I did check...) or
is it simply not on there (yet) ?


It's not there; it should be. Something like

* Need better (safer, less prone to unnecessary failures) scheme for
ensuring CREATE DATABASE gets a consistent copy of the template database


Added:

* Use more reliable method for CREATE DATABASE to get a consistent copy of db

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.