|
|
|
|
|
|
| Author |
Message |
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
 Posted: Tue Jul 18, 2006 7:35 am Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Eric Faulhaber <ecf@goldencode.com> writes:
| Quote: | 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 |
|
| Back to top |
|
 |
Eric Faulhaber
*nix forums beginner
Joined: 18 Jul 2006
Posts: 8
|
| Posted: Tue Jul 18, 2006 6:56 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Tom Lane wrote:
| Quote: | 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 |
|
| Back to top |
|
|
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Tue Jul 18, 2006 10:42 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Eric Faulhaber <ecf@goldencode.com> writes:
| Quote: | OK, but this particular issue is something quite new to the latest
version.
|
Again, PG has never stored such data correctly.
| Quote: | 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 |
|
| Back to top |
|
|
Eric Faulhaber
*nix forums beginner
Joined: 18 Jul 2006
Posts: 8
|
| Posted: Wed Jul 19, 2006 12:03 am Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Tom Lane wrote:
| Quote: | 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?
| Quote: | 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 |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Wed Jul 19, 2006 10:35 am Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
On Tue, Jul 18, 2006 at 08:03:51PM -0400, Eric Faulhaber wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Wed Jul 19, 2006 2:03 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Martijn van Oosterhout <kleptog@svana.org> writes:
| Quote: | 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 |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Wed Jul 19, 2006 7:52 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
On Wed, Jul 19, 2006 at 10:03:34AM -0400, Tom Lane wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Wed Jul 19, 2006 9:13 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Martijn van Oosterhout <kleptog@svana.org> writes:
| Quote: | 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 ;-)
| Quote: | 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 |
|
| Back to top |
|
|
Eric Faulhaber
*nix forums beginner
Joined: 18 Jul 2006
Posts: 8
|
| Posted: Wed Jul 19, 2006 9:24 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Tom Lane wrote:
| Quote: | 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?
| Quote: | 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 |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Wed Jul 19, 2006 9:43 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
On Wed, Jul 19, 2006 at 05:13:04PM -0400, Tom Lane wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Wed Jul 19, 2006 9:45 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
On Wed, Jul 19, 2006 at 05:24:53PM -0400, Eric Faulhaber wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Eric Faulhaber
*nix forums beginner
Joined: 18 Jul 2006
Posts: 8
|
| Posted: Wed Jul 19, 2006 10:06 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Martijn van Oosterhout wrote:
| Quote: | 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Thu Jul 20, 2006 11:51 am Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
On Wed, Jul 19, 2006 at 06:06:08PM -0400, Eric Faulhaber wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Eric Faulhaber
*nix forums beginner
Joined: 18 Jul 2006
Posts: 8
|
| Posted: Thu Jul 20, 2006 4:07 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
Martijn van Oosterhout wrote:
| Quote: | 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Thu Jul 20, 2006 7:47 pm Post subject:
Re: UTF8 conversion differences from v8.1.3 to v8.1.4
|
|
|
On Thu, Jul 20, 2006 at 12:07:54PM -0400, Eric Faulhaber wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Fri Nov 21, 2008 4:13 pm | All times are GMT
|
|
Loans | Online Advertising | MPAA | Western Union Money Transfer | Books
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
