dangers of setlocale() in backend (was: problem with float8 input format)

On Sat, Aug 12, 2000 at 12:15:26PM -0400, Tom Lane wrote:
> 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.

So using "setlocale(LC_MESSAGES, seller_locale)" instead of "LC_ALL"
should be safe? It doesn't touch numeric formatting.

> 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.

The "problem" I am trying to solve is to send e-mail notifications to
auction bidders in their own language with the proper number formatting,
etc. From what you are saying I'll probably have to move these
notifications to the mod_perl layer of the application. Too bad... not
being a C programmer it took me a while to be able to send mail from the
trigger. Oh well.

> 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.

You mean the indices change because accented characters can come into
play w.r.t the sort order?

> 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?).

Right, not yet.

> 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 :-(

Many thanks for the thorough and clear explanation of the issues.

Cheers,

[much relieved at having found "why"]

--
Louis-David Mitterrand - ldm(at)apartia(dot)org - http://www.apartia.org

"Of course Australia was marked for glory, for its people had been
chosen by the finest judges in England."