Re: CREATE DATABASE/DROP DATABASE race conditions

Florian Weimer <Weimer(at)CERT(dot)Uni-Stuttgart(dot)DE> writes:
> \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

Well, this is not what you asserted to begin with. The reason the above
fails is that it takes a nonzero amount of time for a backend to exit
after it detects client disconnect. The "other user" being complained
of is simply your own old backend that had been used for the CREATE
TABLE command. (It doesn't help any that psql doesn't drop the old
connection till it's successfully established a new one; so the normal
backend startup time doesn't offer any offsetting delay in this
scenario.)

It might be possible to tweak the FE/BE protocol to allow this to be
handled more carefully. Right now the normal case is

client sends X
client closes connection

backend closes connection

backend cleans up

but we could do

client sends X

client waits to see EOF

backend cleans up

backend closes connection

client closes connection

The backend code change would be trivial (instead of explicitly closing
the socket, just let it be closed by the kernel when the process exits).
The frontend change would be a little less trivial, and would be wanted
only by a few clients anyway. Not sure how to handle that; maybe create
a variant version of PQfinish ...

regards, tom lane