Thread

1. PSQL Should \sv & \ev work with materialized views?

   Kirk Wolak <wolakk@gmail.com> — 2023-05-15T04:32:54Z

   Personally I would appreciate it if \sv actually showed you the DDL.
   Oftentimes I will \ev something to review it, with syntax highlighting.
   
   Obviously this won't go in until V17, but looking at other tab-completion
   fixes.
   
   This should not be that difficult.  Just looking for feedback.
   Admittedly \e is questionable, because you cannot really apply the changes.
   ALTHOUGH, I would consider that I could
   BEGIN;
   DROP MATERIALIZED VIEW ...;
   CREATE MATERIALIZED VIEW ...;
   
   Which I had to do to change the WITH DATA so it creates with data when we
   reload our object.s
   
   Kirk...
   

2. Re: PSQL Should \sv & \ev work with materialized views?

   Erik Wienhold <ewie@ewie.name> — 2024-03-28T23:02:11Z

   On 2023-05-15 06:32 +0200, Kirk Wolak wrote:
   > Personally I would appreciate it if \sv actually showed you the DDL.
   > Oftentimes I will \ev something to review it, with syntax highlighting.
   
   +1.  I was just reviewing some matviews and was surprised that psql
   lacks commands to show their definitions.
   
   But I think that it should be separate commands \sm and \em because we
   already have commands \dm and \dv that distinguish between matviews and
   views.
   
   > This should not be that difficult.  Just looking for feedback.
   > Admittedly \e is questionable, because you cannot really apply the changes.
   > ALTHOUGH, I would consider that I could
   > BEGIN;
   > DROP MATERIALIZED VIEW ...;
   > CREATE MATERIALIZED VIEW ...;
   > 
   > Which I had to do to change the WITH DATA so it creates with data when we
   > reload our object.s
   
   I think this could even be handled by optional modifiers, e.g. \em emits
   CREATE MATERIALIZED VIEW ... WITH NO DATA and \emD emits WITH DATA.
   Although I wouldn't mind manually changing WITH NO DATA to WITH DATA.
   
   -- 
   Erik
   
   
   
   

3. Re: PSQL Should \sv & \ev work with materialized views?

   Erik Wienhold <ewie@ewie.name> — 2024-03-29T00:38:17Z

   I wrote:
   > On 2023-05-15 06:32 +0200, Kirk Wolak wrote:
   > > Personally I would appreciate it if \sv actually showed you the DDL.
   > > Oftentimes I will \ev something to review it, with syntax highlighting.
   > 
   > +1.  I was just reviewing some matviews and was surprised that psql
   > lacks commands to show their definitions.
   > 
   > But I think that it should be separate commands \sm and \em because we
   > already have commands \dm and \dv that distinguish between matviews and
   > views.
   
   Separate commands are not necessary because \ev and \sv already have a
   (disabled) provision in get_create_object_cmd for when CREATE OR REPLACE
   MATERIALIZED VIEW is available.  So I guess both commands should also
   apply to matview.  The attached patch replaces that provision with a
   transaction that drops and creates the matview.  This uses meta command
   \; to put multiple statements into the query buffer without prematurely
   sending those statements to the server.
   
   Demo:
   
   	=> DROP MATERIALIZED VIEW IF EXISTS test;
   	DROP MATERIALIZED VIEW
   	=> CREATE MATERIALIZED VIEW test AS SELECT s FROM generate_series(1, 10) s;
   	SELECT 10
   	=> \sv test
   	BEGIN \;
   	DROP MATERIALIZED VIEW public.test \;
   	CREATE MATERIALIZED VIEW public.test AS
   	 SELECT s
   	   FROM generate_series(1, 10) s(s)
   	 WITH DATA \;
   	COMMIT
   	=>
   
   And \ev test works as well.
   
   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.
   
   -- 
   Erik
   

4. Re: PSQL Should \sv & \ev work with materialized views?

   Isaac Morland <isaac.morland@gmail.com> — 2024-03-29T03:27:48Z

   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.
   

5. Re: PSQL Should \sv & \ev work with materialized views?

   Erik Wienhold <ewie@ewie.name> — 2024-03-29T04:27:17Z

   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