Thread: New version of money type

New version of money type

From
"D'Arcy J.M. Cain"
Date:
For years I have been promising that a 64 bit version of the money type
was on the way.  Here it is.  So far it compiles and I have done some
basic testing on it and it seems to work fine.  Note that the currency
symbol is also dropped on output as well but it is accepted on input.

darcy=# select '$92,233,720,368,547,758.07'::money;          money
---------------------------- 92,233,720,368,547,758.07
(1 row)


Index: src/backend/utils/adt/cash.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v
retrieving revision 1.68
diff -u -p -u -r1.68 cash.c
--- src/backend/utils/adt/cash.c    14 Jul 2006 14:52:23
-0000    1.68 +++ src/backend/utils/adt/cash.c    14 Sep 2006
14:28:30 -0000 @@ -1,13 +1,17 @@/* * cash.c * Written by D'Arcy J.M. Cain
+ * darcy@druid.net
+ * http://www.druid.net/darcy/ * * Functions to allow input and output of money normally but store
- * and handle it as int4s
+ * and handle it as 64 bit ints * * A slightly modified version of this file and a discussion of the * workings can be
foundin the book "Software Solutions in C" by
 
- * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
+ * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
+ * this version handles 64 bit numbers and so can hold values up to
+ * $92,233,720,368,547,758.07. * * $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14
14:52:23 momjian Exp $ */
@@ -23,17 +27,12 @@#include "utils/cash.h"#include "utils/pg_locale.h"
-
-static const char *num_word(Cash value);
-
-/* when we go to 64 bit values we will have to modify this */
-#define CASH_BUFSZ        24
+#define CASH_BUFSZ        36#define TERMINATOR        (CASH_BUFSZ - 1)#define LAST_PAREN        (TERMINATOR -
1)#defineLAST_DIGIT        (LAST_PAREN - 1)
 
-/* * Cash is a pass-by-ref SQL type, so we must pass and return pointers. * These macros and support routine hide the
pass-by-refness.
@@ -41,6 +40,65 @@ static const char *num_word(Cash value);#define PG_GETARG_CASH(n)  (* ((Cash *)
PG_GETARG_POINTER(n)))#definePG_RETURN_CASH(x)  return CashGetDatum(x)
 
+
+
+/*************************************************************************
+ * Private routines
+
************************************************************************/
+ +static const char *
+num_word(Cash value)
+{
+    static char buf[128];
+    static const char *small[] = {
+        "zero", "one", "two", "three", "four", "five", "six",
"seven",
+        "eight", "nine", "ten", "eleven", "twelve",
"thirteen", "fourteen",
+        "fifteen", "sixteen", "seventeen", "eighteen",
"nineteen", "twenty",
+        "thirty", "forty", "fifty", "sixty", "seventy",
"eighty", "ninety"
+    };
+    const char **big = small + 18;
+    int            tu = value % 100;
+
+    /* deal with the simple cases first */
+    if (value <= 20)
+        return small[value];
+
+    /* is it an even multiple of 100? */
+    if (!tu)
+    {
+        sprintf(buf, "%s hundred", small[value / 100]);
+        return buf;
+    }
+
+    /* more than 99? */
+    if (value > 99)
+    {
+        /* is it an even multiple of 10 other than 10? */
+        if (value % 10 == 0 && tu > 10)
+            sprintf(buf, "%s hundred %s",
+                    small[value / 100], big[tu /
10]);
+        else if (tu < 20)
+            sprintf(buf, "%s hundred and %s",
+                    small[value / 100], small[tu]);
+        else
+            sprintf(buf, "%s hundred %s %s",
+                    small[value / 100], big[tu /
10], small[tu % 10]); +
+    }
+    else
+    {
+        /* is it an even multiple of 10 other than 10? */
+        if (value % 10 == 0 && tu > 10)
+            sprintf(buf, "%s", big[tu / 10]);
+        else if (tu < 20)
+            sprintf(buf, "%s", small[tu]);
+        else
+            sprintf(buf, "%s %s", big[tu / 10], small[tu %
10]);
+    }
+
+    return buf;
+}    /* num_word() */
+static DatumCashGetDatum(Cash value){
@@ -56,12 +114,6 @@ CashGetDatum(Cash value) * Format is [$]###[,]###[.##] * Examples: 123.45 $123.45 $123,456.78 *
- * This is currently implemented as a 32-bit integer.
- * XXX HACK It looks as though some of the symbols for
- *    monetary values returned by localeconv() can be multiple
- *    bytes/characters. This code assumes one byte only. - tgl
97/04/14
- * XXX UNHACK Allow the currency symbol to be multibyte.
- *    - thomas 1998-03-01 */Datumcash_in(PG_FUNCTION_ARGS)
@@ -74,11 +126,11 @@ cash_in(PG_FUNCTION_ARGS)    int            seen_dot = 0;    const char *s = str;    int
fpoint;
 
-    char       *csymbol;
+    const char *csymbol,
+               *nsymbol;    char        dsymbol,                ssymbol,
-                psymbol,
-               *nsymbol;
+                psymbol;    struct lconv *lconvert = PGLC_localeconv();
@@ -120,6 +172,7 @@ cash_in(PG_FUNCTION_ARGS)    /* a leading minus or paren signifies a negative number */    /*
again,better heuristics needed */
 
+    /* XXX - doesn't properly check for balanced parens - djmc */    if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
{       sgn = -1;
 
@@ -152,7 +205,7 @@ cash_in(PG_FUNCTION_ARGS)    for (;; s++)    {
-        /* we look for digits as int4 as we have less */
+        /* we look for digits as int8 as we have less */        /* than the required number of decimal places */
if (isdigit((unsigned char) *s) && dec < fpoint)        {
 
@@ -161,14 +214,14 @@ cash_in(PG_FUNCTION_ARGS)            if (seen_dot)                dec++;
-            /* decimal point? then start counting
fractions... */ }
+        /* decimal point? then start counting fractions... */        else if (*s == dsymbol && !seen_dot)        {
      seen_dot = 1;
 
-            /* "thousands" separator? then skip... */        }
+        /* "thousands" separator? then skip... */        else if (*s == ssymbol)        {
@@ -187,7 +240,9 @@ cash_in(PG_FUNCTION_ARGS)        }    }
-    while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
+    /* should only be trailing digits followed by whitespace or
closing paren */
+    while (isdigit(*s)) s++;
+    while (isspace((unsigned char) *s) || *s == ')')        s++;    if (*s != '\0')
@@ -223,9 +278,8 @@ cash_out(PG_FUNCTION_ARGS)    int            points,                mon_group;    char
comma;
-    char       *csymbol,
-                dsymbol,
-               *nsymbol;
+    const char *nsymbol;
+    char        dsymbol;    char        convention;    struct lconv *lconvert = PGLC_localeconv();
@@ -246,7 +300,6 @@ cash_out(PG_FUNCTION_ARGS)    comma = ((*lconvert->mon_thousands_sep != '\0') ?
*lconvert->mon_thousands_sep : ','); convention = lconvert->n_sign_posn;    dsymbol = ((*lconvert->mon_decimal_point !=
'\0')?
 
*lconvert->mon_decimal_point : '.');
-    csymbol = ((*lconvert->currency_symbol != '\0') ?
lconvert->currency_symbol : "$"); nsymbol = ((*lconvert->negative_sign !
= '\0') ? lconvert->negative_sign : "-");     point_pos = LAST_DIGIT - points;
@@ -276,13 +329,10 @@ cash_out(PG_FUNCTION_ARGS)        else if (comma && count % (mon_group + 1) ==
comma_position) buf[count--] = comma;
-        buf[count--] = ((unsigned int) value % 10) + '0';
-        value = ((unsigned int) value) / 10;
+        buf[count--] = ((uint64) value % 10) + '0';
+        value = ((uint64) value) / 10;    }
-    strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen
(csymbol));
-    count -= strlen(csymbol) - 1;
-    if (buf[LAST_DIGIT] == ',')        buf[LAST_DIGIT] = buf[LAST_PAREN];
@@ -470,9 +520,6 @@ flt8_mul_cash(PG_FUNCTION_ARGS)/* cash_div_flt8() * Divide cash by float8.
- *
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15 */Datumcash_div_flt8(PG_FUNCTION_ARGS)
@@ -490,6 +537,7 @@ cash_div_flt8(PG_FUNCTION_ARGS)    PG_RETURN_CASH(result);}
+/* cash_mul_flt4() * Multiply cash by float4. */
@@ -523,8 +571,6 @@ flt4_mul_cash(PG_FUNCTION_ARGS)/* cash_div_flt4() * Divide cash by float4. *
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15 */Datumcash_div_flt4(PG_FUNCTION_ARGS)
@@ -543,6 +589,56 @@ cash_div_flt4(PG_FUNCTION_ARGS)}
+/* cash_mul_int8()
+ * Multiply cash by int8.
+ */
+Datum
+cash_mul_int8(PG_FUNCTION_ARGS)
+{
+    Cash        c = PG_GETARG_CASH(0);
+    int64        i = PG_GETARG_INT64(1);
+    Cash        result;
+
+    result = c * i;
+    PG_RETURN_CASH(result);
+}
+
+
+/* int8_mul_cash()
+ * Multiply int8 by cash.
+ */
+Datum
+int8_mul_cash(PG_FUNCTION_ARGS)
+{
+    int64        i = PG_GETARG_INT64(0);
+    Cash        c = PG_GETARG_CASH(1);
+    Cash        result;
+
+    result = i * c;
+    PG_RETURN_CASH(result);
+}
+
+/* cash_div_int8()
+ * Divide cash by 8-byte integer.
+ */
+Datum
+cash_div_int8(PG_FUNCTION_ARGS)
+{
+    Cash        c = PG_GETARG_CASH(0);
+    int64        i = PG_GETARG_INT64(1);
+    Cash        result;
+
+    if (i == 0)
+        ereport(ERROR,
+                (errcode(ERRCODE_DIVISION_BY_ZERO),
+                 errmsg("division by zero")));
+
+    result = rint(c / i);
+
+    PG_RETURN_CASH(result);
+}
+
+/* cash_mul_int4() * Multiply cash by int4. */
@@ -550,7 +646,7 @@ Datumcash_mul_int4(PG_FUNCTION_ARGS){    Cash        c = PG_GETARG_CASH(0);
-    int32        i = PG_GETARG_INT32(1);
+    int64        i = PG_GETARG_INT32(1);    Cash        result;    result = c * i;
@@ -576,14 +672,12 @@ int4_mul_cash(PG_FUNCTION_ARGS)/* cash_div_int4() * Divide cash by 4-byte integer. *
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15 */Datumcash_div_int4(PG_FUNCTION_ARGS){    Cash        c = PG_GETARG_CASH(0);
-    int32        i = PG_GETARG_INT32(1);
+    int64        i = PG_GETARG_INT32(1);    Cash        result;    if (i == 0)
@@ -628,8 +722,6 @@ int2_mul_cash(PG_FUNCTION_ARGS)/* cash_div_int2() * Divide cash by int2. *
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15 */Datumcash_div_int2(PG_FUNCTION_ARGS)
@@ -677,7 +769,6 @@ cashsmaller(PG_FUNCTION_ARGS)    PG_RETURN_CASH(result);}
-/* cash_words() * This converts a int4 as well but to a representation using words * Obviously way North American
centric- sorry
 
@@ -686,13 +777,16 @@ Datumcash_words(PG_FUNCTION_ARGS){    Cash        value = PG_GETARG_CASH(0);
-    unsigned int val;
+    uint64 val;    char        buf[256];    char       *p = buf;    Cash        m0;    Cash        m1;    Cash
m2;   Cash        m3;
 
+    Cash        m4;
+    Cash        m5;
+    Cash        m6;    text       *result;    /* work with positive numbers */
@@ -706,12 +800,33 @@ cash_words(PG_FUNCTION_ARGS)        buf[0] = '\0';    /* Now treat as unsigned, to avoid trouble
atINT_MIN */
 
-    val = (unsigned int) value;
+    val = (uint64) value;
+
+    m0 = val % 100ll;                /* cents */
+    m1 = (val / 100ll) % 1000;    /* hundreds */
+    m2 = (val / 100000ll) % 1000; /* thousands */
+    m3 = val / 100000000ll % 1000;    /* millions */
+    m4 = val / 100000000000ll % 1000;    /* billions */
+    m5 = val / 100000000000000ll % 1000;    /* trillions */
+    m6 = val / 100000000000000000ll % 1000;    /* quadrillions
*/ +
+    if (m6)
+    {
+        strcat(buf, num_word(m6));
+        strcat(buf, " quadrillion ");
+    }
-    m0 = val % 100;                /* cents */
-    m1 = (val / 100) % 1000;    /* hundreds */
-    m2 = (val / 100000) % 1000; /* thousands */
-    m3 = val / 100000000 % 1000;    /* millions */
+    if (m5)
+    {
+        strcat(buf, num_word(m5));
+        strcat(buf, " trillion ");
+    }
+
+    if (m4)
+    {
+        strcat(buf, num_word(m4));
+        strcat(buf, " billion ");
+    }    if (m3)    {
@@ -745,61 +860,3 @@ cash_words(PG_FUNCTION_ARGS)    PG_RETURN_TEXT_P(result);}
-
-
-/*************************************************************************
- * Private routines
-
************************************************************************/
- -static const char *
-num_word(Cash value)
-{
-    static char buf[128];
-    static const char *small[] = {
-        "zero", "one", "two", "three", "four", "five", "six",
"seven",
-        "eight", "nine", "ten", "eleven", "twelve",
"thirteen", "fourteen",
-        "fifteen", "sixteen", "seventeen", "eighteen",
"nineteen", "twenty",
-        "thirty", "forty", "fifty", "sixty", "seventy",
"eighty", "ninety"
-    };
-    const char **big = small + 18;
-    int            tu = value % 100;
-
-    /* deal with the simple cases first */
-    if (value <= 20)
-        return small[value];
-
-    /* is it an even multiple of 100? */
-    if (!tu)
-    {
-        sprintf(buf, "%s hundred", small[value / 100]);
-        return buf;
-    }
-
-    /* more than 99? */
-    if (value > 99)
-    {
-        /* is it an even multiple of 10 other than 10? */
-        if (value % 10 == 0 && tu > 10)
-            sprintf(buf, "%s hundred %s",
-                    small[value / 100], big[tu /
10]);
-        else if (tu < 20)
-            sprintf(buf, "%s hundred and %s",
-                    small[value / 100], small[tu]);
-        else
-            sprintf(buf, "%s hundred %s %s",
-                    small[value / 100], big[tu /
10], small[tu % 10]); -
-    }
-    else
-    {
-        /* is it an even multiple of 10 other than 10? */
-        if (value % 10 == 0 && tu > 10)
-            sprintf(buf, "%s", big[tu / 10]);
-        else if (tu < 20)
-            sprintf(buf, "%s", small[tu]);
-        else
-            sprintf(buf, "%s %s", big[tu / 10], small[tu %
10]);
-    }
-
-    return buf;
-}    /* num_word() */
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_type.h,v
retrieving revision 1.171
diff -u -p -u -r1.171 pg_type.h
--- src/include/catalog/pg_type.h    5 Apr 2006 22:11:57
-0000    1.171 +++ src/include/catalog/pg_type.h    14 Sep 2006
14:28:31 -0000 @@ -376,7 +376,7 @@ DATA(insert OID = 718 (  circle
PGNSP DESCR("geometric circle '(center,radius)'");#define CIRCLEOID        718DATA(insert OID = 719 (  _circle   PGNSP
PGUID   -1 f b t \054 0
 
718 array_in array_out array_recv array_send - d x f 0 -1 0 _null_
_null_ )); -DATA(insert OID = 790 (  money       PGNSP
PGUID     4 f b t \054 0 0 cash_in cash_out cash_recv cash_send - i
p f 0 -1 0 _null_ _null_ )); +DATA(insert OID = 790 (  money
PGNSP PGUID     8 f b t \054 0 0 cash_in cash_out cash_recv
cash_send - i p f 0 -1 0 _null_ _null_ )); DESCR("monetary amounts,
$d,ddd.cc"); #define CASHOID 790 DATA(insert OID = 791 (  _money
PGNSP PGUID    -1 f b t \054 0  790 array_in array_out array_recv
array_send - i x f 0 -1 0 _null_ _null_ )); Index:
src/include/utils/cash.h
=================================================================== RCS
file: /cvsroot/pgsql/src/include/utils/cash.h,v retrieving revision
1.23 diff -u -p -u -r1.23 cash.h --- src/include/utils/cash.h    13
Jul 2006 16:49:20 -0000    1.23 +++ src/include/utils/cash.h
14 Sep 2006 14:28:31 -0000 @@ -3,7 +3,7 @@ * Written by D'Arcy J.M. Cain * * Functions to allow input and output of
moneynormally but store
 
