From 3e404817827a58721cf8966080492f1254ea06cb Mon Sep 17 00:00:00 2001 From: jian he 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.1353167301@sss.pgh.pa.us context: https://www.postgresql.org/message-id/flat/20121116162558.90150%40gmx.com discussion: https://postgr.es/m/CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=yg@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 count Notes - COPY TO can be used only with plain - tables, not views, and does not copy rows from child tables + 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. For example, COPY table TO copies the same rows as SELECT * FROM ONLY 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