Re: Strange results when casting string to double - Mailing list pgsql-general

On 19.02.2022 20:34 Tom Lane wrote:

> Per grep, there is no call of fesetround() in the Postgres source
> tree.  I'm not sure offhand whether libc exposes any other APIs
> that could change the rounding mode, but I am quite sure that we
> wouldn't be intentionally changing it anywhere.
> 
> The OS would surely allow each process to have its own setting of the
> rounding mode, so I doubt you can see it from outside.
> 
> Another point to keep in mind is that no matter how invasive that
> import script might be, it's still hard to explain how it'd affect
> the rounding mode in other backend processes.  You have to postulate
> either that the rounding mode has been changed in the postmaster
> process (and then inherited by session backends via fork()), or that
> some code running at the time of child process creation changes the
> mode, or that they replaced numeric_float8 with something else.
> 
> I think the only way that the postmaster's rounding mode could change
> after postmaster start is the cosmic-ray hypothesis; while we do have
> features that'd allow loading extra code into the postmaster, I'm
> pretty sure they only take effect at postmaster start.  So even if
> that import script tried to do that, it wouldn't have succeeded yet.
> 
> Of the other two hypotheses, "substitute numeric_float8" seems like
> the most likely, especially given the other stuff you mentioned the
> script doing.  Have you checked the relevant pg_cast entry to see
> if it's been changed?  It'd also be interesting to see if the odd
> rounding behavior happens in all databases of the cluster or just
> one.

The script has finished!

After a restart of the database, everything works as expected again. 
Rounding as well as text/numeric to double precision works the same on 
all of my servers.

Prior to restarting, I've implemented my own Python based versions of both

int fegetround(void);

int fesetround(int rounding_mode integer);

Have a look a these:

CREATE OR REPLACE FUNCTION fegetround()
   RETURNS integer AS
$BODY$
     if 'fn.fegetround' in SD:
         return SD['fn.fegetround']()

     from ctypes import cdll
     from ctypes.util import find_library
     libm = cdll.LoadLibrary(find_library('m'))
     def fegetround():
         return libm.fegetround()

     SD['fn.fegetround'] = fegetround
     return SD['fn.fegetround']()
  $BODY$
   LANGUAGE plpython3u VOLATILE
   COST 100;

CREATE OR REPLACE FUNCTION fesetround(rounding_mode integer)
   RETURNS integer AS
$BODY$
     if 'fn.fesetround' in SD:
         return SD['fn.fesetround'](rounding_mode)

     from ctypes import cdll
     from ctypes.util import find_library
     libm = cdll.LoadLibrary(find_library('m'))
     def fesetround(rounding_mode):
         return libm.fesetround(rounding_mode)

     SD['fn.fesetround'] = fesetround
     return SD['fn.fesetround'](rounding_mode)
  $BODY$
   LANGUAGE plpython3u VOLATILE STRICT
   COST 100;

With those, I was able to proof, that actually the "wrong" rounding mode

FE_DOWNWARD (0x400)

was in effect for every new process/connection with all the described 
effects on casting from string or numeric to double precision:

SELECT 1.56::double precision
-> 1.55999999999999

Setting rounding mode to

FE_TONEAREST (0x0),

instantly lead back to the expected casting behavior:

SELECT 1.56::double precision
-> 1.56

Setting rounding mode after restarting the database is still possible, 
however, new sessions start off with the "correct" rounding mode 
FE_TONEAREST (0x0). So, the only thing that's really changed after the 
restart was, that the postmaster now has the "correct" rounding mode, 
which it promotes down when forking off child processes.

We'll likely never know, why ever the postmaster got tainted with that 
FE_DOWNWARD (0x400) rounding mode.

As Tom Lane said, no matter how aggressive the script could be, it can, 
if at all, only change its current session's rounding mode. So, maybe it 
actually was a random bit flip or a side effect caused by a quite rare 
error condition in postmaster.

Nearly the same is true for any core functions or casts hijacked by the 
script - these are only in effect for the database the script was ever 
connecting to. In my case, the script only used one database. However, 
the issue was present with any database.

Two official math functions to get and set the session's rounding mode 
provided by PostgreSQL could be a good add-on for any of the next 
versions of the database. Thinking about it again... maybe that's just 
too dangerous :-p

Finally, many thanks to all that supported me and came up with that many 
helpful ideas! :-)

Regards, Carsten