From: Marc Morin <marc@sandvine.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: partitioning and locking problems
Date: Tue, 7 Feb 2006 17:57:03 -0500
Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C2BCC2F4@mailserver.sandvine.com> (raw)
All good ideas, unfortunately, we can't change the inserting applicatin
code easily.
> -----Original Message-----
> From: Simon Riggs [mailto:simon@2ndquadrant.com]
> 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
>
> On Thu, 2006-02-02 at 11:27 -0500, Marc Morin wrote:
>
> > > > 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).
>
> > 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.
>
> Would it not be simpler to have the Inserters change from one
> table to another either upon command, on a fixed timing cycle
> or even better based upon one of the inserted values
> (Logdate?) (or all 3?). (Requires changes in the application
> layer: 3GL or db functions).
>
> The truncates can wait until the data has stopped being used.
>
> I'd be disinclined to using the locking system as a scheduling tool.
>
> Best Regards, Simon Riggs
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marc@sandvine.com, simon@2ndquadrant.com, schabi@logix-tt.com
Subject: Re: partitioning and locking problems
In-Reply-To: <2BCEB9A37A4D354AA276774EE13FB8C2BCC2F4@mailserver.sandvine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox