Re: CREATE DATABASE/DROP DATABASE race conditions

Tom Lane <tgl@sss.pgh.pa.us> writes:

>> You cannot reproduce this with "psql" or "createdb"/"dropdb".
>
> I'm inclined to think it's a bug in your application coding, then.

Well, sort of.

> psql certainly doesn't go out of its way to serialize operations.

Well, unless you play around with \connect, it uses a single
connection, which makes a difference.  Using \connect, I can reproduce
the underlying effect:

\connect template1 fw
CREATE DATABASE aaa;
\connect aaa fw;
CREATE TABLE a (a text);
\connect template1 fw
DROP DATABASE aaa;

sometimes results in:

You are now connected to database template1 as user fw.
CREATE DATABASE aaa;
CREATE DATABASE
You are now connected to database aaa as user fw.
CREATE TABLE a (a text);
CREATE
You are now connected to database template1 as user fw.
DROP DATABASE aaa;
psql:/tmp/t.sql:6: ERROR:  DROP DATABASE: database "aaa" is being accessed by other users

There was indeed a glitch in my test code which didn't retry often
enough (or pause long enough), so the wasn't created reliably.  I
think I've misinterpreted the results.

The behavior shown above using psql is annoying, but has to be
expected.  The standard appraoch to connection termination in the
front end/back end protocol is asynchronous, so front ends cannot know
when the back end process has actually released all locks (or what you
want to call it) on the template1 database.

Hmm, I think I can live with some exponential backoff algorithm.

--
Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          fax +49-711-685-5898