PSQL Should \sv & \ev work with materialized views?
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t47853psql -h localhost -U postgresBuilt from patchset v3 (message #3), October 06, 2026 at 07:52 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t47853_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t47853_3 && git checkout t47853_3Patchset v3 (message #3) is on t47853_3
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...
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
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
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.
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