Re: views, queries, and locks

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.

merlin