Re: bgwriter interfering with consistent view of system tables?

>> 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