Re: any known DBD::pg change caused this utf8 issue?

--00000000000024adb30629a60b9d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you, Christoph!
perl -MDBD::Pg -e'print $DBD::Pg::VERSION'
EL8 3.7.4
EL7 2.19.3

On Thu, Dec 19, 2024 at 2:13=E2=80=AFPM Christoph Lamprecht <
[email protected]> wrote:

> What does the following print:
>
> perl -MDBD::Pg -e'print $DBD::Pg::VERSION'
>
>
> Am 19.12.24 um 17:05 schrieb Shaomei Liu:
> > thank you, Warstone!
> > EL8 pg_lib_version 120001 pg_enable_utf8:1
> > EL7 pg_lib_version: 90224  pg_enable_utf8:1
> > not sure if that is the version you asked though.
> > Shirley
> >
> > On Wed, Dec 18, 2024 at 10:32=E2=80=AFPM [email protected]
> > <mailto:[email protected]> <[email protected] <mailto:[email protected]>>
> > wrote:
> >
> >     Can you provide DBD::Pg versions for your EL7 and EL8 distros? And
> >     check this flag:
> >     https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-(integer)
> >     <https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-(integer)>
> >
> >         =D0=A7=D0=B5=D1=82=D0=B2=D0=B5=D1=80=D0=B3, 19 =D0=B4=D0=B5=D0=
=BA=D0=B0=D0=B1=D1=80=D1=8F 2024, 0:34 +03:00 =D0=BE=D1=82 Shaomei Liu
> >         <[email protected] <mailto:[email protected]>>:
> >         send again after subscribing.
> >         On Wed, Dec 18, 2024 at 11:20AM Shaomei Liu
> >         <[email protected]
> >         <//e.mail.ru/compose/?mailto=3Dmailto%[email protected]=
m>>
> >         wrote:
> >
> >             Hello,
> >             I have a project which uses DBI to write to postgres DB.
> >             after upgrading from RHEL7 to RHEL8, the utf-8 character is
> >             not displayed properly in the DB. DB has correct utf-8
> >             encoding set.
> >             for example, left double quotation mark   =E2=80=9C  is dis=
played as
> >             =C3=A2\u0080\u009C.
> >             with support from DBI community, the issue was solved by
> >             calling decode from Encode module before writing to DB.
> >             wondering what is the change from DBD::pg cause this issue.
> >             perl version is 5.26.3 and 5.16.3 on EL8 and EL7
> respectively.
> >             DBI version is 1.641 and 1.627 on EL8 and EL7 respectively.
> >             here is the program and execution results.
> >             Any feedback are greatly appreciated!
> >             thank you
> >             Shirley
> >             xxx.com> cat testutf_decode.pl
> >             #!/usr/bin/perl
> >             use strict;
> >             use warnings;
> >             use DBI;
> >             use Encode 'decode';
> >             print "DBI version: $DBI::VERSION\n";
> >             my $db =3D "debugutf";
> >             my $host =3D "db";
> >             my $user =3D "postgres";
> >             my $pass =3D "";
> >             my $dbh =3D
> >             DBI->connect("DBI:Pg:dbname=3D$db;host=3D$host",$user,$pass=
);
> >             my $sql =3D 'INSERT INTO table1 (title) VALUES (?)';
> >             my $query =3D $dbh->prepare($sql);
> >             my $bytes =3D '=E2=80=9C';
> >             my $chars =3D decode('UTF-8', $bytes);
> >             print "$bytes contains ".length($bytes)." characters\n";
> >             print "after decode $bytes contains ".length($chars)."
> >             characters\n";
> >             #my @values =3D ($bytes); #=3D=3D=3D=3D=3D=3D=3D>without de=
code, Database
> >             shows =E2=80=9C on EL7 but =C3=A2\u0080\u009C on EL8
> >             my @values =3D ($chars);  #=3D=3D=3D=3D=3D=3D>with decode, =
Database shows
> >             =E2=80=9C on both EL8 and EL7, decode fixed the issue
> >             $query->execute(@values);
> >             ############### running on EL8
> >             xxx.com> ./testutf_decode.pl
> >             DBI version: 1.641
> >             =E2=80=9C contains 3 characters
> >             after decode =E2=80=9C contains 1 characters
> >             [yyy.com]$ psql -Upostgres -hdb debugutf
> >             psql (16.6)
> >             debugutf=3D# select * from table1;
> >             title
> >             ---------------
> >               =C3=A2\u0080\u009C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>NOK wit=
hout decode
> >               =E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with=
 decode, so decode fixed the issue
> >             (2 rows)
> >             ############### running on EL7
> >             xxx.com> ./testutf_decode.pl
> >             DBI version: 1.627
> >             =E2=80=9C contains 3 characters
> >             after decode =E2=80=9C contains 1 characters
> >             [yyy.com]$ psql -Upostgres -hdb debugutf
> >             psql (16.6)
> >             debugutf=3D# select * from table1;
> >             title
> >             ---------------
> >               =E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with=
out decode
> >               =E2=80=9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>OK with=
 decode
> >             (2 rows)
> >
>

--00000000000024adb30629a60b9d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thank you, Christoph!<div><div style=3D"color:rgb(204,204,=
204);background-color:rgb(31,31,31);font-family:Consolas,&quot;Courier New&=
quot;,monospace;font-size:14px;line-height:19px;white-space:pre"><div><span=
 style=3D"color:rgb(206,145,120)">perl -MDBD::Pg -e&#39;print $DBD::Pg::VER=
SION&#39;</span></div><div><span style=3D"color:rgb(206,145,120)">EL8 3.7.4=
</span></div><div><span style=3D"color:rgb(206,145,120)">EL7 2.19.3</span><=
/div></div></div></div><br><div class=3D"gmail_quote gmail_quote_container"=
><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Dec 19, 2024 at 2:13=E2=80=
=AFPM Christoph Lamprecht &lt;<a href=3D"mailto:christoph.lamprecht@online.=
de">[email protected]</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">What does the following print:<br>
<br>
perl -MDBD::Pg -e&#39;print $DBD::Pg::VERSION&#39;<br>
<br>
<br>
Am 19.12.24 um 17:05 schrieb Shaomei Liu:<br>
&gt; thank you, Warstone!<br>
&gt; EL8 pg_lib_version 120001 pg_enable_utf8:1<br>
&gt; EL7 pg_lib_version: 90224 =C2=A0pg_enable_utf8:1<br>
&gt; not sure if that is the version you asked though.<br>
&gt; Shirley<br>
&gt;<br>
&gt; On Wed, Dec 18, 2024 at 10:32=E2=80=AFPM <a href=3D"mailto:Warstone@li=
st.ru" target=3D"_blank">[email protected]</a><br>
&gt; &lt;mailto:<a href=3D"mailto:[email protected]" target=3D"_blank">Warst=
[email protected]</a>&gt; &lt;<a href=3D"mailto:[email protected]" target=3D"_blan=
k">[email protected]</a> &lt;mailto:<a href=3D"mailto:[email protected]" targ=
et=3D"_blank">[email protected]</a>&gt;&gt;<br>
&gt; wrote:<br>
&gt;<br>
&gt;=C2=A0 =C2=A0 =C2=A0Can you provide DBD::Pg versions for your EL7 and E=
L8 distros? And<br>
&gt;=C2=A0 =C2=A0 =C2=A0check this flag:<br>
&gt;=C2=A0 =C2=A0 =C2=A0<a href=3D"https://metacpan.org/pod/DBD::Pg#pg_enab=
le_utf8-(integer)" rel=3D"noreferrer" target=3D"_blank">https://metacpan.or=
g/pod/DBD::Pg#pg_enable_utf8-(integer)</a><br>
&gt;=C2=A0 =C2=A0 =C2=A0&lt;<a href=3D"https://metacpan.org/pod/DBD::Pg#pg_=
enable_utf8-(integer)" rel=3D"noreferrer" target=3D"_blank">https://metacpa=
n.org/pod/DBD::Pg#pg_enable_utf8-(integer)</a>&gt;<br>
&gt;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=D0=A7=D0=B5=D1=82=D0=B2=D0=B5=D1=80=
=D0=B3, 19 =D0=B4=D0=B5=D0=BA=D0=B0=D0=B1=D1=80=D1=8F 2024, 0:34 +03:00 =D0=
=BE=D1=82 Shaomei Liu<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&lt;<a href=3D"mailto:sliu.newjersey@=
gmail.com" target=3D"_blank">[email protected]</a> &lt;mailto:<a hre=
f=3D"mailto:[email protected]" target=3D"_blank">sliu.newjersey@gmai=
l.com</a>&gt;&gt;:<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0send again after subscribing.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0On Wed, Dec 18, 2024 at 11:20AM Shaom=
ei Liu<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&lt;<a href=3D"mailto:sliu.newjersey@=
gmail.com" target=3D"_blank">[email protected]</a><br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&lt;//<a href=3D"http://e.mail.ru/com=
pose/?mailto=3Dmailto%[email protected]" rel=3D"noreferrer" target=
=3D"_blank">e.mail.ru/compose/?mailto=3Dmailto%[email protected]</=
a>&gt;&gt;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0wrote:<br>
&gt;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Hello,<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I have a project which =
uses DBI to write to postgres DB.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0after upgrading from RH=
EL7 to RHEL8, the utf-8 character is<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0not displayed properly =
in the DB. DB has correct utf-8<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0encoding set.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0for example, left doubl=
e quotation mark=C2=A0 =C2=A0=E2=80=9C=C2=A0 is displayed as<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C3=A2\u0080\u009C.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0with support from DBI c=
ommunity, the issue was solved by<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0calling decode from Enc=
ode module before writing to DB.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0wondering what is the c=
hange from DBD::pg cause this issue.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0perl version is 5.26.3 =
and 5.16.3 on EL8 and EL7 respectively.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DBI version is 1.641 an=
d 1.627 on EL8 and EL7 respectively.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0here is the program and=
 execution results.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Any feedback are greatl=
