Обсуждение: pg_autovacuum causes 'create database' to fail when the first is accessing 'template1'

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

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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Bruce Momjian <pgman@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

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.

Frank van Vugt <ftm.van.vugt@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

Tom Lane wrote:
> Frank van Vugt <ftm.van.vugt@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
  pgman@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