- *    and handle it as int4.
+ *    and handle it as 64 bit integer. */#ifndef CASH_H
@@ -11,8 +11,7 @@#include "fmgr.h"
-/* if we store this as 4 bytes, we better make it int, not long, bjm */
-typedef int32 Cash;
+typedef int64 Cash;extern Datum cash_in(PG_FUNCTION_ARGS);extern Datum cash_out(PG_FUNCTION_ARGS);
@@ -31,16 +30,20 @@ extern Datum cash_pl(PG_FUNCTION_ARGS);extern Datum cash_mi(PG_FUNCTION_ARGS);extern Datum
cash_mul_flt8(PG_FUNCTION_ARGS);
-extern Datum cash_div_flt8(PG_FUNCTION_ARGS);extern Datum flt8_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_flt8(PG_FUNCTION_ARGS);extern Datum cash_mul_flt4(PG_FUNCTION_ARGS);
-extern Datum cash_div_flt4(PG_FUNCTION_ARGS);extern Datum flt4_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
+
+extern Datum cash_mul_int8(PG_FUNCTION_ARGS);
+extern Datum int8_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_int8(PG_FUNCTION_ARGS);extern Datum cash_mul_int4(PG_FUNCTION_ARGS);
-extern Datum cash_div_int4(PG_FUNCTION_ARGS);extern Datum int4_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_int4(PG_FUNCTION_ARGS);extern Datum cash_mul_int2(PG_FUNCTION_ARGS);extern Datum
int2_mul_cash(PG_FUNCTION_ARGS);


-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Joshua D. Drake"
Date:
D'Arcy J.M. Cain wrote:
> For years I have been promising that a 64 bit version of the money type
> was on the way.  Here it is.  So far it compiles and I have done some
> basic testing on it and it seems to work fine.  Note that the currency
> symbol is also dropped on output as well but it is accepted on input.

Not to come down on your hard work, but isn't the money type deprecated?

Joshua D. Drake


