Skip site navigation (1) Skip section navigation (2)

bgwriter interfering with consistent view of system tables?

From: Sean Chittenden <chitt(at)speakeasy(dot)net>
To: PGBugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: bgwriter interfering with consistent view of system tables?
Date: 2004-10-04 07:16:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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
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, 

Anyway, Tom/Jan, this code seems to be your areas of expertise, could 
either of you take a look? -sc

Sean Chittenden


pgsql-bugs by date

Next:From: Sean ChittendenDate: 2004-10-04 07:18:28
Subject: Denial of service via VACUUM, all backends exit and restart...
Previous:From: Sean ChittendenDate: 2004-10-04 07:11:06

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group