Re: libpq CREATE DATABASE operation from multiple treads

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
>
>
>
>