On Wed, Apr 11, 2007 at 12:50:37AM +0200, Tobias Brox wrote: > We had problems again, caused by long running transactions. I'm > monitoring the pg_stat_activity view, checking the query_start of all > requests that are not idle - but this one slipped under the radar as the > application was running frequent queries towards the database. > > That's not what concerns me most. We had two databases running under > postgres at this host - like, main production database (A) and a > separate smaller database for a separate project (B). As far as I > understood postgres philosophy, the databases should be isolated from > each other, i.e. one are not allowed to create a query that goes across > the database borders (select * from A.customers join B.logins ...). So, > I was surprised to see that the application working towards database B > managed to jam up database A, to the extent that we couldn't get A > vacuumed properly. Vacuums do ignore other databases, except for shared relations such as pg_database. If one of the databases wasn't being vacuumed properly it means there was in fact a transaction open. Note that until recently, vacuums wouldn't ignore other vacuums, so a long-running vacuum would prevent repeated vacuums on the same table from accomplishing much. Are you sure that your monitoring doesn't accidentally ignore backends marked as <IDLE> in transaction? -- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
