Message-ID: <555bb26e-c27c-ab65-028f-3fd5b82df9de@iki.fi>
Date: Mon, 5 Jun 2023 19:00:34 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>, gdo@leader.it
Cc: pgsql-bugs@lists.postgresql.org
References: <17946-3e84cb577e9551c3@postgresql.org>
 <1564676.1685043220@sss.pgh.pa.us>
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: <1564676.1685043220@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/555bb26e-c27c-ab65-028f-3fd5b82df9de%40iki.fi>
Precedence: bulk

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 € 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@entry=0x55c98bebb1c0 "$libdir/plperl", 
funcname=funcname@entry=0x55c98beba378 "plperl_inline_handler",
     signalNotFound=signalNotFound@entry=true, 
filehandle=filehandle@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@entry=0, arg1=94324124262840) at fmgr.c:1386
#12 0x000055c98b5e1385 in ExecuteDoStmt 
(pstate=pstate@entry=0x55c98beba0b0, stmt=stmt@entry=0x55c98be90858, 
atomic=atomic@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@entry=0x55c98bf0b710, pstmt=pstmt@entry=0x55c98be908e0, 
isTopLevel=isTopLevel@entry=true,
     setHoldSnapshot=setHoldSnapshot@entry=false, dest=0x55c98be90b80, 
qc=0x7ffd20942f30) at pquery.c:1158
#15 0x000055c98b7c0ecb in PortalRunMulti 
(portal=portal@entry=0x55c98bf0b710, isTopLevel=isTopLevel@entry=true, 
setHoldSnapshot=setHoldSnapshot@entry=false, 
dest=dest@entry=0x55c98be90b80,
     altdest=altdest@entry=0x55c98be90b80, qc=qc@entry=0x7ffd20942f30) 
at pquery.c:1322
#16 0x000055c98b7c139d in PortalRun (portal=portal@entry=0x55c98bf0b710, 
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, 
run_once=run_once@entry=true,
     dest=dest@entry=0x55c98be90b80, 
altdest=altdest@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@entry=3, 
argv=argv@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.

-- 
Heikki Linnakangas
Neon (https://neon.tech)