Message-ID: <d18d1dd24fd0af7ebf92c782e6511df2c7b42985.camel@gmail.com>
Subject: Re: libpq CREATE DATABASE operation from multiple treads
From: p.sun.fun@gmail.com
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgsql-sql@lists.postgresql.org
Date: Thu, 08 Oct 2020 22:49:54 -0500
In-Reply-To: 
 <CAECtzeWQJpT9rzDY1-xJQSUjKC7Du+kpKm4d1pUJ4_E9GrhEHw@mail.gmail.com>
References: <390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com>
	 <CAAfz9KOVPRptcva6T97+VgLhYbqMJGC07yHVHMW3Ugxki6y6dA@mail.gmail.com>
	 <124ce8dff8402159f20dec1bc1ac27e26ee39c23.camel@gmail.com>
	 <96718FB0-E258-4342-BD6E-BCE6603BCBD5@gmail.com>
	 <e11780112264fddbae35f53a3b26095f1789e413.camel@gmail.com>
	 <058A9601-35F6-42C1-AC4C-8DD4643E418F@gmail.com>
	 <47a7c4ffa62aaf2b86854a657f484f33913207c9.camel@gmail.com>
	 <23F4DE61-F81D-4740-A227-74B5EB41304C@gmail.com>
	 <2631053.1602190588@sss.pgh.pa.us>
	 <9109e992e899b2ed39b070cd12ecdbcdf435fbb6.camel@gmail.com>
	 <CAECtzeWQJpT9rzDY1-xJQSUjKC7Du+kpKm4d1pUJ4_E9GrhEHw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.4-2 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

On Fri, 2020-10-09 at 05:44 +0200, Guillaume Lelarge wrote:
> Le ven. 9 oct. 2020 à 05:33, <p.sun.fun@gmail.com> a écrit :
> > On Thu, 2020-10-08 at 16:56 -0400, Tom Lane wrote:
> > > Rob Sargent <robjsargent@gmail.com> writes:
> > > > OK, well that’s a special db.  Didn’t know it was that special,
> > > > though!
> > > 
> > > It's not that special.  The issue here is that each session is
> > > connecting
> > > to template1 and then trying to clone template1.  You can't clone
> > an
> > > active database, because you might not get a consistent copy. 
> > CREATE
> > > DATABASE knows that its own session isn't concurrently making any
> > > changes, so it allows copying the current database --- but it
> > can't
> > > know what some other session is doing, so if it sees some other
> > > session
> > > is also connected to the source database, it spits up.
> > > 
> > > As I already said, routinely connecting to template1 is pretty
> > bad
> > > practice to start with, so the preferred answer is "don't do
> > that".
> > > 
> > >                       regards, tom lane
> > 
> > Thank you, guys. I will switch to the "postgres" database as a
> > default
> > one. IMHO, it is worth adding to the documentation into the CREATE
> > DATABASE section. I am glad that PostgreSQL has a strong community
> > that
> > stays behind the product. 
> 
> It's already in the documentation:
> 
> "Although it is possible to copy a database other than template1 by
> specifying its name as the template, this is not (yet) intended as a
> general-purpose “COPY DATABASE” facility. The principal limitation is
> that no other sessions can be connected to the template database
> while it is being copied. CREATE DATABASE will fail if any other
> connection exists when it starts; otherwise, new connections to the
> template database are locked out until CREATE DATABASE completes.
> See Section 22.3 for more information."
> 
> See https://www.postgresql.org/docs/13/sql-createdatabase.html.

Yep, you are right. Probably did't read carefully. Thanks for pointing
out.