On Wed, Apr 2, 2025 at 11:20 PM Fujii Masao <[email protected]> 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.
From ba91ac0d9403f304d6c72683ca2c471e2c3c3ecc Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 3 Apr 2025 16:45:48 +0800
Subject: [PATCH v3 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/[email protected]
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 | 14 ++++++++------
src/backend/commands/copyto.c | 13 ++++++++-----
src/test/regress/expected/copy.out | 11 +++++++++++
src/test/regress/sql/copy.sql | 8 ++++++++
4 files changed, 35 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index df093da97c5..c855e98f757 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -520,12 +520,14 @@ 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>.
+ <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 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,
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 84a3f3879a8..9233cbcecb4 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..39af90f5292 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -338,3 +338,14 @@ 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;
+-- 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.
+DROP MATERIALIZED VIEW matview1;
+DROP MATERIALIZED VIEW matview2;
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 3009bdfdf89..5df65924f8b 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -366,3 +366,11 @@ copy copytest_foreign_table from stdin (freeze);
1
\.
rollback;
+
+-- 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
