Re: Add encoding support to COPY

Today on IRC, someone was wondering what the preferred method of
exporting data in a specific encoding via COPY was. They reply was
wrapping the COPY command in "set client_encoding='foo';", which made
me wonder how hard it would be to add an additional WITH parameter to
the actual COPY statement to specify the encoding, a la:
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ ENCODING [ AS ] 'charset' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Any objections? It seems like a cleaner solution client side than
issuing multiple calls to set the client_encoding. If there are no
objections, I can attempt to prepare a patch for the next commitfest.

David Blewett

David Blewett <david(at)dawninglight(dot)net> writes:
> Today on IRC, someone was wondering what the preferred method of
> exporting data in a specific encoding via COPY was. They reply was
> wrapping the COPY command in "set client_encoding='foo';", which made
> me wonder how hard it would be to add an additional WITH parameter to
> the actual COPY statement to specify the encoding, a la:

What is the point? You'd generally have client_encoding set correctly
for your usage anyway, and if you did not, the data could confuse your
client-side code terribly. Offering an option to let the backend send
data in the "wrong" encoding does NOT seem like a good idea to me.

regards, tom lane

On Wed, Jul 15, 2009 at 12:04 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> What is the point?  You'd generally have client_encoding set correctly
> for your usage anyway, and if you did not, the data could confuse your
> client-side code terribly.  Offering an option to let the backend send
> data in the "wrong" encoding does NOT seem like a good idea to me.

The use case was that the client connection was using one encoding,
but needed to output the file in a different encoding. So they would
have to do the "set client_encoding" dance each time they wanted to
export the file. I don't see how it's "wrong", especially considering
there is already a method to do this, albeit cumbersome. I consider it
simply syntactic sugar over existing functionality.

David Blewett

David Blewett <david(at)dawninglight(dot)net> writes:
> On Wed, Jul 15, 2009 at 12:04 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What is the point? You'd generally have client_encoding set correctly
>> for your usage anyway, and if you did not, the data could confuse your
>> client-side code terribly. Offering an option to let the backend send
>> data in the "wrong" encoding does NOT seem like a good idea to me.

> The use case was that the client connection was using one encoding,
> but needed to output the file in a different encoding. So they would
> have to do the "set client_encoding" dance each time they wanted to
> export the file.

Well, it might make sense to allow an ENCODING option attached to a COPY
with a file source/destination. I remain of the opinion that overriding
client_encoding on a transfer to/from the client is a bad idea.

regards, tom lane

David Blewett wrote:
> Today on IRC, someone was wondering what the preferred method of
> exporting data in a specific encoding via COPY was. They reply was
> wrapping the COPY command in "set client_encoding='foo';", which made
> me wonder how hard it would be to add an additional WITH parameter to
> the actual COPY statement to specify the encoding, a la:
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ ENCODING [ AS ] 'charset' ]
> [ NULL [ AS ] 'null string' ]
> [ CSV [ HEADER ]
> [ QUOTE [ AS ] 'quote' ]
> [ ESCAPE [ AS ] 'escape' ]
> [ FORCE QUOTE column [, ...] ]
>
> Any objections? It seems like a cleaner solution client side than
> issuing multiple calls to set the client_encoding. If there are no
> objections, I can attempt to prepare a patch for the next commitfest.
>
> David Blewett
>
I think that I was the one who wondered about that. Our use case is
related to moving data between different servers which have different
encodings. Ofcourse the encoding should be an option only when COPY
involves files.

--
Nagy Karoly Gabriel
Expert Software Group SRL

(o__ 417495 Sanmartin nr. 205
//\' Bihor, Romania
V_/_ Tel./Fax: +4 0259 317 142, +4 0259 317 143

Apologies to Tom for the duplicate...

On Wed, Jul 15, 2009 at 12:17 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Well, it might make sense to allow an ENCODING option attached to a COPY
> with a file source/destination.  I remain of the opinion that overriding
> client_encoding on a transfer to/from the client is a bad idea.

I really don't see how it is any different from manually flipping the
client_encoding before/after the transfer. We could of course put a
warning sign in the docs, but it seems to me it's more error prone for
clients to set the client_encoding manually rather than include an
option for a single command. What happens if an exception is thrown
during the COPY process and the client doesn't handle things
correctly? The rest of their session could be in an unexpected
encoding, whereas with this method we know to return to the original
client_encoding before doing anything else. By including the encoding
option, their explicitly saying how they want to handle the data.

I could see a use case for remote client code to do a COPY to STDOUT,
that is actually being redirected to a file. If the consensus is for
local file-based operations only, however, I can structure the patch
that way.

David

--On 15. Juli 2009 19:59:56 +0300 Nagy Karoly Gabriel
<nagy(dot)karoly(at)expert-erp(dot)net> wrote:

> I think that I was the one who wondered about that. Our use case is
> related to moving data between different servers which have different
> encodings. Ofcourse the encoding should be an option only when COPY
> involves files.

I find this rather confusing: can't you just tell via client_encoding the
correct encoding your file contains during restore?

--
Thanks

Bernd

David Blewett wrote:

> On Wed, Jul 15, 2009 at 12:17 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Well, it might make sense to allow an ENCODING option attached to a COPY
> > with a file source/destination.  I remain of the opinion that overriding
> > client_encoding on a transfer to/from the client is a bad idea.

> I could see a use case for remote client code to do a COPY to STDOUT,
> that is actually being redirected to a file. If the consensus is for
> local file-based operations only, however, I can structure the patch
> that way.

Yeah, the problem is that reading to/from files is only allowed to
superusers ...

(I'm not sure how this affects \copy in psql; probably something you
should investigate)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

David Blewett <david(at)dawninglight(dot)net> writes:
> On Wed, Jul 15, 2009 at 12:17 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Well, it might make sense to allow an ENCODING option attached to a COPY
>> with a file source/destination. I remain of the opinion that overriding
>> client_encoding on a transfer to/from the client is a bad idea.

> I really don't see how it is any different from manually flipping the
> client_encoding before/after the transfer.

The difference is that the client-side code gets told that the encoding
changed if you do the latter.

regards, tom lane

On Wed, Jul 15, 2009 at 4:07 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Blewett <david(at)dawninglight(dot)net> writes:
>> On Wed, Jul 15, 2009 at 12:17 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Well, it might make sense to allow an ENCODING option attached to a COPY
>>> with a file source/destination.  I remain of the opinion that overriding
>>> client_encoding on a transfer to/from the client is a bad idea.
>
>> I really don't see how it is any different from manually flipping the
>> client_encoding before/after the transfer.
>
> The difference is that the client-side code gets told that the encoding
> changed if you do the latter.

Do you mean at the protocol level?

All I was planning on having the patch do is the equivalent of the set
client_encoding dance. Wouldn't that be sufficent to notify the client
of the encoding change?

David Blewett

Nagy Karoly Gabriel wrote:
> David Blewett wrote:
>> Today on IRC, someone was wondering what the preferred method of
>> exporting data in a specific encoding via COPY was. They reply was
>> wrapping the COPY command in "set client_encoding='foo';", which made
>> me wonder how hard it would be to add an additional WITH parameter to
>> the actual COPY statement to specify the encoding, a la:
>>
> I think that I was the one who wondered about that. Our use case is
> related to moving data between different servers which have different
> encodings. Ofcourse the encoding should be an option only when COPY
> involves files.
>

Well, that is the case that there seems to be consensus about, and it's
also the case that can't be done via client encoding. We tend to have a
bias against providing lots of ways to do the same thing, so let's go
with this case (i.e. do it for cases other than STDIN/STDOUT).

cheers

andrew