Обсуждение: libpq CREATE DATABASE operation from multiple treads

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

p.sun.fun@gmail.com writes:
> I run this function using only one thread and everything works great.
> If I use two threads, the PQresultStatus(res) != PGRES_COMMAND_OK.

Are you trying to use the same PGconn from multiple threads?  That
will not work --- at least not without interlocks that libpq on
its own does not provide.

> Can the server handle CREATE DATABASE requests in parallel? 

Sure.  But they have to be issued over different connections.
Any given connection can only do one thing at a time.

            regards, tom lane

Hi Tom.

Thanks for the quick response. In the function that I execute in each
thread, I create a new PGconn at the beginning. I expect that every
function caller (thread) should use a unique PGconn. I would not expect
that PGconn can be shared between threads. The documentation explicitly
says that.


On Thu, 2020-10-08 at 15:57 -0400, Tom Lane wrote:
> p.sun.fun@gmail.com writes:
> > I run this function using only one thread and everything works
> > great.
> > If I use two threads, the PQresultStatus(res) != PGRES_COMMAND_OK.
> 
> Are you trying to use the same PGconn from multiple threads?  That
> will not work --- at least not without interlocks that libpq on
> its own does not provide.
> 
> > Can the server handle CREATE DATABASE requests in parallel? 
> 
> Sure.  But they have to be issued over different connections.
> Any given connection can only do one thing at a time.
> 
>             regards, tom lane

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

p.sun.fun@gmail.com writes:
> Thanks for the quick response. In the function that I execute in each
> thread, I create a new PGconn at the beginning. I expect that every
> function caller (thread) should use a unique PGconn. I would not expect
> that PGconn can be shared between threads. The documentation explicitly
> says that.

In that case you need to probe a little deeper.  What error message(s)
are you getting?  Is there anything relevant in the server's log?

            regards, tom lane

> On Oct 8, 2020, at 2:30 PM, p.sun.fun@gmail.com wrote:
>
> 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.
>
I would try using dbname=postgres.  If you get the same error, I would say your server is in single user mode?

p.sun.fun@gmail.com writes:
> 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.

You'd be better off to connect to some other database than template1;
not only just for this purpose, but in general.  If you are issuing
random commands in template1, there's a risk of modifying that database
unexpectedly and thereby changing the contents of databases created
in future.

            regards, tom lane

On Thu, 2020-10-08 at 14:35 -0600, Rob Sargent wrote:
> > On Oct 8, 2020, at 2:30 PM, p.sun.fun@gmail.com wrote:
> > 
> > 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.
> > 
> I would try using dbname=postgres.  
aaaaand.... it works. Two databases have been created with return
status "COMMAND_OK".

> If you get the same error, I would say your server is in single user
> mode?
> 

On Thu, 2020-10-08 at 14:43 -0600, Rob Sargent wrote:
> 
> 
> > On Oct 8, 2020, at 2:41 PM, p.sun.fun@gmail.com wrote:
> > 
> > On Thu, 2020-10-08 at 14:35 -0600, Rob Sargent wrote:
> > > > On Oct 8, 2020, at 2:30 PM, p.sun.fun@gmail.com wrote:
> > > > 
> > > > 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.
> > > > 
> > > I would try using dbname=postgres.  
> > aaaaand.... it works. Two databases have been created with return
> > status "COMMAND_OK".
> > 
> > > If you get the same error, I would say your server is in single
> > > user
> > > mode?
> 
> And if you try it again using template1?
> 

Nope, the same errors:
// 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 Oct 8, 2020, at 2:46 PM, p.sun.fun@gmail.com wrote:
>
> On Thu, 2020-10-08 at 14:43 -0600, Rob Sargent wrote:
>>
>>
>>> On Oct 8, 2020, at 2:41 PM, p.sun.fun@gmail.com wrote:
>>>
>>> On Thu, 2020-10-08 at 14:35 -0600, Rob Sargent wrote:
>>>>> On Oct 8, 2020, at 2:30 PM, p.sun.fun@gmail.com wrote:
>>>>>
>>>>> 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.
>>>>>
>>>> I would try using dbname=postgres.
>>> aaaaand.... it works. Two databases have been created with return
>>> status "COMMAND_OK".
>>>
>>>> If you get the same error, I would say your server is in single
>>>> user
>>>> mode?
>>
>> And if you try it again using template1?
>>
>
> Nope, the same errors:
> // 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.
>
>
>
OK, well that’s a special db.  Didn’t know it was that special, though!

On Thu, 2020-10-08 at 16:36 -0400, Tom Lane wrote:
> p.sun.fun@gmail.com writes:
> > 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.
> 
> You'd be better off to connect to some other database than template1;

What would be the best strategy to create a database from user
standpoint of view? If we have an API to create a database on a server.
The user doesn't know about database(s) yet. The possible option would
be: to check the result status and if is not COMMAND_OK, probably
connection should be dropped and established again.  

May be not so elegant but at least safe and stable. 

> not only just for this purpose, but in general.  If you are issuing
> random commands in template1, there's a risk of modifying that
> database
> unexpectedly and thereby changing the contents of databases created
> in future.
> 
>             regards, tom lane

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

p.sun.fun@gmail.com writes:
> On Thu, 2020-10-08 at 16:36 -0400, Tom Lane wrote:
>> You'd be better off to connect to some other database than template1;

> What would be the best strategy to create a database from user
> standpoint of view?

Connect to the postgres database.  That's what it's there for.

            regards, tom lane

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. 

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.