Adding IEEE 754:2008 decimal floating point and hardware support for it

Hi all<br /><br /> Currently DECIMAL is an alias for NUMERIC, Pg's built-in arbitrary precision and scale decimal
type.I'd like to explore the possibility of using hardware decimal floating point support in newer processors,
compilersand C libraries to enhance DECIMAL / NUMERIC performance.<br /><br /> With the advent of _Decimal32,
_Decimal64and _Decimal128 support in IEEE 754:2008 as supported in gcc in <float.h>  TR24732 we have the
opportunityto make use of hardware representations of decimal floating point values and hardware implementations of
operationson them, gaining a potentially huge performance boost in exchange for more limited precision and scale. I'd
liketo gather ideas and suggestions about how we might approach this.<br /><br /> The main thing I'm wondering is
how/ifto handle backward compatibility with the existing NUMERIC and its DECIMAL alias, or whether adding new
DECIMAL32,DECIMAL64, and DECIMAL128 types would be more appropriate. I'd love to just use the SQL standard types name
DECIMALif possible, and the standard would allow for it (see below), but backward compat would be a challenge, as would
comingup with a sensible transparent promotion scheme from 32->64->128->numeric and ways to stop undesired
promotion.<br/><br /> What I'm currently thinking of is using the same strategy we use right now for FLOAT(n) where we
selectbetween float4 and float8 based on the specified precision. We could do the same for DECIMAL; up to DECIMAL(94,7)
wouldbecome decimal32; up to DECIMAL(382,16) would become decimal64 and DECIMAL128(34,6142); everything higher would
becomeNUMERIC as currently. NUMERIC would be unaffected. (Ideally we wouldn't have to do the type change in the parser
hackbut that's not really possible so long as Pg doesn't preserve typmods in calculations and intermediate outputs).<br
/><br/> According to TR24732 ( <a
href="http://www.open-std.org/JTC1/SC22/WG14/www/docs/n1312.pdf">http://www.open-std.org/JTC1/SC22/WG14/www/docs/n1312.pdf</a>)
the_Decimal family of types offer:<br /><br /> _Decimal32: 7 coefficient digits, 10^97 to 10^-94 range.<br />
_Decimal64:16 coefficient digits, 10^385 to 10^-382 range.<br /> _Decimal128  34 coefficient digits, 10^6145 to
10^-6142range.<br /><br /> There was a thread about this on -general some time ago:<br /><br />     <a
href="http://www.postgresql.org/message-id/4CB26B16.7080602@postnewspapers.com.au">http://www.postgresql.org/message-id/4CB26B16.7080602@postnewspapers.com.au</a><br
/><br/> that never went anywhere. Other discussion is mostly of use cases and is more hypothetical, but outlines why
they'dbe useful:<br /><br />     <a
href="http://www.postgresql.org/message-id/CADLWmXVmne9t5x-hR-XGOxEyOWQX5BfZwc9Qb=xhsJ_gkG_AaQ@mail.gmail.com">http://www.postgresql.org/message-id/CADLWmXVmne9t5x-hR-XGOxEyOWQX5BfZwc9Qb=xhsJ_gkG_AaQ@mail.gmail.com</a><br
/><br/><br /> In terms of how they fit in to the standard, the copy of the SQL:2008 draft I have here says:<br /><br />
*NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the<br />
<precision>and <scale>.<br /> * DECIMAL specifies the data type exact numeric, with the decimal scale
specifiedby the <scale> and<br /> the implementation-defined decimal precision equal to or greater than the value
ofthe specified <precision>.<br /><br /> Additionally:<br /><br /> * For the <exact numeric type>s DECIMAL
andNUMERIC, the maximum values of <precision> and<br /> of <scale> are implementation-defined.<br /><br />
...so it seems we'd be free to use the hardware types and could possibly internally promote from smaller to larger
decimaltypes as appropriate.<br /><br /> My main concern is that even the largest fixed-size decimal can't store the
arbitraryprecision and scale supported by Pg's NUMERIC, and people are used to using DECIMAL as an alias for NUMERIC.
We'rein a bit of a BC trap, as the spec would allow us to just use the hardware types, but we've already provided
supportfor a nearly unlimited (but much, much slower) type using the same name. <br /><br /><br /> Regarding the
breadthof support, it looks like we could use gcc's built-in types if available, and otherwise fall back to one of the
portabledecimal floating point maths libraries.<br /><br /> gcc 4.3 added _Decimal types, see <a
href="http://gcc.gnu.org/gcc-4.3/changes.html">http://gcc.gnu.org/gcc-4.3/changes.html</a>,so it's ancient history for
gcc.Hardware support isn't required; in fact the real question would be whether gcc actually uses the hardware
operationswhere they're supported. I'll need to dig into that.<br /><br /> For Windows, MSVC doesn't support the types
asbuilt-ins. There's the Intel Decimal Floating Point Library (<a
href="http://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library">http://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library</a>,
alsoavailable from <a href="http://www.netlib.org/misc/intel/">http://www.netlib.org/misc/intel/</a>) to provide
supportas a library. IBM's "decnumber" libraray is another possible alternative.<br /><br /> LLVM's clang does not
supportthose types according to the manual: "clang does not support decimal floating point types (_Decimal32 and
friends)or fixed-point types (_Fract and friends); nobody has expressed interest in these features yet, so it’s hard to
saywhen they will be implemented." (<a
href="http://clang.llvm.org/docs/UsersManual.html">http://clang.llvm.org/docs/UsersManual.html</a>).The Intel library
orIBM decnumber should work, but would need to be checked.<br /><br /> See <a
href="http://www.ac.usc.es/arith19/sites/default/files/3670a225-spec-session-DFP-paper2.pdf">http://www.ac.usc.es/arith19/sites/default/files/3670a225-spec-session-DFP-paper2.pdf</a>
for(somewhat old) details on Intel processor support. There's also support in POWER6 and System z10 according to
commentson the Intel library article.<br /><br /><br /> So ... thoughts/comments? Think this is a reasonable avenue to
pursue?<br/><br /> I'd be inclined to start by adding basic DECIMAL32, DECIMAL64 and DECIMAL128 support with an
implicitpromotion to NUMERIC available. After that the idea would be to progressively add operators and functions that
workeddirectly on these types, letting the numeric versions handle what wasn't implemented for decimal yet. Finally I'd
wantto change the parser's interpretation of qualified DECIMAL to translate to DECIMAL32/64/128 as appropriate.<br
/><br/> This isn't work I have any funded time for, so to the degree I could do it at all it'd be something I'd be
takingon as a project out of personal interest and for learning. That means "not fast".<br /><pre class="moz-signature"
cols="72">--Craig Ringer                   <a class="moz-txt-link-freetext"
href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQLDevelopment, 24x7 Support, Training &
Services</pre>