Thread: BUG #15384: dropping views and materialized views

BUG #15384: dropping views and materialized views

From
PG Bug reporting form
Date:
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


Re: BUG #15384: dropping views and materialized views

From
Merlin Moncure
Date:
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


RE: BUG #15384: dropping views and materialized views

From
Terence Zekveld
Date:
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


Re: BUG #15384: dropping views and materialized views

From
Tom Lane
Date:
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


RE: BUG #15384: dropping views and materialized views

From
Terence Zekveld
Date:
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