From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Gaetano Mendola <mendola(at)gmail(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Lock a viewe |
Date: | 2016-12-26 19:53:15 |
Message-ID: | 20161226195315.3frvv36pnvf2rk2b@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Gaetano Mendola wrote:
> 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.
So what you want is
LOCK VIEW f;
(which is exactly what your hack does) but we don't allow that: you can
only lock tables. I wonder what's the reason for that restriction.
AFAICT changing it is trivial, since it just requires a very minor
change in RangeVarCallbackForLockTable() to allow views.
> Is there a clean way to achieve it without the "hack"?
I can't think of anything.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Viktor Bojović | 2016-12-26 22:15:42 | localized fulltext search |
Previous Message | Luis Marin | 2016-12-26 19:34:53 | Re: Lock a viewe |