From: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> |
---|---|
To: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | views, queries, and locks |
Date: | 2012-04-03 17:01:30 |
Message-ID: | CAKuK5J0Xz18eyce4v2F3BUPpT-1irDX3NYsE2k5fjmcE7MBZ6w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
--
Jon
From | Date | Subject | |
---|---|---|---|
Next Message | Bartosz Dmytrak | 2012-04-03 17:08:09 | Re: Re: Please help me to take a look of the erros in my functions. Thanks. |
Previous Message | Robert Haas | 2012-04-03 16:25:20 | Re: Switching to Homebrew as recommended Mac install? |