BUG #15384: dropping views and materialized views

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15384: dropping views and materialized views

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15384
Logged by:          Terence Zekveld
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15384: dropping views and materialized views

Merlin Moncure-2
On Fri, Sep 14, 2018 at 4:41 AM PG Bug reporting form
<[hidden email]> wrote:

>
> The following bug has been logged on the website:
>
> Bug reference:      15384
> Logged by:          Terence Zekveld
> Email address:      [hidden email]
> 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

Reply | Threaded
Open this post in threaded view
|

RE: BUG #15384: dropping views and materialized views

Terence Zekveld
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 build the 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
[hidden email]    |    www.eoh.co.za                                                           

Consulting | Technology | Outsourcing





-----Original Message-----
From: Merlin Moncure [mailto:[hidden email]]
Sent: 14 September 2018 02:55 PM
To: Terence Zekveld; [hidden email]
Subject: Re: BUG #15384: dropping views and materialized views

On Fri, Sep 14, 2018 at 4:41 AM PG Bug reporting form
<[hidden email]> wrote:

>
> The following bug has been logged on the website:
>
> Bug reference:      15384
> Logged by:          Terence Zekveld
> Email address:      [hidden email]
> 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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15384: dropping views and materialized views

Tom Lane-2
Terence Zekveld <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

RE: BUG #15384: dropping views and materialized views

Terence Zekveld
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
[hidden email]    |    www.eoh.co.za                                                           

Consulting | Technology | Outsourcing





-----Original Message-----
From: Tom Lane [mailto:[hidden email]]
Sent: 14 September 2018 04:42 PM
To: Terence Zekveld
Cc: Merlin Moncure; [hidden email]
Subject: Re: BUG #15384: dropping views and materialized views

Terence Zekveld <[hidden email]> 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