Обсуждение: problem with float8 input format

Hello,

Suddenly I am getting errors with the following function:

    SELECT incr(max_price($1),0.05)

    000810.17:20:41.181  [2246] ERROR:  Bad float8 input format '0.05'
    000810.17:20:41.181  [2246] AbortCurrentTransaction

Where incr() is defined as:

    CREATE FUNCTION "incr" (float8,float8 ) RETURNS float8 AS '
    SELECT CASE WHEN $1 < dpow(10,int8(log($1))+1)/2
        THEN (dpow(10,int8(log($1)))) * $2
        ELSE (dpow(10,int8(log($1))+1)/2) * $2
    END
    ' LANGUAGE 'SQL';

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

Thanks in advance for any help,

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

  "Kill a man, and you are an assassin. Kill millions of men, and you
  are a conqueror. Kill everyone, and you are a god." -- Jean Rostand

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