MIME-Version: 1.0
References: <CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=yg@mail.gmail.com>
 <Z2Sz7LU1akdewn5U@paquier.xyz> <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>
In-Reply-To: <f6abc172-f300-4e90-bcc6-c189d29d2f9f@oss.nttdata.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 1 Apr 2025 11:12:45 +0800
Message-ID: <CACJufxF3noNs9O6O3fL783h2-Sy1wfJb=0H+WMFmOLuhkfJw8w@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="0000000000003de8c40631aeeb25"
Archived-At: <https://www.postgresql.org/message-id/CACJufxF3noNs9O6O3fL783h2-Sy1wfJb%3D0H%2BWMFmOLuhkfJw8w%40mail.gmail.com>
Precedence: bulk

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

On Mon, Mar 31, 2025 at 11:27=E2=80=AFPM Fujii Masao
<masao.fujii@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.

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

RnJvbSAzZTQwNDgxNzgyN2E1ODcyMWNmODk2NjA4MDQ5MmYxMjU0ZWEwNmNiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFR1ZSwgMSBBcHIgMjAyNSAxMToxMTozMiArMDgwMApTdWJqZWN0OiBbUEFUQ0ggdjIg
MS8xXSBDT1BZIG1hdGVyaWFsaXplZF92aWV3IFRPCgpnZW5lcmFsbHkgYENPUFkgdGFibGUgVE9g
IGlzIGZhc3RlciB0aGFuIGBDT1BZIChxdWVyeSlgLgpzaW5jZSBwb3B1bGF0ZWQgbWF0ZXJpYWxp
emVkIHZpZXcgaGF2ZSBwaHlzaWNhbCBzdG9yYWdlLCBzbwp0aGlzIGNhbiB1c2UgdGFibGVfYmVn
aW5zY2FuLCB0YWJsZV9lbmRzY2FuIHRvIHNjYW4gYSB0YWJsZS4KCmNvbnRleHQ6IGh0dHBzOi8v
cG9zdGdyLmVzL20vODk2Ny4xMzUzMTY3MzAxQHNzcy5wZ2gucGEudXMKY29udGV4dDogaHR0cHM6
Ly93d3cucG9zdGdyZXNxbC5vcmcvbWVzc2FnZS1pZC9mbGF0LzIwMTIxMTE2MTYyNTU4LjkwMTUw
JTQwZ214LmNvbQpkaXNjdXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBQ0p1ZnhIVnhueVJZ
eTY3aGlQZVBOQ1B3VkJNemhUUTZGYUw5X1RlNU9uOXVkRz15Z0BtYWlsLmdtYWlsLmNvbQpjb21t
aXRmZXN0IGVudHJ5OiBodHRwczovL2NvbW1pdGZlc3QucG9zdGdyZXNxbC5vcmcvcGF0Y2gvNTUz
My8KLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL2NvcHkuc2dtbCAgICAgICAgICB8ICA0ICsrLS0KIHNy
Yy9iYWNrZW5kL2NvbW1hbmRzL2NvcHl0by5jICAgICAgIHwgMTMgKysrKysrKystLS0tLQogc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jb3B5Mi5vdXQgfCAxMSArKysrKysrKysrKwogc3JjL3Rl
c3QvcmVncmVzcy9zcWwvY29weTIuc3FsICAgICAgfCAgOCArKysrKysrKwogNCBmaWxlcyBjaGFu
Z2VkLCAyOSBpbnNlcnRpb25zKCspLCA3IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL2RvYy9z
cmMvc2dtbC9yZWYvY29weS5zZ21sIGIvZG9jL3NyYy9zZ21sL3JlZi9jb3B5LnNnbWwKaW5kZXgg
ZGYwOTNkYTk3YzUuLmMzMTA3NDg4YzgxIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvcmVmL2Nv
cHkuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvcmVmL2NvcHkuc2dtbApAQCAtNTIwLDggKzUyMCw4
IEBAIENPUFkgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPmNvdW50PC9yZXBsYWNlYWJs
ZT4KICAgPHRpdGxlPk5vdGVzPC90aXRsZT4KIAogICAgPHBhcmE+Ci0gICAgPGNvbW1hbmQ+Q09Q
WSBUTzwvY29tbWFuZD4gY2FuIGJlIHVzZWQgb25seSB3aXRoIHBsYWluCi0gICAgdGFibGVzLCBu
b3Qgdmlld3MsIGFuZCBkb2VzIG5vdCBjb3B5IHJvd3MgZnJvbSBjaGlsZCB0YWJsZXMKKyAgICA8
Y29tbWFuZD5DT1BZIFRPPC9jb21tYW5kPiBjYW4gYmUgdXNlZCB3aXRoIHBsYWluCisgICAgdGFi
bGVzIGFuZCBtYXRlcmlhbGl6ZWQgdmlld3MsIG5vdCByZWd1bGFyIHZpZXdzLCBhbmQgZG9lcyBu
b3QgY29weSByb3dzIGZyb20gY2hpbGQgdGFibGVzCiAgICAgb3IgY2hpbGQgcGFydGl0aW9ucy4g
IEZvciBleGFtcGxlLCA8bGl0ZXJhbD5DT1BZIDxyZXBsYWNlYWJsZQogICAgIGNsYXNzPSJwYXJh
bWV0ZXIiPnRhYmxlPC9yZXBsYWNlYWJsZT4gVE88L2xpdGVyYWw+IGNvcGllcwogICAgIHRoZSBz
YW1lIHJvd3MgYXMgPGxpdGVyYWw+U0VMRUNUICogRlJPTSBPTkxZIDxyZXBsYWNlYWJsZQpkaWZm
IC0tZ2l0IGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvY29weXRvLmMgYi9zcmMvYmFja2VuZC9jb21t
YW5kcy9jb3B5dG8uYwppbmRleCA4NGEzZjM4NzlhOC4uZWVhYjIyNWE2OTAgMTAwNjQ0Ci0tLSBh
L3NyYy9iYWNrZW5kL2NvbW1hbmRzL2NvcHl0by5jCisrKyBiL3NyYy9iYWNrZW5kL2NvbW1hbmRz
L2NvcHl0by5jCkBAIC02NTMsMTEgKzY1MywxNCBAQCBCZWdpbkNvcHlUbyhQYXJzZVN0YXRlICpw
c3RhdGUsCiAJCQkJCQkJUmVsYXRpb25HZXRSZWxhdGlvbk5hbWUocmVsKSksCiAJCQkJCSBlcnJo
aW50KCJUcnkgdGhlIENPUFkgKFNFTEVDVCAuLi4pIFRPIHZhcmlhbnQuIikpKTsKIAkJZWxzZSBp
ZiAocmVsLT5yZF9yZWwtPnJlbGtpbmQgPT0gUkVMS0lORF9NQVRWSUVXKQotCQkJZXJlcG9ydChF
UlJPUiwKLQkJCQkJKGVycmNvZGUoRVJSQ09ERV9XUk9OR19PQkpFQ1RfVFlQRSksCi0JCQkJCSBl
cnJtc2coImNhbm5vdCBjb3B5IGZyb20gbWF0ZXJpYWxpemVkIHZpZXcgXCIlc1wiIiwKLQkJCQkJ
CQlSZWxhdGlvbkdldFJlbGF0aW9uTmFtZShyZWwpKSwKLQkJCQkJIGVycmhpbnQoIlRyeSB0aGUg
Q09QWSAoU0VMRUNUIC4uLikgVE8gdmFyaWFudC4iKSkpOworCQl7CisJCQlpZiAoIVJlbGF0aW9u
SXNQb3B1bGF0ZWQocmVsKSkKKwkJCQllcmVwb3J0KEVSUk9SLAorCQkJCQkJZXJyY29kZShFUlJD
T0RFX0ZFQVRVUkVfTk9UX1NVUFBPUlRFRCksCisJCQkJCQllcnJtc2coImNhbm5vdCBjb3B5IGZy
b20gdW5wb3B1bGF0ZWQgbWF0ZXJpYWxpemVkIHZpZXcgXCIlc1wiIiwKKwkJCQkJCQkJUmVsYXRp
b25HZXRSZWxhdGlvbk5hbWUocmVsKSksCisJCQkJCQllcnJoaW50KCJVc2UgdGhlIFJFRlJFU0gg
TUFURVJJQUxJWkVEIFZJRVcgY29tbWFuZCB0byBwb3B1bGF0ZSB0aGUgbWF0ZXJpYWxpemVkIHZp
ZXcgZmlyc3QuIikpOworCQl9CiAJCWVsc2UgaWYgKHJlbC0+cmRfcmVsLT5yZWxraW5kID09IFJF
TEtJTkRfRk9SRUlHTl9UQUJMRSkKIAkJCWVyZXBvcnQoRVJST1IsCiAJCQkJCShlcnJjb2RlKEVS
UkNPREVfV1JPTkdfT0JKRUNUX1RZUEUpLApkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9l
eHBlY3RlZC9jb3B5Mi5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2NvcHkyLm91dApp
bmRleCA2NGVhMzNhZWFlOC4uZjdhYTU1ZTE2OTEgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jl
c3MvZXhwZWN0ZWQvY29weTIub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY29w
eTIub3V0CkBAIC05MjksMyArOTI5LDE0IEBAIHRydW5jYXRlIGNvcHlfZGVmYXVsdDsKIC0tIERF
RkFVTFQgY2Fubm90IGJlIHVzZWQgaW4gQ09QWSBUTwogY29weSAoc2VsZWN0IDEgYXMgdGVzdCkg
VE8gc3Rkb3V0IHdpdGggKGRlZmF1bHQgJ1xEJyk7CiBFUlJPUjogIENPUFkgREVGQVVMVCBjYW5u
b3QgYmUgdXNlZCB3aXRoIENPUFkgVE8KKy0tIENPUFkgVE8gd2l0aCBtYXRlcmlhbGl6ZWQgdmll
dworQ1JFQVRFIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcxIEFTIFNFTEVDVCAxIGFzIGlkOwor
Q1JFQVRFIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcyIEFTIFNFTEVDVCAxIGFzIGlkIFdJVEgg
Tk8gREFUQTsKK2NvcHkgbWF0dmlldzEoaWQpIFRPIHN0ZG91dCB3aXRoIChoZWFkZXIpOworaWQK
KzEKK2NvcHkgbWF0dmlldzIgVE8gc3Rkb3V0IHdpdGggKGhlYWRlcik7CitFUlJPUjogIGNhbm5v
dCBjb3B5IGZyb20gdW5wb3B1bGF0ZWQgbWF0ZXJpYWxpemVkIHZpZXcgIm1hdHZpZXcyIgorSElO
VDogIFVzZSB0aGUgUkVGUkVTSCBNQVRFUklBTElaRUQgVklFVyBjb21tYW5kIHRvIHBvcHVsYXRl
IHRoZSBtYXRlcmlhbGl6ZWQgdmlldyBmaXJzdC4KK0RST1AgTUFURVJJQUxJWkVEIFZJRVcgbWF0
dmlldzE7CitEUk9QIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcyOwpkaWZmIC0tZ2l0IGEvc3Jj
L3Rlc3QvcmVncmVzcy9zcWwvY29weTIuc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY29weTIu
c3FsCmluZGV4IDQ1MjczNTU3Y2UwLi5lZWVkNzA2NDMxNSAxMDA2NDQKLS0tIGEvc3JjL3Rlc3Qv
cmVncmVzcy9zcWwvY29weTIuc3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2NvcHkyLnNx
bApAQCAtNzA3LDMgKzcwNywxMSBAQCB0cnVuY2F0ZSBjb3B5X2RlZmF1bHQ7CiAKIC0tIERFRkFV
TFQgY2Fubm90IGJlIHVzZWQgaW4gQ09QWSBUTwogY29weSAoc2VsZWN0IDEgYXMgdGVzdCkgVE8g
c3Rkb3V0IHdpdGggKGRlZmF1bHQgJ1xEJyk7CisKKy0tIENPUFkgVE8gd2l0aCBtYXRlcmlhbGl6
ZWQgdmlldworQ1JFQVRFIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcxIEFTIFNFTEVDVCAxIGFz
IGlkOworQ1JFQVRFIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcyIEFTIFNFTEVDVCAxIGFzIGlk
IFdJVEggTk8gREFUQTsKK2NvcHkgbWF0dmlldzEoaWQpIFRPIHN0ZG91dCB3aXRoIChoZWFkZXIp
OworY29weSBtYXR2aWV3MiBUTyBzdGRvdXQgd2l0aCAoaGVhZGVyKTsKK0RST1AgTUFURVJJQUxJ
WkVEIFZJRVcgbWF0dmlldzE7CitEUk9QIE1BVEVSSUFMSVpFRCBWSUVXIG1hdHZpZXcyOwotLSAK
Mi4zNC4xCgo=
--0000000000003de8c40631aeeb25--