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

Started by Kirk Wolakover 2 years ago5 messages

wolakk@gmail.com

over 2 years ago

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

ewie@ewie.name

almost 2 years ago

In reply to: Kirk Wolak (#1)

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

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

ewie@ewie.name

almost 2 years ago

In reply to: Erik Wienhold (#2)

1 attachment(s)

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

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

Attachments:

v1-0001-psql-ev-and-sv-for-matviews.patchtext/plain; charset=us-asciiDownload

From efb5e37d90b668011307b602655f28455d700635 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Fri, 29 Mar 2024 01:08:35 +0100
Subject: [PATCH v1] psql: \ev and \sv for matviews

CREATE OR REPLACE is not available for materialized views so DROP and
CREATE them inside a transaction.  Use meta command \; to compose the
query buffer without sending it to the server.

TODO: Re-create indexes and privileges which are currently lost by
      relying on DROP and CREATE.
---
 src/bin/psql/command.c | 29 ++++++++++++++++++++---------
 1 file changed, 20 insertions(+), 9 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9b0fa041f7..f40c1d7f99 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -5575,19 +5575,22 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 					char	   *reloptions = PQgetvalue(res, 0, 4);
 					char	   *checkoption = PQgetvalue(res, 0, 5);
 
-					/*
-					 * If the backend ever supports CREATE OR REPLACE
-					 * MATERIALIZED VIEW, allow that here; but as of today it
-					 * does not, so editing a matview definition in this way
-					 * is impossible.
-					 */
 					switch (relkind[0])
 					{
-#ifdef NOT_USED
 						case RELKIND_MATVIEW:
-							appendPQExpBufferStr(buf, "CREATE OR REPLACE MATERIALIZED VIEW ");
+							/*
+							 * Allow editing a matview via separate DROP and
+							 * CREATE statement inside a transaction.  Use meta
+							 * command \; to write more than one statement to
+							 * the query buffer without sending it to the server.
+							 */
+							appendPQExpBufferStr(buf, "BEGIN \\;\n");
+							appendPQExpBufferStr(buf, "DROP MATERIALIZED VIEW ");
+							appendPQExpBuffer(buf, "%s.", fmtId(nspname));
+							appendPQExpBufferStr(buf, fmtId(relname));
+							appendPQExpBufferStr(buf, " \\;\n");
+							appendPQExpBufferStr(buf, "CREATE MATERIALIZED VIEW ");
 							break;
-#endif
 						case RELKIND_VIEW:
 							appendPQExpBufferStr(buf, "CREATE OR REPLACE VIEW ");
 							break;
@@ -5625,6 +5628,14 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 					if (checkoption && checkoption[0] != '\0')
 						appendPQExpBuffer(buf, "\n WITH %s CHECK OPTION",
 										  checkoption);
+
+					/* Matview is re-created inside a transaction. */
+					if (relkind[0] == RELKIND_MATVIEW)
+						/*
+						 * TODO Also re-create indexes and privileges that are
+						 *      lost by using DROP and CREATE.
+						 */
+						appendPQExpBufferStr(buf, "\n WITH DATA \\;\nCOMMIT");
 				}
 				break;
 		}
-- 
2.44.0

isaac.morland@gmail.com

almost 2 years ago

In reply to: Erik Wienhold (#3)

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

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.

ewie@ewie.name

almost 2 years ago

In reply to: Isaac Morland (#4)

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

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