Date: Mon, 13 Jun 2022 10:32:13 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
	Greg Stark <stark@mit.edu>,
	=?utf-8?B?UsOpbWk=?= Lapeyre <remi.lapeyre@lenstra.fr>,
	Zhihong Yu <zyu@yugabyte.com>, David Steele <david@pgmasters.net>,
	Michael Paquier <michael@paquier.xyz>,
	vignesh C <vignesh21@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Daniel Verite <daniel@manitou-mail.org>
Subject: Re: Add header support to text format and matching feature
Message-ID: <20220613023213.bj7qjnplvgfmx522@jrouhaud>
References: <94FDDB44-A608-4C75-A733-9F8FC5180D6B@lenstra.fr>
 <4816b2df-646f-dc19-e5cc-4bfbd04c0e43@enterprisedb.com>
 <07891B9D-2140-4C06-AF96-C14F1E1B0E8E@lenstra.fr>
 <f62f0262-2a3c-8839-5ced-25ca2421d738@enterprisedb.com>
 <8eba1968-9503-5be7-b473-37383f17bb72@enterprisedb.com>
 <CAM-w4HPkjYQGPm0-zx3XZHnQ_+B8AyWuCn_LyTeX7sNJCv4x8A@mail.gmail.com>
 <ae4dc590-090f-ae9b-6fdb-01a901dd4841@enterprisedb.com>
 <206ac69a-4e47-c49a-66ae-e748c339ba35@enterprisedb.com>
 <20220607154744.vvmitnqhyxrne5ms@jrouhaud>
 <d35e05ac-fde1-0dba-39bf-b30e75082b48@dunslane.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="7dpw752g5cnup6cx"
Content-Disposition: inline
In-Reply-To: <d35e05ac-fde1-0dba-39bf-b30e75082b48@dunslane.net>
Archived-At: 
 <https://www.postgresql.org/message-id/20220613023213.bj7qjnplvgfmx522%40jrouhaud>
Precedence: bulk


--7dpw752g5cnup6cx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi,

On Sun, Jun 12, 2022 at 09:36:13AM -0400, Andrew Dunstan wrote:
> 
> On 2022-06-07 Tu 11:47, Julien Rouhaud wrote:
> >
> > First, probably nitpicking, the HEADER MATCH is allowed for COPY TO, is that
> > expected?  The documentation isn't really explicit about it, but there's
> > nothing to match when exporting data it's a bit surprising.  I'm not opposed to
> > have HEADER MATCH means HEADER ON for COPY TO, as as-is one can easily reuse
> > the commands history, but maybe it should be clearly documented?
> 
> 
> I think it makes more sense to have a sanity check to prevent HEADER
> MATCH with COPY TO.

I'm fine with it.  I added such a check and mentioned it in the documentation.

> > Then, apparently HEADER MATCH doesn't let you do sanity checks against a custom
> > column list.  This one looks like a clear oversight, as something like that
> > should be entirely valid IMHO:
> >
> > CREATE TABLE tbl(col1 int, col2 int);
> > COPY tbl (col2, col1) TO '/path/to/file' WITH (HEADER MATCH);
> > COPY tbl (col2, col1) FROM '/path/to/file' WITH (HEADER MATCH);
> >
> > but right now it errors out with:
> >
> > ERROR:  column name mismatch in header line field 1: got "col1", expected "col2"
> >
> > Note that the error message is bogus if you specify attributes in a
> > different order from the relation, as the code is mixing access to the tuple
> > desc and access to the raw fields with the same offset.
> > [...]
> I think it should, but a temporary alternative would be to forbid HEADER
> MATCH with explicit column lists until we can make it work right.

I think it would still be problematic if the target table has dropped columns.
Fortunately, as I initially thought the problem is only due to a thinko in the
original commit which used a wrong variable for the raw_fields offset.  Once
fixed (attached v1) I didn't see any other problem in the rest of the logic and
all the added regression tests work as expected.

--7dpw752g5cnup6cx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="v1-0001-Fix-processing-of-header-match-option-for-COPY.patch"