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.
That doesn't surprise me at all: I couldn't think of what else it would've been.
% 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.
*shrug* It'd be good from a security stand point to wait if there is any chance the connection could be resurrected via a man-in-the-middle attack. As it stands, this isn't a real important bug given that the SQL is programatically created and it's trivial to throw in some kind of a sleep... still, it did bother me. I figured locks on tables were stored in stuffed into some kind of a refcount in shared memory segment and that the time needed to decrease the refcount would be insignificant or done as soon as the client signaled their intention to disconnect, not controlled by wait*(2) and the listening postmaster.
-sc
-- Sean Chittenden
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
