Re: Race condition with libpq

"Dietmar May" <dcmay(at)dmis(dot)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