y appreciated!<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0thank you<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Shirley<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<a href=3D"http://xxx.c=
om" rel=3D"noreferrer" target=3D"_blank">xxx.com</a>&gt; cat <a href=3D"htt=
p://testutf_decode.pl" rel=3D"noreferrer" target=3D"_blank">testutf_decode.=
pl</a><br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0#!/usr/bin/perl<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0use strict;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0use warnings;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0use DBI;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0use Encode &#39;decode&=
#39;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0print &quot;DBI version=
: $DBI::VERSION\n&quot;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $db =3D &quot;debugu=
tf&quot;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $host =3D &quot;db&q=
uot;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $user =3D &quot;post=
gres&quot;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $pass =3D &quot;&quo=
t;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $dbh =3D<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DBI-&gt;connect(&quot;D=
BI:Pg:dbname=3D$db;host=3D$host&quot;,$user,$pass);<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $sql =3D &#39;INSERT=
 INTO table1 (title) VALUES (?)&#39;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $query =3D $dbh-&gt;=
prepare($sql);<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $bytes =3D &#39;=E2=
=80=9C&#39;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my $chars =3D decode(&#=
39;UTF-8&#39;, $bytes);<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0print &quot;$bytes cont=
ains &quot;.length($bytes).&quot; characters\n&quot;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0print &quot;after decod=
e $bytes contains &quot;.length($chars).&quot;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0characters\n&quot;;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0#my @values =3D ($bytes=
); #=3D=3D=3D=3D=3D=3D=3D&gt;without decode, Database<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0shows =E2=80=9C on EL7 =
but =C3=A2\u0080\u009C on EL8<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0my @values =3D ($chars)=
; =C2=A0#=3D=3D=3D=3D=3D=3D&gt;with decode, Database shows<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9C on both EL8 a=
nd EL7, decode fixed the issue<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0$query-&gt;execute(@val=
ues);<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0############### running=
 on EL8<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<a href=3D"http://xxx.c=
om" rel=3D"noreferrer" target=3D"_blank">xxx.com</a>&gt; ./<a href=3D"http:=
//testutf_decode.pl" rel=3D"noreferrer" target=3D"_blank">testutf_decode.pl=
</a><br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DBI version: 1.641<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9C contains 3 ch=
aracters<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0after decode =E2=80=9C =
contains 1 characters<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0[<a href=3D"http://yyy.=
com" rel=3D"noreferrer" target=3D"_blank">yyy.com</a>]$ psql -Upostgres -hd=
b debugutf<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0psql (16.6)<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0debugutf=3D# select * f=
rom table1;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0title<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0---------------<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C3=A2\u0080\u00=
9C =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D&gt;NOK without decode<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9C =3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D&gt;OK with decode, so decode fixed the is=
sue<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(2 rows)<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0############### running=
 on EL7<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<a href=3D"http://xxx.c=
om" rel=3D"noreferrer" target=3D"_blank">xxx.com</a>&gt; ./<a href=3D"http:=
//testutf_decode.pl" rel=3D"noreferrer" target=3D"_blank">testutf_decode.pl=
</a><br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DBI version: 1.627<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9C contains 3 ch=
aracters<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0after decode =E2=80=9C =
contains 1 characters<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0[<a href=3D"http://yyy.=
com" rel=3D"noreferrer" target=3D"_blank">yyy.com</a>]$ psql -Upostgres -hd=
b debugutf<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0psql (16.6)<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0debugutf=3D# select * f=
rom table1;<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0title<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0---------------<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9C =3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D&gt;OK without decode<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9C =3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D&gt;OK with decode<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(2 rows)<br>
&gt;<br>
</blockquote></div>

--00000000000024adb30629a60b9d--