Content-Type: multipart/alternative;
 boundary="------------0ijNBDOjUu0VSys7M6X0dvw3"
Message-ID: <442e62f7-ef64-4df1-8453-d682fe9bdaa3@dunslane.net>
Date: Sat, 29 Mar 2025 14:56:03 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of
 COPY (query).
To: "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>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAmwQTAQgARQIbAwIX
 gAIZAQULCQgHAgMiAgEGFQoJCAsCBBYCAwECHgcWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUC
 ZFlxxwUJGVGAbgAKCRCZ+n/LWfw7gXikB/9ZdcUy6CTBFIIuL/bVsc1eLEW/gJBjJBF6HxNY
 xgEkAgXAp4Lg4A5U+QB9GouFr7+GYxF0BU4hzoGhNPUWltxnHdMWP8nC/38LAqgMi8L/bbsm
 HW5YPBdWYaAZAPJQVfOAgjTbRUb26KSprpyrrJKW0ZmrZfjhNPcQ72jpWzoPLQqx2X6B0fru
 1jq+cBh8lb6r1mJTim1T3JIn+F/v5VpdQS+EL8xqsHkfzKjIPsW3CIXpkypSk6saA55Rkkbl
 26AW8ftPVB0Q6Lnn6FLt9CP0MGNixBQ55yq8r1K+nCBvCCjvQjM8RDm0UUum0WNl+ifQgTLO
 E8TWEnwVtkBf+3QWzsBNBE7KWFkBCADRnOM0FCzsYW6jtncg+dWIagjUZpvaClmqn/sJluLa
 Q3v1VXMQJzYs3eC1gh386W+XBwLRpDj3jzH81lX+p73Re3d3oJW7X+ffsxuzu5ZVdMUkqBYo
 nkAbKxr6gyJ12F/+JkUVzLcoTN+d/7YsQvUVi7NaKH8mJgjz112O4fUe3p9wfAaFa0RXHc5S
 GPzRTYRRlv/XZBIho4J2tkZOnteZJZ+GbxQVlINt6fd8P6al3MWOvpP/ExJPguEfjOsO6Njy
 xjo3WfpD4lHMOR/Oc3/8mScEF84rF2jXbsFgelWnbPWAvXY+pD0dXOFRkagGmC/viwBDqq5b
 5tk76kKmUbZxABEBAAHCwHwEGAEIACYCGwwWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUCZFlx
 5wUJGVGAjgAKCRCZ+n/LWfw7gf+iB/4g8CPY5jihf5r/8EsoIGe2H+dpVmpPF8YGBzTIvCz/
 fQoOq8AX/pE76QEuFnFZWfjw+wgBXgCVmkox2Eflkk6z4ND3pcwGZ6CfCxTQCDk/dij+2DQ4
 6bmDCy/sBgcbz9mTpoLC11HLoPae6YN9nBNQRZDcEFEu54OaVOqlIdbA6m+POIBCXZdHOFc0
 WoDTgxHRzC1jgQNidyd6tKqcsVJs0dzF0oKTmFFmUAqTdJO12LBuNA1rlqrR3EtpYk8B/wtS
 5dIMD7Q8hwQpL+4C6GNpb6ZKnPkLi47pDOLhz2qBrqN+rqUEsT3YnExYpzj5yOBi+FlmV1Hw
 49QYe1sn2ZPs
In-Reply-To: 
 <CAKFQuwbD775kSHFfyF3shu+XMTX=3SEH+_JYLOWvrPo3y5dK-w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/442e62f7-ef64-4df1-8453-d682fe9bdaa3%40dunslane.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0ijNBDOjUu0VSys7M6X0dvw3
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 2025-03-29 Sa 12:17 PM, David G. Johnston wrote:
> On Sat, Mar 29, 2025 at 9:06 AM 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 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.
>
>
>
>
> "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.
>
>


I don't believe that the premise supports the conclusion.


cheers


andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

--------------0ijNBDOjUu0VSys7M6X0dvw3
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 2025-03-29 Sa 12:17 PM, David G.
      Johnston wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwbD775kSHFfyF3shu+XMTX=3SEH+_JYLOWvrPo3y5dK-w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><span
              style="font-family:Arial,Helvetica,sans-serif">On Sat, Mar
              29, 2025 at 9:06 AM Andrew Dunstan &lt;<a
                href="mailto:andrew@dunslane.net" moz-do-not-send="true"
                class="moz-txt-link-freetext">andrew@dunslane.net</a>&gt;
              wrote:</span></div>
        </div>
        <div class="gmail_quote gmail_quote_container">
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
            <div>
              <p><br>
              </p>
              <div>On 2025-03-29 Sa 10:40 AM, David G. Johnston wrote:<br>
              </div>
              <blockquote type="cite"> On Saturday, March 29, 2025,
                Kirill Reshke &lt;<a
                  href="mailto:reshkekirill@gmail.com" target="_blank"
                  moz-do-not-send="true" class="moz-txt-link-freetext">reshkekirill@gmail.com</a>&gt;
                wrote:<br>
                <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On
                  Sat, 29 Mar 2025 at 09:47, jian he &lt;<a
                    href="mailto:jian.universality@gmail.com"
                    target="_blank" moz-do-not-send="true"
                    class="moz-txt-link-freetext">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't have storage, table_beginscan
                  mechanism won't work.<br>
                  &gt;<br>
                  &gt; so i don'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't sure I'm<br>
                  buying that, but whatever, let's hear some other
                  voices from the<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> At present copy {table} to only exists to support
                  pg_dump.  It is not marketed as a general purpose
                  export facility.  </div>
              </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="gmail_default"
            style="font-family:arial,helvetica,sans-serif">"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.</div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><br>
          </div>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p><br>
    </p>
    <p>I don't believe that the premise supports the conclusion. <br>
    </p>
    <p><br>
    </p>
    <p>cheers</p>
    <p><br>
    </p>
    <p>andrew<br>
    </p>
    <pre class="moz-signature"
    signature-switch-id="d0437855-2267-4610-80b3-83167ec45b0b" cols="72">--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
  </body>
</html>

--------------0ijNBDOjUu0VSys7M6X0dvw3--