Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Mon, Jun 19, 2017 at 3:47 PM, Peter Geoghegan  wrote:
> On Mon, Jun 19, 2017 at 12:19 PM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Peter Geoghegan]

On Mon, Jun 19, 2017 at 12:19 PM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Mon, Jun 19, 2017 at 1:10 PM, Peter Geoghegan  wrote:
> On Mon, Jun 19, 2017 at 10:00 AM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Peter Geoghegan]

On Mon, Jun 19, 2017 at 10:00 AM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Mon, Jun 19, 2017 at 12:18 PM, Tom Lane  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

Re: [HACKERS] Decimal64 and Decimal128

[Tom Lane]

Robert Haas  writes:
> On Sun, Jun 18, 2017 at 6:28 PM, Thomas Munro
>  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

Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Sun, Jun 18, 2017 at 6:28 PM, Thomas Munro
 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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Mon, Jun 19, 2017 at 2:24 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Sat, Jun 17, 2017 at 11:58 PM, Thomas Munro
>>  wrote:
>>> On Sun, Jun 18, 2017 at 2:31 PM, Robert Haas  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] seems to be the more popular and is about
20kloc with a choice of ICU or GPL license.  pgdecimal[3] (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] 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] 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

Re: [HACKERS] Decimal64 and Decimal128

[Tom Lane]

Robert Haas  writes:
> On Sat, Jun 17, 2017 at 11:58 PM, Thomas Munro
>  wrote:
>> On Sun, Jun 18, 2017 at 2:31 PM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Sat, Jun 17, 2017 at 11:58 PM, Thomas Munro
 wrote:
> On Sun, Jun 18, 2017 at 2:31 PM, Robert Haas  wrote:
>> On Sat, Jun 17, 2017 at 3:50 PM, Thomas Munro
>>  wrote:
>>> On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Sun, Jun 18, 2017 at 2:31 PM, Robert Haas  wrote:
> On Sat, Jun 17, 2017 at 3:50 PM, Thomas Munro
>  wrote:
>> On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas  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

Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Sat, Jun 17, 2017 at 3:50 PM, Thomas Munro
 wrote:
> On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas  wrote:
>> On Thu, Jun 15, 2017 at 10:27 PM, Thomas Munro
>>  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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Sun, Jun 18, 2017 at 5:38 AM, Robert Haas  wrote:
> On Thu, Jun 15, 2017 at 10:27 PM, Thomas Munro
>  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

Re: [HACKERS] Decimal64 and Decimal128

[Robert Haas]

On Thu, Jun 15, 2017 at 10:27 PM, Thomas Munro
 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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Fri, Jun 16, 2017 at 1:24 PM, Craig Ringer  wrote:
> On 16 June 2017 at 05:42, Thomas Munro  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[()] (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

Re: [HACKERS] Decimal64 and Decimal128

[Craig Ringer]

On 16 June 2017 at 05:42, Thomas Munro  wrote:
> On Fri, Sep 25, 2015 at 5:06 PM, Pavel Stehule  
> wrote:
>> 2015-09-25 0:25 GMT+02:00 Jim Nasby :
>>>
>>> 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[()] (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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Fri, Sep 25, 2015 at 5:06 PM, Pavel Stehule  wrote:
> 2015-09-25 0:25 GMT+02:00 Jim Nasby :
>>
>> 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[()] (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

Re: [HACKERS] Decimal64 and Decimal128

[Pavel Stehule]

2015-09-25 0:25 GMT+02:00 Jim Nasby :

> 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


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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Fri, Sep 25, 2015 at 10:25 AM, Jim Nasby  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

Re: [HACKERS] Decimal64 and Decimal128

[Josh Berkus]

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

Re: [HACKERS] Decimal64 and Decimal128

[Jim Nasby]

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

Re: [HACKERS] Decimal64 and Decimal128

[Thomas Munro]

On Fri, Sep 25, 2015 at 9:23 AM, Feng Tian  wrote:
>
>
> On Thu, Sep 24, 2015 at 2:17 PM, Feng Tian  wrote:
>>
>>
>>
>> On Thu, Sep 24, 2015 at 1:55 PM, Peter Geoghegan  wrote:
>>>
>>> On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane  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] (this maps to IBM hardware if available,
or decNum otherwise)
3.  The Intel library
4.  The future built-in C language support[2] (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

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

Re: [HACKERS] Decimal64 and Decimal128

[David Rowley]

On 25 September 2015 at 08:29, Feng Tian  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/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Decimal64 and Decimal128

[Feng Tian]

On Thu, Sep 24, 2015 at 2:17 PM, Feng Tian  wrote:

>
>
> On Thu, Sep 24, 2015 at 1:55 PM, Peter Geoghegan  wrote:
>
>> On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane  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.

Re: [HACKERS] Decimal64 and Decimal128

[Feng Tian]

On Thu, Sep 24, 2015 at 1:55 PM, Peter Geoghegan  wrote:

> On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane  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,

Re: [HACKERS] Decimal64 and Decimal128

[Peter Geoghegan]

On Thu, Sep 24, 2015 at 1:53 PM, Tom Lane  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

Re: [HACKERS] Decimal64 and Decimal128

[Tom Lane]

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

Re: [HACKERS] Decimal64 and Decimal128

[Peter Geoghegan]

On Thu, Sep 24, 2015 at 1:29 PM, Feng Tian  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

Re: [HACKERS] Decimal64 and Decimal128

[Feng Tian]

On Thu, Sep 24, 2015 at 1:29 PM, Feng Tian  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
>
>
> http://www.postgresql.org/message-id/CAFj8pRApakE6s-H2yJcXD=ubpukwa6i7rx4vuvtb4puhga5...@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

[HACKERS] Decimal64 and Decimal128

[Feng Tian]

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

http://www.postgresql.org/message-id/CAFj8pRApakE6s-H2yJcXD=ubpukwa6i7rx4vuvtb4puhga5...@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