agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: p.sun.fun@gmail.com
To: pgsql-sql@lists.postgresql.org
Subject: libpq CREATE DATABASE operation from multiple treads
Date: Thu, 08 Oct 2020 14:46:24 -0500
Message-ID: <390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com> (raw)

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. 

Can the server handle CREATE DATABASE requests in parallel? 

Thanks,

-Pavlo

view thread (18+ messages)  latest in thread

Message-ID: <390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com>
Permalink:  ../390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com/
Also on:    postgresql.org/message-id/390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: p.sun.fun@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: libpq CREATE DATABASE operation from multiple treads
  In-Reply-To: <390804abe88e4e9da9049f6e05c841783acaca47.camel@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox