Thread: Race condition with libpq

Hello,

PGsql v7.3.2

I'm trying to:

1) determine if a database exists;
2) delete if it does; and
3) create a new database of the same name.

The code performs a PQsetdbLogin(... dbname='somedb' ...); (I would prefer
to use PQconnectdb(), since PQsetdbLogin appears to be deprecated; but there
is no libpq accessor function to obtain addr from a PGconn.)

If the login status returns OK, then I know that the database exists. So,
first the PGconn needs to be closed, via a call to PQfinish(). Next, I use a
different connection (one opened against the "template1" database) to
execute a "DROP DATABASE somedb".

This succeeds if, and only if, I am running gdb (ddd front-end) in single
step mode. If just executing without any time lag between calls (i.e. no
human interaction), then I consistently and repeatably receive the error
"Drop database failed : ERROR:  DROP DATABASE: database \"somedb\" is being
accessed by other users\n".

The "other user" is the PGconn that was just released using a PGfinish!

The code is not using any asynchronous calls, so I would expect that when
PQfinish() has been called, any lock that my application has on the database
would be released. How is an application to know when a resource that it is
no longer using will be freed for it to use again? It appears quite
problematic if this cannot be reliably determined.

-----------------------------


As a someone related issue, it's not clear from the documentation exactly
HOW to use the libpq functions for some basic cases.

I consider it a reasonable expectation to connect to the PGsql server
without opening any database, and being able to perform some basic
operations that do not rely on having an open database (CREATE USER, ALTER
USER, DROP USER, CREATE DATABASE, DROP DATABASE, etc). Unfortunately, if
<dbname=> is not provided, PQconnectdb seems to utilize the supplied <user=>
as the database to attempt to open (at least, that's what the error
reports). Typically, the user name will not be the name of a database in the
system, and of course the attempt will fail.

The only way to access a freshly created cluster to create some tables seems
to be to perform a PQconnectdb with dbname='template1'. Is this guaranteed
to always exist? And is it really the design intent to force the application
to connect to template1 in order to create a database? Both seem like bugs
to me; or at the very least, undesirable (and apparently undocumented)
"features".


Thanks,
Dietmar May

"Dietmar May" <dcmay@dmis.com> writes:
> ... If just executing without any time lag between calls (i.e. no
> human interaction), then I consistently and repeatably receive the error
> "Drop database failed : ERROR:  DROP DATABASE: database \"somedb\" is being
> accessed by other users\n".

> The "other user" is the PGconn that was just released using a PGfinish!

Yeah, we have seen this behavior reported before; though I've not found
it to be easily reproducible myself.  It's pretty hard to believe that a
backend shutdown would take longer than a backend startup; so my take on
it is that for some reason, the kernel is not choosing to promptly
report closure of the first connection to the first backend.  Normally
it doesn't matter much if a backend is slow to exit ... but it does if
you wanna drop its database.

My recommendation would be not to connect to the victim database at all.
Connect to template1, then try to issue a DROP DATABASE.  You don't much
care if that fails.  Then issue CREATE DATABASE.

A marginally cleaner-sounding approach is to look in pg_database (from
your connection to template1) to see if you need to issue the DROP.
But when you think about race conditions with other people doing the
same procedure, I'm not sure this is really any cleaner.

> I consider it a reasonable expectation to connect to the PGsql server
> without opening any database, and being able to perform some basic
> operations that do not rely on having an open database (CREATE USER, ALTER
> USER, DROP USER, CREATE DATABASE, DROP DATABASE, etc).

There is no such thing as a "connection that doesn't open any database",
and I do not think there could be.  The convention is to connect to
template1, which is supposed to be there.  We could perhaps put stronger
restrictions on what you could do to template1, to positively ensure
that template1 is always available to connect to --- but that wouldn't
really solve the problem you discuss above.
        regards, tom lane

"Dietmar May" <dcmay@dmis.com> writes:
> Would I be right in guessing (from your comments) that if PQexec issues a
> command that does not return a result set, that it executes it
> asynchronously?

PQexec is synchronous.  Connection closure is not, though: there is no
response from the backend (and hardly could be).

> That's the only way that I could see this could fail -- because my code
> can't issue a CREATE DATABASE until after the PQexec("DROP DATABASE") has
> returned.

You're missing the point.  It's the DROP that's failing, not the CREATE,
and it's failing because the old backend is still connected to the
victim database.

> I guess I had misread the first statement as meaning "you can drop the
> template1 database and have a cluster that doesn't contain a database called
> template1"; but this is apparently not a valid interpretation, since PGsql
> seems to use template1 for many internal operations.

It is not used for any "internal" operations.  It is conventionally
present so that clients have a standard place to connect to.
        regards, tom lane

"Dietmar May" <dcmay@dmis.com> writes:
> So, why can't the server-side code that closes the connection release any
> resources associated with that connection before the socket is closed? It
> seems that doing so could fix this problem quite cleanly.

How would that help?  The client isn't waiting around for it, because it
already closed its socket.

> Perhaps I'm misunderstanding the manual - "4.1: When a new database is
> created, the template database [template1] is essentially cloned.

At least in recent versions, template1 is only the default template,
not the sole possibility.

In theory you could in fact run without any template1, but it would
break enough client-side code that I doubt anyone would find it useful
to do so in practice.
        regards, tom lane

Hi Tom,

Thanks for your response. My post (non-member) was delayed for quite some
time; and I'm grateful to actually get a response from someone.

> "Dietmar May" <dcmay@dmis.com> writes:
> > ... If just executing without any time lag between calls (i.e. no
> > human interaction), then I consistently and repeatably receive the error
> > "Drop database failed : ERROR:  DROP DATABASE: database
> \"somedb\" is being
> > accessed by other users\n".
>
> > The "other user" is the PGconn that was just released using a PGfinish!
>
> Yeah, we have seen this behavior reported before; though I've not found
> it to be easily reproducible myself.  It's pretty hard to believe that a
> backend shutdown would take longer than a backend startup; so my take on
> it is that for some reason, the kernel is not choosing to promptly
> report closure of the first connection to the first backend.  Normally
> it doesn't matter much if a backend is slow to exit ... but it does if
> you wanna drop its database.

FYI, I've "solved" the problem by adding a 1ms sleep before issuing the DROP
DATABASE command. Yuck!

It is obviously a race condition, though.

Would I be right in guessing (from your comments) that if PQexec issues a
command that does not return a result set, that it executes it
asynchronously? In other words, after verifying that there are no error
conditions (like dropping a table that doesn't exist), it starts off a
back-end thread (or perhaps connects to a thread from a pool) that actually
performs the operation.

That's the only way that I could see this could fail -- because my code
can't issue a CREATE DATABASE until after the PQexec("DROP DATABASE") has
returned.

If this is indeed the case, I would expect the problem to pop up again if I
were dropping a large table with a lot of data; because that would likely
take longer than my "sleep" time.

> My recommendation would be not to connect to the victim database at all.
> Connect to template1, then try to issue a DROP DATABASE.  You don't much
> care if that fails.  Then issue CREATE DATABASE.

Unfortunately, since I'm working on a generic C++ wrapper for PGsql, I do
care if it fails. I have no way to distinguish between a DROP DATABASE
failing because the table doesn't exist vs. failing because of some valid
error condition, like another process using the table (other than perhaps
the very undesirable approach of parsing out the text of PQerrorMessage).

> > I consider it a reasonable expectation to connect to the PGsql server
> > without opening any database, and being able to perform some basic
> > operations that do not rely on having an open database (CREATE
> USER, ALTER
> > USER, DROP USER, CREATE DATABASE, DROP DATABASE, etc).
>
> There is no such thing as a "connection that doesn't open any database",
> and I do not think there could be.  The convention is to connect to
> template1, which is supposed to be there.  We could perhaps put stronger
> restrictions on what you could do to template1, to positively ensure
> that template1 is always available to connect to --- but that wouldn't
> really solve the problem you discuss above.

The admin guide 4.1.1 states, "for example, one could drop template1 and
recreate it from template0 without any ill effects". [4.1, on the other
hand, states "this database is called template1 and cannot be deleted".]

I guess I had misread the first statement as meaning "you can drop the
template1 database and have a cluster that doesn't contain a database called
template1"; but this is apparently not a valid interpretation, since PGsql
seems to use template1 for many internal operations.

I don't understand the internals of PGsql - not sure I understand your
comment that "I don't think there could be a connection that doesn't open
any database". Perhaps that is because PGsql internally manages information
about users and databases within some internal database tables.

Nonetheless, from an application perspective (that is, regardless of the
internal operation of PGsql), creating users and databases have nothing to
do with a specific database. A user is at the connection level, not the
database level. Please correct me if I'm wrong, but I don't think it's
possible to create a user that applies only to one database in the system.
So, as a programmer using the system, I think it's undesirable to deal with
template1 for these kinds of operations, even if that's what PGsql does
internally.

Thanks again for your response,
Dietmar

Thanks Tom,

> You're missing the point.  It's the DROP that's failing, not the CREATE,
> and it's failing because the old backend is still connected to the
> victim database.

Actually, I understand that it's the DROP that's failing. What I didn't
understand is that connection closure was asynchronous, but everything else
is synchronous.

So, why can't the server-side code that closes the connection release any
resources associated with that connection before the socket is closed? It
seems that doing so could fix this problem quite cleanly.

> [template1] is not used for any "internal" operations.  It is
> conventionally present so that clients have a standard place to
> connect to.

Perhaps I'm misunderstanding the manual - "4.1: When a new database is
created, the template database [template1] is essentially cloned. This means
that any changes you make in template1 are propagated to all subsequently
created databases."

Thanks again for your quick response,
Dietmar

Hi Tom,

> > So, why can't the server-side code that closes the connection
> > release any resources associated with that connection before
> > the socket is closed? It seems that doing so could fix this
> > problem quite cleanly.
>
> How would that help?  The client isn't waiting around for it,
> because it already closed its socket.

Ah-ha! Your comments are really helping to narrow down the problem. Maybe
we're getting to a solution here.

Perhaps instead of simply shutting down the connection, PQfinish() could
request the server to close the connection, and block until the connection
is closed. The server would first clean up any resources associated with the
connection, and then close the connection. The client code would then be
sure that it no longer has any resources in use when the socket shuts down.

This would take care of the race condition, not only for the case of DROP
DATABASE, but perhaps for other cases as well. After all, there is really no
guaranteed order of process execution, nor of process execution scheduling,
nor of the priority at which various processes are running.

In theory, a whole host of other similar race condition failures could occur
due to the current design of connection closure. By changing this operation
slightly, the possibility of these race conditions can be eliminated.

The current behavior could perhaps still be supported using an extra boolean
parameter to PQfinish(), in case someone doesn't need to ensure that
server-side resources are released before returning from PQfinish().

Regards,
Dietmar