SEO

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEvgsMIB7bNG8LQkwRAgcPAJ4w0PZkW3VwZR62EEmjQR MnKoqjdwCgjzF3
w9F5h0yx6DZw/YLh3dXyHTg=
=PNb1
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEvo2WIB7bNG8LQkwRAr7/AJ48YOZdDJbfcqcpXFJQ+9W+4FKoyQCeOatn
c2cBvjdYkKTEezxEWLbXOtk=
=DpM2
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match