Re: libpq CREATE DATABASE operation from multiple treads

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(dot)sun(dot)fun(at)gmail(dot)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
> >
> >
> >