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

BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      17946
Logged by:          Guido Brugnara
Email address:      gdo@leader.it
PostgreSQL version: 12.15
Operating system:   Ubuntu 20.04
Description:

After upgrading an application using Postgresql from version 10 to 12,
fields of type "money" are no longer generated with the € symbol but with
$.
I identified the problem that occurs when making use of functions with
"LANGUAGE plperl," see with the following queries to be executed in order:
# from shell ...
sudo su -c psql\ postgres postgres <<'__SQL__';
  SET lc_monetary TO 'C';
  SELECT 12.34::money AS price;
  SET lc_monetary TO 'it_IT.UTF-8';
  SELECT 12.34::money AS price;
  SET lc_monetary TO 'en_GB.UTF-8';
  SELECT 12.34::money AS price;
  CREATE EXTENSION plperl;
  SET lc_monetary TO 'C';
  SELECT 12.34::money AS price;
  DO LANGUAGE 'plperl' $$ my $rv = spi_exec_query(q{SELECT 12.34::money AS
price;}, 1);elog(NOTICE, $rv->{rows}[0]->{price});$$;
  SET lc_monetary TO 'it_IT.UTF-8';
  SELECT 12.34::money AS price;
  DO LANGUAGE 'plperl' $$ my $rv = spi_exec_query(q{SELECT 12.34::money AS
price;}, 1);elog(NOTICE, $rv->{rows}[0]->{price});$$;
  SET lc_monetary TO 'en_GB.UTF-8';
  SELECT 12.34::money AS price;
  DO LANGUAGE 'plperl' $$ my $rv = spi_exec_query(q{SELECT 12.34::money AS
price;}, 1);elog(NOTICE, $rv->{rows}[0]->{price});$$;
__SQL__
#end.

The first three SELECTs generate content with the currencies Dollar, Euro &
Pound, as expected, while the last three only with Dollar.
It would appear that after first DO LANGUAGE 'plper' call, LC_MONETARY even
if it is varied, has no effect in subsequent queries.
Any suggestions?


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

From
Tom Lane
Date:
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:

$ psql
psql (12.15)
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)

postgres=#   DO LANGUAGE 'plperl' $$ my $rv = spi_exec_query(q{SELECT 12.34::money AS
price;}, 1);elog(NOTICE, $rv->{rows}[0]->{price});$$;
NOTICE:  £12.34
DO
postgres=# SET lc_monetary TO 'it_IT.UTF-8';
SET
postgres=#   SELECT 12.34::money AS price;
  price
---------
 € 12,34
(1 row)

postgres=#   DO LANGUAGE 'plperl' $$ my $rv = spi_exec_query(q{SELECT 12.34::money AS
price;}, 1);elog(NOTICE, $rv->{rows}[0]->{price});$$;
NOTICE:  € 12,34
DO

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.

            regards, tom lane



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

From
Heikki Linnakangas
Date:
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)




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

From
"Tristan Partin"
Date:
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 € 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.

I am looking into this bug. I have also reproduced it.

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



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

From
Joe Conway
Date:
On 6/9/23 11:31, Tristan Partin wrote:
> 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 € 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.
> 
> I am looking into this bug. I have also reproduced it.

It reproduces for me on both pg16beta1 and pg10. I wonder if it isn't a 
behavior change in libperl itself. It seems that merely doing "load 
'plperl';" is enough to cause the issue as long as it is done prior to 
doing "SET lc_monetary TO 'en_GB.UTF-8'; SELECT 12.34::money AS price;". 
When done in the opposite order the problem does not occur.

8<------------------------------
# On pg10 with perl v5.34.0
# note that on my system
# LC_NUMERIC=""
# LC_ALL=""
# LANG="en_US.UTF-8"
#
# this works correctly
psql nmx << EOF
SET lc_monetary TO 'en_GB.UTF-8';
SELECT 12.34::money AS price;
load 'plperl';
SELECT 12.34::money AS price;
EOF
SET
  price
--------
  £12.34
(1 row)

LOAD
  price
--------
  £12.34
(1 row)

# this does not
psql nmx << EOF
SET lc_monetary TO 'en_GB.UTF-8';
load 'plperl';
SELECT 12.34::money AS price;
EOF
SET
LOAD
  price
--------
  $12.34
(1 row)
8<------------------------------

Since I am also seeing this on pg10, I wonder if it is a change in 
perl.I found this[1]:

   "What did change is that perl space code no
    longer pays attention to the LC_NUMERIC
    category outside 'use locale'. This is the way
    it has always worked, AFAIK, for LC_COLLATE
    and, mostly, LC_CTYPE, and for some uses of
    LC_NUMERIC."


[1] "locale changes in 5.19.1 break LC_NUMERIC
      handling"
      https://github.com/Perl/perl5/issues/13089
-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com




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

From
Joe Conway
Date:
On 6/9/23 15:05, Joe Conway wrote:
> I wonder if it isn't a behavior change in libperl itself. It seems
> that merely doing "load 'plperl';" is enough to cause the issue
I can reproduce with a simple test program by linking libperl:

8<-------- test.c ----------------
#include <locale.h>
#include <stdio.h>

#define off64_t         __off64_t
#include <EXTERN.h>
#include <perl.h>

int
main(int argc, char *argv[])
{
   struct lconv *extlconv;
#ifdef WITH_PERL
   PerlInterpreter *plperl;
   plperl = perl_alloc();
   perl_construct(plperl);
#endif
   setlocale(LC_MONETARY, "en_GB.UTF-8");
   extlconv = localeconv();
   printf("currency symbol = \"%s\"\n",
          extlconv->currency_symbol);
   return 0;
}
8<-------- test.c ----------------

Adjust the perl paths to suit:

8<------------------------
gcc -O0 -ggdb3 -o test \
  -I /usr/lib64/perl5/CORE \
  -lperl \
  test.c

./test
currency symbol = "£"

gcc -O0 -ggdb3 -o test \
  -I /usr/lib64/perl5/CORE \
  -lperl -DWITH_PERL \
  test.c

./test
currency symbol = "$"
8<------------------------

It happens because somehow loading libperl prevents localeconv() from 
returning the correct values, even though libperl only seems to call 
"setlocale(LC_ALL, NULL)" which ought not change anything.

8<------------------------
gdb ./test

Reading symbols from ./test...
(gdb) b setlocale
Breakpoint 1 at 0x10f0
(gdb) r
Starting program: /opt/src/pgsql-

Breakpoint 1, __GI_setlocale (category=6, locale=0x0) at 
./locale/setlocale.c:218
218     ./locale/setlocale.c: No such file or directory.
(gdb) bt
#0  __GI_setlocale (category=6, locale=0x0) at ./locale/setlocale.c:218
#1  0x00007ffff7d96b97 in Perl_init_i18nl10n () from 
/lib/x86_64-linux-gnu/libperl.so.5.34
#2  0x0000555555555225 in main (argc=1, argv=0x7fffffffe1d8) at test.c:18
(gdb) c
Continuing.

Breakpoint 1, __GI_setlocale (category=4, locale=0x55555555602e 
"en_GB.UTF-8") at ./locale/setlocale.c:218
218     in ./locale/setlocale.c
(gdb) bt
#0  __GI_setlocale (category=4, locale=0x55555555602e "en_GB.UTF-8") at 
./locale/setlocale.c:218
#1  0x0000555555555239 in main (argc=1, argv=0x7fffffffe1d8) at test.c:20

