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
