MIME-Version: 1.0
References: <s0491qrn-343s-0757-8sn5-120rr8610qqq@tzk.arg>
In-Reply-To: <s0491qrn-343s-0757-8sn5-120rr8610qqq@tzk.arg>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 26 Aug 2025 13:31:12 -0700
Message-ID: <CAKFQuwYBVFjoNFUzsomdvRQXEEQH5A4T-+_STE=WNUomw6LZSQ@mail.gmail.com>
Subject: Re: In-order pg_dump (or in-order COPY TO)
To: Dimitrios Apostolou <jimis@gmx.net>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bfcd69063d4a8f47"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYBVFjoNFUzsomdvRQXEEQH5A4T-%2B_STE%3DWNUomw6LZSQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, Aug 26, 2025 at 12:43=E2=80=AFPM Dimitrios Apostolou <jimis@gmx.net=
> wrote:

> Could the
> row-order have changed when doing COPY FROM with pg_restore?


There is no reliable, meaningful, row ordering when it comes to the
physical files.  Sure, cluster does make an attempt, but it is quite
limited in practice.


> A *logical* dump of data shouldn't be affected by on-disk order.
> Internal representation shouldn't affect the output.
>

The logical dump has no ordering - it will come out however it comes out.
"COPY <table> TO ..." doesn't have an order by clause - there is no way to
make or communicate to it that ordering is important.  For adhoc work you
can use "COPY <query> TO ..." and put and order by in the query.

David J.

--000000000000bfcd69063d4a8f47
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 Tue, Aug 26, 2025 at 12:43=E2=80=AFPM Dimitrios Apostol=
ou &lt;<a href=3D"mailto:jimis@gmx.net">jimis@gmx.net</a>&gt; wrote:</span>=
<span style=3D"font-family:Arial,Helvetica,sans-serif">=C2=A0</span></div><=
/div><div class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">Could the <br>
row-order have changed when doing COPY FROM with pg_restore?</blockquote><d=
iv><br></div><div><div class=3D"gmail_default" style=3D"font-family:arial,h=
elvetica,sans-serif">There is no reliable, meaningful, row ordering when it=
 comes to the physical files.=C2=A0 Sure, cluster does make an attempt, but=
 it is quite limited in practice.</div></div><div class=3D"gmail_default" s=
tyle=3D"font-family:arial,helvetica,sans-serif"><br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
<br>
A *logical* dump of data shouldn&#39;t be affected by on-disk order. <br>
Internal representation shouldn&#39;t affect the output.<br></blockquote><d=
iv><br></div><div class=3D"gmail_default" style=3D"font-family:arial,helvet=
ica,sans-serif">The logical dump has no ordering - it will come out however=
 it comes out.=C2=A0 &quot;COPY &lt;table&gt; TO ...&quot; doesn&#39;t have=
 an order by clause - there is no way to make or communicate to it that ord=
ering is important.=C2=A0 For adhoc work you can use &quot;COPY &lt;query&g=
t; TO ...&quot; and put and order by in the query.</div><div class=3D"gmail=
_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div c=
lass=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Dav=
id J.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetic=
a,sans-serif"><br></div></div></div>

--000000000000bfcd69063d4a8f47--