Обсуждение: Decimal64 and Decimal128
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
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
On Thu, Sep 24, 2015 at 1:29 PM, Feng Tian <ftian@vitessedata.com> wrote:
https://github.com/vitesse-ftian/pgdecimalHi,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 atI 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.
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
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
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
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 GeogheganIf 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/
David Rowley http://www.2ndQuadrant.com/
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] (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
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
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
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
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
--
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
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
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
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
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
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
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
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
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
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
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] 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
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
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
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
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
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
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
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
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
regards, tom lane