RE: BUG #15384: dropping views and materialized views

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(dot)zekveld(at)eoh(dot)com    |    www.eoh.co.za                                                           

Consulting | Technology | Outsourcing

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
Sent: 14 September 2018 02:55 PM
To: Terence Zekveld; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15384: dropping views and materialized views

On Fri, Sep 14, 2018 at 4:41 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 15384
> Logged by: Terence Zekveld
> Email address: terence(dot)zekveld(at)eoh(dot)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