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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
