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

hi.
generally  COPY TO``COPY table`` is faster than ``COPY (select * from table)``.

in BeginCopyTo. we have
"""
        else if (rel->rd_rel->relkind == RELKIND_MATVIEW)
            ereport(ERROR,
                    (errcode(ERRCODE_WRONG_OBJECT_TYPE),
                     errmsg("cannot copy from materialized view \"%s\"",
                            RelationGetRelationName(rel)),
                     errhint("Try the COPY (SELECT ...) TO variant.")));
"""
Since materialized views have physical storage,

we can make materialized views also using COPY table_name, instead of
COPY(query).

Some simple tests show around %3.7 or 4.3% speed up.

On Fri, Dec 20, 2024 at 8:02 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Thu, Dec 19, 2024 at 02:28:21PM +0800, jian he wrote:
> > Since materialized views have physical storage,
> >
> > we can make materialized views also using COPY table_name, instead of
> > COPY(query).
> >
> > Some simple tests show around %3.7 or 4.3% speed up.
>
> This restriction comes from 3bf3ab8c5636 as such relations may not be
> scannable when they have no data, no?  Perhaps this restriction could
> be lifted, but I'd suggest to dig more into the lists, there should be
> arguments and ideas explaining what could be done in this case
> (spoiler: I did not look at that).
> --


Thanks for the suggestion.
it was mentioned in link [1] and [2].

[1] https://www.postgresql.org/message-id/flat/8967.1353167301%40sss.pgh.pa.us#f5e947cfa9357dba780d238f1c5f6932
[2] https://www.postgresql.org/message-id/20121116162558.90150%40gmx.com


Basically we want to have the two directions of COPY.
so
copy the_materialized_view to stdout;
copy the_materialized_view  from stdin;

both will work fine.
obviously "copy the_materialized_view  from stdin; " will not work.