On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Tue, Apr 3, 2012 at 12:30 PM, Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> wrote: >> On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >>> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> wrote: >>>> I have a situation that I'd like some help resolving. >>>> Using PostgreSQL 8.4.<reasonably recent> on Linux, I have three things >>>> coming together that cause me pain. I have a VIEW used by a bunch of >>>> queries. Usually, these queries are fairly short (subsecond) but >>>> sometimes they can be very long (days). I also update this view with >>>> CREATE OR REPLACE VIEW every 15-30 minutes. What I observe, sometimes, >>>> is this: >>>> >>>> 1. query A comes in. It's a big one. >>>> 2. another process comes along, needs to update the view definition. >>>> It issues create or replace view. It blocks on [1]. >>>> 3. queries B through N come in, are blocked by [2], which is blocked by [1]. >>>> 4. pandemonium! >>>> >>>> I can reduce (some) but not eliminate the need to update the view >>>> multiple times a day. What might be some good ways to prevent queries >>>> B through N blocking? >>>> >>>> Addendum: I can work around the issue by timing out and failing the >>>> CREATE OR REPLACE VIEW (by canceling the query) after a short >>>> duration, but is there a better way? >>> >>> Yeah -- this is just asking for trouble. Why do you have to replace >>> the view every 30 minutes? Your solution is probably going to involve >>> not doing that. >> >> As I said, I can reduce the frequency, but not eliminate it. I'm >> curious - if views are little more than sql macros, by the time the >> query has begun to execute why is a lock still needed on the view >> definition? > > Generally speaking, in SQL, locks are held until the transaction > commits; there are tons of reasons why things have to work that way. > Anyways, I'm betting your requirement to have to re-CREATE the view > can be abstracted out somehow. I'm guessing you have some type of > table rotation going on? Close, but not quite. It's not rotation but every N minutes a newly-built table appears. I'd like that table to appear as part of the view as soon as possible. Regarding locks: I know that's how locks usually work - but what I'm asking is if holding on to the lock for the view, once the view has been 'expanded', is necessary at all. -- Jon -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
