Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u0J2N-00GD2j-OO for pgsql-hackers@arkaria.postgresql.org; Thu, 03 Apr 2025 11:47:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u0J2M-006IGu-7m for pgsql-hackers@arkaria.postgresql.org; Thu, 03 Apr 2025 11:47:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u0J2L-006IGm-Sw for pgsql-hackers@lists.postgresql.org; Thu, 03 Apr 2025 11:47:09 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u0J2I-0039M2-1P for pgsql-hackers@postgresql.org; Thu, 03 Apr 2025 11:47:09 +0000 Received: from [192.168.11.5] (p1696134-ipoe.ipoe.ocn.ne.jp [118.0.93.133]) by oss.nttdata.com (Postfix) with ESMTPSA id 37F2F60538; Thu, 3 Apr 2025 20:47:00 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.103.11 at oss.nttdata.com Content-Type: multipart/mixed; boundary="------------ktBq07x0iJsd7i0LylIVu0Oh" Message-ID: Date: Thu, 3 Apr 2025 20:46:59 +0900 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query). To: jian he Cc: Andrew Dunstan , "David G. Johnston" , Kirill Reshke , PostgreSQL-development , Tom Lane , Michael Paquier References: <442e62f7-ef64-4df1-8453-d682fe9bdaa3@dunslane.net> <425ebe4b-06c5-4212-af68-fd0e5537c250@dunslane.net> <4f9481ea-5dbc-43b2-a8a1-ec4cbecce6e7@oss.nttdata.com> Content-Language: en-US From: Fujii Masao In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ktBq07x0iJsd7i0LylIVu0Oh Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2025/04/03 17:53, jian he wrote: > On Wed, Apr 2, 2025 at 11:20 PM Fujii Masao 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 ) 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: > > > 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 class="parameter">table. > 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 --------------ktBq07x0iJsd7i0LylIVu0Oh Content-Type: text/plain; charset=UTF-8; name="v4-0001-Allow-COPY-table-TO-command-to-copy-rows-from-mat.patch" Content-Disposition: attachment; filename*0="v4-0001-Allow-COPY-table-TO-command-to-copy-rows-from-mat.pa"; filename*1="tch" Content-Transfer-Encoding: base64 RnJvbSAxOGJkZWFiM2Q3NGU1Mzk4MzVkNTljOWE4NmRhOGY0Y2NmMjI0M2JlIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBGdWppaSBNYXNhbyA8ZnVqaWlAcG9zdGdyZXNxbC5v cmc+CkRhdGU6IFRodSwgMyBBcHIgMjAyNSAxODo0MTo0NCArMDkwMApTdWJqZWN0OiBbUEFU Q0ggdjRdIEFsbG93ICJDT1BZIHRhYmxlIFRPIiBjb21tYW5kIHRvIGNvcHkgcm93cyBmcm9t CiBtYXRlcmlhbGl6ZWQgdmlld3MuCgpQcmV2aW91c2x5LCAiQ09QWSB0YWJsZSBUTyIgY29t bWFuZCB3b3JrZWQgb25seSB3aXRoIHBsYWluIHRhYmxlcyBhbmQKZGlkIG5vdCBzdXBwb3J0 IG1hdGVyaWFsaXplZCB2aWV3cywgZXZlbiB3aGVuIHRoZXkgd2VyZSBwb3B1bGF0ZWQgYW5k CmhhZCBwaHlzaWNhbCBzdG9yYWdlLiBUbyBjb3B5IHJvd3MgZnJvbSBtYXRlcmlhbGl6ZWQg dmlld3MsCiJDT1BZIChxdWVyeSkgVE8iIGNvbW1hbmQgaGFkIHRvIGJlIHVzZWQsIGluc3Rl YWQuCgpUaGlzIGNvbW1pdCBleHRlbmRzICJDT1BZIHRhYmxlIFRPIiB0byBzdXBwb3J0IHBv cHVsYXRlZCBtYXRlcmlhbGl6ZWQKdmlld3MgZGlyZWN0bHksIGltcHJvdmluZyB1c2FiaWxp dHkgYW5kIHBlcmZvcm1hbmNlLCBhcyAiQ09QWSB0YWJsZSBUTyIKaXMgZ2VuZXJhbGx5IGZh c3RlciB0aGFuICJDT1BZIChxdWVyeSkgVE8iLiBOb3RlIHRoYXQgY29weWluZyBmcm9tCnVu cG9wdWxhdGVkIG1hdGVyaWFsaXplZCB2aWV3cyB3aWxsIHN0aWxsIHJlc3VsdCBpbiBhbiBl cnJvci4KCkF1dGhvcjogamlhbiBoZSA8amlhbi51bml2ZXJzYWxpdHlAZ21haWwuY29tPgpS ZXZpZXdlZC1ieTogS2lyaWxsIFJlc2hrZSA8cmVzaGtla2lyaWxsQGdtYWlsLmNvbT4KUmV2 aWV3ZWQtYnk6IERhdmlkIEcuIEpvaG5zdG9uIDxkYXZpZC5nLmpvaG5zdG9uQGdtYWlsLmNv bT4KUmV2aWV3ZWQtYnk6IFZpZ25lc2ggQyA8dmlnbmVzaDIxQGdtYWlsLmNvbT4KUmV2aWV3 ZWQtYnk6IEZ1amlpIE1hc2FvIDxtYXNhby5mdWppaUBnbWFpbC5jb20+CkRpc2N1c3Npb246 IGh0dHBzOi8vcG9zdGdyLmVzL20vQ0FDSnVmeEhWeG55Ull5NjdoaVBlUE5DUHdWQk16aFRR NkZhTDlfVGU1T245dWRHPXlnQG1haWwuZ21haWwuY29tCi0tLQogZG9jL3NyYy9zZ21sL3Jl Zi9jb3B5LnNnbWwgICAgICAgICB8IDIwICsrKysrKysrKystLS0tLS0tLS0tCiBzcmMvYmFj a2VuZC9jb21tYW5kcy9jb3B5dG8uYyAgICAgIHwgMTMgKysrKysrKystLS0tLQogc3JjL3Rl c3QvcmVncmVzcy9leHBlY3RlZC9jb3B5Lm91dCB8IDEyICsrKysrKysrKysrKwogc3JjL3Rl c3QvcmVncmVzcy9zcWwvY29weS5zcWwgICAgICB8ICA5ICsrKysrKysrKwogNCBmaWxlcyBj aGFuZ2VkLCAzOSBpbnNlcnRpb25zKCspLCAxNSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQg YS9kb2Mvc3JjL3NnbWwvcmVmL2NvcHkuc2dtbCBiL2RvYy9zcmMvc2dtbC9yZWYvY29weS5z Z21sCmluZGV4IGRmMDkzZGE5N2M1Li5kNjg1OTI3NmJlZCAxMDA2NDQKLS0tIGEvZG9jL3Ny Yy9zZ21sL3JlZi9jb3B5LnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3JlZi9jb3B5LnNnbWwK QEAgLTUyMCwxNiArNTIwLDE2IEBAIENPUFkgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0 ZXIiPmNvdW50PC9yZXBsYWNlYWJsZT4KICAgPHRpdGxlPk5vdGVzPC90aXRsZT4KIAogICAg PHBhcmE+Ci0gICAgPGNvbW1hbmQ+Q09QWSBUTzwvY29tbWFuZD4gY2FuIGJlIHVzZWQgb25s eSB3aXRoIHBsYWluCi0gICAgdGFibGVzLCBub3Qgdmlld3MsIGFuZCBkb2VzIG5vdCBjb3B5 IHJvd3MgZnJvbSBjaGlsZCB0YWJsZXMKLSAgICBvciBjaGlsZCBwYXJ0aXRpb25zLiAgRm9y IGV4YW1wbGUsIDxsaXRlcmFsPkNPUFkgPHJlcGxhY2VhYmxlCi0gICAgY2xhc3M9InBhcmFt ZXRlciI+dGFibGU8L3JlcGxhY2VhYmxlPiBUTzwvbGl0ZXJhbD4gY29waWVzCi0gICAgdGhl IHNhbWUgcm93cyBhcyA8bGl0ZXJhbD5TRUxFQ1QgKiBGUk9NIE9OTFkgPHJlcGxhY2VhYmxl Ci0gICAgY2xhc3M9InBhcmFtZXRlciI+dGFibGU8L3JlcGxhY2VhYmxlPjwvbGl0ZXJhbD4u Ci0gICAgVGhlIHN5bnRheCA8bGl0ZXJhbD5DT1BZIChTRUxFQ1QgKiBGUk9NIDxyZXBsYWNl YWJsZQotICAgIGNsYXNzPSJwYXJhbWV0ZXIiPnRhYmxlPC9yZXBsYWNlYWJsZT4pIFRPIC4u LjwvbGl0ZXJhbD4gY2FuIGJlIHVzZWQgdG8KLSAgICBkdW1wIGFsbCBvZiB0aGUgcm93cyBp biBhbiBpbmhlcml0YW5jZSBoaWVyYXJjaHksIHBhcnRpdGlvbmVkIHRhYmxlLAotICAgIG9y IHZpZXcuCisgICAgPGNvbW1hbmQ+Q09QWSBUTzwvY29tbWFuZD4gY2FuIGJlIHVzZWQgd2l0 aCBwbGFpbgorICAgIHRhYmxlcyBhbmQgcG9wdWxhdGVkIG1hdGVyaWFsaXplZCB2aWV3cy4K KyAgICBGb3IgZXhhbXBsZSwKKyAgICA8bGl0ZXJhbD5DT1BZIDxyZXBsYWNlYWJsZSBjbGFz cz0icGFyYW1ldGVyIj50YWJsZTwvcmVwbGFjZWFibGU+CisgICAgVE88L2xpdGVyYWw+IGNv cGllcyB0aGUgc2FtZSByb3dzIGFzCisgICAgPGxpdGVyYWw+U0VMRUNUICogRlJPTSBPTkxZ IDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1ldGVyIj50YWJsZTwvcmVwbGFjZWFibGU+PC9s aXRlcmFsPi4KKyAgICBIb3dldmVyIGl0IGRvZXNuJ3QgZGlyZWN0bHkgc3VwcG9ydCBvdGhl ciByZWxhdGlvbiB0eXBlcywKKyAgICBzdWNoIGFzIHBhcnRpdGlvbmVkIHRhYmxlcywgaW5o ZXJpdGFuY2UgY2hpbGQgdGFibGVzLCBvciB2aWV3cy4KKyAgICBUbyBjb3B5IGFsbCByb3dz IGZyb20gc3VjaCByZWxhdGlvbnMsIHVzZSA8bGl0ZXJhbD5DT1BZIChTRUxFQ1QgKiBGUk9N CisgICAgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPnRhYmxlPC9yZXBsYWNlYWJs ZT4pIFRPPC9saXRlcmFsPi4KICAgIDwvcGFyYT4KIAogICAgPHBhcmE+CmRpZmYgLS1naXQg YS9zcmMvYmFja2VuZC9jb21tYW5kcy9jb3B5dG8uYyBiL3NyYy9iYWNrZW5kL2NvbW1hbmRz L2NvcHl0by5jCmluZGV4IDg0YTNmMzg3OWE4Li5mODdlNDA1MzUxZCAxMDA2NDQKLS0tIGEv c3JjL2JhY2tlbmQvY29tbWFuZHMvY29weXRvLmMKKysrIGIvc3JjL2JhY2tlbmQvY29tbWFu ZHMvY29weXRvLmMKQEAgLTY1MywxMSArNjUzLDE0IEBAIEJlZ2luQ29weVRvKFBhcnNlU3Rh dGUgKnBzdGF0ZSwKIAkJCQkJCQlSZWxhdGlvbkdldFJlbGF0aW9uTmFtZShyZWwpKSwKIAkJ CQkJIGVycmhpbnQoIlRyeSB0aGUgQ09QWSAoU0VMRUNUIC4uLikgVE8gdmFyaWFudC4iKSkp OwogCQllbHNlIGlmIChyZWwtPnJkX3JlbC0+cmVsa2luZCA9PSBSRUxLSU5EX01BVFZJRVcp Ci0JCQllcmVwb3J0KEVSUk9SLAotCQkJCQkoZXJyY29kZShFUlJDT0RFX1dST05HX09CSkVD VF9UWVBFKSwKLQkJCQkJIGVycm1zZygiY2Fubm90IGNvcHkgZnJvbSBtYXRlcmlhbGl6ZWQg dmlldyBcIiVzXCIiLAotCQkJCQkJCVJlbGF0aW9uR2V0UmVsYXRpb25OYW1lKHJlbCkpLAot CQkJCQkgZXJyaGludCgiVHJ5IHRoZSBDT1BZIChTRUxFQ1QgLi4uKSBUTyB2YXJpYW50LiIp KSk7CisJCXsKKwkJCWlmICghUmVsYXRpb25Jc1BvcHVsYXRlZChyZWwpKQorCQkJCWVyZXBv cnQoRVJST1IsCisJCQkJCQllcnJjb2RlKEVSUkNPREVfRkVBVFVSRV9OT1RfU1VQUE9SVEVE KSwKKwkJCQkJCWVycm1zZygiY2Fubm90IGNvcHkgZnJvbSB1bnBvcHVsYXRlZCBtYXRlcmlh bGl6ZWQgdmlldyBcIiVzXCIiLAorCQkJCQkJCSAgIFJlbGF0aW9uR2V0UmVsYXRpb25OYW1l KHJlbCkpLAorCQkJCQkJZXJyaGludCgiVXNlIHRoZSBSRUZSRVNIIE1BVEVSSUFMSVpFRCBW SUVXIGNvbW1hbmQuIikpOworCQl9CiAJCWVsc2UgaWYgKHJlbC0+cmRfcmVsLT5yZWxraW5k ID09IFJFTEtJTkRfRk9SRUlHTl9UQUJMRSkKIAkJCWVyZXBvcnQoRVJST1IsCiAJCQkJCShl cnJjb2RlKEVSUkNPREVfV1JPTkdfT0JKRUNUX1RZUEUpLApkaWZmIC0tZ2l0IGEvc3JjL3Rl c3QvcmVncmVzcy9leHBlY3RlZC9jb3B5Lm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0 ZWQvY29weS5vdXQKaW5kZXggMDZiYWU4YzYxYWUuLjhkNWEwNjU2M2M0IDEwMDY0NAotLS0g YS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2NvcHkub3V0CisrKyBiL3NyYy90ZXN0L3Jl Z3Jlc3MvZXhwZWN0ZWQvY29weS5vdXQKQEAgLTMzOCwzICszMzgsMTUgQEAgY3JlYXRlIGZv cmVpZ24gdGFibGUgY29weXRlc3RfZm9yZWlnbl90YWJsZSAoYSBpbnQpIHNlcnZlciBjb3B5 dGVzdF9zZXJ2ZXI7CiBjb3B5IGNvcHl0ZXN0X2ZvcmVpZ25fdGFibGUgZnJvbSBzdGRpbiAo ZnJlZXplKTsKIEVSUk9SOiAgY2Fubm90IHBlcmZvcm0gQ09QWSBGUkVFWkUgb24gYSBmb3Jl aWduIHRhYmxlCiByb2xsYmFjazsKKy0tIFRlc3RzIGZvciBDT1BZIFRPIHdpdGggbWF0ZXJp YWxpemVkIHZpZXdzLgorLS0gQ09QWSBUTyBzaG91bGQgZmFpbCBmb3IgYW4gdW5wb3B1bGF0 ZWQgbWF0ZXJpYWxpemVkIHZpZXcKKy0tIGJ1dCBzdWNjZWVkIGZvciBhIHBvcHVsYXRlZCBv bmUuCitDUkVBVEUgTUFURVJJQUxJWkVEIFZJRVcgY29weXRlc3RfbXYgQVMgU0VMRUNUIDEg QVMgaWQgV0lUSCBOTyBEQVRBOworQ09QWSBjb3B5dGVzdF9tdihpZCkgVE8gc3Rkb3V0IFdJ VEggKGhlYWRlcik7CitFUlJPUjogIGNhbm5vdCBjb3B5IGZyb20gdW5wb3B1bGF0ZWQgbWF0 ZXJpYWxpemVkIHZpZXcgImNvcHl0ZXN0X212IgorSElOVDogIFVzZSB0aGUgUkVGUkVTSCBN QVRFUklBTElaRUQgVklFVyBjb21tYW5kLgorUkVGUkVTSCBNQVRFUklBTElaRUQgVklFVyBj b3B5dGVzdF9tdjsKK0NPUFkgY29weXRlc3RfbXYoaWQpIFRPIHN0ZG91dCBXSVRIIChoZWFk ZXIpOworaWQKKzEKK0RST1AgTUFURVJJQUxJWkVEIFZJRVcgY29weXRlc3RfbXY7CmRpZmYg LS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9jb3B5LnNxbCBiL3NyYy90ZXN0L3JlZ3Jl c3Mvc3FsL2NvcHkuc3FsCmluZGV4IDMwMDliZGZkZjg5Li5mMGI4OGEyM2RiOCAxMDA2NDQK LS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY29weS5zcWwKKysrIGIvc3JjL3Rlc3QvcmVn cmVzcy9zcWwvY29weS5zcWwKQEAgLTM2NiwzICszNjYsMTIgQEAgY29weSBjb3B5dGVzdF9m b3JlaWduX3RhYmxlIGZyb20gc3RkaW4gKGZyZWV6ZSk7CiAxCiBcLgogcm9sbGJhY2s7CisK Ky0tIFRlc3RzIGZvciBDT1BZIFRPIHdpdGggbWF0ZXJpYWxpemVkIHZpZXdzLgorLS0gQ09Q WSBUTyBzaG91bGQgZmFpbCBmb3IgYW4gdW5wb3B1bGF0ZWQgbWF0ZXJpYWxpemVkIHZpZXcK Ky0tIGJ1dCBzdWNjZWVkIGZvciBhIHBvcHVsYXRlZCBvbmUuCitDUkVBVEUgTUFURVJJQUxJ WkVEIFZJRVcgY29weXRlc3RfbXYgQVMgU0VMRUNUIDEgQVMgaWQgV0lUSCBOTyBEQVRBOwor Q09QWSBjb3B5dGVzdF9tdihpZCkgVE8gc3Rkb3V0IFdJVEggKGhlYWRlcik7CitSRUZSRVNI IE1BVEVSSUFMSVpFRCBWSUVXIGNvcHl0ZXN0X212OworQ09QWSBjb3B5dGVzdF9tdihpZCkg VE8gc3Rkb3V0IFdJVEggKGhlYWRlcik7CitEUk9QIE1BVEVSSUFMSVpFRCBWSUVXIGNvcHl0 ZXN0X212OwotLSAKMi40OC4xCgo= --------------ktBq07x0iJsd7i0LylIVu0Oh--