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: 4F45A58B-15D5-11D9-AD00-000A95C705DC@speakeasy.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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
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

Responses

Browse pgsql-bugs by date

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