Decimal64 and Decimal128

On Thu, Sep 24, 2015 at 1:29 PM, Feng Tian <ftian@vitessedata.com> wrote:

Hi,

Here is an extension for 64 and 128 bit decimal types using IEEE decimal
floating point. The original idea/implementation is from
http://pgxn.org/dist/pgdecimal/1.0.0/ Original thread for dicussion is
at

/messages/by-id/CAFj8pRApakE6s-H2yJcXD=UBpukWA6i7rx4VUVTb4PUHgA5FeA@mail.gmail.com

I reimplemented 64/128 bits instead of 32/64 bits. The code use decNumber
library instead of _Decimal64/128 of GCC. Also added more operators.

Compared to numeric type, decimal64 arithmetics is about 2x faster,
decimal128 is about 1.5x faster. However, the cast between decimal and
float4/8 is implemented rather naively and slow. As always, it depends on
workload, decimal may take more, or less space, may be slower if cast is
frequently performed.

Agains, thanks to the original author okbob (Pavel). Enjoy.

Thanks,
Feng

Ah, link.

https://github.com/vitesse-ftian/pgdecimal

Thanks,
Feng

On Thu, Sep 24, 2015 at 1:29 PM, Feng Tian <ftian@vitessedata.com> wrote:

Here is an extension for 64 and 128 bit decimal types using IEEE decimal
floating point. The original idea/implementation is from
http://pgxn.org/dist/pgdecimal/1.0.0/

Interesting. A default B-Tree operator class for the decimal types
would be nice.

I would worry about the implicit casts you've added. They might cause problems.

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Feng Tian <ftian@vitessedata.com> writes:

Ah, link.
https://github.com/vitesse-ftian/pgdecimal

Please include the actual patch as an attachment. We do not consider mere
URLs to be acceptable patch submission format, because that provides no
permanent record in our archives of what was submitted.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please include the actual patch as an attachment. We do not consider mere
URLs to be acceptable patch submission format, because that provides no
permanent record in our archives of what was submitted.

I was under the impression that this was not intended as a patch submission.

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Sep 24, 2015 at 1:55 PM, Peter Geoghegan <pg@heroku.com> wrote:

On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please include the actual patch as an attachment. We do not consider

mere

URLs to be acceptable patch submission format, because that provides no
permanent record in our archives of what was submitted.

I was under the impression that this was not intended as a patch
submission.

--
Peter Geoghegan

If there is enough interest, would be great for it to go into the official
contrib dir.
Thanks,

On Thu, Sep 24, 2015 at 2:17 PM, Feng Tian <ftian@vitessedata.com> wrote:

On Thu, Sep 24, 2015 at 1:55 PM, Peter Geoghegan <pg@heroku.com> wrote:

On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please include the actual patch as an attachment. We do not consider

mere

URLs to be acceptable patch submission format, because that provides no
permanent record in our archives of what was submitted.

I was under the impression that this was not intended as a patch
submission.

--
Peter Geoghegan

If there is enough interest, would be great for it to go into the official
contrib dir.
Thanks,

Second thought, the extension depends on decNumber, which is either GPL,

or ICU license. Maybe this is trouble.

On 25 September 2015 at 08:29, Feng Tian <ftian@vitessedata.com> wrote:

Compared to numeric type, decimal64 arithmetics is about 2x faster,
decimal128 is about 1.5x faster. However, the cast between decimal and
float4/8 is implemented rather naively and slow. As always, it depends on
workload, decimal may take more, or less space, may be slower if cast is
frequently performed.

Are you able to share the processor vendor, and perhaps some other specs of
the machine you obtained these results from?

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/&gt;
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Sep 25, 2015 at 9:23 AM, Feng Tian <ftian@vitessedata.com> wrote:

On Thu, Sep 24, 2015 at 2:17 PM, Feng Tian <ftian@vitessedata.com> wrote:

On Thu, Sep 24, 2015 at 1:55 PM, Peter Geoghegan <pg@heroku.com> wrote:

On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please include the actual patch as an attachment. We do not consider
mere
URLs to be acceptable patch submission format, because that provides no
permanent record in our archives of what was submitted.

I was under the impression that this was not intended as a patch
submission.

--
Peter Geoghegan

If there is enough interest, would be great for it to go into the official
contrib dir.
Thanks,

Second thought, the extension depends on decNumber, which is either GPL, or
ICU license. Maybe this is trouble.

This is a very cool feature. I would be great to get a useful class
of decimal numbers into a pass-by-value fixed sized standardised data
type.

The Intel BID library seems to have a more permissive license at first
glance. I have heard that the Intel library is faster than the IBM
library at a variety of arithmetic and conversions (YMMV; I saw an
unpublished benchmark result that I can't share) on commodity hardware
at least, and it would be interesting to test that. I wonder if BID
(a single integer significand field) is inherently better for software
implementations than DPD (the significand as an array of 10 bit wide
base-1000 digits called "declets", not entirely unlike our numeric's
encoding). Those using POWER hardware might want the option to use
DPD though, because they have hardware support for that.

Perhaps ideally there could be a build option to use any of the following:

1. The IBM decNum library
2. The IBM DFPAL library[1]http://speleotrove.com/decimal/dfpal/dfpalugaio.html (this maps to IBM hardware if available,
or decNum otherwise)
3. The Intel library
4. The future built-in C language support[2]http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1312.pdf, http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1781.pdf (which could use either
binary format!), currently only a proposal but already implemented by
IBM XL C and GCC (using the libraries above)

I have a suspicion that if only one of those has to be chosen, the
Intel library would be best for the majority of users based on license
+ performances.

[1]: http://speleotrove.com/decimal/dfpal/dfpalugaio.html
[2]: http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1312.pdf, http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1781.pdf
http://www.open-std.org/jtc1/sc22/wg14/www/docs/n1781.pdf

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 9/24/15 3:35 PM, Peter Geoghegan wrote:

I would worry about the implicit casts you've added. They might cause problems.

Given the cycle created between numeric->decimal and decimal->numeric, I
can pretty much guarantee they will. In any case, I don't think implicit
casting from numeric->decimal is a good idea since it can overflow. I'm
not sure that the other direction is safe either... I can't remember
offhand if casting correctly obeys typmod or not.

BTW, have you talked to Pavel about making these changes to his code?
Seems a shame to needlessly fork it. :/
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 09/24/2015 02:23 PM, Feng Tian wrote:

If there is enough interest, would be great for it to go into the
official contrib dir.
Thanks,

Second thought, the extension depends on decNumber, which is either GPL,
or ICU license. Maybe this is trouble.

Yes. Please just build an external extension and submit it to PGXN.
Thanks!

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Sep 25, 2015 at 10:25 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 9/24/15 3:35 PM, Peter Geoghegan wrote:

I would worry about the implicit casts you've added. They might cause
problems.

Given the cycle created between numeric->decimal and decimal->numeric, I can
pretty much guarantee they will. In any case, I don't think implicit casting
from numeric->decimal is a good idea since it can overflow. I'm not sure
that the other direction is safe either... I can't remember offhand if
casting correctly obeys typmod or not.

FWIW it looks like DB2 promotes DECIMAL to DECFLOAT, not the other way around.

https://www-304.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_promotionofdatatypes.dita

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-09-25 0:25 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 9/24/15 3:35 PM, Peter Geoghegan wrote:

I would worry about the implicit casts you've added. They might cause
problems.

Given the cycle created between numeric->decimal and decimal->numeric, I
can pretty much guarantee they will. In any case, I don't think implicit
casting from numeric->decimal is a good idea since it can overflow. I'm not
sure that the other direction is safe either... I can't remember offhand if
casting correctly obeys typmod or not.

BTW, have you talked to Pavel about making these changes to his code?
Seems a shame to needlessly fork it. :/

yes, he talked with me, and I gave a agreement to continue/enhance/fork
this project how will be necessary

Regards

Pavel

On Fri, Sep 25, 2015 at 5:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-09-25 0:25 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 9/24/15 3:35 PM, Peter Geoghegan wrote:

I would worry about the implicit casts you've added. They might cause
problems.

Given the cycle created between numeric->decimal and decimal->numeric, I
can pretty much guarantee they will. In any case, I don't think implicit
casting from numeric->decimal is a good idea since it can overflow. I'm not
sure that the other direction is safe either... I can't remember offhand if
casting correctly obeys typmod or not.

BTW, have you talked to Pavel about making these changes to his code?
Seems a shame to needlessly fork it. :/

yes, he talked with me, and I gave a agreement to continue/enhance/fork this
project how will be necessary

Bumping this ancient thread to say that DECFLOAT appears to have
landed in the SQL standard. I haven't looked at SQL:2016 myself by I
just saw this on Markus Winand's Modern SQL blog:

"There is a new type decfloat[(<precision>)] (T076)."

http://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

So far it's supported only by DB2 (inventor) and FirebirdSQL has just
announced support in the next release.

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 16 June 2017 at 05:42, Thomas Munro <thomas.munro@enterprisedb.com> wrote:

On Fri, Sep 25, 2015 at 5:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-09-25 0:25 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 9/24/15 3:35 PM, Peter Geoghegan wrote:

I would worry about the implicit casts you've added. They might cause
problems.

Given the cycle created between numeric->decimal and decimal->numeric, I
can pretty much guarantee they will. In any case, I don't think implicit
casting from numeric->decimal is a good idea since it can overflow. I'm not
sure that the other direction is safe either... I can't remember offhand if
casting correctly obeys typmod or not.

BTW, have you talked to Pavel about making these changes to his code?
Seems a shame to needlessly fork it. :/

yes, he talked with me, and I gave a agreement to continue/enhance/fork this
project how will be necessary

Bumping this ancient thread to say that DECFLOAT appears to have
landed in the SQL standard. I haven't looked at SQL:2016 myself by I
just saw this on Markus Winand's Modern SQL blog:

"There is a new type decfloat[(<precision>)] (T076)."

http://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

So far it's supported only by DB2 (inventor) and FirebirdSQL has just
announced support in the next release.

I was pretty excited by decimal floating point initially, but the lack
of support for its use in hardware in commonplace CPUs makes me less
thrilled. IIRC Intel was talking about adding it, but I can't find any
references to that anymore. POWER6 and POWER7 has it, which is great,
but hardly justifies a push for getting it into the core Pg.

Some of the discussion on
https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library?page=1
suggests that doing it fully in hardware is very expensive, so a mixed
software/microcode implementation with some hardware assistance is
likely if/when it comes.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jun 16, 2017 at 1:24 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

On 16 June 2017 at 05:42, Thomas Munro <thomas.munro@enterprisedb.com> wrote:

Bumping this ancient thread to say that DECFLOAT appears to have
landed in the SQL standard. I haven't looked at SQL:2016 myself by I
just saw this on Markus Winand's Modern SQL blog:

"There is a new type decfloat[(<precision>)] (T076)."

http://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

So far it's supported only by DB2 (inventor) and FirebirdSQL has just
announced support in the next release.

I was pretty excited by decimal floating point initially, but the lack
of support for its use in hardware in commonplace CPUs makes me less
thrilled. IIRC Intel was talking about adding it, but I can't find any
references to that anymore. POWER6 and POWER7 has it, which is great,
but hardly justifies a push for getting it into the core Pg.

Some of the discussion on
https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library?page=1
suggests that doing it fully in hardware is very expensive, so a mixed
software/microcode implementation with some hardware assistance is
likely if/when it comes.

There are considerations other than raw arithmetic performance though:

1. They are fixed size, and DECFLOAT(9) [= 32 bit] and DECFLOAT(17)
[= 64 bit] could in theory be passed by value. Of course we don't
have a way to make those pass-by-value and yet pass DECFLOAT(34) [=
128 bit] by reference! That is where I got stuck last time I was
interested in this subject, because that seems like the place where we
would stand to gain a bunch of performance, and yet the limited
technical factors seems to be very well baked into Postgres.

2. They may be increasingly used as 'vocabulary' datatypes as more
languages and databases adopt them. That's probably not a huge
semantic problem since DECIMAL can represent most useful DECFLOAT
values exactly (but not some IEEE 754 quirks like -0, -inf, +inf, NaN,
and I haven't checked what SQL:2016 says about that anyway but if it's
based directly on IEEE 754:2008 then I guess they'll be in there).

I don't understand these things but it looks like the support in C
(originally proposed as N1312 and implemented by IBM, HP, GCC and
Intel compilers) has reached the next stage and been published as
ISO/IEC TS 18661-2:2015, and now N2079 proposes that TS 18661-2 be
absorbed into C2x (!). As glacial as ISO processes may be, it's
encouraging that there is now a TS even though I'm not allowed to
download it without paying CHF178. Meanwhile Python and others just
did it (albeit vastly less efficiently).

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 15, 2017 at 10:27 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

1. They are fixed size, and DECFLOAT(9) [= 32 bit] and DECFLOAT(17)
[= 64 bit] could in theory be passed by value. Of course we don't
have a way to make those pass-by-value and yet pass DECFLOAT(34) [=
128 bit] by reference! That is where I got stuck last time I was
interested in this subject, because that seems like the place where we
would stand to gain a bunch of performance, and yet the limited
technical factors seems to be very well baked into Postgres.

I feel like these would logically just be different types, like int4
and int8 are. We don't have integer(9) and integer(18).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jun 15, 2017 at 10:27 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

1. They are fixed size, and DECFLOAT(9) [= 32 bit] and DECFLOAT(17)
[= 64 bit] could in theory be passed by value. Of course we don't
have a way to make those pass-by-value and yet pass DECFLOAT(34) [=
128 bit] by reference! That is where I got stuck last time I was
interested in this subject, because that seems like the place where we
would stand to gain a bunch of performance, and yet the limited
technical factors seems to be very well baked into Postgres.

I feel like these would logically just be different types, like int4
and int8 are. We don't have integer(9) and integer(18).

Hmm. Perhaps format_type.c could render decfloat16 as decfloat(16)
and decfloat34 as decfloat(34), and gram.y could have a production
that selects the right one when you write DECFLOAT(x) and rejects
values of x other than 16 and 34.

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Jun 17, 2017 at 3:50 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jun 15, 2017 at 10:27 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

1. They are fixed size, and DECFLOAT(9) [= 32 bit] and DECFLOAT(17)
[= 64 bit] could in theory be passed by value. Of course we don't
have a way to make those pass-by-value and yet pass DECFLOAT(34) [=
128 bit] by reference! That is where I got stuck last time I was
interested in this subject, because that seems like the place where we
would stand to gain a bunch of performance, and yet the limited
technical factors seems to be very well baked into Postgres.

I feel like these would logically just be different types, like int4
and int8 are. We don't have integer(9) and integer(18).

Hmm. Perhaps format_type.c could render decfloat16 as decfloat(16)
and decfloat34 as decfloat(34), and gram.y could have a production
that selects the right one when you write DECFLOAT(x) and rejects
values of x other than 16 and 34.

What would be the point of that?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jun 18, 2017 at 2:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Jun 17, 2017 at 3:50 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas <robertmhaas@gmail.com> wrote:

I feel like these would logically just be different types, like int4
and int8 are. We don't have integer(9) and integer(18).

Hmm. Perhaps format_type.c could render decfloat16 as decfloat(16)
and decfloat34 as decfloat(34), and gram.y could have a production
that selects the right one when you write DECFLOAT(x) and rejects
values of x other than 16 and 34.

What would be the point of that?

We'd accept and display the new SQL:2016 standard type name with
length, but by mapping it onto different internal types we could use a
pass-by-value type when it fits in a Datum.

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers