Re: BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG

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(at)postgresql(dot)org> writes:
> >> After upgrading an application using Postgresql from version 10 to 12,
> >> fields of type "money" are no longer generated with the € symbol but with
> >> $.
> >
> > Hmm, seems to work for me:
>
> I can reproduce this:
>
> psql (16beta1)
> Type "help" for help.
>
> postgres=# DO LANGUAGE 'plperl' $$ elog(NOTICE, 'foo') $$;
> NOTICE: foo
> DO
> postgres=# SET lc_monetary TO 'en_GB.UTF-8';
> SET
> postgres=# 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=# SET lc_monetary TO 'en_GB.UTF-8';
> SET
> postgres=# SELECT 12.34::money AS price;
> price
> --------
> £12.34
> (1 row)
>
> I should note that 'en_GB.UTF-8' is the default locale in my system, and
> 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=0x7f9f47ff0940 <_nl_C_locobj>) at
> ./locale/uselocale.c:31
> #1 0x00007f9f373bd069 in ?? () from
> /usr/lib/x86_64-linux-gnu/libperl.so.5.36
> #2 0x00007f9f373bce74 in ?? () from
> /usr/lib/x86_64-linux-gnu/libperl.so.5.36
> #3 0x00007f9f373bfc15 in Perl_init_i18nl10n () from
> /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=0x55c98bebaf90
> "/home/heikki/pgsql.fsmfork/lib/plperl.so") at dfmgr.c:289
> #7 0x000055c98b8ea1c2 in load_external_function
> (filename=filename(at)entry=0x55c98bebb1c0 "$libdir/plperl",
> funcname=funcname(at)entry=0x55c98beba378 "plperl_inline_handler",
> signalNotFound=signalNotFound(at)entry=true,
> filehandle=filehandle(at)entry=0x7ffd20942b48) at dfmgr.c:116
> #8 0x000055c98b8ea864 in fmgr_info_C_lang (functionId=129304,
> procedureTuple=0x7f9f4778ccb8, finfo=0x7ffd20942bf0) at fmgr.c:386
> #9 fmgr_info_cxt_security (functionId=129304, finfo=0x7ffd20942bf0,
> mcxt=<optimized out>, ignore_security=<optimized out>) at fmgr.c:246
> #10 0x000055c98b8eba72 in fmgr_info (finfo=0x7ffd20942bf0,
> functionId=<optimized out>) at fmgr.c:129
> #11 OidFunctionCall1Coll (functionId=<optimized out>,
> collation=collation(at)entry=0, arg1=94324124262840) at fmgr.c:1386
> #12 0x000055c98b5e1385 in ExecuteDoStmt
> (pstate=pstate(at)entry=0x55c98beba0b0, stmt=stmt(at)entry=0x55c98be90858,
> atomic=atomic(at)entry=false) at functioncmds.c:2144
> #13 0x000055c98b7c24ce in standard_ProcessUtility (pstmt=0x55c98be908e0,
> queryString=0x55c98be8fd50 "DO LANGUAGE 'plperl' $$ elog(NOTICE, 'foo')
> $$;", readOnlyTree=<optimized out>,
> context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
> dest=0x55c98be90b80, qc=0x7ffd20942f30) at utility.c:714
> #14 0x000055c98b7c0d9f in PortalRunUtility
> (portal=portal(at)entry=0x55c98bf0b710, pstmt=pstmt(at)entry=0x55c98be908e0,
> isTopLevel=isTopLevel(at)entry=true,
> setHoldSnapshot=setHoldSnapshot(at)entry=false, dest=0x55c98be90b80,
> qc=0x7ffd20942f30) at pquery.c:1158
> #15 0x000055c98b7c0ecb in PortalRunMulti
> (portal=portal(at)entry=0x55c98bf0b710, isTopLevel=isTopLevel(at)entry=true,
> setHoldSnapshot=setHoldSnapshot(at)entry=false,
> dest=dest(at)entry=0x55c98be90b80,
> altdest=altdest(at)entry=0x55c98be90b80, qc=qc(at)entry=0x7ffd20942f30)
> at pquery.c:1322
> #16 0x000055c98b7c139d in PortalRun (portal=portal(at)entry=0x55c98bf0b710,
> count=count(at)entry=9223372036854775807, isTopLevel=isTopLevel(at)entry=true,
> run_once=run_once(at)entry=true,
> dest=dest(at)entry=0x55c98be90b80,
> altdest=altdest(at)entry=0x55c98be90b80, qc=0x7ffd20942f30) at pquery.c:791
> #17 0x000055c98b7bd85d in exec_simple_query (query_string=0x55c98be8fd50
> "DO LANGUAGE 'plperl' $$ elog(NOTICE, 'foo') $$;") at postgres.c:1274
> #18 0x000055c98b7bf978 in PostgresMain (dbname=<optimized out>,
> username=<optimized out>) at postgres.c:4632
> #19 0x000055c98b73f743 in BackendRun (port=<optimized out>,
> port=<optimized out>) at postmaster.c:4461
> #20 BackendStartup (port=<optimized out>) at postmaster.c:4189
> #21 ServerLoop () at postmaster.c:1779
> #22 0x000055c98b74077a in PostmasterMain (argc=argc(at)entry=3,
> argv=argv(at)entry=0x55c98be88fc0) at postmaster.c:1463
> #23 0x000055c98b4a96be in main (argc=3, argv=0x55c98be88fc0) at main.c:198
>
> I think the uselocale() call renders ineffective the setlocale() calls
> that we make later. Maybe we should replace our setlocale() calls with
> 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.

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