Message-ID: <f6abc172-f300-4e90-bcc6-c189d29d2f9f@oss.nttdata.com>
Date: Tue, 1 Apr 2025 00:26:58 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of
 COPY (query).
To: Andrew Dunstan <andrew@dunslane.net>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>,
 jian he <jian.universality@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>, Michael Paquier <michael@paquier.xyz>
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>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <425ebe4b-06c5-4212-af68-fd0e5537c250@dunslane.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f6abc172-f300-4e90-bcc6-c189d29d2f9f%40oss.nttdata.com>
Precedence: bulk


On 2025/03/30 5:01, Andrew Dunstan wrote:
> 
> On 2025-03-29 Sa 2:58 PM, David G. Johnston wrote:
>> On Sat, Mar 29, 2025 at 11:56 AM Andrew Dunstan <andrew@dunslane.net> wrote:
>>
>>     I don't believe that the premise supports the conclusion.
>>
>>
>> Regardless, I do support this patch and probably any similar ones proposed in the future.  Do you have an opinion on that?
>>
>>
> 
> In principle I think it would be good to have COPY materialized_view TO ...

I haven't found any reasons to object to this patch for now,
so I have no objections to this change.


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.".


The copy.sgml documentation should clarify that COPY TO can
be used with a materialized view only if it is populated.


Wouldn't it be beneficial to add a regression test to check
whether COPY matview TO works as expected?

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION