Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).

On Tue, 1 Apr 2025 at 08:43, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Mon, Mar 31, 2025 at 11:27 PM Fujii Masao
> <masao(dot)fujii(at)oss(dot)nttdata(dot)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."

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

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

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

-- After refresh the data will be available
postgres=# refresh materialized view mv2;
REFRESH MATERIALIZED VIEW
postgres=# 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