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

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
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