Thread: BUG #15384: dropping views and materialized views
The following bug has been logged on the website: Bug reference: 15384 Logged by: Terence Zekveld Email address: terence.zekveld@eoh.com PostgreSQL version: 9.6.1 Operating system: Windows Description: Sometimes we change a view to a materialized view. We have a general upgrading script to update all our postgres db's to keep them in sync. So I like to add this to my general upgrading script before creating the materialized view: DROP VIEW IF EXISTS theschema.theviewname; -- for in case this db still has the 'un'materialized view DROP MATERIALIZED VIEW IF EXISTS theschema.theviewname; -- for in case this db already has an older version of the materialized view CREATE MATERIALIZED VIEW theschema.theviewname AS ... But either the 1st or the 2nd DROP functions throw an error, either "theschema.theviewname is not a view" or "theschema.theviewname is not a materialized view". I would think these errors are not relevant when using the "IF EXISTS" option, i.e. it should execute both, 'skipping' the one that refers to the incorrect type of view... Kind regards, and thanks for a great db, Terence
On Fri, Sep 14, 2018 at 4:41 AM PG Bug reporting form <noreply@postgresql.org> wrote: > > The following bug has been logged on the website: > > Bug reference: 15384 > Logged by: Terence Zekveld > Email address: terence.zekveld@eoh.com > PostgreSQL version: 9.6.1 > Operating system: Windows > Description: > > Sometimes we change a view to a materialized view. > > We have a general upgrading script to update all our postgres db's to keep > them in sync. > > So I like to add this to my general upgrading script before creating the > materialized view: > > DROP VIEW IF EXISTS theschema.theviewname; -- > for in case this db still has the 'un'materialized view > DROP MATERIALIZED VIEW IF EXISTS theschema.theviewname; -- for in case this > db already has an older version of the materialized view > CREATE MATERIALIZED VIEW theschema.theviewname AS ... > > But either the 1st or the 2nd DROP functions throw an error, either > "theschema.theviewname is not a view" or "theschema.theviewname is not a > materialized view". > > I would think these errors are not relevant when using the "IF EXISTS" > option, i.e. it should execute both, 'skipping' the one that refers to the > incorrect type of view... One option here is to wrap those commands in a DO block and trap the error. I consider this to be SOP for standardized schema refresh scripts. merlin
Hi Merlin Thanks for the speedy response. Don't know that I agree with your assessment of the issue though... Think it would be cool if this could maybe be addressed in the future? In the meantime I will see if I can read the metadata in the db to determine if a view is materialized or not and then buildthe applicable 'drop' statement from there. Best regards, Terence Zekveld Developer EOH Roads & Highways A division of EOH Industrial Technologies (Pty) Ltd 70 Regency Drive, Route 21 Corporate Park, Centurion Tel: +27 (12) 346 1255 | Mobile: +27 (79) 696 5363 terence.zekveld@eoh.com | www.eoh.co.za Consulting | Technology | Outsourcing -----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.com] Sent: 14 September 2018 02:55 PM To: Terence Zekveld; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #15384: dropping views and materialized views On Fri, Sep 14, 2018 at 4:41 AM PG Bug reporting form <noreply@postgresql.org> wrote: > > The following bug has been logged on the website: > > Bug reference: 15384 > Logged by: Terence Zekveld > Email address: terence.zekveld@eoh.com > PostgreSQL version: 9.6.1 > Operating system: Windows > Description: > > Sometimes we change a view to a materialized view. > > We have a general upgrading script to update all our postgres db's to keep > them in sync. > > So I like to add this to my general upgrading script before creating the > materialized view: > > DROP VIEW IF EXISTS theschema.theviewname; -- > for in case this db still has the 'un'materialized view > DROP MATERIALIZED VIEW IF EXISTS theschema.theviewname; -- for in case this > db already has an older version of the materialized view > CREATE MATERIALIZED VIEW theschema.theviewname AS ... > > But either the 1st or the 2nd DROP functions throw an error, either > "theschema.theviewname is not a view" or "theschema.theviewname is not a > materialized view". > > I would think these errors are not relevant when using the "IF EXISTS" > option, i.e. it should execute both, 'skipping' the one that refers to the > incorrect type of view... One option here is to wrap those commands in a DO block and trap the error. I consider this to be SOP for standardized schema refresh scripts. merlin
Terence Zekveld <Terence.Zekveld@eoh.com> writes:
>> But either the 1st or the 2nd DROP functions throw an error, either
>> "theschema.theviewname is not a view" or "theschema.theviewname is not a
>> materialized view".
>> I would think these errors are not relevant when using the "IF EXISTS"
>> option, i.e. it should execute both, 'skipping' the one that refers to the
>> incorrect type of view...
We've discussed this before, but the current policy is that IF [NOT]
EXISTS are narrowly read as applying only to object-does-not-exist
or object-already-exists errors. They're not "get out of jail free"
cards. If you start opening that up, you get into all sorts of
squishy questions; for instance, should a permissions failure become
a non-error?
In the particular case of DROP IF EXISTS, there's a good rationale for
treating doesn't-exist specially: the state after the command is the same
whether the object was there or not, so it's reasonable to consider
doesn't-exist as success rather than an error condition. This does not
hold when the problem is there's-an-object-but-it's-the-wrong-type; then,
that object is still blocking creation of a new object by that name.
I think a more reasonable way to attack this would be, not to make IF
EXISTS more permissive, but to have a distinct command type that's
specifically defined as not caring about the relkind, perhaps
DROP RELATION. v11's DROP ROUTINE is a precedent ...
regards, tom lane
Thanks Tom
I checked the 'DROP ROUTINE' documentation.
There ROUTINE is sort of a generic name for several object kinds.
Something similar for VIEW and MATERIALIZED VIEW would be helpful for my case.
Best regards,
Terence Zekveld
Senior Developer
EOH Roads & Highways
A division of EOH Industrial Technologies (Pty) Ltd
70 Regency Drive, Route 21 Corporate Park, Centurion
Tel: +27 (12) 346 1255 | Mobile: +27 (79) 696 5363
terence.zekveld@eoh.com | www.eoh.co.za
Consulting | Technology | Outsourcing
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 14 September 2018 04:42 PM
To: Terence Zekveld
Cc: Merlin Moncure; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15384: dropping views and materialized views
Terence Zekveld <Terence.Zekveld@eoh.com> writes:
>> But either the 1st or the 2nd DROP functions throw an error, either
>> "theschema.theviewname is not a view" or "theschema.theviewname is not a
>> materialized view".
>> I would think these errors are not relevant when using the "IF EXISTS"
>> option, i.e. it should execute both, 'skipping' the one that refers to the
>> incorrect type of view...
We've discussed this before, but the current policy is that IF [NOT]
EXISTS are narrowly read as applying only to object-does-not-exist
or object-already-exists errors. They're not "get out of jail free"
cards. If you start opening that up, you get into all sorts of
squishy questions; for instance, should a permissions failure become
a non-error?
In the particular case of DROP IF EXISTS, there's a good rationale for
treating doesn't-exist specially: the state after the command is the same
whether the object was there or not, so it's reasonable to consider
doesn't-exist as success rather than an error condition. This does not
hold when the problem is there's-an-object-but-it's-the-wrong-type; then,
that object is still blocking creation of a new object by that name.
I think a more reasonable way to attack this would be, not to make IF
EXISTS more permissive, but to have a distinct command type that's
specifically defined as not caring about the relkind, perhaps
DROP RELATION. v11's DROP ROUTINE is a precedent ...
regards, tom lane