Re: bgwriter interfering with consistent view of system tables?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sean Chittenden <chitt(at)speakeasy(dot)net>
Cc: PGBugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: bgwriter interfering with consistent view of system tables?
Date: 2004-10-04 16:51:55
Message-ID: 246.1096908715@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Sean Chittenden <chitt(at)speakeasy(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-10-04 17:02:33 Re: BUG #1277: plpgsql EXECUTE bug in beta3
Previous Message Tom Lane 2004-10-04 15:44:34 Re: Denial of service via VACUUM, all backends exit and restart...