Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jCgFz-0001aS-Oz for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Mar 2020 09:05:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jCgFy-0000ZP-Hy for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Mar 2020 09:05:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jCg16-0007WP-P1 for pgsql-bugs@lists.postgresql.org; Fri, 13 Mar 2020 08:50:04 +0000 Received: from starship.nidsa.net ([212.227.110.112]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jCg14-0007bm-6j for pgsql-bugs@lists.postgresql.org; Fri, 13 Mar 2020 08:50:04 +0000 Received: from [10.13.1.101] (port=46349 helo=starship.nidsa.net) by starship.nidsa.net with esmtps (TLSv1.2:AES256-SHA:256) (Exim 4.82_1-5b7a7c0-XX) (envelope-from ) id 1jCg0v-0002bj-0G; Fri, 13 Mar 2020 09:49:53 +0100 Received: from W2012-02.nidsa.loc (10.13.1.101) by W2012-02.nidsa.loc (10.13.1.101) with Microsoft SMTP Server (TLS) id 15.0.1497.2; Fri, 13 Mar 2020 09:49:50 +0100 Received: from W2012-02.nidsa.loc ([::1]) by W2012-02.nidsa.loc ([::1]) with mapi id 15.00.1497.006; Fri, 13 Mar 2020 09:49:50 +0100 X-CTCH-RefID: str=0001.0A782F22.5E6B4922.00AA,ss=1,re=0.000,recu=0.000,reip=0.000,cl=1,cld=1,fgs=0 From: Hans Buschmann To: "David G. Johnston" , PostgreSQL mailing lists Subject: AW: BUG #16300: Text line order corruption with COPY command Thread-Topic: BUG #16300: Text line order corruption with COPY command Thread-Index: AQHV+K/pQZdX5FhnEEaPRl9C2wkqeKhGNnvy Date: Fri, 13 Mar 2020 08:49:50 +0000 Message-ID: <1584089392834.36289@nidsa.net> References: <16300-b952db3f81f7f40d@postgresql.org>, In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [185.101.187.99] Content-Type: multipart/alternative; boundary="_000_158408939283436289nidsanet_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_158408939283436289nidsanet_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ________________________________ Von: David G. Johnston Gesendet: Donnerstag, 12. M=E4rz 2020 21:42 An: Hans Buschmann; PostgreSQL mailing lists Betreff: Re: BUG #16300: Text line order corruption with COPY command Thank you for the quick reply. When looking into the documentation I find under SQL COPY command: "If a column list is specified, COPY TO copies only the data in the specifi= ed columns to the file. For COPY FROM, each field in the file is inserted, in order, into the speci= fied column. Table columns not specified in the COPY FROM column list will receive their= default values. " So I expected the insertion in order as said above. In my opinion it is essential to preserve the order of textfile input in CO= PY FROM. This also holds true when copying from another source like a progr= am, where often it is not practicable to add an orderable column on input. Not preserving (and rendering on a select) the order makes the COPY FROM mo= stly unusable for cases where the order must be preserved. (Think of XML, J= SON, Source code, Log files, Disassembly etc.). The size of such kind of files can it make also impractible to take the men= tioned circumventing methods you provided. I have not inspected the resulting tuple orders on the physical file. I will try to nail down the first occurence of the disordering during the i= mport by comparing import and export. My concern also goes to pg_dump and friends, which could change such not na= tural orderable tables through pg_dump/pg_restore. Hans Buschmann --_000_158408939283436289nidsanet_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable


Von: David G. Johnston &l= t;david.g.johnston@gmail.com>
Gesendet: Donnerstag, 12. M=E4rz 2020 21:42
An: Hans Buschmann; PostgreSQL mailing lists
Betreff: Re: BUG #16300: Text line order corruption with COPY comman= d
 


Thank you for the quick reply.

 

When looking into the documentation I find under SQL COPY command:

 

"If a column list is specified, COPY TO copies only the data in the= specified columns to the file.
For COPY FROM, each field in the file is inserted, in order, into the speci= fied column.
Table columns not specified in the COPY FROM column list will receive their= default values. "

 

So I expected the insertion in order as said above.

 

In my opinion it is essential to preserve the order of textfile input in= COPY FROM. This also holds true when copying from another source like a pr= ogram, where often it is not practicable to add an orderable column on inpu= t.

 

Not preserving (and rendering on a select) the order makes the COPY FROM= mostly unusable for cases where the order must be preserved. (Think of XML= , JSON, Source code, Log files, Disassembly etc.).

 

The size of such kind of files can it make also impractible to take the = mentioned circumventing methods you provided.

 

I have not inspected the resulting tuple orders on the physical file.

 

I will try to nail down the first occurence of the disordering during th= e import by comparing import and export.

 

My concern also goes to pg_dump and friends, which could change such not= natural orderable tables through pg_dump/pg_restore.

 

Hans Buschmann

 

--_000_158408939283436289nidsanet_--