X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 147479DCAE0 for ; Thu, 2 Feb 2006 12:29:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01051-03 for ; Thu, 2 Feb 2006 12:29:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailserver.sandvine.com (sandvine.com [199.243.201.138]) by postgresql.org (Postfix) with ESMTP id AADBE9DCA1C for ; Thu, 2 Feb 2006 12:29:06 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: Re: partitioning and locking problems Date: Thu, 2 Feb 2006 11:27:38 -0500 Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C2BCC2A6@mailserver.sandvine.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] partitioning and locking problems Thread-Index: AcYn9jI+Hg9aRe0DS42sG9SqksFpBAAH3Avw From: "Marc Morin" To: "Markus Schaber" Cc: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200602/40 X-Sequence-Number: 17026 Using a separate lock table is what we've decided to do in this particular case to serialize #1 and #3. Inserters don't take this lock and as such will not be stalled.=20 > -----Original Message----- > From: Markus Schaber [mailto:schabi@logix-tt.com]=20 > Sent: Thursday, February 02, 2006 7:44 AM > To: Marc Morin > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] partitioning and locking problems >=20 > Hi, Marc, >=20 > Marc Morin wrote: >=20 > > 1- long running report is running on view > > 2- continuous inserters into view into a table via a rule > > 3- truncate or rule change occurs, taking an exclusive lock. > > Must wait for #1 to finish. > > 4- new reports and inserters must now wait for #3. > > 5- now everyone is waiting for a single query in #1. Results > > in loss of insert data granularity (important for our application). >=20 > Apart from having two separate views (one for report, one for=20 > insert) as Richard suggested: >=20 > If you have fixed times for #3, don't start any #1 that won't=20 > finish before it's time for #3. >=20 > You could also use the LOCK command on an empty lock table at=20 > the beginning of each #1 or #3 transaction to prevent #3 from=20 > getting the view lock before #1 is finished. >=20 >=20 > HTH, > Markus >=20 > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS >=20 > Fight against software patents in EU! www.ffii.org=20 > www.nosoftwarepatents.org >=20 >=20