Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Thu, 29 Jun 2023 21:13:26 -0500
Cc: <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG
To: "Heikki Linnakangas" <hlinnaka@iki.fi>, "Tom Lane" <tgl@sss.pgh.pa.us>,
 <gdo@leader.it>, "Joe Conway" <mail@joeconway.com>
From: "Tristan Partin" <tristan@neon.tech>
Message-Id: <CTPMCSM9UB07.3CUPU2JW4BBZ6@gonk>
References: <17946-3e84cb577e9551c3@postgresql.org>
 <1564676.1685043220@sss.pgh.pa.us>
 <555bb26e-c27c-ab65-028f-3fd5b82df9de@iki.fi>
In-Reply-To: <555bb26e-c27c-ab65-028f-3fd5b82df9de@iki.fi>
Archived-At: 
 <https://www.postgresql.org/message-id/CTPMCSM9UB07.3CUPU2JW4BBZ6%40gonk>
Precedence: bulk

On Mon Jun 5, 2023 at 11:00 AM CDT, Heikki Linnakangas wrote:
> On 25/05/2023 15:33, Tom Lane wrote:
> > PG Bug reporting form <noreply@postgresql.org> writes:
> >> After upgrading an application using Postgresql from version 10 to 12,
> >> fields of type "money" are no longer generated with the =E2=82=AC symb=
ol but with
> >> $.
> >=20
> > Hmm, seems to work for me:
>
> I can reproduce this:
>
> psql (16beta1)
> Type "help" for help.
>
> postgres=3D# DO LANGUAGE 'plperl' $$ elog(NOTICE, 'foo') $$;
> NOTICE:  foo
> DO
> postgres=3D# SET lc_monetary TO 'en_GB.UTF-8';
> SET
> postgres=3D# SELECT 12.34::money AS price;
>   price
> --------
>   $12.34
> (1 row)
>
>
> If I don't call the plperl function, it works as expected:
>
> sql (16beta1)
> Type "help" for help.
>
> postgres=3D# SET lc_monetary TO 'en_GB.UTF-8';
> SET
> postgres=3D# SELECT 12.34::money AS price;
>   price
> --------
>   =C2=A312.34
> (1 row)
>
> I should note that 'en_GB.UTF-8' is the default locale in my system, and=
=20
> that's what I used in initdb. I don't know if it makes a difference.
>
> > IIRC, we've seen trouble in the past with some versions of libperl
> > clobbering the host application's locale settings.  Maybe you
> > have a plperl.on_init or plperl.on_plperl_init action that is
> > causing that to happen?  In any case, I'd call it a Perl bug not
> > a Postgres bug
> I did some debugging, initializing the perl interpreter calls uselocale()=
:
>
> #0  __GI___uselocale (newloc=3D0x7f9f47ff0940 <_nl_C_locobj>) at=20
> ./locale/uselocale.c:31
> #1  0x00007f9f373bd069 in ?? () from=20
> /usr/lib/x86_64-linux-gnu/libperl.so.5.36
> #2  0x00007f9f373bce74 in ?? () from=20
> /usr/lib/x86_64-linux-gnu/libperl.so.5.36
> #3  0x00007f9f373bfc15 in Perl_init_i18nl10n () from=20
> /usr/lib/x86_64-linux-gnu/libperl.so.5.36
> #4  0x00007f9f48b74cfb in plperl_init_interp () at plperl.c:809
> #5  0x00007f9f48b78adc in _PG_init () at plperl.c:483
> #6  0x000055c98b8e9b63 in internal_load_library (libname=3D0x55c98bebaf90=
=20
> "/home/heikki/pgsql.fsmfork/lib/plperl.so") at dfmgr.c:289
> #7  0x000055c98b8ea1c2 in load_external_function=20
> (filename=3Dfilename@entry=3D0x55c98bebb1c0 "$libdir/plperl",=20
> funcname=3Dfuncname@entry=3D0x55c98beba378 "plperl_inline_handler",
>      signalNotFound=3DsignalNotFound@entry=3Dtrue,=20
> filehandle=3Dfilehandle@entry=3D0x7ffd20942b48) at dfmgr.c:116
> #8  0x000055c98b8ea864 in fmgr_info_C_lang (functionId=3D129304,=20
> procedureTuple=3D0x7f9f4778ccb8, finfo=3D0x7ffd20942bf0) at fmgr.c:386
> #9  fmgr_info_cxt_security (functionId=3D129304, finfo=3D0x7ffd20942bf0,=
=20
> mcxt=3D<optimized out>, ignore_security=3D<optimized out>) at fmgr.c:246
> #10 0x000055c98b8eba72 in fmgr_info (finfo=3D0x7ffd20942bf0,=20
> functionId=3D<optimized out>) at fmgr.c:129
> #11 OidFunctionCall1Coll (functionId=3D<optimized out>,=20
> collation=3Dcollation@entry=3D0, arg1=3D94324124262840) at fmgr.c:1386
> #12 0x000055c98b5e1385 in ExecuteDoStmt=20
> (pstate=3Dpstate@entry=3D0x55c98beba0b0, stmt=3Dstmt@entry=3D0x55c98be908=
58,=20
> atomic=3Datomic@entry=3Dfalse) at functioncmds.c:2144
> #13 0x000055c98b7c24ce in standard_ProcessUtility (pstmt=3D0x55c98be908e0=
,=20
> queryString=3D0x55c98be8fd50 "DO LANGUAGE 'plperl' $$ elog(NOTICE, 'foo')=
=20
> $$;", readOnlyTree=3D<optimized out>,
>      context=3DPROCESS_UTILITY_TOPLEVEL, params=3D0x0, queryEnv=3D0x0,=20
> dest=3D0x55c98be90b80, qc=3D0x7ffd20942f30) at utility.c:714
> #14 0x000055c98b7c0d9f in PortalRunUtility=20
> (portal=3Dportal@entry=3D0x55c98bf0b710, pstmt=3Dpstmt@entry=3D0x55c98be9=
08e0,=20
> isTopLevel=3DisTopLevel@entry=3Dtrue,
>      setHoldSnapshot=3DsetHoldSnapshot@entry=3Dfalse, dest=3D0x55c98be90b=
80,=20
> qc=3D0x7ffd20942f30) at pquery.c:1158
> #15 0x000055c98b7c0ecb in PortalRunMulti=20
> (portal=3Dportal@entry=3D0x55c98bf0b710, isTopLevel=3DisTopLevel@entry=3D=
true,=20
> setHoldSnapshot=3DsetHoldSnapshot@entry=3Dfalse,=20
> dest=3Ddest@entry=3D0x55c98be90b80,
>      altdest=3Daltdest@entry=3D0x55c98be90b80, qc=3Dqc@entry=3D0x7ffd2094=
2f30)=20
> at pquery.c:1322
> #16 0x000055c98b7c139d in PortalRun (portal=3Dportal@entry=3D0x55c98bf0b7=
10,=20
> count=3Dcount@entry=3D9223372036854775807, isTopLevel=3DisTopLevel@entry=
=3Dtrue,=20
> run_once=3Drun_once@entry=3Dtrue,
>      dest=3Ddest@entry=3D0x55c98be90b80,=20
> altdest=3Daltdest@entry=3D0x55c98be90b80, qc=3D0x7ffd20942f30) at pquery.=
c:791
> #17 0x000055c98b7bd85d in exec_simple_query (query_string=3D0x55c98be8fd5=
0=20
> "DO LANGUAGE 'plperl' $$ elog(NOTICE, 'foo') $$;") at postgres.c:1274
> #18 0x000055c98b7bf978 in PostgresMain (dbname=3D<optimized out>,=20
> username=3D<optimized out>) at postgres.c:4632
> #19 0x000055c98b73f743 in BackendRun (port=3D<optimized out>,=20
> port=3D<optimized out>) at postmaster.c:4461
> #20 BackendStartup (port=3D<optimized out>) at postmaster.c:4189
> #21 ServerLoop () at postmaster.c:1779
> #22 0x000055c98b74077a in PostmasterMain (argc=3Dargc@entry=3D3,=20
> argv=3Dargv@entry=3D0x55c98be88fc0) at postmaster.c:1463
> #23 0x000055c98b4a96be in main (argc=3D3, argv=3D0x55c98be88fc0) at main.=
c:198
>
> I think the uselocale() call renders ineffective the setlocale() calls=20
> that we make later. Maybe we should replace our setlocale() calls with=20
> uselocale(), too.

For what it's worth to everyone else in the thread (especially Joe), I
have a patch locally that fixes the mentioned bug using uselocale(). I
am not sure that it is worth committing for v16 given how _large_ (the
patch is actually quite small, +216 -235) of a change it is. I am going
to spend tomorrow combing over it a bit more and evaluating other
setlocale uses in the codebase.

--=20
Tristan Partin
Neon (https://neon.tech)