Обсуждение: UTF8 conversion differences from v8.1.3 to v8.1.4

Hi,

Can anyone help me understand why converting the NULL code point (0000)
from UTF8 to ISO8859_1 is no longer legal in v8.1.4?  The conversion
proc
(backend/utils/mb/conversion_procs/utf8_and_iso8859_1/utf8_and_iso8859_1.c)
changed considerably between 8.1.3 and 8.1.4.  The utf8_to_iso8859_1
conversion function now goes out of its way to reject this code point
specifically, even though it is valid in both character sets.

We have some JDBC code which passes "\000" as a substitution parameter
for a PreparedStatement.  It worked in the last version, but it now
fails at the server with a ERRCODE_CHARACTER_NOT_IN_REPERTOIRE error.

Thanks in advance for any assistance.

Regards,
Eric Faulhaber

Eric Faulhaber <ecf@goldencode.com> writes:
> Can anyone help me understand why converting the NULL code point (0000)
> from UTF8 to ISO8859_1 is no longer legal in v8.1.4?

Embedded nulls in text strings have never behaved sanely in PG ... or
hadn't you noticed?  You'd have been better off passing an empty string,
because that was effectively what you were getting.

            regards, tom lane

Tom Lane wrote:
> Eric Faulhaber <ecf@goldencode.com> writes:
>> Can anyone help me understand why converting the NULL code point (0000)
>> from UTF8 to ISO8859_1 is no longer legal in v8.1.4?
>
> Embedded nulls in text strings have never behaved sanely in PG ... or
> hadn't you noticed?  You'd have been better off passing an empty string,
> because that was effectively what you were getting.
>
>             regards, tom lane

