Lock a viewe

From: Gaetano Mendola <mendola(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Lock a viewe
Date: 2016-12-20 20:17:42
Message-ID: CAJycT5rFswAFAykB7aZve7ZF=dm_coTpp2Bb7a4SQmRYqoL7sA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,
I was wondering if there is a clean view to lock the usage of a view.

Basically during a schema migration, with applications still running a
typical
schema change is:

BEGIN;
ALTER TABLE x ADD COLUMN (a INTEGER);
CREATE OR REPLACE VIEW v_x
AS
SELECT a,b FROM x;
COMMIT;

now the issue is that if an application performs a:

SELECT * from v_x;

between the ALTER and the view redefinition then a deadlock happens.

I'm preventing this issue doing a:

ALTER VIEW v_x ALTER COLUMN b DROP DEFAULT;
(anyway there was no default on the view)
before the ALTER TABLE, that's basically reorders the locks sequence
avoiding the dead lock.

Is there a clean way to achieve it without the "hack"?

GM

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message neos 2016-12-21 05:00:37 Too long startup time after crash.
Previous Message xu jian 2016-12-20 15:33:57 答复: Does special index type have index bloat issue? for instance gist, gin, brin?