Обсуждение: Our FLOAT(p) precision does not conform to spec

Fernando Nasser of Red Hat pointed out to me that we are not quite
spec-compliant on the FLOAT(p) datatype notation.  We interpret P as
the number of decimal digits of precision, and hence translate
P = 1..6  => float4 (a/k/a REAL)P = 7..15 => float8 (a/k/a DOUBLE PRECISION)otherwise error

However, the spec is perfectly clear that P is to be interpreted as
the number of *binary* digits of precision, not decimal digits.  SQL92
section 4.4.1 says:
        An approximate numeric value consists of a mantissa and an expo-        nent. The mantissa is a signed numeric
value,and the exponent is        a signed integer that specifies the magnitude of the mantissa. An        approximate
numericvalue has a precision. The precision is a posi-        tive integer that specifies the number of significant
binarydigits                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^        in the mantissa. The
valueof an approximate numeric value is the        mantissa multiplied by 10^exponent.
 

So it's fairly clear that P is not the number of decimal digits.  (The
reference to multiplying by 10^exponent seems bogus, since on machines
where the mantissa is in fact binary, one would expect a base-2 or
possibly base-16 exponent to be used.  But this does not affect the
precision of the mantissa AFAICS.)

On the assumption that most platforms have IEEE float math, it would be
appropriate to interpret P like this:
P = 1..24  => float4P = 25..53 => float8otherwise error

This is a straightforward change and would not break pg_dump files,
since fortunately pg_dump always references the underlying types and
never refers to anything as FLOAT(p).  But I wonder whether it is
likely to break many existing applications.  There is a hazard of some
existing app asking for (what it thinks is) float8 and getting float4
instead.

Is it worth trying to provide some sort of backwards-compatibility mode?
We could imagine adding a GUC variable to select binary or decimal
precision, but I really don't want to.  It would increase the amount of
work needed by more than an order of magnitude, and this problem doesn't
seem worth it.  I'd rather just list this under Incompatibilities in the
7.4 release notes.

Comments?
        regards, tom lane

Tom Lane wrote:
> Is it worth trying to provide some sort of backwards-compatibility mode?
> We could imagine adding a GUC variable to select binary or decimal
> precision, but I really don't want to.  It would increase the amount of
> work needed by more than an order of magnitude, and this problem doesn't
> seem worth it.  I'd rather just list this under Incompatibilities in the
> 7.4 release notes.

Let's just change it and list it in the release notes as an
incompatibility --- anything else is too confusing.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On 16 Jun 2003 at 18:15, Tom Lane wrote:
> This is a straightforward change and would not break pg_dump files,
> since fortunately pg_dump always references the underlying types and
> never refers to anything as FLOAT(p).  But I wonder whether it is
> likely to break many existing applications.  There is a hazard of some
> existing app asking for (what it thinks is) float8 and getting float4
> instead.

Especially apps. which rely on number(3) not to accept anything greate than +/-
999.

I hate the syntax of putting decimal digits as range checkers in SQL field. But 
oracle does that and consequently lot of oracle apps rely on it. I won't be 
surprised if float(p) notion brings same assurance to such app developers.

I think this would become and FAQ after changes are implemented. Who reads 
release notes anyway?..:-)
> Is it worth trying to provide some sort of backwards-compatibility mode?
> We could imagine adding a GUC variable to select binary or decimal
> precision, but I really don't want to.  It would increase the amount of
> work needed by more than an order of magnitude, and this problem doesn't
> seem worth it.  I'd rather just list this under Incompatibilities in the
> 7.4 release notes.

Is it possible to have float-2(p) and float-10(p)? Would it be adding code 
sections instead of replacing it?

That would be cleaner than GUC variable. Everything defaults to float-10(p) for 
backward compatibility for a period of a release and in 7.5, it gets switched 
to float-2(p). In the meantime, the behaviour remains available who cares to 
read the docs and notes.

Just a thought..

ByeShridhar

--
Genderplex, n.:    The predicament of a person in a restaurant who is unable to    
determine his or her designated restroom (e.g., turtles and tortoises).        -- 
Rich Hall, "Sniglets"

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> On 16 Jun 2003 at 18:15, Tom Lane wrote:
>> This is a straightforward change and would not break pg_dump files,
>> since fortunately pg_dump always references the underlying types and
>> never refers to anything as FLOAT(p).  But I wonder whether it is
>> likely to break many existing applications.  There is a hazard of some
>> existing app asking for (what it thinks is) float8 and getting float4
>> instead.

> I hate the syntax of putting decimal digits as range checkers in SQL
> field. But oracle does that and consequently lot of oracle apps rely
> on it. I won't be surprised if float(p) notion brings same assurance
> to such app developers.

You are confusing NUMERIC --- which does allow exact precision limits to
be specified --- with FLOAT, which does no such thing.  It has never
been the case in Postgres that FLOAT(p) would restrict you to exactly p
digits.  The underlying implementation is that there are just two kinds
of float (single and double precision) and you get whichever can hold at
least p digits.  This is per spec, which states that you get at least p
digits, not exactly p digits.  Our only problem is that whoever wrote
that code failed to notice that p is supposed to be measured differently
for FLOAT than for NUMERIC.

AFAICT, other databases get this right (at least Oracle and DB2 do), so
expectations of developers are more likely to be that we conform to the
spec than that we don't.
        regards, tom lane

Tom Lane writes:

> This is a straightforward change and would not break pg_dump files,
> since fortunately pg_dump always references the underlying types and
> never refers to anything as FLOAT(p).  But I wonder whether it is
> likely to break many existing applications.  There is a hazard of some
> existing app asking for (what it thinks is) float8 and getting float4
> instead.

Considering that the data type float(x) isn't documented anywhere, I'm not
worried.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> Considering that the data type float(x) isn't documented anywhere, I'm not
> worried.

Good point ... I'll fix that while I'm at it ...
        regards, tom lane

Fix the problem and inform the users about code that may break.

Rick