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>
 <CACJufxF3noNs9O6O3fL783h2-Sy1wfJb=0H+WMFmOLuhkfJw8w@mail.gmail.com>
In-Reply-To: 
 <CACJufxF3noNs9O6O3fL783h2-Sy1wfJb=0H+WMFmOLuhkfJw8w@mail.gmail.com>
From: vignesh C <vignesh21@gmail.com>
Date: Tue, 1 Apr 2025 15:15:28 +0530
Message-ID: 
 <CALDaNm3_oLZK=L6Si3JXDh7fL+NurF07CHtABPkhFOZ9xmsYZg@mail.gmail.com>
Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of
 COPY (query).
To: jian he <jian.universality@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>,
 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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALDaNm3_oLZK%3DL6Si3JXDh7fL%2BNurF07CHtABPkhFOZ9xmsYZg%40mail.gmail.com>
Precedence: bulk

On Tue, 1 Apr 2025 at 08:43, jian he <jian.universality@gmail.com> wrote:
>
> 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 fro=
m 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 REFRES=
H 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."

One thing I noticed was that if the materialized view is not refreshed
user will get stale data:
postgres=3D# create table t1(c1 int);
CREATE TABLE
postgres=3D# create materialized view mv2 as select * from t1;
SELECT 0

postgres=3D# insert into t1 values(10);
INSERT 0 1
postgres=3D# select * from t1;
 c1
----
 10
(1 row)

-- Before refresh the data will not be selected
postgres=3D# copy mv2 to stdout with (header);
c1

-- After refresh the data will be available
postgres=3D# refresh materialized view mv2;
REFRESH MATERIALIZED VIEW
postgres=3D# copy mv2 to stdout with (header);
c1
10

Should we document this?

The following can be changed to keep it consistent:
+copy matview1(id) TO stdout with (header);
+copy matview2 TO stdout with (header);
To:
COPY matview1(id) TO stdout with (header);
COPY matview2 TO stdout  with (header);

Regards,
Vignesh