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>
In-Reply-To: <e38cbf42-0f8e-4c3b-8c77-70c83116fdb2@dunslane.net>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 29 Mar 2025 09:17:21 -0700
Message-ID: 
 <CAKFQuwbD775kSHFfyF3shu+XMTX=3SEH+_JYLOWvrPo3y5dK-w@mail.gmail.com>
Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of
 COPY (query).
To: Andrew Dunstan <andrew@dunslane.net>
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>
Content-Type: multipart/alternative; boundary="000000000000c6429e06317d8763"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwbD775kSHFfyF3shu%2BXMTX%3D3SEH%2B_JYLOWvrPo3y5dK-w%40mail.gmail.com>
Precedence: bulk

--000000000000c6429e06317d8763
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Mar 29, 2025 at 9:06=E2=80=AFAM Andrew Dunstan <andrew@dunslane.net=
> wrote:

>
> On 2025-03-29 Sa 10:40 AM, David G. Johnston wrote:
>
> On Saturday, March 29, 2025, Kirill Reshke <reshkekirill@gmail.com> wrote=
:
>
>> On Sat, 29 Mar 2025 at 09:47, jian he <jian.universality@gmail.com>
>> wrote:
>> >
>> > will use {table_beginscan, table_scan_getnextslot, table_endscan}
>> > to output the data.
>> > but views don't have storage, table_beginscan mechanism won't work.
>> >
>> > so i don't think this is possible for view.
>>
>> Well... So you are saying that let us have inconsistent features
>> because of how things are implemented in core... I don't sure I'm
>> buying that, but whatever, let's hear some other voices from the
>> community. My argument is that while we are working on it, perhaps we
>> should revise certain implementation specifics along the way. However,
>> this is merely my opinion on the matter.
>>
>
>  At present copy {table} to only exists to support pg_dump.  It is not
> marketed as a general purpose export facility.
>
>
> *ahem*
>
>
> What is your evidence for that proposition? If this were true we would no=
t
> support CSV mode, which pg_dump does not use. It might have limitations,
> but its use goes far beyond just pg_dump, both in theory and practice.
>
>
>
>
"copy {subquery} to" is a general-purpose exporter that makes use of those
additional features.  Sure, they also work for the narrowed case of "copy
{relation/table} to" but I make my claim on the very fact that {relation}
cannot be stuff like foreign tables or partitioned tables, which pg_dump
has no need to target.

David J.

--000000000000c6429e06317d8763
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Sat, Mar 29, 2025 at 9:06=E2=80=AFAM Andrew Dunstan &lt=
;<a href=3D"mailto:andrew@dunslane.net">andrew@dunslane.net</a>&gt; wrote:<=
/span></div></div><div class=3D"gmail_quote gmail_quote_container"><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <p><br>
    </p>
    <div>On 2025-03-29 Sa 10:40 AM, David G.
      Johnston wrote:<br>
    </div>
    <blockquote type=3D"cite">
     =20
      On Saturday, March 29, 2025, Kirill Reshke &lt;<a href=3D"mailto:resh=
kekirill@gmail.com" target=3D"_blank">reshkekirill@gmail.com</a>&gt;
      wrote:<br>
      <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex">On
        Sat, 29 Mar 2025 at 09:47, jian he &lt;<a href=3D"mailto:jian.unive=
rsality@gmail.com" target=3D"_blank">jian.universality@gmail.com</a>&gt;
        wrote:<br>
        &gt;<br>
        &gt; will use {table_beginscan, table_scan_getnextslot,
        table_endscan}<br>
        &gt; to output the data.<br>
        &gt; but views don&#39;t have storage, table_beginscan mechanism
        won&#39;t work.<br>
        &gt;<br>
        &gt; so i don&#39;t think this is possible for view.<br>
        <br>
        Well... So you are saying that let us have inconsistent features<br=
>
        because of how things are implemented in core... I don&#39;t sure
        I&#39;m<br>
        buying that, but whatever, let&#39;s hear some other voices from th=
e<br>
        community. My argument is that while we are working on it,
        perhaps we<br>
        should revise certain implementation specifics along the way.
        However,<br>
        this is merely my opinion on the matter.<br>
      </blockquote>
      <div><br>
      </div>
      <div>=C2=A0At present copy {table} to only exists to support pg_dump.=
=C2=A0
        It is not marketed as a general purpose export facility.=C2=A0 </di=
v>
    </blockquote>
    <p><br>
    </p>
    <p>*ahem*</p>
    <p><br>
    </p>
    <p>What is your evidence for that proposition? If this were true we
      would not support CSV mode, which pg_dump does not use. It might
      have limitations, but its use goes far beyond just pg_dump, both
      in theory and practice.</p>
    <p><br><br></p></div></blockquote><div><br></div><div class=3D"gmail_de=
fault" style=3D"font-family:arial,helvetica,sans-serif">&quot;copy {subquer=
y} to&quot; is a general-purpose exporter that makes use of those additiona=
l features.=C2=A0 Sure, they also work for the narrowed case of &quot;copy =
{relation/table} to&quot; but I make my claim on the very fact that {relati=
on} cannot be stuff like foreign tables or partitioned tables, which pg_dum=
p has no need to target.</div><div class=3D"gmail_default" style=3D"font-fa=
mily:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_default" sty=
le=3D"font-family:arial,helvetica,sans-serif">David J.</div><div class=3D"g=
mail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div></=
div></div>

--000000000000c6429e06317d8763--