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 1ur1qY-00H4Lv-WF for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 22:08:56 +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 1ur1qY-00AkR6-EG for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 22:08:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ur1qY-00AkQw-2q for pgsql-general@lists.postgresql.org; Tue, 26 Aug 2025 22:08:54 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ur1qW-00228a-1h for pgsql-general@postgresql.org; Tue, 26 Aug 2025 22:08:54 +0000 Received: by mail-oi1-x236.google.com with SMTP id 5614622812f47-435de7d6d05so3890655b6e.2 for ; Tue, 26 Aug 2025 15:08:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756246130; x=1756850930; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=XRQi4P46uWreTR6e6iw5MvSUFMGaRh5+YB2SONqi2d4=; b=RQCneMvBQxhySqoFmR+YjcIYEouKQMWwdimFu6hU4Qte77btfQEbUy6FEXLenPIpUK YOiQ78uSZrYfgXmbB/SuvnJ4MK9mLRTFSGPiJL6kl1utpJmsLVhJuojY870mQY6NQTPe jDI9u8PkKp+SbPJidr+A+I5XgBDR+pBo+t30/IHzfJSzORtTxIwFjCRbp9jJgGJnjicI 6E2IHsnMdHSPPzap8YHOfmLB5hTE2PudrVNzTyZFpqmm76tTnOYHs6I8SegO636WuAcG GZ0+B9pZDXM2QW7zxguan0j+/4zrEOcHhi+VWRaLsSHbwzrI0oEPkzQKepHxydtKMbso pcwA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756246130; x=1756850930; h=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=XRQi4P46uWreTR6e6iw5MvSUFMGaRh5+YB2SONqi2d4=; b=Vf1Z2BqiX2gE/7l8PTUwPGAgrDXqkwFqRwxCLUkSSH2NHNKIGf66oI/Ck1Y/MysgB3 kw+RYA74Q1wd2YmcT/4Dwwl6GDZYQ0LMg7cZKZs9VB2fFgazuREiD/4HgJeo5q2dP5dm gEoXeh7rckhFnW59Rkc5dd6KVrtkbIucL/p3JAtj3lstEGKD9WkbMvHzb6zNSEiSpxLm zarSAyPJ++CWrqYL4V11WXkURmoL88HGdwyKgRLbTLQJevuO0vJHXDnRp5h4V4Vfl+Mq y3T8xHA/W+CVjshGNHkOOxLyDmLlm7G1sSX6eJV2QwjI2MoIcO9xkYomAE7AsCU5w1vo Nzsg== X-Gm-Message-State: AOJu0YyAS4fz5kfbrxSXwCwNqP2ZY7jcl6z6v4BQ7tE5VlB9sJaN7dHf gPF7YiDrEtFHZ5jiz3QDuvu2eIlOsPSpVvvkuNifQuBcd3wJUDw9lw65tyS6zc8ZZdBwi7rRDAL K4xktBana4ELp9yhpMhPWWQ2rsV/uKGARFQ== X-Gm-Gg: ASbGncuwqtyXSLko8qt+sJay/XMJC4WKSFjTl3fOxzdPMmST52nau+lYyTfYXGfV3AX NvrTiicwD8KXD24iHQeF+dfTfDaEAiErK20biejcA7ReUs5sNXUcRkTZpSv5HcroV2AnwyOXHi/ TqR7q3xWrKP3ALm70H5/42WuKQqo8y9izdoAPswZma48JTXi9Lp82Uwvu/XBkxrcFxYKPO//pOF zkSVspe X-Google-Smtp-Source: AGHT+IFN+efoY6X+wkPqUB0SHKFaw0H441cRlpkvPUWTbgobVCafiCB+qMS2dVCVex68n8hmafbTGH79fC4SZ8/8jCQ= X-Received: by 2002:a05:6808:818f:20b0:437:b396:b931 with SMTP id 5614622812f47-437b396cd45mr2838638b6e.20.1756246130558; Tue, 26 Aug 2025 15:08:50 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 26 Aug 2025 18:08:39 -0400 X-Gm-Features: Ac12FXygRZ6RxfEAYZE2AwbJmyfMBw_PLxR46JQ1f2lraSBxVBiM0fJ24oR8RXM Message-ID: Subject: Re: In-order pg_dump (or in-order COPY TO) To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000c00c1a063d4bea6e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c00c1a063d4bea6e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Aug 26, 2025 at 4:31=E2=80=AFPM David G. Johnston < david.g.johnston@gmail.com> wrote: > On Tue, Aug 26, 2025 at 12:43=E2=80=AFPM Dimitrios Apostolou > 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 TO ..." doesn't have an order by clause - there is no way t= o > make or communicate to it that ordering is important. > Doesn't COPY TO copy out records in the order they appeared in the physical files? That _seems_ to mean that the records laid down by COPY FROM should be in the same order as they were in the old dump files. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000c00c1a063d4bea6e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Aug 26, 2025 at 4:31=E2=80=AFPM D= avid G. Johnston <david.g.= johnston@gmail.com> wrote:
On Tue, Aug 26, 202= 5 at 12:43=E2=80=AFPM Dimitrios Apostolou <jimis@gmx.net> wrote:=C2=A0
Could the <= br> row-order have changed when doing COPY FROM with pg_restore?

The= re is no reliable, meaningful, row ordering when it comes to the physical f= iles.=C2=A0 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 logi= cal dump has no ordering - it will come out however it comes out.=C2=A0 &qu= ot;COPY <table> TO ..." doesn't have an order by clause - th= ere is no way to make or communicate to it that ordering is important.
=C2=A0
Doesn't COPY TO c= opy out records in the order they appeared in the physical files?=C2=A0 Tha= t _seems_ to mean that the records laid down by COPY=C2=A0FROM should be in= the same order as they were in the old dump files.

--
Death to <Redacted>, and butter sa= uce.
Don't boil me, I'm still alive.
<Redacted&= gt; lobster!
--000000000000c00c1a063d4bea6e--