> 
> darcy=# select '$92,233,720,368,547,758.07'::money;
>            money
> ----------------------------
>   92,233,720,368,547,758.07
> (1 row)
> 
> 
> Index: src/backend/utils/adt/cash.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v
> retrieving revision 1.68
> diff -u -p -u -r1.68 cash.c
> --- src/backend/utils/adt/cash.c    14 Jul 2006 14:52:23
> -0000    1.68 +++ src/backend/utils/adt/cash.c    14 Sep 2006
> 14:28:30 -0000 @@ -1,13 +1,17 @@
>  /*
>   * cash.c
>   * Written by D'Arcy J.M. Cain
> + * darcy@druid.net
> + * http://www.druid.net/darcy/
>   *
>   * Functions to allow input and output of money normally but store
> - * and handle it as int4s
> + * and handle it as 64 bit ints
>   *
>   * A slightly modified version of this file and a discussion of the
>   * workings can be found in the book "Software Solutions in C" by
> - * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
> + * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
> + * this version handles 64 bit numbers and so can hold values up to
> + * $92,233,720,368,547,758.07.
>   *
>   * $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14
> 14:52:23 momjian Exp $ */
> @@ -23,17 +27,12 @@
>  #include "utils/cash.h"
>  #include "utils/pg_locale.h"
>  
> -
> -static const char *num_word(Cash value);
> -
> -/* when we go to 64 bit values we will have to modify this */
> -#define CASH_BUFSZ        24
> +#define CASH_BUFSZ        36
>  
>  #define TERMINATOR        (CASH_BUFSZ - 1)
>  #define LAST_PAREN        (TERMINATOR - 1)
>  #define LAST_DIGIT        (LAST_PAREN - 1)
>  
> -
>  /*
>   * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
>   * These macros and support routine hide the pass-by-refness.
> @@ -41,6 +40,65 @@ static const char *num_word(Cash value);
>  #define PG_GETARG_CASH(n)  (* ((Cash *) PG_GETARG_POINTER(n)))
>  #define PG_RETURN_CASH(x)  return CashGetDatum(x)
>  
> +
> +
> +/*************************************************************************
> + * Private routines
> +
> ************************************************************************/
> + +static const char *
> +num_word(Cash value)
> +{
> +    static char buf[128];
> +    static const char *small[] = {
> +        "zero", "one", "two", "three", "four", "five", "six",
> "seven",
> +        "eight", "nine", "ten", "eleven", "twelve",
> "thirteen", "fourteen",
> +        "fifteen", "sixteen", "seventeen", "eighteen",
> "nineteen", "twenty",
> +        "thirty", "forty", "fifty", "sixty", "seventy",
> "eighty", "ninety"
> +    };
> +    const char **big = small + 18;
> +    int            tu = value % 100;
> +
> +    /* deal with the simple cases first */
> +    if (value <= 20)
> +        return small[value];
> +
> +    /* is it an even multiple of 100? */
> +    if (!tu)
> +    {
> +        sprintf(buf, "%s hundred", small[value / 100]);
> +        return buf;
> +    }
> +
> +    /* more than 99? */
> +    if (value > 99)
> +    {
> +        /* is it an even multiple of 10 other than 10? */
> +        if (value % 10 == 0 && tu > 10)
> +            sprintf(buf, "%s hundred %s",
> +                    small[value / 100], big[tu /
> 10]);
> +        else if (tu < 20)
> +            sprintf(buf, "%s hundred and %s",
> +                    small[value / 100], small[tu]);
> +        else
> +            sprintf(buf, "%s hundred %s %s",
> +                    small[value / 100], big[tu /
> 10], small[tu % 10]); +
> +    }
> +    else
> +    {
> +        /* is it an even multiple of 10 other than 10? */
> +        if (value % 10 == 0 && tu > 10)
> +            sprintf(buf, "%s", big[tu / 10]);
> +        else if (tu < 20)
> +            sprintf(buf, "%s", small[tu]);
> +        else
> +            sprintf(buf, "%s %s", big[tu / 10], small[tu %
> 10]);
> +    }
> +
> +    return buf;
> +}    /* num_word() */
> +
>  static Datum
>  CashGetDatum(Cash value)
>  {
> @@ -56,12 +114,6 @@ CashGetDatum(Cash value)
>   * Format is [$]###[,]###[.##]
>   * Examples: 123.45 $123.45 $123,456.78
>   *
> - * This is currently implemented as a 32-bit integer.
> - * XXX HACK It looks as though some of the symbols for
> - *    monetary values returned by localeconv() can be multiple
> - *    bytes/characters. This code assumes one byte only. - tgl
> 97/04/14
> - * XXX UNHACK Allow the currency symbol to be multibyte.
> - *    - thomas 1998-03-01
>   */
>  Datum
>  cash_in(PG_FUNCTION_ARGS)
> @@ -74,11 +126,11 @@ cash_in(PG_FUNCTION_ARGS)
>      int            seen_dot = 0;
>      const char *s = str;
>      int            fpoint;
> -    char       *csymbol;
> +    const char *csymbol,
> +               *nsymbol;
>      char        dsymbol,
>                  ssymbol,
> -                psymbol,
> -               *nsymbol;
> +                psymbol;
>  
>      struct lconv *lconvert = PGLC_localeconv();
>  
> @@ -120,6 +172,7 @@ cash_in(PG_FUNCTION_ARGS)
>  
>      /* a leading minus or paren signifies a negative number */
>      /* again, better heuristics needed */
> +    /* XXX - doesn't properly check for balanced parens - djmc */
>      if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
>      {
>          sgn = -1;
> @@ -152,7 +205,7 @@ cash_in(PG_FUNCTION_ARGS)
>  
>      for (;; s++)
>      {
> -        /* we look for digits as int4 as we have less */
> +        /* we look for digits as int8 as we have less */
>          /* than the required number of decimal places */
>          if (isdigit((unsigned char) *s) && dec < fpoint)
>          {
> @@ -161,14 +214,14 @@ cash_in(PG_FUNCTION_ARGS)
>              if (seen_dot)
>                  dec++;
>  
> -            /* decimal point? then start counting
> fractions... */ }
> +        /* decimal point? then start counting fractions... */
>          else if (*s == dsymbol && !seen_dot)
>          {
>              seen_dot = 1;
>  
> -            /* "thousands" separator? then skip... */
>          }
> +        /* "thousands" separator? then skip... */
>          else if (*s == ssymbol)
>          {
>  
> @@ -187,7 +240,9 @@ cash_in(PG_FUNCTION_ARGS)
>          }
>      }
>  
> -    while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
> +    /* should only be trailing digits followed by whitespace or
> closing paren */
> +    while (isdigit(*s)) s++;
> +    while (isspace((unsigned char) *s) || *s == ')')
>          s++;
>  
>      if (*s != '\0')
> @@ -223,9 +278,8 @@ cash_out(PG_FUNCTION_ARGS)
>      int            points,
>                  mon_group;
>      char        comma;
> -    char       *csymbol,
> -                dsymbol,
> -               *nsymbol;
> +    const char *nsymbol;
> +    char        dsymbol;
>      char        convention;
>  
>      struct lconv *lconvert = PGLC_localeconv();
> @@ -246,7 +300,6 @@ cash_out(PG_FUNCTION_ARGS)
>      comma = ((*lconvert->mon_thousands_sep != '\0') ?
> *lconvert->mon_thousands_sep : ','); convention = lconvert->n_sign_posn;
>      dsymbol = ((*lconvert->mon_decimal_point != '\0') ?
> *lconvert->mon_decimal_point : '.');
> -    csymbol = ((*lconvert->currency_symbol != '\0') ?
> lconvert->currency_symbol : "$"); nsymbol = ((*lconvert->negative_sign !
> = '\0') ? lconvert->negative_sign : "-"); 
>      point_pos = LAST_DIGIT - points;
> @@ -276,13 +329,10 @@ cash_out(PG_FUNCTION_ARGS)
>          else if (comma && count % (mon_group + 1) ==
> comma_position) buf[count--] = comma;
>  
> -        buf[count--] = ((unsigned int) value % 10) + '0';
> -        value = ((unsigned int) value) / 10;
> +        buf[count--] = ((uint64) value % 10) + '0';
> +        value = ((uint64) value) / 10;
>      }
>  
> -    strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen
> (csymbol));
> -    count -= strlen(csymbol) - 1;
> -
>      if (buf[LAST_DIGIT] == ',')
>          buf[LAST_DIGIT] = buf[LAST_PAREN];
>  
> @@ -470,9 +520,6 @@ flt8_mul_cash(PG_FUNCTION_ARGS)
>  
>  /* cash_div_flt8()
>   * Divide cash by float8.
> - *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
>   */
>  Datum
>  cash_div_flt8(PG_FUNCTION_ARGS)
> @@ -490,6 +537,7 @@ cash_div_flt8(PG_FUNCTION_ARGS)
>      PG_RETURN_CASH(result);
>  }
>  
> +
>  /* cash_mul_flt4()
>   * Multiply cash by float4.
>   */
> @@ -523,8 +571,6 @@ flt4_mul_cash(PG_FUNCTION_ARGS)
>  /* cash_div_flt4()
>   * Divide cash by float4.
>   *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
>   */
>  Datum
>  cash_div_flt4(PG_FUNCTION_ARGS)
> @@ -543,6 +589,56 @@ cash_div_flt4(PG_FUNCTION_ARGS)
>  }
>  
>  
> +/* cash_mul_int8()
> + * Multiply cash by int8.
> + */
> +Datum
> +cash_mul_int8(PG_FUNCTION_ARGS)
> +{
> +    Cash        c = PG_GETARG_CASH(0);
> +    int64        i = PG_GETARG_INT64(1);
> +    Cash        result;
> +
> +    result = c * i;
> +    PG_RETURN_CASH(result);
> +}
> +
> +
> +/* int8_mul_cash()
> + * Multiply int8 by cash.
> + */
> +Datum
> +int8_mul_cash(PG_FUNCTION_ARGS)
> +{
> +    int64        i = PG_GETARG_INT64(0);
> +    Cash        c = PG_GETARG_CASH(1);
> +    Cash        result;
> +
> +    result = i * c;
> +    PG_RETURN_CASH(result);
> +}
> +
> +/* cash_div_int8()
> + * Divide cash by 8-byte integer.
> + */
> +Datum
> +cash_div_int8(PG_FUNCTION_ARGS)
> +{
> +    Cash        c = PG_GETARG_CASH(0);
> +    int64        i = PG_GETARG_INT64(1);
> +    Cash        result;
> +
> +    if (i == 0)
> +        ereport(ERROR,
> +                (errcode(ERRCODE_DIVISION_BY_ZERO),
> +                 errmsg("division by zero")));
> +
> +    result = rint(c / i);
> +
> +    PG_RETURN_CASH(result);
> +}
> +
> +
>  /* cash_mul_int4()
>   * Multiply cash by int4.
>   */
> @@ -550,7 +646,7 @@ Datum
>  cash_mul_int4(PG_FUNCTION_ARGS)
>  {
>      Cash        c = PG_GETARG_CASH(0);
> -    int32        i = PG_GETARG_INT32(1);
> +    int64        i = PG_GETARG_INT32(1);
>      Cash        result;
>  
>      result = c * i;
> @@ -576,14 +672,12 @@ int4_mul_cash(PG_FUNCTION_ARGS)
>  /* cash_div_int4()
>   * Divide cash by 4-byte integer.
>   *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
>   */
>  Datum
>  cash_div_int4(PG_FUNCTION_ARGS)
>  {
>      Cash        c = PG_GETARG_CASH(0);
> -    int32        i = PG_GETARG_INT32(1);
> +    int64        i = PG_GETARG_INT32(1);
>      Cash        result;
>  
>      if (i == 0)
> @@ -628,8 +722,6 @@ int2_mul_cash(PG_FUNCTION_ARGS)
>  /* cash_div_int2()
>   * Divide cash by int2.
>   *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
>   */
>  Datum
>  cash_div_int2(PG_FUNCTION_ARGS)
> @@ -677,7 +769,6 @@ cashsmaller(PG_FUNCTION_ARGS)
>      PG_RETURN_CASH(result);
>  }
>  
> -
>  /* cash_words()
>   * This converts a int4 as well but to a representation using words
>   * Obviously way North American centric - sorry
> @@ -686,13 +777,16 @@ Datum
>  cash_words(PG_FUNCTION_ARGS)
>  {
>      Cash        value = PG_GETARG_CASH(0);
> -    unsigned int val;
> +    uint64 val;
>      char        buf[256];
>      char       *p = buf;
>      Cash        m0;
>      Cash        m1;
>      Cash        m2;
>      Cash        m3;
> +    Cash        m4;
> +    Cash        m5;
> +    Cash        m6;
>      text       *result;
>  
>      /* work with positive numbers */
> @@ -706,12 +800,33 @@ cash_words(PG_FUNCTION_ARGS)
>          buf[0] = '\0';
>  
>      /* Now treat as unsigned, to avoid trouble at INT_MIN */
> -    val = (unsigned int) value;
> +    val = (uint64) value;
> +
> +    m0 = val % 100ll;                /* cents */
> +    m1 = (val / 100ll) % 1000;    /* hundreds */
> +    m2 = (val / 100000ll) % 1000; /* thousands */
> +    m3 = val / 100000000ll % 1000;    /* millions */
> +    m4 = val / 100000000000ll % 1000;    /* billions */
> +    m5 = val / 100000000000000ll % 1000;    /* trillions */
> +    m6 = val / 100000000000000000ll % 1000;    /* quadrillions
> */ +
> +    if (m6)
> +    {
> +        strcat(buf, num_word(m6));
> +        strcat(buf, " quadrillion ");
> +    }
>  
> -    m0 = val % 100;                /* cents */
> -    m1 = (val / 100) % 1000;    /* hundreds */
> -    m2 = (val / 100000) % 1000; /* thousands */
> -    m3 = val / 100000000 % 1000;    /* millions */
> +    if (m5)
> +    {
> +        strcat(buf, num_word(m5));
> +        strcat(buf, " trillion ");
> +    }
> +
> +    if (m4)
> +    {
> +        strcat(buf, num_word(m4));
> +        strcat(buf, " billion ");
> +    }
>  
>      if (m3)
>      {
> @@ -745,61 +860,3 @@ cash_words(PG_FUNCTION_ARGS)
>  
>      PG_RETURN_TEXT_P(result);
>  }
> -
> -
> -/*************************************************************************
> - * Private routines
> -
> ************************************************************************/
> - -static const char *
> -num_word(Cash value)
> -{
> -    static char buf[128];
> -    static const char *small[] = {
> -        "zero", "one", "two", "three", "four", "five", "six",
> "seven",
> -        "eight", "nine", "ten", "eleven", "twelve",
> "thirteen", "fourteen",
> -        "fifteen", "sixteen", "seventeen", "eighteen",
> "nineteen", "twenty",
> -        "thirty", "forty", "fifty", "sixty", "seventy",
> "eighty", "ninety"
> -    };
> -    const char **big = small + 18;
> -    int            tu = value % 100;
> -
> -    /* deal with the simple cases first */
> -    if (value <= 20)
> -        return small[value];
> -
> -    /* is it an even multiple of 100? */
> -    if (!tu)
> -    {
> -        sprintf(buf, "%s hundred", small[value / 100]);
> -        return buf;
> -    }
> -
> -    /* more than 99? */
> -    if (value > 99)
> -    {
> -        /* is it an even multiple of 10 other than 10? */
> -        if (value % 10 == 0 && tu > 10)
> -            sprintf(buf, "%s hundred %s",
> -                    small[value / 100], big[tu /
> 10]);
> -        else if (tu < 20)
> -            sprintf(buf, "%s hundred and %s",
> -                    small[value / 100], small[tu]);
> -        else
> -            sprintf(buf, "%s hundred %s %s",
> -                    small[value / 100], big[tu /
> 10], small[tu % 10]); -
> -    }
> -    else
> -    {
> -        /* is it an even multiple of 10 other than 10? */
> -        if (value % 10 == 0 && tu > 10)
> -            sprintf(buf, "%s", big[tu / 10]);
> -        else if (tu < 20)
> -            sprintf(buf, "%s", small[tu]);
> -        else
> -            sprintf(buf, "%s %s", big[tu / 10], small[tu %
> 10]);
> -    }
> -
> -    return buf;
> -}    /* num_word() */
> Index: src/include/catalog/pg_type.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/catalog/pg_type.h,v
> retrieving revision 1.171
> diff -u -p -u -r1.171 pg_type.h
> --- src/include/catalog/pg_type.h    5 Apr 2006 22:11:57
> -0000    1.171 +++ src/include/catalog/pg_type.h    14 Sep 2006
> 14:28:31 -0000 @@ -376,7 +376,7 @@ DATA(insert OID = 718 (  circle
> PGNSP DESCR("geometric circle '(center,radius)'");
>  #define CIRCLEOID        718
>  DATA(insert OID = 719 (  _circle   PGNSP PGUID    -1 f b t \054 0
> 718 array_in array_out array_recv array_send - d x f 0 -1 0 _null_
> _null_ )); -DATA(insert OID = 790 (  money       PGNSP
> PGUID     4 f b t \054 0 0 cash_in cash_out cash_recv cash_send - i
> p f 0 -1 0 _null_ _null_ )); +DATA(insert OID = 790 (  money
> PGNSP PGUID     8 f b t \054 0 0 cash_in cash_out cash_recv
> cash_send - i p f 0 -1 0 _null_ _null_ )); DESCR("monetary amounts,
> $d,ddd.cc"); #define CASHOID 790 DATA(insert OID = 791 (  _money
> PGNSP PGUID    -1 f b t \054 0  790 array_in array_out array_recv
> array_send - i x f 0 -1 0 _null_ _null_ )); Index:
> src/include/utils/cash.h
> =================================================================== RCS
> file: /cvsroot/pgsql/src/include/utils/cash.h,v retrieving revision
> 1.23 diff -u -p -u -r1.23 cash.h --- src/include/utils/cash.h    13
> Jul 2006 16:49:20 -0000    1.23 +++ src/include/utils/cash.h
> 14 Sep 2006 14:28:31 -0000 @@ -3,7 +3,7 @@
>   * Written by D'Arcy J.M. Cain
>   *
>   * Functions to allow input and output of money normally but store
> - *    and handle it as int4.
> + *    and handle it as 64 bit integer.
>   */
>  
>  #ifndef CASH_H
> @@ -11,8 +11,7 @@
>  
>  #include "fmgr.h"
>  
> -/* if we store this as 4 bytes, we better make it int, not long, bjm */
> -typedef int32 Cash;
> +typedef int64 Cash;
>  
>  extern Datum cash_in(PG_FUNCTION_ARGS);
>  extern Datum cash_out(PG_FUNCTION_ARGS);
> @@ -31,16 +30,20 @@ extern Datum cash_pl(PG_FUNCTION_ARGS);
>  extern Datum cash_mi(PG_FUNCTION_ARGS);
>  
>  extern Datum cash_mul_flt8(PG_FUNCTION_ARGS);
> -extern Datum cash_div_flt8(PG_FUNCTION_ARGS);
>  extern Datum flt8_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_flt8(PG_FUNCTION_ARGS);
>  
>  extern Datum cash_mul_flt4(PG_FUNCTION_ARGS);
> -extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
>  extern Datum flt4_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
> +
> +extern Datum cash_mul_int8(PG_FUNCTION_ARGS);
> +extern Datum int8_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_int8(PG_FUNCTION_ARGS);
>  
>  extern Datum cash_mul_int4(PG_FUNCTION_ARGS);
> -extern Datum cash_div_int4(PG_FUNCTION_ARGS);
>  extern Datum int4_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_int4(PG_FUNCTION_ARGS);
>  
>  extern Datum cash_mul_int2(PG_FUNCTION_ARGS);
>  extern Datum int2_mul_cash(PG_FUNCTION_ARGS);
> 
> 


-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240   Providing the most comprehensive  PostgreSQL
solutionssince 1997             http://www.commandprompt.com/
 




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 14 Sep 2006 07:59:07 -0700
"Joshua D. Drake" <jd@commandprompt.com> wrote:
> D'Arcy J.M. Cain wrote:
> > For years I have been promising that a 64 bit version of the money type
> > was on the way.  Here it is.  So far it compiles and I have done some
> > basic testing on it and it seems to work fine.  Note that the currency
> > symbol is also dropped on output as well but it is accepted on input.
> 
> Not to come down on your hard work, but isn't the money type deprecated?

Not by me.  :-)

The biggest argument about the money type is that it has an unrealistic
limit.  With this change we can go to almost one hundred thousand
trillion dollars.  That should handle even the US federal budget for a
few more years.

The benefit of the money type is speed.  Because internal operations
are done on integers they can generally be handled by single CPU ops.
My tests on the 64 bit version show 10% to 25% improvement over numeric
for many operations.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Joshua D. Drake"
Date:
D'Arcy J.M. Cain wrote:
> On Thu, 14 Sep 2006 07:59:07 -0700
> "Joshua D. Drake" <jd@commandprompt.com> wrote:
>> D'Arcy J.M. Cain wrote:
>>> For years I have been promising that a 64 bit version of the money type
>>> was on the way.  Here it is.  So far it compiles and I have done some
>>> basic testing on it and it seems to work fine.  Note that the currency
>>> symbol is also dropped on output as well but it is accepted on input.
>> Not to come down on your hard work, but isn't the money type deprecated?
> 
> Not by me.  :-)

Obviously ;), but it is deprecated by the project.

> 
> The biggest argument about the money type is that it has an unrealistic
> limit.  With this change we can go to almost one hundred thousand
> trillion dollars.  That should handle even the US federal budget for a
> few more years.

Isn't that what numeric is for?

> 
> The benefit of the money type is speed.  Because internal operations
> are done on integers they can generally be handled by single CPU ops.
> My tests on the 64 bit version show 10% to 25% improvement over numeric
> for many operations.

Well that is certainly cool :) I will leave it to others to determine if 
we should include it.

Sincerely,

Joshua D. Drake





-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240   Providing the most comprehensive  PostgreSQL
solutionssince 1997             http://www.commandprompt.com/
 




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 14 Sep 2006 08:17:29 -0700
"Joshua D. Drake" <jd@commandprompt.com> wrote:
> Obviously ;), but it is deprecated by the project.

I keep hearing that but no action is ever taken.  I think that there
are too many people who still find it useful.

By the way, I removed the currency symbol from the output.  Would
removing the commas also make sense?  These are the sorts of things
that can be added by applications.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Joshua D. Drake"
Date:
D'Arcy J.M. Cain wrote:
> On Thu, 14 Sep 2006 08:17:29 -0700
> "Joshua D. Drake" <jd@commandprompt.com> wrote:
>> Obviously ;), but it is deprecated by the project.
> 
> I keep hearing that but no action is ever taken.  I think that there
> are too many people who still find it useful.
> 
> By the way, I removed the currency symbol from the output.  Would
> removing the commas also make sense?  These are the sorts of things
> that can be added by applications.

I don't think that we should be providing *any* presentation beyond the 
actual representation of the data. What if it is not US dollars? :)

Joshua D. Drake


> 


-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240   Providing the most comprehensive  PostgreSQL
solutionssince 1997             http://www.commandprompt.com/
 




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 14 Sep 2006 10:33:19 -0700
"Joshua D. Drake" <jd@commandprompt.com> wrote:
> > By the way, I removed the currency symbol from the output.  Would
> > removing the commas also make sense?  These are the sorts of things
> > that can be added by applications.
> 
> I don't think that we should be providing *any* presentation beyond the 
> actual representation of the data. What if it is not US dollars? :)

That's what locale is for.  It looks at that to determine that sort of
stuff including currency symbol before I removed it.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
AgentM
Date:
On Sep 14, 2006, at 14:04 , D'Arcy J.M. Cain wrote:

> On Thu, 14 Sep 2006 10:33:19 -0700
> "Joshua D. Drake" <jd@commandprompt.com> wrote:
>>> By the way, I removed the currency symbol from the output.  Would
>>> removing the commas also make sense?  These are the sorts of things
>>> that can be added by applications.
>>
>> I don't think that we should be providing *any* presentation  
>> beyond the
>> actual representation of the data. What if it is not US dollars? :)
>
> That's what locale is for.  It looks at that to determine that sort of
> stuff including currency symbol before I removed it.

If you force the locale into the money type, then the entire column  
must be of the same currency. That seems like an unnecessary  
limitation. Does your type support banker's rounding?

-M


Re: New version of money type

From
Stephen Frost
Date:
* D'Arcy J.M. Cain (darcy@druid.net) wrote:
> The benefit of the money type is speed.  Because internal operations
> are done on integers they can generally be handled by single CPU ops.
> My tests on the 64 bit version show 10% to 25% improvement over numeric
> for many operations.

