Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tzSf7-002HXt-W4 for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Apr 2025 03:51:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tzSf5-00Fbww-Qb for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Apr 2025 03:51:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tzSf5-00Fbwo-Cd for pgsql-hackers@lists.postgresql.org; Tue, 01 Apr 2025 03:51:39 +0000 Received: from mail-oa1-x36.google.com ([2001:4860:4864:20::36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tzSf3-002GFV-0n for pgsql-hackers@postgresql.org; Tue, 01 Apr 2025 03:51:38 +0000 Received: by mail-oa1-x36.google.com with SMTP id 586e51a60fabf-2c7e5f7f0e3so1451355fac.3 for ; Mon, 31 Mar 2025 20:51:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743479496; x=1744084296; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=e/Y9wHQ0bErRXHCmjje5y+ZDlZkflFmkj6EwzrdLBac=; b=RbwA38Gyv7nAxDA76YpGslY87DBjAz+Oc16aFLpBPGvBxY20RdxDEFOHqjPDrivJgV NXvvvRvPYRlTnV87ToAwbUkEgFbtBx05a+RvBz/YtuX5EgSDXOYh5IoUFKHiFzkrjmak EPgZ7XOW2e8QSRPxi0+hFBLaeNwPk2AtjzwsuKG9VcVjbO4R7c21Ssb9xVGwrhrwAzlw kNZMFdH2WfLfAd3DE7pvHof/tRSOouguM082dNb/hiEgcVDjRHweBTYm7eQhw+PGHcDM mPd7BenUds4XhwskLPEXX/FnEybIGVZlji4oNPmJs5vDmQORvR9uK+rqtcW+KxLjpydR CMJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743479496; x=1744084296; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=e/Y9wHQ0bErRXHCmjje5y+ZDlZkflFmkj6EwzrdLBac=; b=OOMs7Ni+cdix8GxHQMkutmCyCYElc61Ye5BiRoNpZ6bCtlfinObGPEerQ9xm8G+PBW qJvSyZZT0r4KtsGFs2QU9/tgD4FkPV7bDibXtHFjhjn8cZ0QeyvXHcfAQrzgSZoXFU+b Ktj3rLBKCjTg3GbvVkOvaOuiGkK/9gnEIyltr+ZapvDwlynWLYVitpy/8dJBA60njRSA R6V3ESfx47S2UoKV9IIxyjbacjwZLQcvTFzzOPAcyfmBHtBNXmlq2mXcE4BrF8L1Ml5Y xhzSHA3WjaAUZheBA80JeycTaj7rt5swffwV/nABI7QIesBPQDGcentMYxAwAJFMJncr lXVg== X-Forwarded-Encrypted: i=1; AJvYcCVfJ1AKQrfr7yqeYZ5y9J4fiC9VYCys4wFBAj+aKJGk3emq8fFFkWWk6ql40M8R4sbwvUISERwYlc9ZqRul@postgresql.org X-Gm-Message-State: AOJu0YxX8Mah2Cw2Gtw/rY+NLK8GAdQNNGWYF2j9N6edz1pJOfjHzMd9 CCGZ1uI9CiEqNWy1QRM8sPBhKGdG9LLxu4bsY0TLdygJGbMgfWKtBAG/V7lpF83uuuJG1+lEgmq +WejvIIo8w+xbfLLwNp61s+/8B7s= X-Gm-Gg: ASbGncsEpLwpMe5+wjmCNR7Rd4qEN74eDs844VCbHQNu6EV0kwjT76qLHF2SIkBsWDK +srgzrac1/uLiKbxOmCr+NLd1+Y4RUCxx8uOpHXlWuQngA70zZ8GRZ8hLFBzYGWCH/6Fa744PTk yuzoGN+CheKwxvqiCEXZ8ri03r X-Google-Smtp-Source: AGHT+IGX+XD7YPxKNYEC3x+3ALCoT0lrW+aWVQ6CD/ge4850P0sqtoPgFWUw7qpIYk4iBnVXTZRFqu23Ird58CYAJKg= X-Received: by 2002:a05:6870:8a09:b0:29e:6f32:6d91 with SMTP id 586e51a60fabf-2cbcf8060c7mr6599409fac.33.1743479496160; Mon, 31 Mar 2025 20:51:36 -0700 (PDT) MIME-Version: 1.0 References: <442e62f7-ef64-4df1-8453-d682fe9bdaa3@dunslane.net> <425ebe4b-06c5-4212-af68-fd0e5537c250@dunslane.net> In-Reply-To: From: "David G. Johnston" Date: Mon, 31 Mar 2025 20:50:59 -0700 X-Gm-Features: AQ5f1JoPauI0nelts-itBELhpGAe3fPioQI0wLHMhvma3FGxWXt_2G8Ed2npD-U Message-ID: Subject: Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query). To: jian he Cc: Fujii Masao , Andrew Dunstan , Kirill Reshke , PostgreSQL-development , Tom Lane , Michael Paquier Content-Type: multipart/alternative; boundary="0000000000000ac05d0631af74db" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000ac05d0631af74db Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Mar 31, 2025 at 8:13=E2=80=AFPM jian he wrote: > On Mon, Mar 31, 2025 at 11:27=E2=80=AFPM Fujii Masao > > > > > 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." > > Those don't address the "populated" aspect of the materialized view. I'm unclear ATM (can check later if needed) if populated means "non-empty" or simply "had refresh executed at least once on it". i.e., if the refresh produces zero rows stored in the MV is it still populated? I'm guessing yes; and this only pertains to "WITH NO DATA", which itself already calls out that "...and cannot be queried until RMV is used". I find it of marginal usefulness to bring that distinction over to COPY TO absent people showing up confused about the error message, which likely will be quite rare. That said I'd probably settle with: COPY TO can only be used with plain tables and populated materialized views. It does not copy rows from child tables or child partitions (i.e., copy table to copies the same rows as select * from only table). The syntax COPY (select * from table) TO ... can be used to dump all of the rows in an inheritance hierarchy, partitioned table, or foreign table; as well as ordinary view results. Curious about sequences; no way to name an index here. I'm second-guessing why "composite type" shows up in the glossary under "Relation"...though that originally came up IIRC discussing namespaces. David J. --0000000000000ac05d0631af74db Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Mar 31, 2025 at 8:13=E2=80=AFPM jian he <jian.universality@gmail.com>= ; wrote:
On Mon, Mar 31, 2025 at = 11:27=E2=80=AFPM Fujii Masao

>
> 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 materialize= d views,
not regular views. It also does not copy rows from child tables or
child partitions."


Those don'= ;t address the "populated" aspect of the materialized view.
=

I'm unclear ATM (can check later if needed) if populate= d means "non-empty" or simply "had refresh executed at least= once on it".=C2=A0 i.e., if the refresh produces zero rows stored in = the MV is it still populated?=C2=A0 I'm guessing yes; and this only per= tains to "WITH NO DATA", which itself already calls out that &quo= t;...and cannot be queried until RMV is used".=C2=A0 I find it of marg= inal usefulness to bring that distinction over to COPY TO absent people sho= wing up confused about the error message, which likely will be quite rare.= =C2=A0 That said I'd probably settle with:

COPY TO= can only be used with plain tables and populated
materialized view= s. It does not copy rows from child tables
or child partitions (i.e= ., copy table to copies the same rows as
select * from only table).= The syntax COPY (select * from table) TO ...=C2=A0
can be used to = dump all of the rows in an inheritance hierarchy,
partitioned table= , or foreign table; as well as ordinary view results.

= Curious about sequences; no way to name an index here.

I'm second-guessing why "composite type" shows up in the glo= ssary under "Relation"...though that originally came up IIRC disc= ussing namespaces.

David J.

--0000000000000ac05d0631af74db--