Thread: 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. For these examples, I'd create a quick Makefile to aid in testing. printf "testing_delay:" > Makefile.bug printf "\tpsql -c 'DROP DATABASE mydb' template1" >> Makefile.bug printf "\tpsql -c 'CREATE DATABASE mydb' template1" >> Makefile.bug To reproduce and test this bug, issue `make -f Makefile.bug`. With the following config settings: # - Background writer - bgwriter_delay = 5000 # 10-5000 milliseconds bgwriter_percent = 1 # 0-100% of dirty buffers bgwriter_maxpages = 1 # 1-1000 buffers max at once it is *very* easy to reproduce this problem (note, there is a bug in the default config, the min percent is 1, no 0 as the comment suggests). With the default settings, it has been harder to spot on my laptop. I believe that higher end systems with higher values will trip over this problem less frequently. With the settings set: % 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 *** Error code 1 The problem being, I've disconnected from template1 already, but the database hasn't flushed this to disk so the parent postmaster process isn't aware of the disconnection, so when I connect to the backend again, the newly created child has an inconsistent view of the current connections which prevents me from creating a new database (maybe the old backend is still around cleaning up and really hasn't exited, I'm not sure). I think the same phenomena used to exist with temp tables across connections that reconnected to a backend with the same backend # (ie, connect to backend 123, create a temp table, disconnect, reconnect and get backend 123, recreate the same temp table and you'll get an error... though I can't reproduce the temp table error right now, yay!). Anyway, Tom/Jan, this code seems to be your areas of expertise, could either of you take a look? -sc -- Sean Chittenden
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
>> 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