pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLock a viewe
4+ messages / 3 participants
[nested] [flat]
* Lock a viewe
@ 2016-12-20 20:17 Gaetano Mendola <mendola@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Gaetano Mendola @ 2016-12-20 20:17 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Lock a viewe
@ 2016-12-26 19:34 Luis Marin <luismarinaray@gmail.com>
parent: Gaetano Mendola <mendola@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Luis Marin @ 2016-12-26 19:34 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: pgsql-admin
Hi,
Have you tried,
https://wiki.postgresql.org/wiki/Lock_Monitoring
https://www.postgresql.org/docs/9.1/static/explicit-locking.html
https://www.postgresql.org/docs/9.1/static/sql-lock.html
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
>
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Lock a viewe
@ 2016-12-26 19:53 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Gaetano Mendola <mendola@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2016-12-26 19:53 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Lock a viewe
@ 2017-01-04 20:32 Gaetano Mendola <mendola@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Gaetano Mendola @ 2017-01-04 20:32 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-01-04 20:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-12-20 20:17 Lock a viewe Gaetano Mendola <mendola@gmail.com>
2016-12-26 19:34 ` Luis Marin <luismarinaray@gmail.com>
2016-12-26 19:53 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2017-01-04 20:32 ` Gaetano Mendola <mendola@gmail.com>
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