Erm, the numeric is doing integer ops too mostly, no?  Perhaps I'm
missing something here..  What *exactly* makes it faster than numeric,
and why couldn't numeric use that improvement?  The one thing I can
think of right off would be having a 64bit-base numeric type instead of
the current 32bit-base (which limits us to base-10,000 while 64bit would
give us base-1,000,000,000, which means more done in one operation and
so less work overall- *if* you can do fast 64bit integer operations,
which isn't necessairly the case on all architectures...).

If that's where the improvment is then let's add a 'numeric64' type.
Thanks,
    Stephen

Re: New version of money type

From
Josh Berkus
Date:
Darcy,

> The biggest argument about the money type is that it has an unrealistic
> limit. 

Funny, I thought it was the lack of operators, conversions and any clear plan 
on how to have a money type that supports multiple currencies.

Or are you working on those?   That would be keen ...

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: New version of money type

From
Martijn van Oosterhout
Date:
On Thu, Sep 14, 2006 at 01:56:16PM -0700, Josh Berkus wrote:
> Darcy,
>
> > The biggest argument about the money type is that it has an unrealistic
> > limit.
>
> Funny, I thought it was the lack of operators, conversions and any clear plan
> on how to have a money type that supports multiple currencies.

Indeed, the multiple currencies is what I thought was the real killer.
The taggedtypes module provides a way to handle the multiple currencies
part, I don't think there have been any other real contenders.

Ofcorse, if this is a faster numeric type, you could use the
taggedtypes module to turn it into a generic money type. Win win.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: New version of money type

From
Andrew - Supernews
Date:
On 2006-09-15, Martijn van Oosterhout <kleptog@svana.org> wrote:
> Ofcorse, if this is a faster numeric type,

Presumably the same speed as bigint, which is to say that while it is
faster than numeric for calculation, it is (much) slower for input/output.
(The difference in speed between bigint output and numeric output is
measured in multiples, not in percentages.)

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Fri, 15 Sep 2006 10:17:55 -0000
Andrew - Supernews <andrew+nonews@supernews.com> wrote:
> Presumably the same speed as bigint, which is to say that while it is
> faster than numeric for calculation, it is (much) slower for input/output.
> (The difference in speed between bigint output and numeric output is
> measured in multiples, not in percentages.)

I/O for money seems at least as compareable to numeric if not slightly
better.  Other than that it has all the speed advantages as bigint for
basically the same reasons.  It's basically bigint with modified input
and output functions.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 14 Sep 2006 14:12:30 -0400
AgentM <agentm@themactionfaction.com> wrote:
> If you force the locale into the money type, then the entire column  
> must be of the same currency. That seems like an unnecessary  
> limitation. Does your type support banker's rounding?

The whole point of money is to have a high speed type suitable for
accounting apps.  I had an application that used money that we had to
switch to numeric due to the size limitation.  When we did we saw a
dramatic degredation in performance.  The app was a gift card system
that tracked card balances.  A card might have hundreds of transactions
and one client might have millions of cards.  We had to sum all of
those transactions grouped by card.  It would have been great to have
been able to keep the original money type but total sales broke the
limit.

We use rint(), same as the previous version.  I know that that isn't
precisely banker's rounding.  I think that those special rules would
have to be handled in code.  In that environment you would probably
want to do that for auditing (code and otherwise) purposes.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Andrew - Supernews
Date:
On 2006-09-15, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> On Fri, 15 Sep 2006 10:17:55 -0000
> Andrew - Supernews <andrew+nonews@supernews.com> wrote:
>> Presumably the same speed as bigint, which is to say that while it is
>> faster than numeric for calculation, it is (much) slower for input/output.
>> (The difference in speed between bigint output and numeric output is
>> measured in multiples, not in percentages.)
>
> I/O for money seems at least as compareable to numeric if not slightly
> better.

Seems? Have you benchmarked it?

> Other than that it has all the speed advantages as bigint for
> basically the same reasons.  It's basically bigint with modified input
> and output functions.

The point is that bigint is _not_ faster than numeric for I/O (in fact
even integer is not faster than numeric for output).

Numbers from an actual benchmark:

int4out(0)     - 0.42us/call
numeric_out(0) - 0.32us/call

int4out(1000000000)     - 0.67us/call
numeric_out(1000000000) - 0.42us/call

For numbers at the top end of bigint's range, the speed difference is on
the order of 4x (albeit on my 32-bit machine)

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Fri, 15 Sep 2006 15:14:10 -0000
Andrew - Supernews <andrew+nonews@supernews.com> wrote:
> On 2006-09-15, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> > On Fri, 15 Sep 2006 10:17:55 -0000
> > Andrew - Supernews <andrew+nonews@supernews.com> wrote:
> >> Presumably the same speed as bigint, which is to say that while it is
> >> faster than numeric for calculation, it is (much) slower for input/output.
> >> (The difference in speed between bigint output and numeric output is
> >> measured in multiples, not in percentages.)
> >
> > I/O for money seems at least as compareable to numeric if not slightly
> > better.
> 
> Seems? Have you benchmarked it?

Not rigourously but a few "ANALYZE EXPLAIN" statements bear out this
observation.

> The point is that bigint is _not_ faster than numeric for I/O (in fact
> even integer is not faster than numeric for output).
> 
> Numbers from an actual benchmark:
> 
> int4out(0)     - 0.42us/call
> numeric_out(0) - 0.32us/call
> 
> int4out(1000000000)     - 0.67us/call
> numeric_out(1000000000) - 0.42us/call

Whay benchmark is this?  Perhaps I can modify it to include my new
implementation.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Alvaro Herrera
Date:
Andrew - Supernews wrote:
> Numbers from an actual benchmark:
> 
> int4out(0)     - 0.42us/call
> numeric_out(0) - 0.32us/call
> 
> int4out(1000000000)     - 0.67us/call
> numeric_out(1000000000) - 0.42us/call

Is this really int4out, or is it int8out?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: New version of money type

From
Andrew - Supernews
Date:
On 2006-09-15, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
>> Seems? Have you benchmarked it?
>
> Not rigourously but a few "ANALYZE EXPLAIN" statements bear out this
> observation.

The overhead of EXPLAIN ANALYZE is so large that it completely swamps any
real difference.

>> The point is that bigint is _not_ faster than numeric for I/O (in fact
>> even integer is not faster than numeric for output).
>> 
>> Numbers from an actual benchmark:
>> 
>> int4out(0)     - 0.42us/call
>> numeric_out(0) - 0.32us/call
>> 
>> int4out(1000000000)     - 0.67us/call
>> numeric_out(1000000000) - 0.42us/call
>
> Whay benchmark is this?

Simple queries output to /dev/null. Use \timing in psql to get times.

First measure the benchmark overhead:

select null::integer from generate_series(1,1000) s1,                         generate_series(1,1000) s2;

Since output functions are strict, this does not call int4out at all, so
this measures the time taken to generate the million rows, output and discard
them.

Then do the real tests:

select 0::integer from generate_series(1,1000) s1,                      generate_series(1,1000) s2;

This calls int4out(0) a million times. (the input function is only called
once since it is a constant, and therefore handled during planning)

select 0::numeric from generate_series(1,1000) s1,                      generate_series(1,1000) s2;

This calls numeric_out(0) a million times. And so on.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services


Re: New version of money type

From
Andrew - Supernews
Date:
On 2006-09-15, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Andrew - Supernews wrote:
>> Numbers from an actual benchmark:
>> 
>> int4out(0)     - 0.42us/call
>> numeric_out(0) - 0.32us/call
>> 
>> int4out(1000000000)     - 0.67us/call
>> numeric_out(1000000000) - 0.42us/call
>
> Is this really int4out, or is it int8out?

int4out. int8out is slower.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Fri, 15 Sep 2006 16:15:24 -0000
Andrew - Supernews <andrew+nonews@supernews.com> wrote:
> On 2006-09-15, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > Andrew - Supernews wrote:
> >> Numbers from an actual benchmark:
> >> 
> >> int4out(0)     - 0.42us/call
> >> numeric_out(0) - 0.32us/call
> >> 
> >> int4out(1000000000)     - 0.67us/call
> >> numeric_out(1000000000) - 0.42us/call
> >
> > Is this really int4out, or is it int8out?
> 
> int4out. int8out is slower.

int8out is probably a better comparison since it is the same range.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Fri, 15 Sep 2006 16:15:04 -0000
Andrew - Supernews <andrew+nonews@supernews.com> wrote:
> On 2006-09-15, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> >> Seems? Have you benchmarked it?
> >
> > Not rigourously but a few "ANALYZE EXPLAIN" statements bear out this
> > observation.
> 
> The overhead of EXPLAIN ANALYZE is so large that it completely swamps any
> real difference.

Hence my "not rigourously" comment.

> First measure the benchmark overhead:
> 
> select null::integer from generate_series(1,1000) s1,
>                           generate_series(1,1000) s2;

Time: 870.531 ms

> Since output functions are strict, this does not call int4out at all, so
> this measures the time taken to generate the million rows, output and discard
> them.
> 
> Then do the real tests:
> 
> select 0::integer from generate_series(1,1000) s1,
>                        generate_series(1,1000) s2;

Time: 1410.690 ms

> This calls int4out(0) a million times. (the input function is only called
> once since it is a constant, and therefore handled during planning)
> 
> select 0::numeric from generate_series(1,1000) s1,
>                        generate_series(1,1000) s2;

Time: 1256.539 ms

Selecting "'0'::money" gives:

Time: 1487.757 ms

Bigint gives:

Time: 1450.405 ms

The extra processing over int and bigint is probably due to locale
formatting.  That's partially why I was wondering if the basic type
should be doing that as opposed to doing it in app code.  Also, I
wonder if some of the techniques in numeric could be applied here.  I
haven't looked carefully at the numeric output code yet.

In any case, I/O speed is probably not that important with this type.
Internal calculations, in my experience, are much more critical.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Jim C. Nasby"
Date:
On Thu, Sep 14, 2006 at 11:12:14AM -0400, D'Arcy J.M. Cain wrote:
> The benefit of the money type is speed.  Because internal operations
> are done on integers they can generally be handled by single CPU ops.
> My tests on the 64 bit version show 10% to 25% improvement over numeric
> for many operations.

Has anyone looked at changing numeric so that for numbers with less than
9 digits it stores/uses an int, and for between 10 and 18 digits it uses
a bigint? Perhaps that would net every numeric user a speed improvement.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


Re: New version of money type

From
Theo Schlossnagle
Date:
On Sep 16, 2006, at 5:27 PM, Jim C. Nasby wrote:

> On Thu, Sep 14, 2006 at 11:12:14AM -0400, D'Arcy J.M. Cain wrote:
>> The benefit of the money type is speed.  Because internal operations
>> are done on integers they can generally be handled by single CPU ops.
>> My tests on the 64 bit version show 10% to 25% improvement over  
>> numeric
>> for many operations.
>
> Has anyone looked at changing numeric so that for numbers with less  
> than
> 9 digits it stores/uses an int, and for between 10 and 18 digits it  
> uses
> a bigint? Perhaps that would net every numeric user a speed  
> improvement.

Would that pose indexing issues?  It would also mean that when  
joining two tables you'd have to handle some interesting type  
conversion issues (at times).  We had someone accidentally create a  
largish table with userid as "numeric" and other tables are "bigint",  
it was disastrous for performance (joining).  I'd imagine that if the  
above wasn't done cleverly, that performance problem would be repeated.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/




Re: New version of money type

From
Stephen Frost
Date:
* Theo Schlossnagle (jesus@omniti.com) wrote:
> Would that pose indexing issues?  It would also mean that when
> joining two tables you'd have to handle some interesting type
> conversion issues (at times).  We had someone accidentally create a
> largish table with userid as "numeric" and other tables are "bigint",
> it was disastrous for performance (joining).  I'd imagine that if the
> above wasn't done cleverly, that performance problem would be repeated.

The performance issue you ran into with joins was more likely because
there's no hash function for numeric than the way numerics are stored.
I'm not really sure how I feel about this idea...  If it's handled
completely inside numeric then it might be reasonable to do (there
wouldn't *be* any real 'type conversion', numeric would just be modified
to support both sizes and would handle an upgrading/downgrading, I don't
think the code would be all *that* complex, honestly...).

I don't think the indexing would be an issue either as you can provide
the appropriate operations regardless of the size..  It might make
writing the hash function a bit more interesting, but probably not...

We might want to have a compile-time option for this tho, as not all
architectures handle 64bit integer ops very well.
Thanks,
    Stephen

Re: New version of money type

From
Gregory Stark
Date:
Theo Schlossnagle <jesus@omniti.com> writes:

> Would that pose indexing issues?  It would also mean that when joining two
> tables you'd have to handle some interesting type  conversion issues (at
> times).  We had someone accidentally create a  largish table with userid as
> "numeric" and other tables are "bigint",  it was disastrous for performance
> (joining).  I'd imagine that if the  above wasn't done cleverly, that
> performance problem would be repeated.

That used to be a problem but Tom solved it a little while back. Not a perfect
solution in that it requires lots of cross-data-type operators as the number
of data types grows but it works.

In any case I think Jim was suggesting this be handled internally to the
numeric data type which wouldn't cause this problem. However I'm not sure
anything has to be done. A numeric is an array of 16 bit integers, so anything
under 64k *is* stored just as an integer. 

Well, just an integer plus a useless exponent. I think it would be a neat
trick to normalize the exponent to the end of the last element of the mantissa
rather than the first digit so that integers don't need an exponent.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com


Re: New version of money type

From
Stephen Frost
Date:
* Gregory Stark (stark@enterprisedb.com) wrote:
> In any case I think Jim was suggesting this be handled internally to the
> numeric data type which wouldn't cause this problem. However I'm not sure
> anything has to be done. A numeric is an array of 16 bit integers, so anything
> under 64k *is* stored just as an integer.

Right, which is fine, but for >64k (Actually, isn't it 10,000?),
operations could be done in 1 step using 64bit ints instead of in
multiple steps.  On systems with fast 64bit integer ops (quite a few of
them out there these days...) this seems likely to be an improvement in
performance.

Of course, there's the question of how much of an improvement, how
complicated it makes the code, backwards-compatibility issues, and what
to do about the binary in/out operations.
Thanks,
    Stephen

Re: New version of money type

From
Jim Nasby
Date:
On Sep 16, 2006, at 7:31 PM, Gregory Stark wrote:
>> Would that pose indexing issues?  It would also mean that when  
>> joining two
>> tables you'd have to handle some interesting type  conversion  
>> issues (at
>> times).  We had someone accidentally create a  largish table with  
>> userid as
>> "numeric" and other tables are "bigint",  it was disastrous for  
>> performance
>> (joining).  I'd imagine that if the  above wasn't done cleverly, that
>> performance problem would be repeated.
>
> That used to be a problem but Tom solved it a little while back.  
> Not a perfect
> solution in that it requires lots of cross-data-type operators as  
> the number
> of data types grows but it works.
>
> In any case I think Jim was suggesting this be handled internally  
> to the
> numeric data type which wouldn't cause this problem. However I'm  
> not sure
> anything has to be done. A numeric is an array of 16 bit integers,  
> so anything
> under 64k *is* stored just as an integer.

Yes, I definitely meant for this to be internal-only... end users  
shouldn't notice any difference (except hopefully improved performance).

If all the math is done in 64k chunks then this might not be as big a  
help. Numbers between 2^16 and 2^64 (or 2^32 on some platforms) would  
probably be faster, but how much faster isn't clear. Perhaps the OP  
could do some testing if someone came up with a quick and dirty  
prototype patch.

> Well, just an integer plus a useless exponent. I think it would be  
> a neat
> trick to normalize the exponent to the end of the last element of  
> the mantissa
> rather than the first digit so that integers don't need an exponent.

How would that help? If I'm understanding correctly you're just  
talking about storing how many places after the decimal instead of  
how many in front of it?
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 14 Sep 2006 10:35:03 -0400
"D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> For years I have been promising that a 64 bit version of the money type
> was on the way.  Here it is.  So far it compiles and I have done some
> basic testing on it and it seems to work fine.  Note that the currency
> symbol is also dropped on output as well but it is accepted on input.
> 
> darcy=# select '$92,233,720,368,547,758.07'::money;
>            money
> ----------------------------
>   92,233,720,368,547,758.07

There has been plenty of discussion back and forth but still no ruling
from core.  Is money out in the next release in which case I can
convert this to a contrib module or will this improvement be accepted
for the next release.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Luke Lonergan"
Date:
Though this may be the kiss of death, I favor a 64 bit float version of money.  It's more terse than numeric and a
*lot*faster when performing numeric operations because it would use a cpu intrinsic operand. 


- Luke

Msg is shrt cuz m on ma treo
-----Original Message-----
From:     D'Arcy J.M. Cain [mailto:darcy@druid.net]
Sent:    Thursday, September 28, 2006 11:02 AM Eastern Standard Time
To:    D'Arcy J.M. Cain
Cc:    pgsql-hackers@postgreSQL.org
Subject:    Re: [HACKERS] New version of money type

On Thu, 14 Sep 2006 10:35:03 -0400
"D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> For years I have been promising that a 64 bit version of the money type
> was on the way.  Here it is.  So far it compiles and I have done some
> basic testing on it and it seems to work fine.  Note that the currency
> symbol is also dropped on output as well but it is accepted on input.
>
> darcy=# select '$92,233,720,368,547,758.07'::money;
>            money
> ----------------------------
>   92,233,720,368,547,758.07

There has been plenty of discussion back and forth but still no ruling
from core.  Is money out in the next release in which case I can
convert this to a contrib module or will this improvement be accepted
for the next release.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to
majordomo@postgresql.orgso that your      message can get through to the mailing list cleanly 




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 11:09:17 -0400
"Luke Lonergan" <LLonergan@greenplum.com> wrote:
> Though this may be the kiss of death, I favor a 64 bit float version of money.  It's more terse than numeric and a

I assume you mean "...64 bit INT version..."

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Luke Lonergan"
Date:
Oic - so it's a floating point in an 8 byte int.  That probably limits the speed benefits, no?

- Luke

Msg is shrt cuz m on ma treo
-----Original Message-----
From:     D'Arcy J.M. Cain [mailto:darcy@druid.net]
Sent:    Thursday, September 28, 2006 11:14 AM Eastern Standard Time
To:    Luke Lonergan
Cc:    pgsql-hackers@postgreSQL.org
Subject:    Re: [HACKERS] New version of money type

On Thu, 28 Sep 2006 11:09:17 -0400
"Luke Lonergan" <LLonergan@greenplum.com> wrote:
> Though this may be the kiss of death, I favor a 64 bit float version of money.  It's more terse than numeric and a

I assume you mean "...64 bit INT version..."

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 11:25:45 -0400
"Luke Lonergan" <LLonergan@greenplum.com> wrote:
> Oic - so it's a floating point in an 8 byte int.  That probably limits the speed benefits, no?

No, it's an int type.  Floating point has nothing to do with the money
type, either in the old 32 bit version or the proposed 64 bit version.
It does display in a DECIMAL format but just because there is a decimal
point in the output does not make it floating point.  All internal
calculations are done as integer arithmetic.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Jim C. Nasby"
Date:
On Thu, Sep 28, 2006 at 11:32:37AM -0400, D'Arcy J.M. Cain wrote:
> On Thu, 28 Sep 2006 11:25:45 -0400
> "Luke Lonergan" <LLonergan@greenplum.com> wrote:
> > Oic - so it's a floating point in an 8 byte int.  That probably limits the speed benefits, no?
> 
> No, it's an int type.  Floating point has nothing to do with the money
> type, either in the old 32 bit version or the proposed 64 bit version.
> It does display in a DECIMAL format but just because there is a decimal
> point in the output does not make it floating point.  All internal
> calculations are done as integer arithmetic.

Floating point math and hard-earned money are two things that don't mix
well. :)
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 10:35:01 -0500
"Jim C. Nasby" <jim@nasby.net> wrote:
> Floating point math and hard-earned money are two things that don't mix
> well. :)

Using FP to track money is a good way to stop making any.  :-)

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Luke Lonergan"
Date:
D'Arcy,

On 9/28/06 8:43 AM, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:

> On Thu, 28 Sep 2006 10:35:01 -0500
> "Jim C. Nasby" <jim@nasby.net> wrote:
>> Floating point math and hard-earned money are two things that don't mix
>> well. :)
> 
> Using FP to track money is a good way to stop making any.  :-)

Understood - a cent here and there in rounding can add up, as the earliest
hackers found out.

What I meant is the use of the "point" that "floats", is there an exponent
stored as part of the format?  If so, then the integer arithmetic is not
standard integer operands, right?

Which routines implement the money arithmetic?

- Luke




Re: New version of money type

From
"Luke Lonergan"
Date:
D'Arcy,

On 9/28/06 9:00 AM, "Luke Lonergan" <llonergan@greenplum.com> wrote:

> Which routines implement the money arithmetic?

Ok - so now having read the old documentation and the routine "
backend/utils/adt/cash.c" and the type definition for Cash in "
backend/include/utils/adt/cash.h" I can see that it's:

- Fixed point at NNN.MM
- Stored as an INT32 (or your proposed INT64)
- Operations use native operands (=<>+*/)

The disappointing thing is that there is always a function call involved in
any arithmetic operation.  An even larger benefit could probably be gained
by inlining the routines in cash.c, which is probably inhibited by the
"FUNCTIONCALLxxx" indirections in the executor for operators (different
topic).

So, the NUMERIC arithmetic must be really slow to get 10% improvements in
computational speed.

Based on all of this, I know I would use the 64 bit money type for things
like the TPC-H benchmark...

- Luke 




Re: New version of money type

From
Stephen Frost
Date:
* Luke Lonergan (LLonergan@greenplum.com) wrote:
> Though this may be the kiss of death, I favor a 64 bit float version of money.  It's more terse than numeric and a
*lot*faster when performing numeric operations because it would use a cpu intrinsic operand.
 

What about just having a numeric64, or changing numeric to support
moving to 64bit sizes when necessary and supported by the platform?
Exactly how much faster would it *really* be?  Have you tested it?  At
what point does it become a 'winning' change?

I'm not sure about 'money' in general but these claims of great
performance improvments over numeric just don't fly so easily with me.
numeric isn't all *that* much slower than regular old integer in the
tests that I've done.
Thanks,
    Stephen

Re: New version of money type

From
"Luke Lonergan"
Date:
Stephen,

On 9/28/06 9:44 AM, "Stephen Frost" <sfrost@snowman.net> wrote:

> I'm not sure about 'money' in general but these claims of great
> performance improvments over numeric just don't fly so easily with me.
> numeric isn't all *that* much slower than regular old integer in the
> tests that I've done.

Part of the problem is the *size* of Numeric.  I've just looked for
something that describes the size of a Numeric and I saw an old post that
says: 10 + x/2 bytes

So, a minimum of 10 bytes (compared to the 8 proposed for money64) plus
scale (x) divided by two.

Currently on the TPC-H benchmark, Postgres requires 1.7 times the amount of
internal database storage as what is in the ASCII data file representation.
Oracle and MSFT SQLServer are almost 1:1.  Part of this fluff is the 24
bytes of tuple header, part of it is in the Numeric.

- Luke




Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 12:44:24 -0400
Stephen Frost <sfrost@snowman.net> wrote:
> I'm not sure about 'money' in general but these claims of great
> performance improvments over numeric just don't fly so easily with me.
> numeric isn't all *that* much slower than regular old integer in the
> tests that I've done.

Numeric has been shown to be as good or better than money in I/O
operations.  Where money shines is in internal calculations.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Stephen Frost
Date:
* D'Arcy J.M. Cain (darcy@druid.net) wrote:
> On Thu, 28 Sep 2006 12:44:24 -0400
> Stephen Frost <sfrost@snowman.net> wrote:
> > I'm not sure about 'money' in general but these claims of great
> > performance improvments over numeric just don't fly so easily with me.
> > numeric isn't all *that* much slower than regular old integer in the
> > tests that I've done.
>
> Numeric has been shown to be as good or better than money in I/O
> operations.  Where money shines is in internal calculations.

Which may be an area which could be improved on for numeric, or even a
numeric64 type added for it.  I'm not entirely sure there's a huge
amount to gain there either though...
Thanks,
    Stephen

Re: New version of money type

From
"Luke Lonergan"
Date:
D'Arcy,

On 9/28/06 10:12 AM, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:

> Numeric has been shown to be as good or better than money in I/O
> operations.

What exactly does that mean in the context of a Datum: "I/O operations"?

- Luke




Re: New version of money type

From
Martijn van Oosterhout
Date:
On Thu, Sep 28, 2006 at 11:39:31AM -0700, Luke Lonergan wrote:
> D'Arcy,
>
> On 9/28/06 10:12 AM, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
>
> > Numeric has been shown to be as good or better than money in I/O
> > operations.
>
> What exactly does that mean in the context of a Datum: "I/O operations"?

Converting to/from text format for when dealing with client
applications. Numeric can convert faster than plain integers sometimes.
Numeric isn't that slow really...

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: New version of money type

From
"Luke Lonergan"
Date:
Martijn,

On 9/28/06 11:53 AM, "Martijn van Oosterhout" <kleptog@svana.org> wrote:

> Converting to/from text format for when dealing with client
> applications. Numeric can convert faster than plain integers sometimes.
> Numeric isn't that slow really...

Got it - so the performance benefits of the fixed point versus Numeric are:

- Smaller size of fixed point (less than half)
- Faster arithmetic operations

These should be quantified, so that we can evaluate Money64 as a proposal
and to understand Numeric better.

- Luke 




Re: New version of money type

From
Martijn van Oosterhout
Date:
On Thu, Sep 28, 2006 at 11:57:10AM -0700, Luke Lonergan wrote:
> Got it - so the performance benefits of the fixed point versus Numeric are:
>
> - Smaller size of fixed point (less than half)
> - Faster arithmetic operations
>
> These should be quantified, so that we can evaluate Money64 as a proposal
> and to understand Numeric better.

However, none of this seems to deal with the major problems with the
money type right now:

- Only supports one currency (dollars)
- Only supports one scale (yen has no decimal normally, but what if you
want to track hundredths of a dollar-cent?)

My question, what is this Money64 type buying you over just storing an
integer in your database? There should be some value-add somewhere, but
what is it?

I've written applications tracking money using just an integer, if
there were a special money type, I'd expect it to do something more.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: New version of money type

From
"Luke Lonergan"
Date:
Martijn,

On 9/28/06 12:42 PM, "Martijn van Oosterhout" <kleptog@svana.org> wrote:

> - Only supports one currency (dollars)

What are the manifestations of this?

> - Only supports one scale (yen has no decimal normally, but what if you
> want to track hundredths of a dollar-cent?)

So, without a quantified benefit, this is certainly a non-starter.

- Luke 




Re: New version of money type

From
Martijn van Oosterhout
Date:
On Thu, Sep 28, 2006 at 01:29:57PM -0700, Luke Lonergan wrote:
> Martijn,
>
> On 9/28/06 12:42 PM, "Martijn van Oosterhout" <kleptog@svana.org> wrote:
>
> > - Only supports one currency (dollars)
>
> What are the manifestations of this?

test=# select '100'::money; money
---------$100.00
(1 row)

The use of the dollar sign and the two decimal places make it wrong for
the vast majority of the world's population.

Now, there is some localization involved, so you can play tricks like:

test=# set lc_monetary ='nl_NL';
SET
test=# select '100'::money;  money
-----------EUR100,00
(1 row)

Oops, by changing a GUC variable we just changed the semantic value of
every currency field in the database. You still can't change the number
of decimal places though.

> > - Only supports one scale (yen has no decimal normally, but what if you
> > want to track hundredths of a dollar-cent?)
>
> So, without a quantified benefit, this is certainly a non-starter.

Every new type needs to have a well-defined use-case before it can be
considered for includion.

Currently we have:
- Is possibly faster than numeric
- Takes less space than numeric
- Customisable output (only one currency at a time though)
- Fixed number of decimal places

What else?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 11:39:31 -0700
"Luke Lonergan" <llonergan@greenplum.com> wrote:
> > Numeric has been shown to be as good or better than money in I/O
> > operations.
> 
> What exactly does that mean in the context of a Datum: "I/O operations"?

It means that numeric is better and parsing/storing/displaying than
money.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 22:53:34 +0200
Martijn van Oosterhout <kleptog@svana.org> wrote:
> Every new type needs to have a well-defined use-case before it can be
> considered for includion.

Well, it is already included.  The current proposal is simply to
improve the existing type.  I guess you are arguing a different
proposal altogether - to remove the existing type.

> Currently we have:
> - Is possibly faster than numeric

I suppose I should quantify this but it's hard to get motivated after
the many man-hours (mine and my staff) I had to spend on code and
schema optimizations I needed to do just to get closer to the previous
speed our aps had before we converted from money to numeric.  I will
try to find time to put together a test that appoximates that real
world example.

> - Takes less space than numeric

Never really considered this a major improvement over numeric given the
cost of disk these days.  I suppose it could be contributing to the
speed increase.

> - Customisable output (only one currency at a time though)
> - Fixed number of decimal places

The original code actually handled number of decimal places.  It tended
to cause problems though.  These are areas that the existing type, as
well as the proposed change, could be worked on.  I would hesitate to
work on both together though and going to 64bit will probably add more
value right now than those things, certainly for existing users of the
type.

By the way, the current proposal actually removes the currency symbol
but I have received complaints about that.  It should probably go back
just because it is outside of the scope of the primary change.  That
can be dealt with later.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"Jim C. Nasby"
Date:
On Thu, Sep 28, 2006 at 05:19:47PM -0400, D'Arcy J.M. Cain wrote:
> On Thu, 28 Sep 2006 22:53:34 +0200
> Martijn van Oosterhout <kleptog@svana.org> wrote:
> > Every new type needs to have a well-defined use-case before it can be
> > considered for includion.
> 
> Well, it is already included.  The current proposal is simply to
> improve the existing type.  I guess you are arguing a different
> proposal altogether - to remove the existing type.
The existing type is depricated and has been since at least 8.1; so yes,
it's slated for removal.

