Re: PSQL Should \sv & \ev work with materialized views? - Mailing list pgsql-hackers

From Erik Wienhold
Subject Re: PSQL Should \sv & \ev work with materialized views?
Date
Msg-id 3t7aicqqldztf5drtwblug7ghg5trzqxuvkv63o7gkflly7tkd@h6tolf2elnlv
Whole thread Raw
In response to Re: PSQL Should \sv & \ev work with materialized views?  (Isaac Morland <isaac.morland@gmail.com>)
List pgsql-hackers
On 2024-03-29 04:27 +0100, Isaac Morland wrote:
> On Thu, 28 Mar 2024 at 20:38, Erik Wienhold <ewie@ewie.name> wrote:
> 
> 
> > Of course the problem with using DROP and CREATE is that indexes and
> > privileges (anything else?) must also be restored.  I haven't bothered
> > with that yet.
> >
> 
> Not just those — also anything that depends on the matview, such as views
> and other matviews.

Right.  But you'd run into the same issue for a regular view if you use
\ev and add  DROP VIEW myview CASCADE  which may be necessary if you
want to change columns names and/or types.  Likewise, you'd have to
manually change  DROP MATERIALIZED VIEW  and add the CASCADE option to
lose dependent objects.

I think implementing  CREATE OR REPLACE MATERIALIZED VIEW  has more
value.  But the semantics have to be defined first.  I guess it has to
behave like  CREATE OR REPLACE VIEW  in that it only allows changing the
query without altering column names and types.

We could also implement \sv so that it only prints  CREATE MATERIALIZED
VIEW  and change \ev to not work with matviews.  Both commands use
get_create_object_cmd to populate the query buffer, so you get \ev for
free when changing \sv.

-- 
Erik



pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Introduce XID age and inactive timeout based replication slot invalidation
Next
From: Жарков Роман
Date:
Subject: Re: type cache cleanup improvements