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: Tue, 7 Feb 2006 17:57:03 -0500
Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C2BCC2F4@mailserver.sandvine.com>
Thread-Topic: [PERFORM] partitioning and locking problems
Thread-Index: AcYsMu7hyeeAf+K0S/+g7J0iFrCDmwABwmKA
From: "Marc Morin" <marc@sandvine.com>
To: "Simon Riggs" <simon@2ndquadrant.com>
Cc: "Markus Schaber" <schabi@logix-tt.com>, <pgsql-performance@postgresql.org>

All good ideas, unfortunately, we can't change the inserting applicatin
code easily.=20

> -----Original Message-----
> From: Simon Riggs [mailto:simon@2ndquadrant.com]=20
> Sent: Tuesday, February 07, 2006 5:09 PM
> To: Marc Morin
> Cc: Markus Schaber; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] partitioning and locking problems
>=20
> On Thu, 2006-02-02 at 11:27 -0500, Marc Morin wrote:
>=20
> > > > 	1- long running report is running on view
> > > > 	2- continuous inserters into view into a table=20
> via a rule
> > > > 	3- truncate or rule change occurs, taking an=20
> 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=20
> in #1.   Results
> > > > in loss of insert data granularity (important for our=20
> application).
>=20
> > Using a separate lock table is what we've decided to do in this=20
> > particular case to serialize #1 and #3.  Inserters don't take this=20
> > lock and as such will not be stalled.
>=20
> Would it not be simpler to have the Inserters change from one=20
> table to another either upon command, on a fixed timing cycle=20
> or even better based upon one of the inserted values=20
> (Logdate?) (or all 3?). (Requires changes in the application=20
> layer: 3GL or db functions).
>=20
> The truncates can wait until the data has stopped being used.
>=20
> I'd be disinclined to using the locking system as a scheduling tool.
>=20
> Best Regards, Simon Riggs
>=20
>=20
>=20