> > Currently we have:
> > - Is possibly faster than numeric
> 
> I suppose I should quantify this but it's hard to get motivated after
> the many man-hours (mine and my staff) I had to spend on code and
> schema optimizations I needed to do just to get closer to the previous
> speed our aps had before we converted from money to numeric.  I will
> try to find time to put together a test that appoximates that real
> world example.
> 
> > - Takes less space than numeric
> 
> Never really considered this a major improvement over numeric given the
> cost of disk these days.  I suppose it could be contributing to the
> speed increase.
Less space == more speed

> > - Customisable output (only one currency at a time though)
> > - Fixed number of decimal places
> 
> The original code actually handled number of decimal places.  It tended
> to cause problems though.  These are areas that the existing type, as
> well as the proposed change, could be worked on.  I would hesitate to
> work on both together though and going to 64bit will probably add more
> value right now than those things, certainly for existing users of the
> type.
> 
> By the way, the current proposal actually removes the currency symbol
> but I have received complaints about that.  It should probably go back
> just because it is outside of the scope of the primary change.  That
> can be dealt with later.

Perhaps a good compromise would be to call your type 'USD' or something
similar. I can see where there's use for it, but it seems too limited to
consider it a generic money type.

What would be ideal is a money type that stored what currency was used
and let you change precision (within reason).
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


Re: New version of money type

From
Tom Lane
Date:
"Jim C. Nasby" <jim@nasby.net> writes:
> On Thu, Sep 28, 2006 at 05:19:47PM -0400, D'Arcy J.M. Cain wrote:
>> Well, it is already included.  The current proposal is simply to
>> improve the existing type.  I guess you are arguing a different
>> proposal altogether - to remove the existing type.
> The existing type is depricated and has been since at least 8.1; so yes,
> it's slated for removal.

Well, my perception of that has always been "it needs to be upgraded or
removed".  So if D'Arcy wants to work on the improvement angle, I have
no problem with him doing so.  The thing we need to negotiate is "how
much improvement is needed to keep it in core".
        regards, tom lane


Re: New version of money type

From
"Jim C. Nasby"
Date:
On Thu, Sep 28, 2006 at 11:23:30PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > On Thu, Sep 28, 2006 at 05:19:47PM -0400, D'Arcy J.M. Cain wrote:
> >> Well, it is already included.  The current proposal is simply to
> >> improve the existing type.  I guess you are arguing a different
> >> proposal altogether - to remove the existing type.
>  
> > The existing type is depricated and has been since at least 8.1; so yes,
> > it's slated for removal.
> 
> Well, my perception of that has always been "it needs to be upgraded or
> removed".  So if D'Arcy wants to work on the improvement angle, I have
> no problem with him doing so.  The thing we need to negotiate is "how
> much improvement is needed to keep it in core".

I think it's also important to protect for the possibility of a more
complete (and probably incompatible) type in the future, such as one
that stores what currency a value is in.

Hrm... does ANSI say anything about money types?
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


Re: New version of money type

From
Tom Lane
Date:
"Jim C. Nasby" <jim@nasby.net> writes:
> I think it's also important to protect for the possibility of a more
> complete (and probably incompatible) type in the future, such as one
> that stores what currency a value is in.

Well, such a type could be called "currency", "cash", "forex" or several
other possibilities, so I don't see any particular argument that "money"
has to be removed before something better can exist.  The tightrope that
D'Arcy has to walk is different: improving "money" without making it so
incompatible as to break existing apps that use it.

> Hrm... does ANSI say anything about money types?

No.
        regards, tom lane


Re: New version of money type

From
Martijn van Oosterhout
Date:
On Thu, Sep 28, 2006 at 06:32:11PM -0500, Jim C. Nasby wrote:
> What would be ideal is a money type that stored what currency was used
> and let you change precision (within reason).

The taggedtypes version of currency does half of that, by storing the
currency and allowing the output format to depend on that. It doesn't
allow you to easily change the precision though, that would require
user-defined typmod which is still under discussion.

It would be possible to create a taggedtypes version of currency based
on int64. With the currency tag it would be 12 bytes total. And the
number of decimal places could be defined per currency...

Interesting thought, probably wouldn't take more than an hour to whip
up.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: New version of money type

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Sep 29, 2006 at 12:19:07PM +0200, Martijn van Oosterhout wrote:
> On Thu, Sep 28, 2006 at 06:32:11PM -0500, Jim C. Nasby wrote:
> > What would be ideal is a money type that stored what currency was used
> > and let you change precision (within reason).
> 
> The taggedtypes version of currency does half of that, by storing the
> currency and allowing the output format to depend on that. It doesn't
> allow you to easily change the precision though, that would require
> user-defined typmod which is still under discussion.
> 
> It would be possible to create a taggedtypes version of currency based
> on int64. With the currency tag it would be 12 bytes total. And the
> number of decimal places could be defined per currency...
> 
> Interesting thought, probably wouldn't take more than an hour to whip
> up.

If you are at that, it's worth noting that the currency tag consists of
three capital ASCII letters. That would be fifteen bits, take or give.
That leaves fourty-eightish bits for the number or about 10^14.

THis is only half-serious, since there are other problems with
currencies (their value is dependent on time, all that Pandora's box).

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFHfWlBcgs9XrR2kYRAhieAJ9GYKruXeW3nqGyg0TO8Mo5bFBNMQCfbfzK
lbARH+l5PxIexOElcxTg3WE=
=//LX
-----END PGP SIGNATURE-----



Re: New version of money type

From
David Fetter
Date:
On Sat, Sep 30, 2006 at 04:42:13AM +0000, tomas@tuxteam.de wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Fri, Sep 29, 2006 at 12:19:07PM +0200, Martijn van Oosterhout wrote:
> > On Thu, Sep 28, 2006 at 06:32:11PM -0500, Jim C. Nasby wrote:
> If you are at that, it's worth noting that the currency tag consists
> of three capital ASCII letters. That would be fifteen bits, take or
> give.

I feel silly for even mentioning this, but there are less than 256
countries in the UN, and as far as I know, each has at most one
currency, so you could use 8 bits instead of 15.

> That leaves forty-eightish bits for the number or about 10^14.

By the above calculation, 56 bits or about 7.2 * 10^16.

> THis is only half-serious, since there are other problems with
> currencies (their value is dependent on time, all that Pandora's
> box).

It's not just dependent on time.  Exchange rates vary in such a way
that the relationships are not always transitive :P

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: New version of money type

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Sep 29, 2006 at 10:43:53PM -0700, David Fetter wrote:
> On Sat, Sep 30, 2006 at 04:42:13AM +0000, tomas@tuxteam.de wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> > 
> > On Fri, Sep 29, 2006 at 12:19:07PM +0200, Martijn van Oosterhout wrote:
> > > On Thu, Sep 28, 2006 at 06:32:11PM -0500, Jim C. Nasby wrote:

[15 bit for three upcase ASCII]

> I feel silly for even mentioning this, but there are less than 256
> countries in the UN, and as far as I know, each has at most one
> currency, so you could use 8 bits instead of 15.

Hm. But then you'd have to cope with a mapping (currency-id ->
description) which changes over time. Maybe it'd suffice to postulate
that "no id be reused".

> > That leaves forty-eightish bits for the number or about 10^14.
> 
> By the above calculation, 56 bits or about 7.2 * 10^16.

Yes, way more useful than 10^14 (about a hundred times ;)

> It's not just dependent on time.  Exchange rates vary in such a way
> that the relationships are not always transitive :P

:-)

Regards
- -- tomas
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFHilyBcgs9XrR2kYRAg+mAJ96+TTAjqEJK7J1nbI7EwVibYFoxwCfWElO
VJCskdQThqxI90gzTX+gR8A=
=OCN0
-----END PGP SIGNATURE-----



Re: New version of money type

From
"Xiaofeng Zhao"
Date:
> I feel silly for even mentioning this, but there are less than 256
> countries in the UN, and as far as I know, each has at most one
> currency, so you could use 8 bits instead of 15.
>
That's not always true, e.g. China has RMB and HKD.  Also Taiwan is not a 
member country of UN but I don't think one would exclude TWD.

There'll also times a country may transit from one currency to another. 
Even a currency (currency of most continental European countries before 
Euro) is no more being used, it may still need to be supported.

xz 



Re: New version of money type

From
David Fetter
Date:
On Sat, Sep 30, 2006 at 11:36:04AM -0400, Xiaofeng Zhao wrote:
> >I feel silly for even mentioning this, but there are less than 256
> >countries in the UN, and as far as I know, each has at most one
> >currency, so you could use 8 bits instead of 15.
> >
> That's not always true, e.g. China has RMB and HKD.  Also Taiwan is
> not a member country of UN but I don't think one would exclude TWD.

Right.  There are several countries whose currency is USD, so I still
contend that at any given instant, there are fewer than 256
currencies, so we're back to 8 bits.

> There'll also times a country may transit from one currency to
> another.  Even a currency (currency of most continental European
> countries before Euro) is no more being used, it may still need to
> be supported.

The "money" type is far too simplistic to model this kind of thing.  A
really sophisticated representation of money would have to take time,
inflation/deflation, pairwise exchange rates, etc. into account.  It
would look more like a schema with a large data set and a large body
of code loaded into it than it would a data type.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: New version of money type

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> The "money" type is far too simplistic to model this kind of thing.  A
> really sophisticated representation of money would have to take time,
> inflation/deflation, pairwise exchange rates, etc. into account.  It
> would look more like a schema with a large data set and a large body
> of code loaded into it than it would a data type.

I don't think that's the appropriate functionality for a data type.
I used to be in the currency trading game (before I decided hacking
Postgres was more fun), and if you ask me, the people who want this
functionality are specifically interested in those exchange rates and
time variations --- it's exactly the purpose of their databases to
store, search, and manipulate that data, so burying it behind the scenes
in a datatype is exactly the wrong approach.

At least for what I was doing back then, a tagged type is exactly the
right thing: all we'd have wanted is for it to keep us from thinking
that adding 2 USD and 2 EUR directly was a sane computation.

Oh BTW: 10^14 is not enough dynamic range --- those guys push around
*serious* amounts of money.  Bill Gates' net wealth is somewhere north
of 10^13 cents, and he's just a private citizen not a bank.
        regards, tom lane


Re: New version of money type

From
"Xiaofeng Zhao"
Date:
>> There'll also times a country may transit from one currency to
>> another.  Even a currency (currency of most continental European
>> countries before Euro) is no more being used, it may still need to
>> be supported.
>
> The "money" type is far too simplistic to model this kind of thing.  A
> really sophisticated representation of money would have to take time,
> inflation/deflation, pairwise exchange rates, etc. into account.  It
> would look more like a schema with a large data set and a large body
> of code loaded into it than it would a data type.

The statement of my bank account does not contain any of the quantities you 
mentioned.  But when some body open a statement from year 2000, most likely 
he expect to see the balance and transcations are in, say, German Marks, not 
in Euros.

xz



Re: New version of money type

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Sep 30, 2006 at 01:00:05PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
[...]
> Oh BTW: 10^14 is not enough dynamic range --- those guys push around
> *serious* amounts of money.  Bill Gates' net wealth is somewhere north
> of 10^13 cents, and he's just a private citizen not a bank.

I do agree that a range in the 10^14 is too small. Even 10^16 seems to
be uncomfortably near to existing values. And thensome like to do things
with (decimal) sub-cent accuracy (think percents and prices per weight
unit). May be 64 bit is just not enough for a tagged money type?

Regards
- -- tomas
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFH0cDBcgs9XrR2kYRAhBfAJ9xvi1z8N73VpoiPSczZCUgBENKrgCdHGOd
fEY52y+um4jgW1oUkb8YQ64=
=0UGx
-----END PGP SIGNATURE-----



Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 28 Sep 2006 23:23:30 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > The existing type is depricated and has been since at least 8.1; so yes,
> > it's slated for removal.
> 
> Well, my perception of that has always been "it needs to be upgraded or
> removed".  So if D'Arcy wants to work on the improvement angle, I have
> no problem with him doing so.  The thing we need to negotiate is "how
> much improvement is needed to keep it in core".

