On 2025/04/03 17:53, jian he wrote:
On Wed, Apr 2, 2025 at 11:20 PM Fujii Masao <masao.fu...@oss.nttdata.com> wrote:
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."));
I think it's better to use the same hint message as the one output by
"COPY (SELECT * FROM <unpopulated matview>) TO",
specifically: "Use the REFRESH MATERIALIZED VIEW command," for consistency.
ok.
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"
It would be clearer to specify that "COPY TO" applies to *populated*
materialized views rather than just "materialized views"?
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."
If we split the first description into two as you suggested,
I'm tempted to propose the following improvements to enhance
the overall descriptions:
-------------
"COPY TO" can be used with plain tables and populated materialized views. For example, "COPY table
TO" copies the same rows as "SELECT * FROM ONLY table." However, it doesn't directly support other
relation types, such as partitioned tables, inheritance child tables, or views. To copy all rows from these relations,
use "COPY (SELECT * FROM table) TO."
-------------
your wording makes sense to me.
I try to ensure that the changing part in copy.sgml the line width
is less than 80 characters.
but I also want to make sure "<>" "</>" within the same line.
so after the change it becomes:
<para>
<command>COPY TO</command> can be used with plain
tables and populated materialized views.
For example,
<literal>COPY <replaceable class="parameter">table</replaceable>
TO</literal>
copies the same rows as
<literal>SELECT * FROM ONLY <replaceable
class="parameter">table</replaceable></literal>.
However it doesn't directly support other relation types,
such as partitioned tables, inheritance child tables, or views.
The tests seem to have been placed under the category "COPY FROM ... DEFAULT",
which feels a bit misaligned. How about adding them to the end of copy.sql
instead?
ok.
Thanks for updating the patch! I made some minor cosmetic changes
and updated the commit log. The revised patch is attached.
Unless there are any objections, I'll proceed with committing it.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
From 18bdeab3d74e539835d59c9a86da8f4ccf2243be Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Thu, 3 Apr 2025 18:41:44 +0900
Subject: [PATCH v4] Allow "COPY table TO" command to copy rows from
materialized views.
Previously, "COPY table TO" command worked only with plain tables and
did not support materialized views, even when they were populated and
had physical storage. To copy rows from materialized views,
"COPY (query) TO" command had to be used, instead.
This commit extends "COPY table TO" to support populated materialized
views directly, improving usability and performance, as "COPY table TO"
is generally faster than "COPY (query) TO". Note that copying from
unpopulated materialized views will still result in an error.
Author: jian he <jian.universal...@gmail.com>
Reviewed-by: Kirill Reshke <reshkekir...@gmail.com>
Reviewed-by: David G. Johnston <david.g.johns...@gmail.com>
Reviewed-by: Vignesh C <vignes...@gmail.com>
Reviewed-by: Fujii Masao <masao.fu...@gmail.com>
Discussion:
https://postgr.es/m/CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=y...@mail.gmail.com
---
doc/src/sgml/ref/copy.sgml | 20 ++++++++++----------
src/backend/commands/copyto.c | 13 ++++++++-----
src/test/regress/expected/copy.out | 12 ++++++++++++
src/test/regress/sql/copy.sql | 9 +++++++++
4 files changed, 39 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..d6859276bed 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -520,16 +520,16 @@ 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
- or child partitions. For example, <literal>COPY <replaceable
- class="parameter">table</replaceable> TO</literal> copies
- the same rows as <literal>SELECT * FROM ONLY <replaceable
- class="parameter">table</replaceable></literal>.
- The syntax <literal>COPY (SELECT * FROM <replaceable
- class="parameter">table</replaceable>) TO ...</literal> can be used to
- dump all of the rows in an inheritance hierarchy, partitioned table,
- or view.
+ <command>COPY TO</command> can be used with plain
+ tables and populated materialized views.
+ For example,
+ <literal>COPY <replaceable class="parameter">table</replaceable>
+ TO</literal> copies the same rows as
+ <literal>SELECT * FROM ONLY <replaceable
class="parameter">table</replaceable></literal>.
+ However it doesn't directly support other relation types,
+ such as partitioned tables, inheritance child tables, or views.
+ To copy all rows from such relations, use <literal>COPY (SELECT * FROM
+ <replaceable class="parameter">table</replaceable>) TO</literal>.
</para>
<para>
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..f87e405351d 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."));
+ }
else if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
diff --git a/src/test/regress/expected/copy.out
b/src/test/regress/expected/copy.out
index 06bae8c61ae..8d5a06563c4 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -338,3 +338,15 @@ create foreign table copytest_foreign_table (a int) server
copytest_server;
copy copytest_foreign_table from stdin (freeze);
ERROR: cannot perform COPY FREEZE on a foreign table
rollback;
+-- Tests for COPY TO with materialized views.
+-- COPY TO should fail for an unpopulated materialized view
+-- but succeed for a populated one.
+CREATE MATERIALIZED VIEW copytest_mv AS SELECT 1 AS id WITH NO DATA;
+COPY copytest_mv(id) TO stdout WITH (header);
+ERROR: cannot copy from unpopulated materialized view "copytest_mv"
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+REFRESH MATERIALIZED VIEW copytest_mv;
+COPY copytest_mv(id) TO stdout WITH (header);
+id
+1
+DROP MATERIALIZED VIEW copytest_mv;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 3009bdfdf89..f0b88a23db8 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -366,3 +366,12 @@ copy copytest_foreign_table from stdin (freeze);
1
\.
rollback;
+
+-- Tests for COPY TO with materialized views.
+-- COPY TO should fail for an unpopulated materialized view
+-- but succeed for a populated one.
+CREATE MATERIALIZED VIEW copytest_mv AS SELECT 1 AS id WITH NO DATA;
+COPY copytest_mv(id) TO stdout WITH (header);
+REFRESH MATERIALIZED VIEW copytest_mv;
+COPY copytest_mv(id) TO stdout WITH (header);
+DROP MATERIALIZED VIEW copytest_mv;
--
2.48.1