main (argc=1, argv=0x7fffffffe1d8) at test.c:21
21        extlconv = localeconv();
(gdb)
22        printf("currency symbol = \"%s\"\n",
(gdb)
currency symbol = "$"
24        return 0;
(gdb)
8<------------------------

Will continue to dig in the morning.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com




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

From
Joe Conway
Date:
On 6/9/23 22:10, Joe Conway wrote:
> On 6/9/23 15:05, Joe Conway wrote:
>> I wonder if it isn't a behavior change in libperl itself. It seems
>> that merely doing "load 'plperl';" is enough to cause the issue
> I can reproduce with a simple test program by linking libperl:
> 
> 8<-------- test.c ----------------

A bit more spelunking leads me to the following observations and 
conclusions:

1/ On RHEL7 with perl v5.16.3 the problem does not occur

2/ On RHEL9 with perl v5.32.1 the problem does occur

3/ The difference in behavior is triggered by the newer perl doing a 
bunch of newlocale/uselocale calls not done by the older perl, combined 
with a glibc behavior which seems surprising at best.

 From localeinfo.h in glibc source tree:
8<------------------------
/* This fetches the thread-local locale_t pointer, either one set with
    uselocale or &_nl_global_locale.  */
#define _NL_CURRENT_LOCALE      (__libc_tsd_get (locale_t, LOCALE))
8<------------------------

4/ I successfully tested a fix in the simplified reproducer program sent 
earlier. It amounts to adding:

8<------------------------
   uselocale(LC_GLOBAL_LOCALE);
8<------------------------

prior to calling

8<------------------------
   extlconv = localeconv();
8<------------------------

5/ The attached fixes the issue for me on pg10 and passes check-world.

Comments?

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Attachment

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

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> 5/ The attached fixes the issue for me on pg10 and passes check-world.
> Comments?

The call in PGLC_localeconv seems *very* oddly placed.  Why not
do that before it does any other locale calls?  Otherwise you don't
really have reason to believe you're saving the appropriate
values to restore later.

            regards, tom lane



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

From
Joe Conway
Date:
On 6/10/23 14:42, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> 5/ The attached fixes the issue for me on pg10 and passes check-world.
>> Comments?
> 
> The call in PGLC_localeconv seems *very* oddly placed.  Why not
> do that before it does any other locale calls?  Otherwise you don't
> really have reason to believe you're saving the appropriate
> values to restore later.


As far as I can tell it really only affects localeconv(), so I tried to 
place it close to those. But I am fine with moving it up.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com




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

From
Joe Conway
Date:
On 6/10/23 15:07, Joe Conway wrote:
> On 6/10/23 14:42, Tom Lane wrote:
>> Joe Conway <mail@joeconway.com> writes:
>>> 5/ The attached fixes the issue for me on pg10 and passes check-world.
>>> Comments?
>> 
>> The call in PGLC_localeconv seems *very* oddly placed.  Why not
>> do that before it does any other locale calls?  Otherwise you don't
>> really have reason to believe you're saving the appropriate
>> values to restore later.
> 
> 
> As far as I can tell it really only affects localeconv(), so I tried to
> place it close to those. But I am fine with moving it up.

This version is against pg16 (rather than pg10), moves up that hunk, 
mentions localeconv() in the comment as the reason for the call, and 
fixes some whitespace sloppiness. I will plan to apply to all supported 
branches.

Better?


-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Attachment

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

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> This version is against pg16 (rather than pg10), moves up that hunk, 
> mentions localeconv() in the comment as the reason for the call, and 
> fixes some whitespace sloppiness. I will plan to apply to all supported 
> branches.

> Better?

WFM.

            regards, tom lane



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

From
Heikki Linnakangas
Date:
On 10/06/2023 22:28, Joe Conway wrote:
> On 6/10/23 15:07, Joe Conway wrote:
>> On 6/10/23 14:42, Tom Lane wrote:
>>> Joe Conway <mail@joeconway.com> writes:
>>>> 5/ The attached fixes the issue for me on pg10 and passes check-world.
>>>> Comments?
>>>
>>> The call in PGLC_localeconv seems *very* oddly placed.  Why not
>>> do that before it does any other locale calls?  Otherwise you don't
>>> really have reason to believe you're saving the appropriate
>>> values to restore later.
>>
>>
>> As far as I can tell it really only affects localeconv(), so I tried to
>> place it close to those. But I am fine with moving it up.
> 
> This version is against pg16 (rather than pg10), moves up that hunk,
> mentions localeconv() in the comment as the reason for the call, and
> fixes some whitespace sloppiness. I will plan to apply to all supported
> branches.
> 
> Better?

The man page for uselocale(LC_GLOBAL_LOCALE) says: "The calling thread's 
current locale is set to the global locale determined by setlocale(3)." 
Does that undo the effect of calling uselocale() previously, so if you 
later call setlocale(), the new locale takes effect in the thread too? 
Or is it equivalent to "uselocale(LC_ALL, setlocale(NULL))", so that it 
sets the thread's locale to the current global locale, but later 
setlocale() calls have no effect on it?

In any case, this still doesn't feel like the right place. We have many 
more setlocale() calls. Shouldn't we sprinkle them all with 
uselocale(LC_GLOBAL_LOCALE)? cache_locale_time() for example. Or rather, 
all the places where we use any functions that depend on the current locale.

How about we replace all setlocale() calls with uselocale()?

Shouldn't we restore the old thread-specific locale after the calls? I'm 
not sure why libperl calls uselocale(), but we are now overwriting the 
locale that it sets. We have a few other uselocale() calls in 
pg_locale.c, and we take care to restore the old locale in those.

There are a few uselocale() calls in ecpg, and they are protected by 
HAVE_USELOCALE. Interestingly, the calls in pg_locale.c are not, but 
they are protected by HAVE_LOCALE_T. Seems a little inconsistent.

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




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

From
"Tristan Partin"
Date:
On Mon Jun 12, 2023 at 4:13 AM CDT, Heikki Linnakangas wrote:
> There are a few uselocale() calls in ecpg, and they are protected by
> HAVE_USELOCALE. Interestingly, the calls in pg_locale.c are not, but
> they are protected by HAVE_LOCALE_T. Seems a little inconsistent.

Patch is attached. CC-ing hackers.

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

Attachment

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

From
"Tristan Partin"
Date:
On Mon Jun 12, 2023 at 4:13 AM CDT, Heikki Linnakangas wrote:
> There are a few uselocale() calls in ecpg, and they are protected by
> HAVE_USELOCALE. Interestingly, the calls in pg_locale.c are not, but
> they are protected by HAVE_LOCALE_T. Seems a little inconsistent.

Patch is attached. CC-ing hackers.

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

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

From
"Tristan Partin"
Date:
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 € 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.

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)



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

From
"Tristan Partin"
Date:
On Mon Jun 5, 2023 at 11:00 AM CDT, Heikki Linnakangas wrote:
>
> I think the uselocale() call renders ineffective the setlocale() calls
> that we make later. Maybe we should replace our setlocale() calls with
> uselocale(), too.

Should we just stop supporting systems without uselocale() that aren't
Windows, where you can get thread-safe localization using another
method? I am not aware of other systems that might have their own
non-POSIX APIs.

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