Date: Mon, 26 Dec 2016 16:53:15 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Gaetano Mendola <mendola@gmail.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Lock a viewe
Message-ID: <20161226195315.3frvv36pnvf2rk2b@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAJycT5rFswAFAykB7aZve7ZF=dm_coTpp2Bb7a4SQmRYqoL7sA@mail.gmail.com>
User-Agent: NeoMutt/20161104 (1.7.1)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin