Re: DBD::Pg && handling of UTF-8 char columns

--FL5UXtIhxfXey3p5
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

El d=C3=ADa viernes, octubre 11, 2019 a las 04:03:31p. m. -0600, Jon Jensen=
 escribi=C3=B3:

> > is caused by missing UTF-8 on STDOUT. But, the line with
> >
> >>> HexStr: 50e464616 ...
> >
> > is not and shows that the \xe4 is there. Why?
>=20
> Perl's internal storage of string data is a little odd. \xe4 is the=20
> correct Unicode code point as per:
>=20
> https://en.wikipedia.org/wiki/Latin-1_Supplement_%28Unicode_block%29
>=20
> It is not UTF-8 encoded, true, but there's no reason Perl internally need=
s=20
> to use UTF-8 specifically, and I believe for Latin-1 it does not by=20
> default. It's a question of in-memory storage and processing (some kind o=
f=20
> Unicode) vs. input/output (where you want UTF-8).
>=20
> If your script is configured to send UTF-8 to STDOUT, then I would expect=
=20
> that \xe4 will show up as the UTF-8 \xc3\xa4 instead.

The byte \xe4 is not UTF-8. The Unicode Codepoint for the letter

Unicode Character 'LATIN SMALL LETTER A WITH DIAERESIS' (U+00E4)

can be seen here:

http://www.fileformat.info/info/unicode/char/00E4/index.htm

and must be \xc3\xa4. One can see this also on any UNIX shell:

$ echo =C3=A4 | od -tx1
0000000    c3  a4  0a

and if you convert it to ISO-8859-1 then you will get \xe4:

$ echo =C3=A4 | iconv -f utf-8 -t iso-8859-1 | od -tx1
0000000    e4  0a
0000002

I learned meanwhile how to dump strings in Perl with Devel::Peek and this
shows for the column coming out of PostgreSQL:

=2E..
Dump $string;

gives for this case:

SV =3D PVIV(0x386c3d0) at 0x2429050\n  REFCNT =3D 1
  FLAGS =3D (POK,IsCOW,pPOK,UTF8)
  IV =3D 2
  PV =3D 0x39f6aa0 "P\303\244dagogische Hochschule Weingarten"\0 [UTF8 "P\x=
{e4}dagogische Hochschule Weingarten"]
  CUR =3D 35
  LEN =3D 37
  COW_REFCNT =3D 1

i.e. from the database PG server is coming the code point correctly as
(octal) \303\244 which is the same as \xc3\xa4. And Perl mangles this to=20

[UTF8 "P\x{e4}dagogische Hochschule Weingarten"]

which is IMHO not correct and causing all this confusion.

We have to deal with this in our perl code. It's not a PostrgreSQL
problem.

Thanks in any case for your attention to this case.

	matthias

--=20
Matthias Apitz, =E2=9C=89 [email protected], http://www.unixarea.de/ +49-176=
-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

3. Oktober! Wir gratulieren! Der Berliner Fernsehturm wird 50=20
aus: https://www.jungewelt.de/2019/10-02/index.php

--FL5UXtIhxfXey3p5
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCAAdFiEEXmn7rBYYViyzy/vBR8z35Hb+nREFAl2lXSgACgkQR8z35Hb+
nRGq3A//ZI5RDS+IVnaUwoZNPWVk4rjleKhZLD1oXNAuZneez0nzVhqS2meMNtVz
WcHltUBHAv/En/a1T7EibOzx19hGZS1aeV9uHHMkCUlCTxxC291TSav39W8SG/dW
ITmUEgtTY1XAtfB0HDzzI7um3VGU6cw3U/FQH+/K/4/LlabXhCa1AsQ6EGsc3pak
l/aARWo+iNek0WHoENrrpBW4PdrfZH4OAKiLhT+BIOsX2XtvDvdBY81NTV6pMCAA
MHiN/+gtGWr0u+m6EauP8cNN9MdXWEpronOqN7OUpABHImVLjgdSGSbvoyD0YH/Z
a3iOAukRS7fxU+UmkEtZjhECUIMX55JQncng1BYu5bgnE8crh2TRSSxIOJwtuuKp
+NSp5V8HQGSK/0fYPk+6UYNpXNmdLPyiwkAdS4d7aMZKN8KUe0hLtZ9jNr3OElbf
1X+yo7QdIcmzBKsHySm6XK5mLVUWeLarVTQGqyxXtG+6Qw4pxeEx7TmYo8/TYXbx
HFaToSQDpocmYZpHwCmNSP6EpiMDgqGa4gVVtwALk5w4q9+LmE59UoKmAcffWi5T
w+vZ+ZJx2LoLDKDphbtCOGMCTTrMSKWm7+3xikZLk1Nb5HJKoRftcvnhW50wzLon
XrFkS1BCoQH+Au1EYcjPu/OXH3kjrM0QJI17clCgCFwDu+wmm8Q=
=dwC9
-----END PGP SIGNATURE-----

--FL5UXtIhxfXey3p5--