Well, the patch I submitted is definitely an improvement over the
existing version.  Are you saying that I have to make further
improvements before these ones can be imported?  ISTM that going to 64
bit without any other change is big enough to warrant the change as
is.  Once that is done I would be happy to work on other improvements
but my experience tells me not to make more than one major change at a
time.

The one issue I have with my existing patch though is the removal of
the currency symbol from the output.  There have been many suggestions
that that just gets in the way but, following up on my own statement
above, this is two changes, not one, and perhaps should be left out of
the patch for that reason.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Tom Lane
Date:
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, my perception of that has always been "it needs to be upgraded or
>> removed".  So if D'Arcy wants to work on the improvement angle, I have
>> no problem with him doing so.  The thing we need to negotiate is "how
>> much improvement is needed to keep it in core".

> Well, the patch I submitted is definitely an improvement over the
> existing version.  Are you saying that I have to make further
> improvements before these ones can be imported?

I didn't say that.  I was responding to someone whose position seemed to
be "money is going to be removed, therefore you shouldn't work on it".
I wanted to know exactly what would need to be fixed before they'd not
want it removed.
        regards, tom lane


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 12 Oct 2006 13:21:37 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Well, the patch I submitted is definitely an improvement over the
> > existing version.  Are you saying that I have to make further
> > improvements before these ones can be imported?
> 
> I didn't say that.  I was responding to someone whose position seemed to
> be "money is going to be removed, therefore you shouldn't work on it".
> I wanted to know exactly what would need to be fixed before they'd not
> want it removed.

Cool.  So what do I do with the patch?  Should I add the currency
symbol back in and commit or should I resubmit the patch to hackers for
further review?

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Tom Lane
Date:
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> Cool.  So what do I do with the patch?  Should I add the currency
> symbol back in and commit or should I resubmit the patch to hackers for
> further review?

Well, one thing you definitely *don't* do is commit right now, because
we're in feature freeze, not to mention trying to avoid forced initdbs
now that beta has started.  Sit on it till 8.3 is branched, and
meanwhile think about what you want to do with the currency-symbol
issue...
        regards, tom lane


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 12 Oct 2006 14:17:33 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > Cool.  So what do I do with the patch?  Should I add the currency
> > symbol back in and commit or should I resubmit the patch to hackers for
> > further review?
> 
> Well, one thing you definitely *don't* do is commit right now, because
> we're in feature freeze, not to mention trying to avoid forced initdbs
> now that beta has started.  Sit on it till 8.3 is branched, and

OK.  I hadn't thought of it as a new feature per se but I understand
the initdb issue.  Holding at 30,000 feet, ground control.

> meanwhile think about what you want to do with the currency-symbol
> issue...

Personally I don't see a need for it but I am currently in favour of
adding it back in before committing just so that we can deal with the
issue separately.  The same as the other changes being discussed.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Bruce Momjian
Date:
This thread has been saved for the 8.3 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

D'Arcy J.M. Cain wrote:
> On Thu, 12 Oct 2006 14:17:33 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > > Cool.  So what do I do with the patch?  Should I add the currency
> > > symbol back in and commit or should I resubmit the patch to hackers for
> > > further review?
> > 
> > Well, one thing you definitely *don't* do is commit right now, because
> > we're in feature freeze, not to mention trying to avoid forced initdbs
> > now that beta has started.  Sit on it till 8.3 is branched, and
> 
> OK.  I hadn't thought of it as a new feature per se but I understand
> the initdb issue.  Holding at 30,000 feet, ground control.
> 
> > meanwhile think about what you want to do with the currency-symbol
> > issue...
> 
> Personally I don't see a need for it but I am currently in favour of
> adding it back in before committing just so that we can deal with the
> issue separately.  The same as the other changes being discussed.
> 
> -- 
> D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
> http://www.druid.net/darcy/                |  and a sheep voting on
> +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 12 Oct 2006 14:24:22 -0400
"D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> On Thu, 12 Oct 2006 14:17:33 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > > Cool.  So what do I do with the patch?  Should I add the currency
> > > symbol back in and commit or should I resubmit the patch to hackers for
> > > further review?
> >
> > Well, one thing you definitely *don't* do is commit right now, because
> > we're in feature freeze, not to mention trying to avoid forced initdbs
> > now that beta has started.  Sit on it till 8.3 is branched, and
>
> OK.  I hadn't thought of it as a new feature per se but I understand
> the initdb issue.  Holding at 30,000 feet, ground control.
>
> > meanwhile think about what you want to do with the currency-symbol
> > issue...
>
> Personally I don't see a need for it but I am currently in favour of
> adding it back in before committing just so that we can deal with the
> issue separately.  The same as the other changes being discussed.

Now that 8.3 has been branched shall I go ahead and commit?  As
discussed I will put the currency symbol back in just so that it can be
discussed and worked on as a completely separate issue.  I have
attached the current patch against HEAD.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Attachment

Re: New version of money type

From
David Fetter
Date:
On Wed, Dec 20, 2006 at 08:44:07PM -0500, D'Arcy J.M. Cain wrote:
> On Thu, 12 Oct 2006 14:24:22 -0400
> "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> > On Thu, 12 Oct 2006 14:17:33 -0400
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > > > Cool.  So what do I do with the patch?  Should I add the
> > > > currency symbol back in and commit or should I resubmit the
> > > > patch to hackers for further review?
> > > 
> > > Well, one thing you definitely *don't* do is commit right now,
> > > because we're in feature freeze, not to mention trying to avoid
> > > forced initdbs now that beta has started.  Sit on it till 8.3 is
> > > branched, and
> > 
> > OK.  I hadn't thought of it as a new feature per se but I
> > understand the initdb issue.  Holding at 30,000 feet, ground
> > control.
> > 
> > > meanwhile think about what you want to do with the
> > > currency-symbol issue...
> > 
> > Personally I don't see a need for it but I am currently in favour
> > of adding it back in before committing just so that we can deal
> > with the issue separately.  The same as the other changes being
> > discussed.
> 
> Now that 8.3 has been branched shall I go ahead and commit?  As
> discussed I will put the currency symbol back in just so that it can
> be discussed and worked on as a completely separate issue.  I have
> attached the current patch against HEAD.

I noticed that all your numbers are in English.  Is it necessary to
hard-code all that?  Also, you're assuming that powers of 10 which are
divisible by 3 are the relevant ones.  In China, it's powers of 10
divisible by 4, and in India, it's 0, 1, 2, 3, followed by odd numbers
up through 19.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 21 Dec 2006 00:21:08 -0800
David Fetter <david@fetter.org> wrote:
> On Wed, Dec 20, 2006 at 08:44:07PM -0500, D'Arcy J.M. Cain wrote:
> > Now that 8.3 has been branched shall I go ahead and commit?  As
> > discussed I will put the currency symbol back in just so that it can
> > be discussed and worked on as a completely separate issue.  I have
> > attached the current patch against HEAD.
> 
> I noticed that all your numbers are in English.  Is it necessary to
> hard-code all that?  Also, you're assuming that powers of 10 which are
> divisible by 3 are the relevant ones.  In China, it's powers of 10
> divisible by 4, and in India, it's 0, 1, 2, 3, followed by odd numbers
> up through 19.

Very good points.  However, like the currency symbol issue I would like
to separate that into another discussion.  The code already exists with
the warts you mention (and more) and this proposal is to fix one thing
that will make it more useful to others.  Let's get that change in and
then start fixing up some of those other issues.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Tom Lane
Date:
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> Very good points.  However, like the currency symbol issue I would like
> to separate that into another discussion.  The code already exists with
> the warts you mention (and more) and this proposal is to fix one thing
> that will make it more useful to others.  Let's get that change in and
> then start fixing up some of those other issues.

I've forgotten now --- was this patch intended *only* to convert money
from int4 to int8 underlying representation, or did you do other things?
It looks like there are unrelated changes in the patch, but I'm not sure
if you just moved code around or did something more interesting.

One bug I see in it is that you'd better make the alignment 'd' if the
type is to be int8.  Also I much dislike these changes:

-    int32        i = PG_GETARG_INT32(1);
+    int64        i = PG_GETARG_INT32(1);

I think they may not actually be wrong, but they certainly *look* wrong;
in general the declared type of a parameter variable in a C-coded SQL
function ought to match what the SQL signature says.  Anyway there is no
need that I can see to widen these variables.  Every C compiler knows
what to do if you ask it for arithmetic on a long and an int.

(Speaking of which, have you thought about what happens on a machine
with no 64-bit int, such that "int64" is really just 32 bits?  Ideally
the code should continue to function but with reduced range.  I didn't
see any places where you were obviously depending on the range, but
it's something to have in the back of your mind while coding.)
        regards, tom lane


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 21 Dec 2006 10:47:52 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > Very good points.  However, like the currency symbol issue I would like
> > to separate that into another discussion.  The code already exists with
> > the warts you mention (and more) and this proposal is to fix one thing
> > that will make it more useful to others.  Let's get that change in and
> > then start fixing up some of those other issues.
> 
> I've forgotten now --- was this patch intended *only* to convert money
> from int4 to int8 underlying representation, or did you do other things?

Well, the main intention was to just widen the underlying storage and
thus increase the range to the point that the type is useful to more
users.  In fact, as you can see, I have removed the change to drop the
currency on output just to keep this change to a single issue.
However, there was a little bit of cleanup as well.  I removed some
self-balancing XXX comments for example.  That's what CVS log is for.
I moved a few functions around in order to make static functions self
prototyping.  I added some consts to variables where appropriate.  The
cash_words function needed to be changed to accomodate the billions,
trillions and quadrillions that can now be handled.

Everything else should be directly related to the type change and
self-explanatory.

> It looks like there are unrelated changes in the patch, but I'm not sure
> if you just moved code around or did something more interesting.

Hopefully nothing too "interesting."  :-)

> One bug I see in it is that you'd better make the alignment 'd' if the

Fixed in my local tree.  Thanks.

> type is to be int8.  Also I much dislike these changes:
> 
> -    int32        i = PG_GETARG_INT32(1);
> +    int64        i = PG_GETARG_INT32(1);
> 
> I think they may not actually be wrong, but they certainly *look* wrong;
> in general the declared type of a parameter variable in a C-coded SQL
> function ought to match what the SQL signature says.  Anyway there is no
> need that I can see to widen these variables.  Every C compiler knows
> what to do if you ask it for arithmetic on a long and an int.

Right but I still need to accept int64 args here.  I have changed the
two relevant places to use PG_GETARG_INT64(1).

> (Speaking of which, have you thought about what happens on a machine
> with no 64-bit int, such that "int64" is really just 32 bits?  Ideally
> the code should continue to function but with reduced range.  I didn't
> see any places where you were obviously depending on the range, but
> it's something to have in the back of your mind while coding.)

Does PGSQL run on any such machines?  If so perhaps someone can
volunteer to do some testing if they have one.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 21 Dec 2006 10:47:52 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> One bug I see in it is that you'd better make the alignment 'd' if the
> type is to be int8.  Also I much dislike these changes:
> 
> -    int32        i = PG_GETARG_INT32(1);
> +    int64        i = PG_GETARG_INT32(1);

As I have made the few corrections that you pointed out, should I go
ahead and commit so that it can be tested in a wider group?  Also,
there are further ideas out there to improve the type further that
would be easier to handle with this out of the way.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
"D'Arcy J.M. Cain"
Date:
On Thu, 21 Dec 2006 10:47:52 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> One bug I see in it is that you'd better make the alignment 'd' if the
> type is to be int8.  Also I much dislike these changes:
> 
> -    int32        i = PG_GETARG_INT32(1);
> +    int64        i = PG_GETARG_INT32(1);

I changed this and a few other things.  I didn't see any response to my
question though.  Shall I go ahead and commit now so that we can test
in a wider setting?  I haven't committed anything in years and I am
hesitant to do so now without consencus.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: New version of money type

From
Tom Lane
Date:
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> I changed this and a few other things.  I didn't see any response to my
> question though.  Shall I go ahead and commit now so that we can test
> in a wider setting?  I haven't committed anything in years and I am
> hesitant to do so now without consencus.

FWIW, as long as you responded to my coding-style criticisms I don't
have any problem with your committing it.  Perhaps someone else does...
        regards, tom lane