Message-ID: <124ce8dff8402159f20dec1bc1ac27e26ee39c23.camel@gmail.com>
Subject: Re: libpq CREATE DATABASE operation from multiple treads
From: p.sun.fun@gmail.com
To: pgsql-sql@lists.postgresql.org
Date: Thu, 08 Oct 2020 15:30:27 -0500
In-Reply-To: 
 <CAAfz9KOVPRptcva6T97+VgLhYbqMJGC07yHVHMW3Ugxki6y6dA@mail.gmail.com>
References: <390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com>
	 <CAAfz9KOVPRptcva6T97+VgLhYbqMJGC07yHVHMW3Ugxki6y6dA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.4-2 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Indeed, this is a FATAL_ERROR. From two threads I got:

// Thread #1
FATAL_ERROR: ERROR:  source database "template1" is being accessed by
other users
DETAIL:  There is 1 other session using the database.

// Thread #2
FATAL_ERROR: ERROR:  source database "template1" is being accessed by
other users
DETAIL:  There is 1 other session using the database.


On Thu, 2020-10-08 at 23:15 +0300, Dmitry Igrishin wrote:
> 
> 
> On Thu, 8 Oct 2020, 22:46 , <p.sun.fun@gmail.com> wrote:
> > Hi, PostgreSQL community.
> > 
> > I hope everyone is doing great and keep themselves safe and
> > healthy. 
> > 
> > I am not sure whether my question should belong here. Please advise
> > if
> > this is the wrong place. 
> > 
> > I am contributing to the libgda project (
> > https://gitlab.gnome.org/GNOME/libgda) which a convenient wrapper
> > around multiple SQL providers. We have good support for PostgreSQL
> > and
> > would like to keep so in the future. To test our code we use
> > CI+Docker.
> > Docker provides SQL server functionality. Everything is fine but...
> > 
> > We have an API to run DDL operations. Everything works ok but once
> > in a
> > while, we have a problem to create a new database if we run
> > multiple
> > build processes communicating to the same SQL server. In our code,
> > the
> > process to create a new database consist of the following steps:
> > 
> > 1) Connect to "template1" database
> > 2) Execute "CREATE DATABASE <some_name>"
> > 3) Close connection
> > 4) Open a connection to the new database. 
> > 
> > I was trying to explore this problem using libpq to better
> > understand
> > the origin of the problem. This is a test code:
> > 
> > void function_to_run_in_a_thread(void *data) {
> > 
> > /* The passed void *data is a pointer to an SQL string with a
> > randomly
> > generated database name, e.g. CREATE DATABASE kajdygsj */
> >     const char *sql_create_db = (const char *)data;
> >     const char *cnc_string = "host='localhost' user='test'
> > password='test1' dbname='template1'";
> > 
> >     PGconn *cnc = NULL;
> >     PGresult *res = NULL;
> > 
> >     cnc = PQconnectdb(cnc_string);
> > 
> >     if (PQstatus(cnc) != CONNECTION_OK)
> >         abort();        
> > 
> >     res = PQexec(cnc, sql_create_db);
> > 
> >     if (PQresultStatus(res) != PGRES_COMMAND_OK)
> >         abort();
> > 
> > 
> >     PQclear(res);
> >     PQfinish(cnc);
> > }
> > 
> > I run this function using only one thread and everything works
> > great.
> > If I use two threads, the PQresultStatus(res) != PGRES_COMMAND_OK.
> > I
> > was trying to add a delay time for PQresultStatus(res) !=
> > PGRES_COMMAND_OK and repeat the same command but didn't help much.
> > If I
> > drop connection for PQresultStatus(res) != PGRES_COMMAND_OK and
> > open it
> > again after some random delay, it works. 
> 
> Okay. You may want to check PQresultStatus(). If it's
> PGRES_FATAL_ERROR please check the SQLSTATE and message.
> > Can the server handle CREATE DATABASE requests in parallel? 
> > 
> > Thanks,
> > 
> > -Pavlo
> > 
> > 
> >