Re: views, queries, and locks

On Tue, Apr 3, 2012 at 1:36 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Apr 3, 2012 at 12:30 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
>> On Tue, Apr 3, 2012 at 12:16 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>> On Tue, Apr 3, 2012 at 12:01 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> 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