OK, but this particular issue is something quite new to the latest
version.  From utf8_and_iso8859_1.c (utf8_to_iso8859_1 function):

    ...

    unsigned char *src = (unsigned char *) PG_GETARG_CSTRING(2);
    unsigned char *dest = (unsigned char *) PG_GETARG_CSTRING(3);
    int      len = PG_GETARG_INT32(4);
    unsigned short c,
                   c1;

    ...

    while (len > 0)
    {
    c = *src;
        if (c == 0)
            report_invalid_encoding(PG_UTF8, (const char *) src, len);

    ...

This is new code in 8.1.4;  the 8.1.3 version did not screen explicitly
for null bytes.

This has some troubling implications for our runtime layer.  Since the
null byte represents a valid code point in both the database's encoding
(in this case LATIN1) and in the client's encoding (UNICODE/UTF8), I
cannot simply strip out null bytes before handing strings to PG;  they
may well have special meaning to application developers.

Converting varchar/text columns into bytea because they may require
embedded nulls is not an option either, since these are valid strings
and need to be treated as such in our runtime.

Am I stuck at 8.1.3 for the time being?  I'd be happy to create a patch
to resolve this for a future version, but if it is not considered a
defect, it doesn't make sense for me to do that.

Thanks,
Eric

Eric Faulhaber <ecf@goldencode.com> writes:
> OK, but this particular issue is something quite new to the latest
> version.

Again, PG has never stored such data correctly.

> Am I stuck at 8.1.3 for the time being?  I'd be happy to create a patch
> to resolve this for a future version, but if it is not considered a
> defect, it doesn't make sense for me to do that.

It's not a defect ... or at least, it doesn't make sense to change it
unless you are willing to go through the entire system to make it able
to store null bytes in text.  We've looked at that in the past and
always concluded that it was completely impractical :-(

            regards, tom lane

Tom Lane wrote:
> Eric Faulhaber <ecf@goldencode.com> writes:
>> OK, but this particular issue is something quite new to the latest
>> version.
>
> Again, PG has never stored such data correctly.
>

Perhaps not, but it silently tolerated such data until this release, at
least at the encoding conversion level.  I don't know what happened to
the embedded nulls beyond that point (ignorance is bliss), but our JDBC
queries were working as expected...

BTW, any idea why we don't see this problem when issuing the same query
from psql?  I've set psql's encoding to UTF8 to try to trigger the
conversion when running against the LATIN1-encoded database.  It happily
returns the result we previously achieved with JDBC on 8.1.3.  Is psql
filtering out embedded nulls before the backend sees them?

>> Am I stuck at 8.1.3 for the time being?  I'd be happy to create a patch
>> to resolve this for a future version, but if it is not considered a
>> defect, it doesn't make sense for me to do that.
>
> It's not a defect ... or at least, it doesn't make sense to change it
> unless you are willing to go through the entire system to make it able
> to store null bytes in text.  We've looked at that in the past and
> always concluded that it was completely impractical :-(
>
>             regards, tom lane

:-( indeed, though I appreciate the dialog, Tom.  Sadly, this would not
be the first completely impractical task on my todo list ;-)

Thanks,
Eric

On Tue, Jul 18, 2006 at 08:03:51PM -0400, Eric Faulhaber wrote:
> > It's not a defect ... or at least, it doesn't make sense to change it
> > unless you are willing to go through the entire system to make it able
> > to store null bytes in text.  We've looked at that in the past and
> > always concluded that it was completely impractical :-(
>
> :-( indeed, though I appreciate the dialog, Tom.  Sadly, this would not
> be the first completely impractical task on my todo list ;-)

It's a pity postgres doesn't handle nulls in strings. Perl for example
handles it just fine, but I imagine they've reimplemented many of the
string functions themselves anyway.

Looking at the code it doesn't appear that there are too many places
that are problematic. The real killer though is the regex matching and
sorting, they like null terminated strings. The latter could be dealt
with using ICU which doesn't treat the zero code point specially. But
after that, there's probably others too. I suppose a concerted effort
would have to be made to try and make it work properly.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> Looking at the code it doesn't appear that there are too many places
> that are problematic.

Really?

The killer problem is that all datatype I/O goes through C strings.
Fixing this therefore would require breaking every user-defined
datatype on the planet.

            regards, tom lane

On Wed, Jul 19, 2006 at 10:03:34AM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Looking at the code it doesn't appear that there are too many places
> > that are problematic.
>
> Really?
>
> The killer problem is that all datatype I/O goes through C strings.
> Fixing this therefore would require breaking every user-defined
> datatype on the planet.

Well, other than that ofcourse :)

The fact is that if you're using binary format paramaters and output
you can put embedded nulls into strings and get them back out. Given
their use of JDBC that's probably how they're doing it. By changing a
few strcmps to memcmps you can get sane behaviour for sorting a several
other operations. So currently it's not totally sane, but for just
loading and storing string data, it would work just fine.

So from their point of view it would have worked fine and now it
doesn't...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> The fact is that if you're using binary format paramaters and output
> you can put embedded nulls into strings and get them back out.

Not any more ;-)

> By changing a
> few strcmps to memcmps you can get sane behaviour for sorting a several
> other operations.

Given the lack of "memcoll", that proposal isn't going to fly ...
at least not until we replace all the locale support code with something
else (that hopefully will be null-clean).

            regards, tom lane

Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> The fact is that if you're using binary format paramaters and output
>> you can put embedded nulls into strings and get them back out.
>
> Not any more ;-)
>

OK, but now that this "feature" has been removed in 8.1.4, how is this
supposed to be handled, given that we don't control what string data
we're handed?  How does psql deal with it?

>> By changing a
>> few strcmps to memcmps you can get sane behaviour for sorting a several
>> other operations.
>
> Given the lack of "memcoll", that proposal isn't going to fly ...
> at least not until we replace all the locale support code with something
> else (that hopefully will be null-clean).
>
>             regards, tom lane

Thanks,
Eric

On Wed, Jul 19, 2006 at 05:13:04PM -0400, Tom Lane wrote:
> Given the lack of "memcoll", that proposal isn't going to fly ...
> at least not until we replace all the locale support code with something
> else (that hopefully will be null-clean).

Yeah, ICU would give us that, but it won't magically fix any of the
other problems.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Wed, Jul 19, 2006 at 05:24:53PM -0400, Eric Faulhaber wrote:
> OK, but now that this "feature" has been removed in 8.1.4, how is this
> supposed to be handled, given that we don't control what string data
> we're handed?  How does psql deal with it?

Well, bytea handles null like it always has. There must be a way to you
to store strings into bytea columns... But I only have a vague
understanding of why bytea won't work for you...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Wed, Jul 19, 2006 at 05:24:53PM -0400, Eric Faulhaber wrote:
>> OK, but now that this "feature" has been removed in 8.1.4, how is this
>> supposed to be handled, given that we don't control what string data
>> we're handed?  How does psql deal with it?
>
> Well, bytea handles null like it always has. There must be a way to you
> to store strings into bytea columns... But I only have a vague
> understanding of why bytea won't work for you...

Collation, for one.  Our runtime is extremely sensitive to the order in
which records are read, to the point where I've created a custom locale
just for the PostgreSQL cluster.

Then there's case sensitivity, being able to use string functions in
SQL, etc., etc.  Bottom line, these are valid strings, so we need to
treat them as such.

Thanks,
Eric

On Wed, Jul 19, 2006 at 06:06:08PM -0400, Eric Faulhaber wrote:
> Martijn van Oosterhout wrote:
> > On Wed, Jul 19, 2006 at 05:24:53PM -0400, Eric Faulhaber wrote:
> >> OK, but now that this "feature" has been removed in 8.1.4, how is this
> >> supposed to be handled, given that we don't control what string data
> >> we're handed?  How does psql deal with it?
> >
> > Well, bytea handles null like it always has. There must be a way to you
> > to store strings into bytea columns... But I only have a vague
> > understanding of why bytea won't work for you...
>
> Collation, for one.  Our runtime is extremely sensitive to the order in
> which records are read, to the point where I've created a custom locale
> just for the PostgreSQL cluster.
>
> Then there's case sensitivity, being able to use string functions in
> SQL, etc., etc.  Bottom line, these are valid strings, so we need to
> treat them as such.

Well, there's a really nasty workaround: create a cast from bytea to
text which doesn't change the value. This will get your data into the
database without any encoding checks at all. Ofcourse, you're then
responsible for any problems caused down the line...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Wed, Jul 19, 2006 at 06:06:08PM -0400, Eric Faulhaber wrote:
>> Martijn van Oosterhout wrote:
>>> On Wed, Jul 19, 2006 at 05:24:53PM -0400, Eric Faulhaber wrote:
>>>> OK, but now that this "feature" has been removed in 8.1.4, how is this
>>>> supposed to be handled, given that we don't control what string data
>>>> we're handed?  How does psql deal with it?
>>> Well, bytea handles null like it always has. There must be a way to you
>>> to store strings into bytea columns... But I only have a vague
>>> understanding of why bytea won't work for you...
>> Collation, for one.  Our runtime is extremely sensitive to the order in
>> which records are read, to the point where I've created a custom locale
>> just for the PostgreSQL cluster.
>>
>> Then there's case sensitivity, being able to use string functions in
>> SQL, etc., etc.  Bottom line, these are valid strings, so we need to
>> treat them as such.
>
> Well, there's a really nasty workaround: create a cast from bytea to
> text which doesn't change the value. This will get your data into the
> database without any encoding checks at all. Ofcourse, you're then
> responsible for any problems caused down the line...
>
> Have a nice day,

Not sure I understand...  at what point is the cast performed and what
type is actually stored in the database:  text or bytea?

Thanks,
Eric

On Thu, Jul 20, 2006 at 12:07:54PM -0400, Eric Faulhaber wrote:
> > Well, there's a really nasty workaround: create a cast from bytea to
> > text which doesn't change the value. This will get your data into the
> > database without any encoding checks at all. Ofcourse, you're then
> > responsible for any problems caused down the line...
> >
> > Have a nice day,
>
> Not sure I understand...  at what point is the cast performed and what
> type is actually stored in the database:  text or bytea?

Well, the point is that there is actually no difference in how bytea
and text are stored. What you do is use a type-cast to relabel the data
to be text. So the fields in the database would be marked type text but
the data would be transferred there as bytea.

This doesn't fix the fact that the text functions can't handle embedded
nulls, but it's a workaround. Note that you bypass all encoding checks
this way so you're kind on of your own if it acts odd...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Thu, Jul 20, 2006 at 12:07:54PM -0400, Eric Faulhaber wrote:
>>> Well, there's a really nasty workaround: create a cast from bytea to
>>> text which doesn't change the value. This will get your data into the
>>> database without any encoding checks at all. Ofcourse, you're then
>>> responsible for any problems caused down the line...
>>>
>>> Have a nice day,
>> Not sure I understand...  at what point is the cast performed and what
>> type is actually stored in the database:  text or bytea?
>
> Well, the point is that there is actually no difference in how bytea
> and text are stored. What you do is use a type-cast to relabel the data
> to be text. So the fields in the database would be marked type text but
> the data would be transferred there as bytea.
>
> This doesn't fix the fact that the text functions can't handle embedded
> nulls, but it's a workaround. Note that you bypass all encoding checks
> this way so you're kind on of your own if it acts odd...
>
> Have a nice day,

I see, thanks for the suggestion.

I'm also considering requiring UTF8 encoding at the server to make the
database consistent with the JDBC client.  I suppose this also would
bypass the encoding check...

But I'm concerned that both approaches just delay the inevitable.  If
the PG roadmap is to close all of these embedded null "loopholes" rather
than supporting these strings as first class citizens, I expect that a
future version will cut me off at the knees again when I least expect it :-(

Thanks,
Eric