Обсуждение: [ADMIN] Lock a viewe
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;
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
avoiding the dead lock.
Is there a clean way to achieve it without the "hack"?
GM
Hi,
Have you tried,
Regards
2016-12-20 16:17 GMT-04:00 Gaetano Mendola <mendola@gmail.com>:
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
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
On Mon, 26 Dec 2016 at 20:53 Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
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;
Exactly that, it would be ideal indeed that was exactly my first attempt.
Regards.
Regards.