Re: solution! (was: Re: problem with float8 input format)

Louis-David Mitterrand <cunctator(at)apartia(dot)ch> writes:
> When "seller_locale" is, for instance, "de_DE", then I get theses
> errors:
> ERROR: Bad float8 input format '0.05'
> Is Postgres expecting the float as 0,05 (notice the comma) because of
> the locale?

I'm sure that's the issue. If you look at the source of the message
(float8in() in src/backend/utils/adt/float.c) you'll see that it's
just relying on strtod() to parse the input. If your local strtod() is
locale-sensitive then the expected input format changes accordingly.
Not sure whether that's a feature or a bug, but it's how Postgres
has always worked.

IMPORTANT: changing the backend's locale on-the-fly is an EXTREMELY
DANGEROUS thing to do, and I strongly recommend that you find another
way to solve your problem. Running with a different locale changes the
expected sort order for indices, which means that your indices will
become corrupted as items get inserted out of order compared to other
items (for one definition of "order" or the other), leading to failure
to find items that should be found in later searches.

Given that your trigger has been exiting with the changed locale still
in force, I'm surprised your DB is still functional at all (perhaps you
have no indexes on textual columns?). But it'd be extremely dangerous
even if you were to restore the old setting before exit --- what happens
if there's an elog(ERROR) before you can restore?

At present, the only safe way to handle locale is to set it in the
postmaster's environment, never in individual backends. What's more,
you'd better be careful that the postmaster is always started with the
same locale setting for a given database. You can find instances of
people being burnt by this sort of problem in the archives :-(

regards, tom lane