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
Date: Mon, 26 Dec 2016 16:53:15 -0300
Message-ID: <20161226195315.3frvv36pnvf2rk2b@alvherre.pgsql> (raw)
In-Reply-To: <CAJycT5rFswAFAykB7aZve7ZF=dm_coTpp2Bb7a4SQmRYqoL7sA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@2ndquadrant.com, mendola@gmail.com
Subject: Re: Lock a viewe
In-Reply-To: <20161226195315.3frvv36pnvf2rk2b@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox