MIME-Version: 1.0
References: <CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=yg@mail.gmail.com>
 <CACJufxGvy6QNMwZ5xd=L31F+4Rr6aXCASeMFyLZS+TVssK5+vA@mail.gmail.com>
 <CACJufxGZgh4oNV_8TqBwRoL+WFB2n3EqN97aMcuEmjuG+qm0qw@mail.gmail.com>
 <CACJufxHnyH4Zv+RtOABn8-1PjxkKch9R58dGhr+hCnV0LmNxSQ@mail.gmail.com>
 <CALdSSPgsgC_CGDjTyzu=1FAbuavTRsGFDnnm7ufGuWcd29QUHQ@mail.gmail.com>
 <CACJufxE35HxSMqG6su0txZwOLpbnBsDBav5eigxt6L8Nk8bJwQ@mail.gmail.com>
 <CALdSSPi3Gb=fi0ZCoJhAp3HQYKJmiCShcX4xMcUBSL0s5byBXQ@mail.gmail.com>
 <CAKFQuwbs61rxxCUVhECLSq4Xrz9s54dD2TzhX58d7+6WcC8dpA@mail.gmail.com>
 <e38cbf42-0f8e-4c3b-8c77-70c83116fdb2@dunslane.net> <CAKFQuwbD775kSHFfyF3shu+XMTX=3SEH+_JYLOWvrPo3y5dK-w@mail.gmail.com>
 <442e62f7-ef64-4df1-8453-d682fe9bdaa3@dunslane.net> <CAKFQuwaMFiNGhk-F+TG4uVjiAfFwar6t+7fUc0mKDz55DGODcw@mail.gmail.com>
 <425ebe4b-06c5-4212-af68-fd0e5537c250@dunslane.net> <f6abc172-f300-4e90-bcc6-c189d29d2f9f@oss.nttdata.com>
 <CACJufxF3noNs9O6O3fL783h2-Sy1wfJb=0H+WMFmOLuhkfJw8w@mail.gmail.com> <4f9481ea-5dbc-43b2-a8a1-ec4cbecce6e7@oss.nttdata.com>
In-Reply-To: <4f9481ea-5dbc-43b2-a8a1-ec4cbecce6e7@oss.nttdata.com>
From: jian he <jian.universality@gmail.com>
Date: Thu, 3 Apr 2025 16:53:02 +0800
Message-ID: <CACJufxEA-_hUmg=eMrEpd-BK56qXyu9a1izsRHH_8Amw3MXP2Q@mail.gmail.com>
Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of
 COPY (query).
To: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: Andrew Dunstan <andrew@dunslane.net>, "David G. Johnston" <david.g.johnston@gmail.com>, 
	Kirill Reshke <reshkekirill@gmail.com>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Michael Paquier <michael@paquier.xyz>
Content-Type: multipart/mixed; boundary="000000000000f3131b0631dbe773"
Archived-At: <https://www.postgresql.org/message-id/CACJufxEA-_hUmg%3DeMrEpd-BK56qXyu9a1izsRHH_8Amw3MXP2Q%40mail.gmail.com>
Precedence: bulk

--000000000000f3131b0631dbe773
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 2, 2025 at 11:20=E2=80=AFPM Fujii Masao <masao.fujii@oss.nttdat=
a.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 consistenc=
y.
>
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 t=
able." However, it doesn't directly support other relation types, such as p=
artitioned tables, inheritance child tables, or views. To copy all rows fro=
m 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=3D"parameter">table</replaceable>
TO</literal>
    copies the same rows as
    <literal>SELECT * FROM ONLY <replaceable
class=3D"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 ... DEFA=
ULT",
> which feels a bit misaligned. How about adding them to the end of copy.sq=
l instead?
>
ok.

--000000000000f3131b0631dbe773
Content-Type: text/x-patch; charset="US-ASCII"; 
	name="v3-0001-COPY-materialized_view-TO.patch"
Content-Disposition: attachment; 
	filename="v3-0001-COPY-materialized_view-TO.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m914b1ap0>
X-Attachment-Id: f_m914b1ap0

RnJvbSBiYTkxYWMwZDk0MDNmMzA0ZDZjNzI2ODNjYTJjNDcxZTJjM2MzZWNjIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFRodSwgMyBBcHIgMjAyNSAxNjo0NTo0OCArMDgwMApTdWJqZWN0OiBbUEFUQ0ggdjMg
MS8xXSBDT1BZIG1hdGVyaWFsaXplZF92aWV3IFRPCgpnZW5lcmFsbHkgYENPUFkgdGFibGUgVE9g
IGlzIGZhc3RlciB0aGFuIGBDT1BZIChxdWVyeSlgLgpzaW5jZSBwb3B1bGF0ZWQgbWF0ZXJpYWxp
emVkIHZpZXcgaGF2ZSBwaHlzaWNhbCBzdG9yYWdlLCBzbwp0aGlzIGNhbiB1c2UgdGFibGVfYmVn
aW5zY2FuLCB0YWJsZV9lbmRzY2FuIHRvIHNjYW4gYSB0YWJsZS4KCmNvbnRleHQ6IGh0dHBzOi8v
cG9zdGdyLmVzL20vODk2Ny4xMzUzMTY3MzAxQHNzcy5wZ2gucGEudXMKY29udGV4dDogaHR0cHM6
Ly93d3cucG9zdGdyZXNxbC5vcmcvbWVzc2FnZS1pZC9mbGF0LzIwMTIxMTE2MTYyNTU4LjkwMTUw
JTQwZ214LmNvbQpkaXNjdXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBQ0p1ZnhIVnhueVJZ
eTY3aGlQZVBOQ1B3VkJNemhUUTZGYUw5X1RlNU9uOXVkRz15Z0BtYWlsLmdtYWlsLmNvbQpjb21t
aXRmZXN0IGVudHJ5OiBodHRwczovL2NvbW1pdGZlc3QucG9zdGdyZXNxbC5vcmcvcGF0Y2gvNTUz
My8KLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL2NvcHkuc2dtbCAgICAgICAgIHwgMTQgKysrKysrKyst
LS0tLS0KIHNyYy9iYWNrZW5kL2NvbW1hbmRzL2NvcHl0by5jICAgICAgfCAxMyArKysrKysrKy0t
LS0tCiBzcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2NvcHkub3V0IHwgMTEgKysrKysrKysrKysK
IHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2NvcHkuc3FsICAgICAgfCAgOCArKysrKysrKwogNCBmaWxl
cyBjaGFuZ2VkLCAzNSBpbnNlcnRpb25zKCspLCAxMSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQg
YS9kb2Mvc3JjL3NnbWwvcmVmL2NvcHkuc2dtbCBiL2RvYy9zcmMvc2dtbC9yZWYvY29weS5zZ21s
CmluZGV4IGRmMDkzZGE5N2M1Li5jODU1ZTk4Zjc1NyAxMDA2NDQKLS0tIGEvZG9jL3NyYy9zZ21s
L3JlZi9jb3B5LnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3JlZi9jb3B5LnNnbWwKQEAgLTUyMCwx
MiArNTIwLDE0IEBAIENPUFkgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPmNvdW50PC9y
ZXBsYWNlYWJsZT4KICAgPHRpdGxlPk5vdGVzPC90aXRsZT4KIAogICAgPHBhcmE+Ci0gICAgPGNv
bW1hbmQ+Q09QWSBUTzwvY29tbWFuZD4gY2FuIGJlIHVzZWQgb25seSB3aXRoIHBsYWluCi0gICAg
dGFibGVzLCBub3Qgdmlld3MsIGFuZCBkb2VzIG5vdCBjb3B5IHJvd3MgZnJvbSBjaGlsZCB0YWJs
ZXMKLSAgICBvciBjaGlsZCBwYXJ0aXRpb25zLiAgRm9yIGV4YW1wbGUsIDxsaXRlcmFsPkNPUFkg
PHJlcGxhY2VhYmxlCi0gICAgY2xhc3M9InBhcmFtZXRlciI+dGFibGU8L3JlcGxhY2VhYmxlPiBU
TzwvbGl0ZXJhbD4gY29waWVzCi0gICAgdGhlIHNhbWUgcm93cyBhcyA8bGl0ZXJhbD5TRUxFQ1Qg
KiBGUk9NIE9OTFkgPHJlcGxhY2VhYmxlCi0gICAgY2xhc3M9InBhcmFtZXRlciI+dGFibGU8L3Jl
cGxhY2VhYmxlPjwvbGl0ZXJhbD4uCisgICAgPGNvbW1hbmQ+Q09QWSBUTzwvY29tbWFuZD4gY2Fu
IGJlIHVzZWQgd2l0aCBwbGFpbgorICAgIHRhYmxlcyBhbmQgcG9wdWxhdGVkIG1hdGVyaWFsaXpl
ZCB2aWV3cy4KKyAgICBGb3IgZXhhbXBsZSwKKyAgICA8bGl0ZXJhbD5DT1BZIDxyZXBsYWNlYWJs
ZSBjbGFzcz0icGFyYW1ldGVyIj50YWJsZTwvcmVwbGFjZWFibGU+IFRPPC9saXRlcmFsPgorICAg
IGNvcGllcyB0aGUgc2FtZSByb3dzIGFzCisgICAgPGxpdGVyYWw+U0VMRUNUICogRlJPTSBPTkxZ
IDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1ldGVyIj50YWJsZTwvcmVwbGFjZWFibGU+PC9saXRl
cmFsPi4KKyAgICBIb3dldmVyIGl0IGRvZXNuJ3QgZGlyZWN0bHkgc3VwcG9ydCBvdGhlciByZWxh
dGlvbiB0eXBlcywKKyAgICBzdWNoIGFzIHBhcnRpdGlvbmVkIHRhYmxlcywgaW5oZXJpdGFuY2Ug
Y2hpbGQgdGFibGVzLCBvciB2aWV3cy4KICAgICBUaGUgc3ludGF4IDxsaXRlcmFsPkNPUFkgKFNF
TEVDVCAqIEZST00gPHJlcGxhY2VhYmxlCiAgICAgY2xhc3M9InBhcmFtZXRlciI+dGFibGU8L3Jl
cGxhY2VhYmxlPikgVE8gLi4uPC9saXRlcmFsPiBjYW4gYmUgdXNlZCB0bwogICAgIGR1bXAgYWxs
IG9mIHRoZSByb3dzIGluIGFuIGluaGVyaXRhbmNlIGhpZXJhcmNoeSwgcGFydGl0aW9uZWQgdGFi
bGUsCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9jb21tYW5kcy9jb3B5dG8uYyBiL3NyYy9iYWNr
ZW5kL2NvbW1hbmRzL2NvcHl0by5jCmluZGV4IDg0YTNmMzg3OWE4Li45MjMzY2JjZWNiNCAxMDA2
NDQKLS0tIGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvY29weXRvLmMKKysrIGIvc3JjL2JhY2tlbmQv
Y29tbWFuZHMvY29weXRvLmMKQEAgLTY1MywxMSArNjUzLDE0IEBAIEJlZ2luQ29weVRvKFBhcnNl
U3RhdGUgKnBzdGF0ZSwKIAkJCQkJCQlSZWxhdGlvbkdldFJlbGF0aW9uTmFtZShyZWwpKSwKIAkJ
CQkJIGVycmhpbnQoIlRyeSB0aGUgQ09QWSAoU0VMRUNUIC4uLikgVE8gdmFyaWFudC4iKSkpOwog
CQllbHNlIGlmIChyZWwtPnJkX3JlbC0+cmVsa2luZCA9PSBSRUxLSU5EX01BVFZJRVcpCi0JCQll
cmVwb3J0KEVSUk9SLAotCQkJCQkoZXJyY29kZShFUlJDT0RFX1dST05HX09CSkVDVF9UWVBFKSwK
LQkJCQkJIGVycm1zZygiY2Fubm90IGNvcHkgZnJvbSBtYXRlcmlhbGl6ZWQgdmlldyBcIiVzXCIi
LAotCQkJCQkJCVJlbGF0aW9uR2V0UmVsYXRpb25OYW1lKHJlbCkpLAotCQkJCQkgZXJyaGludCgi
VHJ5IHRoZSBDT1BZIChTRUxFQ1QgLi4uKSBUTyB2YXJpYW50LiIpKSk7CisJCXsKKwkJCWlmICgh
UmVsYXRpb25Jc1BvcHVsYXRlZChyZWwpKQorCQkJCWVyZXBvcnQoRVJST1IsCisJCQkJCQllcnJj
b2RlKEVSUkNPREVfRkVBVFVSRV9OT1RfU1VQUE9SVEVEKSwKKwkJCQkJCWVycm1zZygiY2Fubm90
IGNvcHkgZnJvbSB1bnBvcHVsYXRlZCBtYXRlcmlhbGl6ZWQgdmlldyBcIiVzXCIiLAorCQkJCQkJ
CQlSZWxhdGlvbkdldFJlbGF0aW9uTmFtZShyZWwpKSwKKwkJCQkJCWVycmhpbnQoIlVzZSB0aGUg
UkVGUkVTSCBNQVRFUklBTElaRUQgVklFVyBjb21tYW5kLiIpKTsKKwkJfQogCQllbHNlIGlmIChy
ZWwtPnJkX3JlbC0+cmVsa2luZCA9PSBSRUxLSU5EX0ZPUkVJR05fVEFCTEUpCiAJCQllcmVwb3J0
KEVSUk9SLAogCQkJCQkoZXJyY29kZShFUlJDT0RFX1dST05HX09CSkVDVF9UWVBFKSwKZGlmZiAt
LWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY29weS5vdXQgYi9zcmMvdGVzdC9yZWdy
ZXNzL2V4cGVjdGVkL2NvcHkub3V0CmluZGV4IDA2YmFlOGM2MWFlLi4zOWFmOTBmNTI5MiAxMDA2
NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jb3B5Lm91dAorKysgYi9zcmMvdGVz
dC9yZWdyZXNzL2V4cGVjdGVkL2NvcHkub3V0CkBAIC0zMzgsMyArMzM4LDE0IEBAIGNyZWF0ZSBm
b3JlaWduIHRhYmxlIGNvcHl0ZXN0X2ZvcmVpZ25fdGFibGUgKGEgaW50KSBzZXJ2ZXIgY29weXRl
c3Rfc2VydmVyOwogY29weSBjb3B5dGVzdF9mb3JlaWduX3RhYmxlIGZyb20gc3RkaW4gKGZyZWV6
ZSk7CiBFUlJPUjogIGNhbm5vdCBwZXJmb3JtIENPUFkgRlJFRVpFIG9uIGEgZm9yZWlnbiB0YWJs
ZQogcm9sbGJhY2s7CistLSBDT1BZIFRPIHdpdGggbWF0ZXJpYWxpemVkIHZpZXcKK0NSRUFURSBN
QVRFUklBTElaRUQgVklFVyBtYXR2aWV3MSBBUyBTRUxFQ1QgMSBhcyBpZDsKK0NSRUFURSBNQVRF
UklBTElaRUQgVklFVyBtYXR2aWV3MiBBUyBTRUxFQ1QgMSBhcyBpZCBXSVRIIE5PIERBVEE7CitD
T1BZIG1hdHZpZXcxKGlkKSBUTyBzdGRvdXQgV0lUSCAoaGVhZGVyKTsKK2lkCisxCitDT1BZIG1h
dHZpZXcyIFRPIHN0ZG91dCBXSVRIIChoZWFkZXIpOworRVJST1I6ICBjYW5ub3QgY29weSBmcm9t
IHVucG9wdWxhdGVkIG1hdGVyaWFsaXplZCB2aWV3ICJtYXR2aWV3MiIKK0hJTlQ6ICBVc2UgdGhl
IFJFRlJFU0ggTUFURVJJQUxJWkVEIFZJRVcgY29tbWFuZC4KK0RST1AgTUFURVJJQUxJWkVEIFZJ
RVcgbWF0dmlldzE7CitEUk9QIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcyOwpkaWZmIC0tZ2l0
IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY29weS5zcWwgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9j
b3B5LnNxbAppbmRleCAzMDA5YmRmZGY4OS4uNWRmNjU5MjRmOGIgMTAwNjQ0Ci0tLSBhL3NyYy90
ZXN0L3JlZ3Jlc3Mvc3FsL2NvcHkuc3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2NvcHku
c3FsCkBAIC0zNjYsMyArMzY2LDExIEBAIGNvcHkgY29weXRlc3RfZm9yZWlnbl90YWJsZSBmcm9t
IHN0ZGluIChmcmVlemUpOwogMQogXC4KIHJvbGxiYWNrOworCistLSBDT1BZIFRPIHdpdGggbWF0
ZXJpYWxpemVkIHZpZXcKK0NSRUFURSBNQVRFUklBTElaRUQgVklFVyBtYXR2aWV3MSBBUyBTRUxF
Q1QgMSBhcyBpZDsKK0NSRUFURSBNQVRFUklBTElaRUQgVklFVyBtYXR2aWV3MiBBUyBTRUxFQ1Qg
MSBhcyBpZCBXSVRIIE5PIERBVEE7CitDT1BZIG1hdHZpZXcxKGlkKSBUTyBzdGRvdXQgV0lUSCAo
aGVhZGVyKTsKK0NPUFkgbWF0dmlldzIgVE8gc3Rkb3V0IFdJVEggKGhlYWRlcik7CitEUk9QIE1B
VEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcxOworRFJPUCBNQVRFUklBTElaRUQgVklFVyBtYXR2aWV3
MjsKLS0gCjIuMzQuMQoK
--000000000000f3131b0631dbe773--