From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> |
Cc: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: views, queries, and locks |
Date: | 2012-04-03 18:36:11 |
Message-ID: | CAHyXU0wT5tXRJfFoeEYUVY1qPt_dkG+QS1q7D3nGHx=qmPDCGQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Jon Nelson | 2012-04-03 18:41:20 | Re: views, queries, and locks |
Previous Message | leaf_yxj | 2012-04-03 18:13:18 | Re: Please help me to take a look of the erros in my functions. Thanks. |