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

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

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.

Uggh. I'll repeat what has been said on this mailing list many times
before: the SQL standards committee often seems to make life
unnecessarily difficult with its choice of syntax.

--
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

Robert Haas <robertmhaas@gmail.com> writes:

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

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

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.

Uggh. I'll repeat what has been said on this mailing list many times
before: the SQL standards committee often seems to make life
unnecessarily difficult with its choice of syntax.

We could do what we did with FLOAT(n), which is to accept the new
typename syntax but convert it to simple typenames decfloatN, and
not worry about reversing the transformation on output.

But the real question is whether we want to get that deeply invested
in a type that couldn't be considered standard for many years to come.
(Unless somebody wants to write an all-software fallback implementation,
which I sure don't.)

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 Mon, Jun 19, 2017 at 2:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

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

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

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.

Uggh. I'll repeat what has been said on this mailing list many times
before: the SQL standards committee often seems to make life
unnecessarily difficult with its choice of syntax.

We could do what we did with FLOAT(n), which is to accept the new
typename syntax but convert it to simple typenames decfloatN, and
not worry about reversing the transformation on output.

But the real question is whether we want to get that deeply invested
in a type that couldn't be considered standard for many years to come.
(Unless somebody wants to write an all-software fallback implementation,
which I sure don't.)

There are already two well known all-software implementations:

1. IBM's decNumber[1]http://speleotrove.com/decimal/ seems to be the more popular and is about
20kloc with a choice of ICU or GPL license. pgdecimal[3]https://github.com/vitesse-ftian/pgdecimal (the
experimental extension by Feng Tian and Pavel Stehule that this thread
announced) uses that (an earlier version used the C language extension
types like _Decimal64 instead). Several projects seem to be using it
in-tree, including GCC.
2. Intel's RDFPMathLib[2]https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library is much larger.

So I guess the questions would be:

1. Intel or IBM?
2. In tree or out of tree dependency?
3. Also support the new C TS extension types (_Decimal64 etc) as an
alternative for C compilers that have the extension, for the benefit
of xlc/POWER systems?

I speculate that decNumber in-tree would be the path of least
resistance (assuming the "ICU 1.8.1 and later" license[4]https://spdx.org/licenses/ICU.html would be
acceptable -- to my untrained eye it looks rather BSD-ish -- and
20kloc isn't viewed as excessive), and further that a standard
compliant version might have some good reasons to be in core rather
than in an extension like pgdecimal:

1. We'd need gram.y + format_type.c support to get the property I
mentioned above (standard typename mapping to more than one internal
type in order to get pass-by-value for good performance with the
Datum-sized variant).
2. There are probably some casts and conversions among this and the
existing number types and rules for parsing constants etc that finish
up needing core changes.

[1]: http://speleotrove.com/decimal/
[2]: https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library
[3]: https://github.com/vitesse-ftian/pgdecimal
[4]: https://spdx.org/licenses/ICU.html

--
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 Sun, Jun 18, 2017 at 6:28 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

I speculate that decNumber in-tree would be the path of least
resistance (assuming the "ICU 1.8.1 and later" license[4] would be
acceptable -- to my untrained eye it looks rather BSD-ish -- and
20kloc isn't viewed as excessive), and further that a standard
compliant version might have some good reasons to be in core rather
than in an extension like pgdecimal:

I'm not sure it's a good idea to import code under another license,
but leaving that aside, are you volunteering to port every future
change made by the upstream project to our proposed in-tree copy, from
the day the patch is committed until forever? We've had a few
previous run-ins with this sort of thing: the time zone files, the
regular expression engine, the snowball stuff. They're not
fantastically high-maintenance but Tom definitely spends some amount
of time on a fairly regular basis updating them and porting over
changes, and they cause hassles with pgindent and so forth as well.
We should have a very compelling reason for increasing the number of
such hassles -- and, for me, this feature would not clear that bar.

I think that if one or both of these libraries are commonly-packaged
things that are reasonably likely to be installable on newer operating
system images using yum/apt-get/port/emerge/whatever then it would be
fine to have a configure switch --with-decfloat or whatever, which
when used includes support for PostgreSQL data types that use the
library. If those libraries aren't sufficiently commonly-packaged
that this will be realistic option for people, then I vote against
depending on them. In that case, we could have our own, from-scratch,
clean-room implementation that does not depend on anybody else's code
under some other license, or we could wait and see if they become more
mainstream.

--
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

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Jun 18, 2017 at 6:28 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

I speculate that decNumber in-tree would be the path of least
resistance (assuming the "ICU 1.8.1 and later" license[4] would be
acceptable -- to my untrained eye it looks rather BSD-ish -- and
20kloc isn't viewed as excessive), and further that a standard
compliant version might have some good reasons to be in core rather
than in an extension like pgdecimal:

We should have a very compelling reason for increasing the number of
such hassles -- and, for me, this feature would not clear that bar.

It would be interesting to get some handle on the performance differences
between decNumber and our existing NUMERIC implementation. I'm a little
skeptical that they'd be so enormous as to make this an interesting
project, but I could be wrong.

Obviously, the answer could be very different when considering a
mostly-hardware implementation. But until those are fairly readily
available, it's hard to believe very many people will be excited.

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 Mon, Jun 19, 2017 at 12:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It would be interesting to get some handle on the performance differences
between decNumber and our existing NUMERIC implementation. I'm a little
skeptical that they'd be so enormous as to make this an interesting
project, but I could be wrong.

I've never been very happy with the performance of numeric, so I guess
I'm a bit more optimistic about the chances of doing better. Aside
from any computational optimizations, the fact that the datatype could
be pass-by-value rather than a varlena might speed things up quite a
bit in some cases.

On the other hand, the 8-byte version has a decent chance of being
larger on disk than the numeric representation - e.g. $123,456.78 is
only 7 bytes as a short varlena, and won't induce padding out to the
next 8-byte boundary. And it looks to me like the 4-byte version
can't represent that quantity at all. That combination of facts seems
like a big problem to me. A decimal representation that can't handle
more than 7 digits is going to unsuitable for many applications, and
being bigger than our existing numeric on disk for many
commonly-represented values would be awful.

--
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 Mon, Jun 19, 2017 at 10:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:

I've never been very happy with the performance of numeric, so I guess
I'm a bit more optimistic about the chances of doing better. Aside
from any computational optimizations, the fact that the datatype could
be pass-by-value rather than a varlena might speed things up quite a
bit in some cases.

What cases do you have in mind?

--
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 Mon, Jun 19, 2017 at 1:10 PM, Peter Geoghegan <pg@bowt.ie> wrote:

On Mon, Jun 19, 2017 at 10:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:

I've never been very happy with the performance of numeric, so I guess
I'm a bit more optimistic about the chances of doing better. Aside
from any computational optimizations, the fact that the datatype could
be pass-by-value rather than a varlena might speed things up quite a
bit in some cases.

What cases do you have in mind?

I don't have a specific use case in mind. However, datumCopy() is
sure to be a lot faster when typByVal is true, and see also the
documentation changes in commit
8472bf7a73487b0535c95e299773b882f7523463.

--
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 Mon, Jun 19, 2017 at 12:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I don't have a specific use case in mind. However, datumCopy() is
sure to be a lot faster when typByVal is true, and see also the
documentation changes in commit
8472bf7a73487b0535c95e299773b882f7523463.

Fair enough.

I ask because at one time I informally benchmarked Postgres (using
pgbench), where int4 (or maybe int8) primary keys were replaced with
equivalent numeric primary keys. This was a SELECT benchmark. Anyway,
the conclusion at the time was that it makes surprisingly little
difference (I think it was ~5%), because cache misses dominate anyway,
and the page layout doesn't really change (the fan-in didn't change
*at all* either, at least for this one case, because of alignment
considerations). I never published this result, because I didn't have
time to test rigorously, and wasn't sure that there was sufficient
interest.

This was intended to confirm my intuition that cache misses were by
far the main bottleneck (profiling also helped). I was thinking about
putting abbreviated keys within internal B-Tree pages at the time
(probably interleaved with the ItemIdData array). I've since realized
that prefix compression is more or less prerequisite (to get value
from a 1 or 2 byte abbreviated key), and that there are some painful
issues with collations + text. You probably need to encode each
internal page IndexTuple as a simple binary string that you always
just memcmp() in a type/tuple descriptor agnostic fashion, leaving
compression, truncation, and abbreviation as relatively trivial tasks.
This is all very difficult, of course, which is why it wasn't
seriously pursued.

--
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 Mon, Jun 19, 2017 at 3:47 PM, Peter Geoghegan <pg@bowt.ie> wrote:

On Mon, Jun 19, 2017 at 12:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I don't have a specific use case in mind. However, datumCopy() is
sure to be a lot faster when typByVal is true, and see also the
documentation changes in commit
8472bf7a73487b0535c95e299773b882f7523463.

Fair enough.

I ask because at one time I informally benchmarked Postgres (using
pgbench), where int4 (or maybe int8) primary keys were replaced with
equivalent numeric primary keys. This was a SELECT benchmark. Anyway,
the conclusion at the time was that it makes surprisingly little
difference (I think it was ~5%), because cache misses dominate anyway,
and the page layout doesn't really change (the fan-in didn't change
*at all* either, at least for this one case, because of alignment
considerations). I never published this result, because I didn't have
time to test rigorously, and wasn't sure that there was sufficient
interest.

People work pretty hard for a 5% performance improvement, so I
wouldn't dismiss that difference as nothing. However, I think the
difference would probably be larger if you were using the values for
computations (e.g. sum, avg) rather than as PKs.

--
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 Fri, Jun 16, 2017 at 9:42 AM 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:

... and it has just been voted into the next revision of the C language:

https://gustedt.wordpress.com/2018/11/12/c2x/

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

On 13 November 2018 at 10:39, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

... and it has just been voted into the next revision of the C language:

https://gustedt.wordpress.com/2018/11/12/c2x/

Nice. Maybe we can get DECFLOAT into core around PostgreSQL 32 or so :-)

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

On Tue, Nov 13, 2018 at 11:01:33AM +1300, David Rowley wrote:

On 13 November 2018 at 10:39, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

... and it has just been voted into the next revision of the C language:

https://gustedt.wordpress.com/2018/11/12/c2x/

Nice. Maybe we can get DECFLOAT into core around PostgreSQL 32 or so :-)

That's the same schedule we were on for C99, assuming linearity. If
instead we assume that the speed increases with, say, more developers,
it seems reasonable to imagine that we'd have optional C2X features in
PostgreSQL 14 or 15, assuming support for it in at least two common
compiler toolchains ;)

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi,

On 2018-11-12 23:51:35 +0100, David Fetter wrote:

On Tue, Nov 13, 2018 at 11:01:33AM +1300, David Rowley wrote:

On 13 November 2018 at 10:39, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

... and it has just been voted into the next revision of the C language:

https://gustedt.wordpress.com/2018/11/12/c2x/

Nice. Maybe we can get DECFLOAT into core around PostgreSQL 32 or so :-)

That's the same schedule we were on for C99, assuming linearity. If
instead we assume that the speed increases with, say, more developers,
it seems reasonable to imagine that we'd have optional C2X features in
PostgreSQL 14 or 15, assuming support for it in at least two common
compiler toolchains ;)

I don't think developer time is particularly relevant here. C99 adoption
wasn't limited by somebody doing the work to make it so, but the desire
to support some old platforms. I'm personally perfectly fine with being
more aggressive around that, but there are some other quarters that are
more resistant to such ideas... But even if we're more aggressive, 15
seems quite unrealistic - there'll be a lot of platforms that won't have
a bleeding edge version of $compiler.

Greetings,

Andres Freund

On Mon, Nov 12, 2018 at 02:57:37PM -0800, Andres Freund wrote:

Hi,

On 2018-11-12 23:51:35 +0100, David Fetter wrote:

On Tue, Nov 13, 2018 at 11:01:33AM +1300, David Rowley wrote:

On 13 November 2018 at 10:39, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

... and it has just been voted into the next revision of the C language:

https://gustedt.wordpress.com/2018/11/12/c2x/

Nice. Maybe we can get DECFLOAT into core around PostgreSQL 32 or so :-)

That's the same schedule we were on for C99, assuming linearity. If
instead we assume that the speed increases with, say, more developers,
it seems reasonable to imagine that we'd have optional C2X features in
PostgreSQL 14 or 15, assuming support for it in at least two common
compiler toolchains ;)

I don't think developer time is particularly relevant here. C99 adoption
wasn't limited by somebody doing the work to make it so, but the desire
to support some old platforms. I'm personally perfectly fine with being
more aggressive around that, but there are some other quarters that are
more resistant to such ideas... But even if we're more aggressive, 15
seems quite unrealistic - there'll be a lot of platforms that won't have
a bleeding edge version of $compiler.

So if this got added to a lot of compilers, that might suffice.

Does this have any coupling to the C++ integration, or is it pretty
much orthogonal?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 2018-11-13 00:01:49 +0100, David Fetter wrote:

On Mon, Nov 12, 2018 at 02:57:37PM -0800, Andres Freund wrote:

Hi,

On 2018-11-12 23:51:35 +0100, David Fetter wrote:

On Tue, Nov 13, 2018 at 11:01:33AM +1300, David Rowley wrote:

On 13 November 2018 at 10:39, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

... and it has just been voted into the next revision of the C language:

https://gustedt.wordpress.com/2018/11/12/c2x/

Nice. Maybe we can get DECFLOAT into core around PostgreSQL 32 or so :-)

That's the same schedule we were on for C99, assuming linearity. If
instead we assume that the speed increases with, say, more developers,
it seems reasonable to imagine that we'd have optional C2X features in
PostgreSQL 14 or 15, assuming support for it in at least two common
compiler toolchains ;)

I don't think developer time is particularly relevant here. C99 adoption
wasn't limited by somebody doing the work to make it so, but the desire
to support some old platforms. I'm personally perfectly fine with being
more aggressive around that, but there are some other quarters that are
more resistant to such ideas... But even if we're more aggressive, 15
seems quite unrealistic - there'll be a lot of platforms that won't have
a bleeding edge version of $compiler.

So if this got added to a lot of compilers, that might suffice.

No, unless those compiler versions will automatically be available in
older distros. Which they won't.

Does this have any coupling to the C++ integration, or is it pretty
much orthogonal?

Seems largely orthogonal.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:

On 2018-11-13 00:01:49 +0100, David Fetter wrote:

So if this got added to a lot of compilers, that might suffice.

No, unless those compiler versions will automatically be available in
older distros. Which they won't.

Yeah. I think putting this in core is a long way off. Maybe somebody
will write an extension instead.

regards, tom lane

út 13. 11. 2018 v 0:55 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Andres Freund <andres@anarazel.de> writes:

On 2018-11-13 00:01:49 +0100, David Fetter wrote:

So if this got added to a lot of compilers, that might suffice.

No, unless those compiler versions will automatically be available in
older distros. Which they won't.

Yeah. I think putting this in core is a long way off. Maybe somebody
will write an extension instead.

It is exists already https://github.com/okbob/pgDecimal - it is just
experimental

Maybe this code can be more interesting due JIT support.

Regards

Pavel