problem with float8 input format

Louis-David Mitterrand <cunctator@apartia.ch> writes:

Strangely engough the function call works fine when called from psql but
fails (but not always!) from a C trigger.

May we see the C trigger? I'm suspicious it's doing something wrong...

regards, tom lane

On Fri, Aug 11, 2000 at 11:42:06AM -0400, Tom Lane wrote:

Louis-David Mitterrand <cunctator@apartia.ch> writes:

Strangely engough the function call works fine when called from psql but
fails (but not always!) from a C trigger.

May we see the C trigger? I'm suspicious it's doing something wrong...

Please find the trigger attached to this message as well as the .sql
file containing the full DB schema including the functions. Here is a
typicall log entry of the error:

000811.18:02:03.555 [1673] query:
SELECT incr(max_price($1),0.05)

000811.18:02:03.556 [1673] ERROR: Bad float8 input format '0.05'

Thanks for your help,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

Slight disorientation after prolonged system
uptime is normal for new Linux users. Please do
not adjust your browser.

Louis-David Mitterrand <cunctator@apartia.ch> writes:

May we see the C trigger? I'm suspicious it's doing something wrong...

Please find the trigger attached to this message

Although I don't see an obvious connection to the error message you are
getting, I am suspicious that the problem happens because you are
expecting CurrentTriggerData to stay valid throughout the execution of
your trigger --- through executions of sub-queries, in fact.

CurrentTriggerData is a global and should be considered extremely
volatile, because it will get changed if any other trigger is fired
by the sub-query, and may get zeroed anyway if certain paths through
the function manager get taken.

I recommend this coding pattern for user-defined triggers:

1. Copy CurrentTriggerData into a local variable, say
TriggerData *trigdata;
*immediately* upon entry to your trigger function, and then reset
CurrentTriggerData = NULL before doing anything else.

2. Subsequently, use "trigdata" not CurrentTriggerData.

Aside from not causing problems for recursive trigger calls, this
approach will also be a lot easier to convert to 7.1 code --- a word
to the wise eh?

If you still see flaky behavior after making this change, please let me
know and I'll probe more deeply.

regards, tom lane

On Fri, Aug 11, 2000 at 08:35:03PM -0400, Tom Lane wrote:

Although I don't see an obvious connection to the error message you are
getting, I am suspicious that the problem happens because you are
expecting CurrentTriggerData to stay valid throughout the execution of
your trigger --- through executions of sub-queries, in fact.

CurrentTriggerData is a global and should be considered extremely
volatile, because it will get changed if any other trigger is fired
by the sub-query, and may get zeroed anyway if certain paths through
the function manager get taken.

I recommend this coding pattern for user-defined triggers:

1. Copy CurrentTriggerData into a local variable, say
TriggerData *trigdata;
*immediately* upon entry to your trigger function, and then reset
CurrentTriggerData = NULL before doing anything else.

I did just that and the error keeps happening.

On an unrelated matter I have this expression in the trigger:

int stop_date = DatumGetInt32(SPI_getbinval(
SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
SPI_fnumber(SPI_tuptable->tupdesc,"date_part"),
&isnull));

where "date_part" comes from "date_part('epoch', stopdate)" in a
previous query. The problem is the value of stop_date is not the number
of seconds since the epoch but some internal representation of the data.
So I can't compare stop_date with the output of
GetCurrentAbsoluteTime().

What function should I use to convert the Datum to a C int?
DatumGetInt32 doesn't seem to work here.

And what is the method for float8 Datum conversion to C double? I
couldn't find any clearcut examples in the trigger examples.

Thanks in advance,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

Conscience is what hurts when everything else feels so good.

On Fri, Aug 11, 2000 at 10:07:39PM +0200, Louis-David Mitterrand wrote:

On Fri, Aug 11, 2000 at 11:42:06AM -0400, Tom Lane wrote:

Louis-David Mitterrand <cunctator@apartia.ch> writes:

Strangely engough the function call works fine when called from psql but
fails (but not always!) from a C trigger.

May we see the C trigger? I'm suspicious it's doing something wrong...

Please find the trigger attached to this message as well as the .sql
file containing the full DB schema including the functions. Here is a
typicall log entry of the error:

Finally I found the problem:

bindtextdomain("apartia_com", "/usr/local/auction/locale");
textdomain("apartia_com");
setlocale(LC_ALL, seller_locale);

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?

When "seller_locale" is "en_US" all is well.

(C trigger is attached)

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

"When I give food to the poor I am called a saint, when I ask why
they go hungry I am called a communist"
--Bishop Helder Camara

Louis-David Mitterrand <cunctator@apartia.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

Louis-David Mitterrand <cunctator@apartia.ch> writes:

where "date_part" comes from "date_part('epoch', stopdate)" in a
previous query. The problem is the value of stop_date is not the number
of seconds since the epoch but some internal representation of the data.
So I can't compare stop_date with the output of
GetCurrentAbsoluteTime().

GetCurrentAbsoluteTime yields an "abstime", so you should coerce the
"timestamp" result of date_part() to abstime and then you will get a
value you can compare directly.

What function should I use to convert the Datum to a C int?
DatumGetInt32 doesn't seem to work here.

No, because timestamps are really floats. (abstime is an int though.)

And what is the method for float8 Datum conversion to C double?

double x = * DatumGetFloat64(datum);

This is pretty grotty because it exposes the fact that float8 datums
are pass-by-reference (ie, pointers). 7.1 will let you write

double x = DatumGetFloat8(datum);

which is much cleaner. (I am planning that on 64-bit machines it will
someday be possible for float8 and int64 to be pass-by-value, so it's
important to phase out explicit knowledge of the representation in user
functions.)

regards, tom lane

On Sat, Aug 12, 2000 at 12:15:26PM -0400, Tom Lane wrote:

Louis-David Mitterrand <cunctator@apartia.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@apartia.org - http://www.apartia.org

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

Louis-David Mitterrand <cunctator@apartia.ch> writes:

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.

Well, you could fork a subprocess to issue the mail and change locale
only once you're safely inside the subprocess.

regards, tom lane

On Sat, 12 Aug 2000, Louis-David Mitterrand wrote:

On Sat, Aug 12, 2000 at 12:15:26PM -0400, Tom Lane wrote:

Louis-David Mitterrand <cunctator@apartia.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?

The postgreSQL allows to work with locale-numbers. See to_char()
and to_number() functions.

test=# select to_char(1234.456, '9G999D999');
to_char
------------
1�234,456
(1 row)

test=# select to_number('1 234,457', '9G999D999');
to_number
-----------
1234.457
(1 row)

And your backend will out of next Tom's note :-)

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.

Karel

On Sat, Aug 12, 2000 at 01:10:01PM -0400, Tom Lane wrote:

Louis-David Mitterrand <cunctator@apartia.ch> writes:

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.

Well, you could fork a subprocess to issue the mail and change locale
only once you're safely inside the subprocess.

Could you give a minimal example of how forking a subprocess in a PG
trigger is done? Or maybe give a pointer to an existing example?

On an unrelated subject I have to maintain and update a table containing
currency rates for the auction site (URL in .sig):

Table "currency"
Attribute | Type | Modifier
-----------+--------+--------------------
USD | float4 | not null default 1
FRF | float4 |
AUD | float4 |
CAD | float4 |
EUR | float4 |
GBP | float4 |
DEM | float4 |
JPY | float4 |
CHF | float4 |

To update it I wrote a quick perl script that grabs data from Yahoo's
currency web page (attached). This script has to be installed and run as
a cron job, but I'd like to integrate that functionality in the DB
backend as a trigger that performs the data refresh every n'th SELECT on
the table:
- either convert that perl script to C (maybe using libwww and a
regex C library);
- or simpy launch that perl script from the trigger;
The former solution is not easy, and is probably a good programming
exercise, the latter is quick but how would one go about launching a
perl script from C without waiting for its completion?

Thanks in advance,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

Parkinson's Law: Work expands to fill the time alloted it.

On Tue, Aug 15, 2000 at 09:23:15AM +0200, Karel Zak wrote:

Thank for the pointer to these functions, which are indeed convenient.
But the problem remains of not being able to change the backend's locale
on-the-fly. For example if an auction user is spanish and the next one
is german the locale needs to change several times during the life of
the DB, which raises some larger index-related issues apparently.

Before some weeks ago I sent to -patches list patch that allows to change
locales on-the-fly via 'SET LOCALE' command. But as say Tom L. it's
VERY DANGEROUS. Solution is locales per columns or something like this, but
nobody works on this :-)

But your patch sounds incredibly useful :-) Has it been integrated in
the mainline code yet? How does one use this functionality?

Also what is the main difference with using the standard gettext call?

setlocale(LC_ALL, "en_US");

Thanks,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

But your patch sounds incredibly useful :-) Has it been integrated in
the mainline code yet? How does one use this functionality?

It never will integrated into the PG standard main tree, because it is
stupid patch for common usege :-( (and I feel ashamed of this :-)

Also what is the main difference with using the standard gettext call?

setlocale(LC_ALL, "en_US");

This ('LC_ALL') call load support for all locales categories (numbers,
text, currency...etc.). Inside postgreSQL it's dangerous, because it
change for example float numbers deciamal point..etc.

In the PostgreSQL are used (only):

setlocale(LC_CTYPE, "");
setlocale(LC_COLLATE, "");
setlocale(LC_MONETARY, "");

For more information see the file ustils/adt/pg_locale.c in PG sources, that
allows you to change and load *all* locales catg. and set it back to previous
state. It is used for to_char() that needs load LC_NUMERIC informations.

But again: after your functions you must always set correct locales.

And in 7.1 it will more important, because CurrentLocaleConv struct in
pg_locale.c that use to_char() is load only once --- it's performance
option. And not is a way how this struct change if it's already filled,
because we not expect on-the-fly locales....

Karel

On Tue, Aug 15, 2000 at 11:30:11AM +0200, Karel Zak wrote:

But your patch sounds incredibly useful :-) Has it been integrated in
the mainline code yet? How does one use this functionality?

It never will integrated into the PG standard main tree, because it is
stupid patch for common usege :-( (and I feel ashamed of this :-)

Also what is the main difference with using the standard gettext call?

setlocale(LC_ALL, "en_US");

This ('LC_ALL') call load support for all locales categories (numbers,
text, currency...etc.). Inside postgreSQL it's dangerous, because it
change for example float numbers deciamal point..etc.

[SNIP very interesting info on PG internal locale processing]

Considering that would it then be safe to only use LC_NUMERIC and
LC_MESSAGES in setlocale() calls? The dangers Tom Lane talks about in
reference to changing locale in the backend seem to be related to
LC_COLLATE stuff, right?

Thanks for your input, cheers,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

I don't build computers, I'm a cooling engineer.
-- Seymour Cray, founder of Cray Inc.

On Tue, 15 Aug 2000, Louis-David Mitterrand wrote:

[SNIP very interesting info on PG internal locale processing]

Considering that would it then be safe to only use LC_NUMERIC and
LC_MESSAGES in setlocale() calls? The dangers Tom Lane talks about in
reference to changing locale in the backend seem to be related to
LC_COLLATE stuff, right?

Not sure that use the LC_NUMERIC is correct. For example next routine
is inside PG:

Datum
float4out(PG_FUNCTION_ARGS)
{
float4 num = PG_GETARG_FLOAT4(0);
char *ascii = (char *) palloc(MAXFLOATWIDTH + 1);

sprintf(ascii, "%.*g", FLT_DIG, num);
PG_RETURN_CSTRING(ascii);
}

What happen here with/without LC_NUMERIC?

type 'man sprintf':

For some numeric conversion a radic character (Decimal
point') or thousands' grouping character is used. The
actual character used depends on the LC_NUMERIC part of
^^^^^^^^^^^
the locale. The POSIX locale uses .' as radix character,
and does not have a grouping character. Thus,
printf("%'.2f", 1234567.89);
results in {4567.89' in the POSIX locale, in
{4567,89' in the nl_NL locale, and in 234.567,89' in
the da_DK locale.

Very simular it's in the float4in() with strtod() ...etc.

Karel