On Mon, Mar 31, 2025 at 11:27 PM Fujii Masao
<masao.fu...@oss.nttdata.com> wrote:
>
> Regarding the patch, here are some review comments:
>
> + errmsg("cannot copy from
> materialized view when the materialized view is not populated"),
>
> How about including the object name for consistency with
> other error messages in BeginCopyTo(), like this?
>
> errmsg("cannot copy from unpopulated materialized view \"%s\"",
> RelationGetRelationName(rel)),
>
>
> + errhint("Use the REFRESH
> MATERIALIZED VIEW command populate the materialized view first."));
>
> There seems to be a missing "to" just after "command".
> Should it be "Use the REFRESH MATERIALIZED VIEW command to
> populate the materialized view first."? Or we could simplify
> the hint to match what SELECT on an unpopulated materialized
> view logs: "Use the REFRESH MATERIALIZED VIEW command.".
>
based on your suggestion, i changed it to:
if (!RelationIsPopulated(rel))
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot copy from unpopulated
materialized view \"%s\"",
RelationGetRelationName(rel)),
errhint("Use the REFRESH MATERIALIZED VIEW
command to populate the materialized view first."));
>
> The copy.sgml documentation should clarify that COPY TO can
> be used with a materialized view only if it is populated.
>
"COPY TO can be used only with plain tables, not views, and does not
copy rows from child tables or child partitions"
i changed it to
"COPY TO can be used with plain tables and materialized views, not
regular views, and does not copy rows from child tables or child
partitions"
Another alternative wording I came up with:
"COPY TO can only be used with plain tables and materialized views,
not regular views. It also does not copy rows from child tables or
child partitions."
>
> Wouldn't it be beneficial to add a regression test to check
> whether COPY matview TO works as expected?
sure.
From 3e404817827a58721cf8966080492f1254ea06cb Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Tue, 1 Apr 2025 11:11:32 +0800
Subject: [PATCH v2 1/1] COPY materialized_view TO
generally `COPY table TO` is faster than `COPY (query)`.
since populated materialized view have physical storage, so
this can use table_beginscan, table_endscan to scan a table.
context: https://postgr.es/m/8967.1353167...@sss.pgh.pa.us
context: https://www.postgresql.org/message-id/flat/20121116162558.90150%40gmx.com
discussion: https://postgr.es/m/CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=y...@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5533/
---
doc/src/sgml/ref/copy.sgml | 4 ++--
src/backend/commands/copyto.c | 13 ++++++++-----
src/test/regress/expected/copy2.out | 11 +++++++++++
src/test/regress/sql/copy2.sql | 8 ++++++++
4 files changed, 29 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..c3107488c81 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -520,8 +520,8 @@ COPY <replaceable class="parameter">count</replaceable>
<title>Notes</title>
<para>
- <command>COPY TO</command> can be used only with plain
- tables, not views, and does not copy rows from child tables
+ <command>COPY TO</command> can be used with plain
+ tables and materialized views, not regular views, and does not copy rows from child tables
or child partitions. For example, <literal>COPY <replaceable
class="parameter">table</replaceable> TO</literal> copies
the same rows as <literal>SELECT * FROM ONLY <replaceable
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..eeab225a690 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -653,11 +653,14 @@ BeginCopyTo(ParseState *pstate,
RelationGetRelationName(rel)),
errhint("Try the COPY (SELECT ...) TO variant.")));
else if (rel->rd_rel->relkind == RELKIND_MATVIEW)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("cannot copy from materialized view \"%s\"",
- RelationGetRelationName(rel)),
- errhint("Try the COPY (SELECT ...) TO variant.")));
+ {
+ if (!RelationIsPopulated(rel))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot copy from unpopulated materialized view \"%s\"",
+ RelationGetRelationName(rel)),
+ errhint("Use the REFRESH MATERIALIZED VIEW command to populate the materialized view first."));
+ }
else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..f7aa55e1691 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -929,3 +929,14 @@ truncate copy_default;
-- DEFAULT cannot be used in COPY TO
copy (select 1 as test) TO stdout with (default '\D');
ERROR: COPY DEFAULT cannot be used with COPY TO
+-- COPY TO with materialized view
+CREATE MATERIALIZED VIEW matview1 AS SELECT 1 as id;
+CREATE MATERIALIZED VIEW matview2 AS SELECT 1 as id WITH NO DATA;
+copy matview1(id) TO stdout with (header);
+id
+1
+copy matview2 TO stdout with (header);
+ERROR: cannot copy from unpopulated materialized view "matview2"
+HINT: Use the REFRESH MATERIALIZED VIEW command to populate the materialized view first.
+DROP MATERIALIZED VIEW matview1;
+DROP MATERIALIZED VIEW matview2;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..eeed7064315 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -707,3 +707,11 @@ truncate copy_default;
-- DEFAULT cannot be used in COPY TO
copy (select 1 as test) TO stdout with (default '\D');
+
+-- COPY TO with materialized view
+CREATE MATERIALIZED VIEW matview1 AS SELECT 1 as id;
+CREATE MATERIALIZED VIEW matview2 AS SELECT 1 as id WITH NO DATA;
+copy matview1(id) TO stdout with (header);
+copy matview2 TO stdout with (header);
+DROP MATERIALIZED VIEW matview1;
+DROP MATERIALIZED VIEW matview2;
--
2.34.1
