All good ideas, unfortunately, we can't change the inserting applicatin code easily.
> -----Original Message----- > From: Simon Riggs [mailto:[EMAIL PROTECTED] > 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 > > > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
