Sean Chittenden <chitt@speakeasy.net> writes:
> When making lots of DDL changes to a database (I believe this includes
> temp tables too), delayed flushing of dirty buffers from the system
> catalogs is causing a severe problem with maintaining a consistent view
> of the structure of the database.
This analysis is completely bogus.
> % make -f Makefile.bug
> psql -c "DROP DATABASE mydb" template1
> DROP DATABASE
> psql -c "CREATE DATABASE mydb" template1
> ERROR: source database "template1" is being accessed by other users
It's always been possible for this to happen, primarily because libpq
doesn't wait around for the connected backend to exit. If the kernel
prefers to schedule other processes then the old backend may still be
alive when the new one tries to do CREATE DATABASE. There is nothing
stopping the old one from exiting, it's just that the kernel hasn't
given the old backend any cycles at all.
There's been some discussion of making PQfinish() wait to observe
connection closure, which would guarantee that the backend has exited
in the non-SSL-connection case. It's not clear how well it would work
in the SSL case, though. In any case it's a bit of a band-aid solution.
I think the real solution is to find a way to not need the "accessed by
other users" interlock for CREATE DATABASE.
regards, tom lane