Add generate_series(numeric, numeric)
Hi,
I am newbie in postgresql development, so i took easy item in Todo list "
Add generate_series(numeric, numeric)". First, i changed function with
analogue funcionality (generate_series_timestamp) and added new object in
pg_proc (object id is 6000). My changes successfully was compiled.
I have found some problems when code was tested. (remark step=1.0)
1) STATEMENT: SELECT generate_series(1.0,6.1);
1: generate_series (typeid = 1700, len = -1, typmod = -1, byval = f)
----
1: generate_series = "1.0" (typeid = 1700, len = -1, typmod = -1, byval =
f)
----
1: generate_series = "2.0" (typeid = 1700, len = -1, typmod = -1, byval =
f)
----
1: generate_series = "3.0" (typeid = 1700, len = -1, typmod = -1, byval =
f)
----
1: generate_series = "4.0" (typeid = 1700, len = -1, typmod = -1, byval =
f)
----
1: generate_series = "5.0" (typeid = 1700, len = -1, typmod = -1, byval =
f)
----
1: generate_series = "6.0" (typeid = 1700, len = -1, typmod = -1, byval =
f)
----
Function work.
2) STATEMENT: SELECT * FROM generate_series(1.0, 6.1)
1: generate_series (typeid = 1700, len = -1, typmod = -1, byval = f)
----
make_result(): NUMERIC w=0 d=0 POS 0001
CURRENT:: NUMERIC w=0 d=1 POS 0001
FINISH:: NUMERIC w=0 d=1 POS 0006 1000
STEP:: NUMERIC w=0 d=0 POS 0001
make_result(): NUMERIC w=0 d=1 POS 0002
CURRENT:: NUMERIC w=32639 d=16255 NEG 0000 .... (more 0000)
And postgres was crashed.
Could you help to find mistakes?
Some questions:
1) Is correct using Numeric in generate_series_numeric_fctx instead of
NumericVar?
2) How do you determine object id for new function? Maybe you're looking
for last object id in catalog directory (src/include/catalog/pg_*.h) and
increase by one last object id.
P.S. Sorry, I have made mistakes in message, because english isn't native
language.
Attachments:
0001-Add-function-generate_series-numeric-numeric.patchtext/x-patch; charset=US-ASCII; name=0001-Add-function-generate_series-numeric-numeric.patchDownload
From 916bfe117e464fe9185f294cbf4c9979758e7651 Mon Sep 17 00:00:00 2001
From: Malyugin Platon <malugin.p@gmail.com>
Date: Mon, 29 Sep 2014 09:40:56 +0700
Subject: [PATCH] Add function generate_series(numeric, numeric)
---
src/backend/utils/adt/numeric.c | 73 ++++++++++++++++++++++++++++++++++++++---
src/include/catalog/pg_proc.h | 2 ++
src/include/utils/builtins.h | 1 +
3 files changed, 72 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 19d0bdc..73cd169 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -14,7 +14,7 @@
* Copyright (c) 1998-2014, PostgreSQL Global Development Group
*
* IDENTIFICATION
- * src/backend/utils/adt/numeric.c
+ * src/backend/utils/adt/numeric.cn
*
*-------------------------------------------------------------------------
*/
@@ -35,13 +35,13 @@
#include "utils/builtins.h"
#include "utils/int8.h"
#include "utils/numeric.h"
-
+#include "funcapi.h"
/* ----------
* Uncomment the following to enable compilation of dump_numeric()
* and dump_var() and to get a dump of any result produced by make_result().
- * ----------
+ * ---------- */
#define NUMERIC_DEBUG
- */
+
/* ----------
@@ -260,6 +260,14 @@ typedef struct NumericVar
} NumericVar;
+typedef struct
+{
+ Numeric current;
+ Numeric finish;
+ Numeric step;
+ int step_sign;
+} generate_series_numeric_fctx;
+
/* ----------
* Some preinitialized constants
* ----------
@@ -1221,6 +1229,63 @@ numeric_floor(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+Datum
+generate_series_numeric(PG_FUNCTION_ARGS)
+{
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ Numeric res;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start = PG_GETARG_NUMERIC(0);
+ Numeric finish = PG_GETARG_NUMERIC(1);
+ MemoryContext oldcontext;
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ fctx = (generate_series_numeric_fctx *)palloc(sizeof(generate_series_numeric_fctx));
+
+ fctx->current = start;
+ fctx->finish = finish;
+ fctx->step = make_result(&const_one);
+ fctx->step_sign = 1;
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+
+ fctx = funcctx->user_fctx;
+ dump_numeric("CURRENT:", fctx->current);
+ dump_numeric("FINISH:", fctx->finish);
+ dump_numeric("STEP:", fctx->step);
+
+ res = fctx->current;
+
+ if (fctx->step_sign > 0 ?
+ cmp_numerics(fctx->current, fctx->finish) <= 0 :
+ cmp_numerics(fctx->current, fctx->finish) >= 0)
+ {
+ NumericVar current;
+ NumericVar step;
+ NumericVar new;
+
+ init_var_from_num(fctx->current, ¤t);
+ init_var_from_num(fctx->step, &step);
+ add_var(¤t, &step, &new);
+
+ fctx->current = make_result(&new);
+
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(res));
+ }
+ else
+ {
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0af1248..7530b64 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3909,6 +3909,8 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index bbb5d39..341606e 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1036,6 +1036,7 @@ extern Datum int8_avg(PG_FUNCTION_ARGS);
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
--
1.9.1
Hi,
Nice patch! And welcome here.
On Mon, Sep 29, 2014 at 12:42 PM, Платон Малюгин <malugin.p@gmail.com>
wrote:
Could you help to find mistakes?
This implementation is rather broken, particularly when thinking that this
code could be used with a negative step... I also see no point in saving
explicitly the step sign in the function context.
Some questions:
1) Is correct using Numeric in generate_series_numeric_fctx instead of
NumericVar?
I'd rather go with NumericVar to facilitate the arithmetic operations at
each step between the current and to avoid recomputing at the finish and
current values all the time, saving a bit of process for each loop. It also
simplifies the calculation of each value. This way you could as well use
cmp_var with const_zero to be sure that a given NumericVar is positive or
negative, simplifying process.
2) How do you determine object id for new function? Maybe you're looking
for last object id in catalog directory (src/include/catalog/pg_*.h) and
increase by one last object id.
You can use the script unused_oids in src/include/catalog/ to find unused
oids. For example after applying with your patch:
$ cd src/include/catalog && ./unused_oids
2 - 9
32
86 - 88
90
3154
3156
3259 - 3453
3573 - 3591
3787 - 3801
3952
3954
3994 - 3999
4051 - 5999
6001 - 9999
Btw, while looking at your patch, I actually hacked it a bit and finished
with the attached:
- changed process to use NumericVar instead of Numeric
- addition of custom step values with a function
generate_series(numeric,numeric,numeric)
- some cleanup and some comments here and there
That's still WIP, but feel free to use it for future work. If you are able
to add documentation and regression tests to this patch, I would recommend
that you register it to the next commit fest, where it would get more
review, and hopefully it will get committed. The next commit fest begins on
the 15th of October:
https://commitfest.postgresql.org/action/commitfest_view?id=24
Regards,
--
Michael
Attachments:
20140929_generate_series_numeric.patchtext/x-patch; charset=US-ASCII; name=20140929_generate_series_numeric.patchDownload
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 2d6a4cb..975843d 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -28,6 +28,7 @@
#include "access/hash.h"
#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -260,6 +261,13 @@ typedef struct NumericVar
} NumericVar;
+typedef struct
+{
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+} generate_series_numeric_fctx;
+
/* ----------
* Some preinitialized constants
* ----------
@@ -1221,6 +1229,111 @@ numeric_floor(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+
+/*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+Datum
+generate_series_numeric(PG_FUNCTION_ARGS)
+{
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ Numeric res;
+ NumericVar current_var;
+ NumericVar finish_var;
+ NumericVar step_var;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start = PG_GETARG_NUMERIC(0);
+ Numeric finish = PG_GETARG_NUMERIC(1);
+ NumericVar steploc;
+ MemoryContext oldcontext;
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step;
+ step = PG_GETARG_NUMERIC(2);
+
+ /* Transform step into a variable that can be manipulated */
+ init_var_from_num(step, &steploc);
+ }
+ else
+ {
+ init_var(&steploc);
+ set_var_from_var(&steploc, &const_one);
+ }
+
+ /* Step cannot be zero */
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* allocate memory for user context */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *)
+ palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start, ¤t_var);
+ init_var_from_num(finish, &finish_var);
+ set_var_from_var(&steploc, &step_var);
+ free_var(&steploc);
+ fctx->current = current_var;
+ fctx->finish = finish_var;
+ fctx->step = step_var;
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+ current_var = fctx->current;
+ finish_var = fctx->finish;
+ step_var = fctx->step;
+ res = make_result(¤t_var);
+
+ if ((cmp_var(&step_var, &const_zero) > 0 &&
+ cmp_var(¤t_var, &finish_var) <= 0) ||
+ (cmp_var(&step_var, &const_zero) < 0 &&
+ cmp_var(¤t_var, &finish_var) >= 0))
+ {
+ NumericVar tmp;
+ init_var(&tmp);
+
+ /* Increment for next iteration */
+ add_var(¤t_var, &step_var, &tmp);
+ set_var_from_var(&tmp, ¤t_var);
+ fctx->current = current_var;
+ free_var(&tmp);
+
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(res));
+ }
+ else
+ {
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3ce9849..ccdf3e8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3923,6 +3923,10 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
+DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index d88e7a3..511518d 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1040,6 +1040,7 @@ extern Datum int8_avg(PG_FUNCTION_ARGS);
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
On Mon, Sep 29, 2014 at 4:19 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:
Michael
Btw, while looking at your patch, I actually hacked it a bit and finished
with the attached:
- changed process to use NumericVar instead of Numeric
- addition of custom step values with a function
generate_series(numeric,numeric,numeric)
- some cleanup and some comments here and there
That's still WIP, but feel free to use it for future work. If you are able
to add documentation and regression tests to this patch, I would recommend
that you register it to the next commit fest, where it would get more
review, and hopefully it will get committed.
Oops, it seems that I have been too hasty here. With a fresh mind I looked
at my own patch again and found two bugs:
- Incorrect initialization of step variable with const_one
- Incorrect calculation of each step's value, making stuff crash, it is
necessary to switch to the context of the function to perform operations on
a temporary variable first.
Platon (am I writing your name correctly??), feel free to pick up the
attached version, it works for me:
=# SELECT * FROM generate_series(0.8, -4, -0.7);
generate_series
-----------------
0.8
0.1
-0.6
-1.3
-2.0
-2.7
-3.4
(7 rows)
=# SELECT * FROM generate_series(0.8, 5, 1.2);
generate_series
-----------------
0.8
2.0
3.2
4.4
(4 rows)
=# SELECT * FROM generate_series(0.8, 5);
generate_series
-----------------
0.8
1.8
2.8
3.8
4.8
(5 rows)
=# SELECT * FROM generate_series(0.8, 5, 0);
ERROR: 22023: step size cannot equal zero
LOCATION: generate_series_numeric, numeric.c:1271
This could be polished more, but I'm sure you'll deal with that well. If
you are able to have a more accompished version for the next commit fest
I'll have a look at it.
Regards,
--
Michael
On Tue, Sep 30, 2014 at 10:00 AM, Michael Paquier <michael.paquier@gmail.com
wrote:
On Mon, Sep 29, 2014 at 4:19 PM, Michael Paquier <
michael.paquier@gmail.com> wrote:Michael
Btw, while looking at your patch, I actually hacked it a bit and finished
with the attached:
- changed process to use NumericVar instead of Numeric
- addition of custom step values with a function
generate_series(numeric,numeric,numeric)
- some cleanup and some comments here and there
That's still WIP, but feel free to use it for future work. If you are
able to add documentation and regression tests to this patch, I would
recommend that you register it to the next commit fest, where it would get
more review, and hopefully it will get committed.Oops, it seems that I have been too hasty here. With a fresh mind I looked
at my own patch again and found two bugs:
- Incorrect initialization of step variable with const_one
- Incorrect calculation of each step's value, making stuff crash, it is
necessary to switch to the context of the function to perform operations on
a temporary variable first.
And here is the patch...
--
Michael
Attachments:
20140930_generate_series_numeric.patchtext/x-diff; charset=US-ASCII; name=20140930_generate_series_numeric.patchDownload
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 2d6a4cb..4c2ff5b 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -28,6 +28,7 @@
#include "access/hash.h"
#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -260,6 +261,13 @@ typedef struct NumericVar
} NumericVar;
+typedef struct
+{
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+} generate_series_numeric_fctx;
+
/* ----------
* Some preinitialized constants
* ----------
@@ -1221,6 +1229,108 @@ numeric_floor(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+
+/*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+Datum
+generate_series_numeric(PG_FUNCTION_ARGS)
+{
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ Numeric res;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric finish_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc;
+ MemoryContext oldcontext;
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step_num;
+ step_num = PG_GETARG_NUMERIC(2);
+
+ /* Transform step into a variable that can be manipulated */
+ init_var_from_num(step_num, &steploc);
+ }
+ else
+ {
+ init_var(&steploc);
+ set_var_from_var(&const_one, &steploc);
+ }
+
+ /* Step cannot be zero */
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* allocate memory for user context */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *)
+ palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start_num, &(fctx->current));
+ init_var_from_num(finish_num, &(fctx->finish));
+ init_var(&(fctx->step));
+ set_var_from_var(&steploc, &(fctx->step));
+ funcctx->user_fctx = fctx;
+ free_var(&steploc);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+ res = make_result(&(fctx->current));
+
+ if ((cmp_var(&(fctx->step), &const_zero) > 0 &&
+ cmp_var(&(fctx->current), &(fctx->finish)) <= 0) ||
+ (cmp_var(&(fctx->step), &const_zero) < 0 &&
+ cmp_var(&(fctx->current), &(fctx->finish)) >= 0))
+ {
+ NumericVar tmp;
+ MemoryContext oldcontext;
+
+ /*
+ * Increment for next iteration and perform operation in the context
+ * of this function. Switch back to memory context of function before
+ * performing any operations.
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ init_var(&tmp);
+ add_var(&(fctx->current), &(fctx->step), &tmp);
+ set_var_from_var(&tmp, &(fctx->current));
+ free_var(&tmp);
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(res));
+ }
+ else
+ {
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3ce9849..ccdf3e8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3923,6 +3923,10 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
+DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index d88e7a3..511518d 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1040,6 +1040,7 @@ extern Datum int8_avg(PG_FUNCTION_ARGS);
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
Hi
Oops, it seems that I have been too hasty here. With a fresh mind I looked
at my own patch again and found two bugs:
- Incorrect calculation of each step's value, making stuff crash, it is
necessary to switch to the context of the function to perform operations on
a temporary variable first
- i think you can use the fctx->current variable without temporary variable
(there's comment in the add_var function: Full version of add functionality
on variable level (handling signs). result might point to one of the
operands too without danger.). But you _must_ switch the context first
because add_var will allocate new array for the data and freeing the old
one.
- numeric can be NaN. We must reject it as first, finish and last parameter.
- numeric datatype is large, but there are limitations. According to doc,
the limit is: up to 131072 digits before the decimal point; up to 16383
digits after the decimal point. How can we check if the next step
overflows? As a comparison, in int.c, generate_series_step_int4 checks if
its overflows, and stop the next call by setting step to 0. Should we do
that?
~ will try to fix the patch later
--
Ali Akbar
2014-10-05 15:21 GMT+07:00 Ali Akbar <the.apaan@gmail.com>:
Hi
Oops, it seems that I have been too hasty here. With a fresh mind I looked
at my own patch again and found two bugs:- Incorrect calculation of each step's value, making stuff crash, it is
necessary to switch to the context of the function to perform operations on
a temporary variable first- i think you can use the fctx->current variable without temporary
variable (there's comment in the add_var function: Full version of add
functionality on variable level (handling signs). result might point to one
of the operands too without danger.). But you _must_ switch the context
first because add_var will allocate new array for the data and freeing the
old one.- numeric can be NaN. We must reject it as first, finish and last
parameter.
- numeric datatype is large, but there are limitations. According to doc,
the limit is: up to 131072 digits before the decimal point; up to 16383
digits after the decimal point. How can we check if the next step
overflows? As a comparison, in int.c, generate_series_step_int4 checks if
its overflows, and stop the next call by setting step to 0. Should we do
that?~ will try to fix the patch later
attached the patch. Not checking if it overflows, but testing it with 9 *
10 ^ 131072 works (not resulting in an error). Other modifications:
- doc update
- regresssion tests
- while testing regression test, opr_sanity checks that the
generate_series_numeric function is used twice (once for 2 parameter and
once for the 3 parameter function), so i changed the name to
generate_series_step_numeric and created new function
generate_series_numeric that calls generate_series_step_numeric
--
Ali Akbar
Attachments:
20141005_generate_series_numeric.patchtext/x-diff; charset=US-ASCII; name=20141005_generate_series_numeric.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14074,14081 **** AND
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
--- 14074,14081 ----
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type>, or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
***************
*** 14084,14091 **** AND
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
--- 14084,14091 ----
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type> or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 28,33 ****
--- 28,34 ----
#include "access/hash.h"
#include "catalog/pg_type.h"
+ #include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
***************
*** 261,266 **** typedef struct NumericVar
--- 262,279 ----
/* ----------
+ * Data for generate series
+ * ----------
+ */
+ typedef struct
+ {
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+ } generate_series_numeric_fctx;
+
+
+ /* ----------
* Some preinitialized constants
* ----------
*/
***************
*** 1221,1226 **** numeric_floor(PG_FUNCTION_ARGS)
--- 1234,1346 ----
PG_RETURN_NUMERIC(res);
}
+
+ /*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+ Datum
+ generate_series_numeric(PG_FUNCTION_ARGS) {
+ return generate_series_step_numeric(fcinfo);
+ }
+
+ Datum
+ generate_series_step_numeric(PG_FUNCTION_ARGS)
+ {
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric finish_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* Handle NaN in start & finish */
+ if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start and finish cannot be NaN")));
+ }
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3) {
+ Numeric step_num = PG_GETARG_NUMERIC(2);
+
+ if (NUMERIC_IS_NAN(step_num)) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+ }
+
+ init_var_from_num(step_num, &steploc);
+
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+ }
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *) palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start_num, &fctx->current);
+ init_var_from_num(finish_num, &fctx->finish);
+ init_var(&fctx->step);
+ set_var_from_var(&steploc, &fctx->step);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->finish) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->finish) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* increment current in preparation for next iteration */
+ add_var(&fctx->current, &fctx->step, &fctx->current);
+
+
+ MemoryContextSwitchTo(oldcontext);
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3923,3928 **** DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
--- 3923,3932 ----
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+ DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_step_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1040,1045 **** extern Datum int8_avg(PG_FUNCTION_ARGS);
--- 1040,1047 ----
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
***************
*** 1409,1411 **** select 10.0 ^ 2147483647 as overflows;
--- 1409,1627 ----
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 10.0::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 1.1
+ 2.1
+ 3.1
+ 4.1
+ 5.1
+ 6.1
+ 7.1
+ 8.1
+ 9.1
+ (10 rows)
+
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 0.2
+ 0.3
+ 0.4
+ 0.5
+ 0.6
+ 0.7
+ 0.8
+ 0.9
+ 1.0
+ 1.1
+ 1.2
+ 1.3
+ 1.4
+ 1.5
+ 1.6
+ 1.7
+ 1.8
+ 1.9
+ 2.0
+ 2.1
+ 2.2
+ 2.3
+ 2.4
+ 2.5
+ 2.6
+ 2.7
+ 2.8
+ 2.9
+ 3.0
+ 3.1
+ 3.2
+ 3.3
+ 3.4
+ 3.5
+ 3.6
+ 3.7
+ 3.8
+ 3.9
+ 4.0
+ 4.1
+ 4.2
+ 4.3
+ 4.4
+ 4.5
+ 4.6
+ 4.7
+ 4.8
+ 4.9
+ 5.0
+ 5.1
+ 5.2
+ 5.3
+ 5.4
+ 5.5
+ 5.6
+ 5.7
+ 5.8
+ 5.9
+ 6.0
+ 6.1
+ 6.2
+ 6.3
+ 6.4
+ 6.5
+ 6.6
+ 6.7
+ 6.8
+ 6.9
+ 7.0
+ 7.1
+ 7.2
+ 7.3
+ 7.4
+ 7.5
+ 7.6
+ 7.7
+ 7.8
+ 7.9
+ 8.0
+ 8.1
+ 8.2
+ 8.3
+ 8.4
+ 8.5
+ 8.6
+ 8.7
+ 8.8
+ 8.9
+ 9.0
+ 9.1
+ 9.2
+ 9.3
+ 9.4
+ 9.5
+ 9.6
+ 9.7
+ 9.8
+ 9.9
+ 10.0
+ (100 rows)
+
+ select * from generate_series(0.1::numeric, -10.0::numeric, -0.25::numeric);
+ generate_series
+ -----------------
+ 0.1
+ -0.15
+ -0.40
+ -0.65
+ -0.90
+ -1.15
+ -1.40
+ -1.65
+ -1.90
+ -2.15
+ -2.40
+ -2.65
+ -2.90
+ -3.15
+ -3.40
+ -3.65
+ -3.90
+ -4.15
+ -4.40
+ -4.65
+ -4.90
+ -5.15
+ -5.40
+ -5.65
+ -5.90
+ -6.15
+ -6.40
+ -6.65
+ -6.90
+ -7.15
+ -7.40
+ -7.65
+ -7.90
+ -8.15
+ -8.40
+ -8.65
+ -8.90
+ -9.15
+ -9.40
+ -9.65
+ -9.90
+ (41 rows)
+
+ select * from generate_series(100::numeric, -100::numeric, 10::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ ERROR: step size cannot equal zero
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ ERROR: step size cannot be NaN
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
+ numeric
+ ---------
+ -9
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ (19 rows)
+
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
***************
*** 837,839 **** select 10.0 ^ -2147483648 as rounds_to_zero;
--- 837,859 ----
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 10.0::numeric);
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ select * from generate_series(0.1::numeric, -10.0::numeric, -0.25::numeric);
+ select * from generate_series(100::numeric, -100::numeric, 10::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
Also, Платон Малюгин, can you add this patch to postgresql commitfest (
http://commitfest.postgresql.org)?
--
Ali Akbar
On Sun, Oct 5, 2014 at 7:39 PM, Ali Akbar <the.apaan@gmail.com> wrote:
2014-10-05 15:21 GMT+07:00 Ali Akbar <the.apaan@gmail.com>:
- i think you can use the fctx->current variable without temporary
variable (there's comment in the add_var function: Full version of add
functionality on variable level (handling signs). result might point to one
of the operands too without danger.). But you _must_ switch the context
first because add_var will allocate new array for the data and freeing the
old one.Yep.
- numeric can be NaN. We must reject it as first, finish and last
parameter.
Makes sense.
- numeric datatype is large, but there are limitations. According to doc,
the limit is: up to 131072 digits before the decimal point; up to 16383
digits after the decimal point. How can we check if the next step
overflows? As a comparison, in int.c, generate_series_step_int4 checks if
its overflows, and stop the next call by setting step to 0. Should we do
that?Yes we should.
- while testing regression test, opr_sanity checks that the
generate_series_numeric function is used twice (once for 2 parameter and
once for the 3 parameter function), so i changed the name to
generate_series_step_numeric and created new function
generate_series_numeric that calls generate_series_step_numeric.
Yep.
It seems to me that this patch is heading in a good direction (haven't
tested or tried to break it, just looked at the code). However please be
careful of code format, particularly brackets for "if" blocks. For example
this thing:
if (foo) {
blah;
}
Should be that:
if (foo)
blah;
Then in the case of multiple lines, this thing:
if (foo) {
blah;
blah2;
}
Should be that:
if (foo)
{
blah;
blah2;
}
Code convention is detailed in the docs:
http://www.postgresql.org/docs/devel/static/source.html
Regards,
--
Michael
Thanks for the review. Attached the formatted patch according to your
suggestion.
- numeric datatype is large, but there are limitations. According to doc,
the limit is: up to 131072 digits before the decimal point; up to 16383
digits after the decimal point. How can we check if the next step
overflows? As a comparison, in int.c, generate_series_step_int4 checks if
its overflows, and stop the next call by setting step to 0. Should we do
that?Yes we should.
how can we check the overflow after add_var?
(in int.c, the code checks for integer calculation overflow, that wraps the
result to negative value)
in numeric.sql regression test, i've added this query:
select (i / (10::numeric ^ 131071))::numeric(1,0)
from generate_series(-9*(10::numeric ^ 131071),
9*(10::numeric ^ 131071),
(10::numeric ^ 131071))
as a(i);
Because the doc notes that the maximum numeric digit before decimal point
is 131072, i hope this query covers the overflow case (in the last value it
will generate, if we add 9 x 10^13071 with 10^13071, add_var will
overflows). But in my tests, that isn't the case. The code works without
any error and returns the correct rows:
numeric
---------
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
1
2
3
4
5
6
7
8
9
(19 rows)
Regards,
--
Ali Akbar
Attachments:
20141006_generate_series_numeric.patchtext/x-diff; charset=US-ASCII; name=20141006_generate_series_numeric.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14074,14081 **** AND
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
--- 14074,14081 ----
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type>, or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
***************
*** 14084,14091 **** AND
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
--- 14084,14091 ----
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type> or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 28,33 ****
--- 28,34 ----
#include "access/hash.h"
#include "catalog/pg_type.h"
+ #include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
***************
*** 261,266 **** typedef struct NumericVar
--- 262,279 ----
/* ----------
+ * Data for generate series
+ * ----------
+ */
+ typedef struct
+ {
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+ } generate_series_numeric_fctx;
+
+
+ /* ----------
* Some preinitialized constants
* ----------
*/
***************
*** 1221,1226 **** numeric_floor(PG_FUNCTION_ARGS)
--- 1234,1346 ----
PG_RETURN_NUMERIC(res);
}
+
+ /*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+ Datum
+ generate_series_numeric(PG_FUNCTION_ARGS)
+ {
+ return generate_series_step_numeric(fcinfo);
+ }
+
+ Datum
+ generate_series_step_numeric(PG_FUNCTION_ARGS)
+ {
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric finish_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* Handle NaN in start & finish */
+ if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start and finish cannot be NaN")));
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step_num = PG_GETARG_NUMERIC(2);
+
+ if (NUMERIC_IS_NAN(step_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+
+ init_var_from_num(step_num, &steploc);
+
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+ }
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *) palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start_num, &fctx->current);
+ init_var_from_num(finish_num, &fctx->finish);
+ init_var(&fctx->step);
+ set_var_from_var(&steploc, &fctx->step);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->finish) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->finish) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* increment current in preparation for next iteration */
+ add_var(&fctx->current, &fctx->step, &fctx->current);
+
+
+ MemoryContextSwitchTo(oldcontext);
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3923,3928 **** DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
--- 3923,3932 ----
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+ DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_step_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1040,1045 **** extern Datum int8_avg(PG_FUNCTION_ARGS);
--- 1040,1047 ----
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
***************
*** 1409,1411 **** select 10.0 ^ 2147483647 as overflows;
--- 1409,1627 ----
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 10.0::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 1.1
+ 2.1
+ 3.1
+ 4.1
+ 5.1
+ 6.1
+ 7.1
+ 8.1
+ 9.1
+ (10 rows)
+
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 0.2
+ 0.3
+ 0.4
+ 0.5
+ 0.6
+ 0.7
+ 0.8
+ 0.9
+ 1.0
+ 1.1
+ 1.2
+ 1.3
+ 1.4
+ 1.5
+ 1.6
+ 1.7
+ 1.8
+ 1.9
+ 2.0
+ 2.1
+ 2.2
+ 2.3
+ 2.4
+ 2.5
+ 2.6
+ 2.7
+ 2.8
+ 2.9
+ 3.0
+ 3.1
+ 3.2
+ 3.3
+ 3.4
+ 3.5
+ 3.6
+ 3.7
+ 3.8
+ 3.9
+ 4.0
+ 4.1
+ 4.2
+ 4.3
+ 4.4
+ 4.5
+ 4.6
+ 4.7
+ 4.8
+ 4.9
+ 5.0
+ 5.1
+ 5.2
+ 5.3
+ 5.4
+ 5.5
+ 5.6
+ 5.7
+ 5.8
+ 5.9
+ 6.0
+ 6.1
+ 6.2
+ 6.3
+ 6.4
+ 6.5
+ 6.6
+ 6.7
+ 6.8
+ 6.9
+ 7.0
+ 7.1
+ 7.2
+ 7.3
+ 7.4
+ 7.5
+ 7.6
+ 7.7
+ 7.8
+ 7.9
+ 8.0
+ 8.1
+ 8.2
+ 8.3
+ 8.4
+ 8.5
+ 8.6
+ 8.7
+ 8.8
+ 8.9
+ 9.0
+ 9.1
+ 9.2
+ 9.3
+ 9.4
+ 9.5
+ 9.6
+ 9.7
+ 9.8
+ 9.9
+ 10.0
+ (100 rows)
+
+ select * from generate_series(0.1::numeric, -10.0::numeric, -0.25::numeric);
+ generate_series
+ -----------------
+ 0.1
+ -0.15
+ -0.40
+ -0.65
+ -0.90
+ -1.15
+ -1.40
+ -1.65
+ -1.90
+ -2.15
+ -2.40
+ -2.65
+ -2.90
+ -3.15
+ -3.40
+ -3.65
+ -3.90
+ -4.15
+ -4.40
+ -4.65
+ -4.90
+ -5.15
+ -5.40
+ -5.65
+ -5.90
+ -6.15
+ -6.40
+ -6.65
+ -6.90
+ -7.15
+ -7.40
+ -7.65
+ -7.90
+ -8.15
+ -8.40
+ -8.65
+ -8.90
+ -9.15
+ -9.40
+ -9.65
+ -9.90
+ (41 rows)
+
+ select * from generate_series(100::numeric, -100::numeric, 10::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ ERROR: step size cannot equal zero
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ ERROR: step size cannot be NaN
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
+ numeric
+ ---------
+ -9
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ (19 rows)
+
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
***************
*** 837,839 **** select 10.0 ^ -2147483648 as rounds_to_zero;
--- 837,859 ----
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 10.0::numeric);
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ select * from generate_series(0.1::numeric, -10.0::numeric, -0.25::numeric);
+ select * from generate_series(100::numeric, -100::numeric, 10::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
I'm a bit confused about who I should be replying to, but since you
were the last one with a patch...
On Mon, Oct 6, 2014 at 11:44 AM, Ali Akbar <the.apaan@gmail.com> wrote:
Thanks for the review. Attached the formatted patch according to your
suggestion.
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric);
+ generate_series
+ -----------------
+ 0.1
...
+ 10.0
+ (100 rows)Unless there is a good reason, can you please keep individual test
output fewer than 100 lines? I think the 41-line result is an overkill
as well. This just bloats the repository size unnecessarily.
Also, I see that this patch was added to the 2014-10 commitfest and
then deleted again (by user apaan). Why?
Regards,
Marti
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
+ select * from generate_series(0.1::numeric, 10.0::numeric, 0.1::numeric); + generate_series + ----------------- + 0.1 ... + 10.0 + (100 rows)Unless there is a good reason, can you please keep individual test
output fewer than 100 lines? I think the 41-line result is an overkill
as well. This just bloats the repository size unnecessarily.
Okay. In this revision i cut the tests' result except the last one (the one
that tests values just before numeric overflow).
Also, I see that this patch was added to the 2014-10 commitfest and
then deleted again (by user apaan). Why?
User apaan is me. When i added to the commitfest, the patch is listed there
by me (apaan). I only added some bits from original patch by Платон Малюгин
that was revised further by Michael Paquier. So i think they should have
the credits for this patch, not me.
In this situation, what should i do?
Thanks for the review Manti!
Regards,
--
Ali Akbar
Attachments:
20141006a_generate_series_numeric.patchtext/x-diff; charset=US-ASCII; name=20141006a_generate_series_numeric.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14074,14081 **** AND
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
--- 14074,14081 ----
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type>, or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
***************
*** 14084,14091 **** AND
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
--- 14084,14091 ----
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type> or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 28,33 ****
--- 28,34 ----
#include "access/hash.h"
#include "catalog/pg_type.h"
+ #include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
***************
*** 261,266 **** typedef struct NumericVar
--- 262,279 ----
/* ----------
+ * Data for generate series
+ * ----------
+ */
+ typedef struct
+ {
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+ } generate_series_numeric_fctx;
+
+
+ /* ----------
* Some preinitialized constants
* ----------
*/
***************
*** 1221,1226 **** numeric_floor(PG_FUNCTION_ARGS)
--- 1234,1346 ----
PG_RETURN_NUMERIC(res);
}
+
+ /*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+ Datum
+ generate_series_numeric(PG_FUNCTION_ARGS)
+ {
+ return generate_series_step_numeric(fcinfo);
+ }
+
+ Datum
+ generate_series_step_numeric(PG_FUNCTION_ARGS)
+ {
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric finish_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* Handle NaN in start & finish */
+ if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start and finish cannot be NaN")));
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step_num = PG_GETARG_NUMERIC(2);
+
+ if (NUMERIC_IS_NAN(step_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+
+ init_var_from_num(step_num, &steploc);
+
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+ }
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *) palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start_num, &fctx->current);
+ init_var_from_num(finish_num, &fctx->finish);
+ init_var(&fctx->step);
+ set_var_from_var(&steploc, &fctx->step);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->finish) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->finish) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* increment current in preparation for next iteration */
+ add_var(&fctx->current, &fctx->step, &fctx->current);
+
+
+ MemoryContextSwitchTo(oldcontext);
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3923,3928 **** DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
--- 3923,3932 ----
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+ DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_step_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1040,1045 **** extern Datum int8_avg(PG_FUNCTION_ARGS);
--- 1040,1047 ----
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
***************
*** 1409,1411 **** select 10.0 ^ 2147483647 as overflows;
--- 1409,1495 ----
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 5.0::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 1.1
+ 2.1
+ 3.1
+ 4.1
+ (5 rows)
+
+ select * from generate_series(0.1::numeric, 1.0::numeric, 0.1::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 0.2
+ 0.3
+ 0.4
+ 0.5
+ 0.6
+ 0.7
+ 0.8
+ 0.9
+ 1.0
+ (10 rows)
+
+ select * from generate_series(0.1::numeric, -2.0::numeric, -0.25::numeric);
+ generate_series
+ -----------------
+ 0.1
+ -0.15
+ -0.40
+ -0.65
+ -0.90
+ -1.15
+ -1.40
+ -1.65
+ -1.90
+ (9 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ ERROR: step size cannot equal zero
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ ERROR: step size cannot be NaN
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
+ numeric
+ ---------
+ -9
+ -8
+ -7
+ -6
+ -5
+ -4
+ -3
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ (19 rows)
+
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
***************
*** 837,839 **** select 10.0 ^ -2147483648 as rounds_to_zero;
--- 837,858 ----
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 5.0::numeric);
+ select * from generate_series(0.1::numeric, 1.0::numeric, 0.1::numeric);
+ select * from generate_series(0.1::numeric, -2.0::numeric, -0.25::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(-9*(10::numeric ^ 131071),
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
On Mon, Oct 6, 2014 at 6:12 PM, Ali Akbar <the.apaan@gmail.com> wrote:
User apaan is me. When i added to the commitfest, the patch is listed there
by me (apaan).
That's fine I think, it's just for tracking who made the changes in
the CommitFest app. What actually matters is what you write in the
"Author" field, which could contain all 3 names separated by commas.
the one that tests values just before numeric overflow
Actually I don't know if that's too useful. I think you should add a
test case that causes an error to be thrown.
Also, I noticed that there are a few trailing spaces in the patch that
should be removed:
+generate_series_numeric(PG_FUNCTION_ARGS)
...
+ if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num))
...
+ if (PG_NARGS() == 3)
...
+ if (NUMERIC_IS_NAN(step_num))Regards,
Marti
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Oct 7, 2014 at 12:51 AM, Marti Raudsepp <marti@juffo.org> wrote:
On Mon, Oct 6, 2014 at 6:12 PM, Ali Akbar <the.apaan@gmail.com> wrote:
User apaan is me. When i added to the commitfest, the patch is listed
there
by me (apaan).
That's fine I think, it's just for tracking who made the changes in
the CommitFest app. What actually matters is what you write in the
"Author" field, which could contain all 3 names separated by commas.
It's fine not to add mine to the list of authors, I did a hack review. Feel
free to add it to the list of reviewers though.
--
Michael
2014-10-06 22:51 GMT+07:00 Marti Raudsepp <marti@juffo.org>:
That's fine I think, it's just for tracking who made the changes in
the CommitFest app. What actually matters is what you write in the
"Author" field, which could contain all 3 names separated by commas.
Ok. Added to commitfest:
https://commitfest.postgresql.org/action/patch_view?id=1591
the one that tests values just before numeric overflow
Actually I don't know if that's too useful. I think you should add a
test case that causes an error to be thrown.
Actually i added the test case because in the code, when adding step into
current for the last value, i expected it to overflow:
/* increment current in preparation for next iteration */
add_var(&fctx->current, &fctx->step, &fctx->current);
where in the last calculation, current is 9 * 10^131071. Plus 10^131071, it
will be 10^131072, which i expected to overflow numeric type (in the doc,
numeric's range is "up to 131072 digits before the decimal point").
In attached patch, i narrowed the test case to produce smaller result.
Also, I noticed that there are a few trailing spaces in the patch that
should be removed:
+generate_series_numeric(PG_FUNCTION_ARGS) ... + if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num)) ... + if (PG_NARGS() == 3) ... + if (NUMERIC_IS_NAN(step_num))
Ah, didn't see it. Thanks. Fixed in this patch.
Regards,
--
Ali Akbar
Attachments:
20141007_generate_series_numeric.patchtext/x-diff; charset=US-ASCII; name=20141007_generate_series_numeric.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14074,14081 **** AND
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
--- 14074,14081 ----
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type>, or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
***************
*** 14084,14091 **** AND
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type> or <type>bigint</type></entry>
! <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
--- 14084,14091 ----
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
! <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
! <entry><type>setof int</type>, <type>setof bigint</type> or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 28,33 ****
--- 28,34 ----
#include "access/hash.h"
#include "catalog/pg_type.h"
+ #include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
***************
*** 261,266 **** typedef struct NumericVar
--- 262,279 ----
/* ----------
+ * Data for generate series
+ * ----------
+ */
+ typedef struct
+ {
+ NumericVar current;
+ NumericVar finish;
+ NumericVar step;
+ } generate_series_numeric_fctx;
+
+
+ /* ----------
* Some preinitialized constants
* ----------
*/
***************
*** 1221,1226 **** numeric_floor(PG_FUNCTION_ARGS)
--- 1234,1346 ----
PG_RETURN_NUMERIC(res);
}
+
+ /*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+ Datum
+ generate_series_numeric(PG_FUNCTION_ARGS)
+ {
+ return generate_series_step_numeric(fcinfo);
+ }
+
+ Datum
+ generate_series_step_numeric(PG_FUNCTION_ARGS)
+ {
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric finish_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* Handle NaN in start & finish */
+ if (NUMERIC_IS_NAN(start_num) || NUMERIC_IS_NAN(finish_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start and finish cannot be NaN")));
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step_num = PG_GETARG_NUMERIC(2);
+
+ if (NUMERIC_IS_NAN(step_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+
+ init_var_from_num(step_num, &steploc);
+
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+ }
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *) palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value
+ */
+ init_var_from_num(start_num, &fctx->current);
+ init_var_from_num(finish_num, &fctx->finish);
+ init_var(&fctx->step);
+ set_var_from_var(&steploc, &fctx->step);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->finish) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->finish) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* increment current in preparation for next iteration */
+ add_var(&fctx->current, &fctx->step, &fctx->current);
+
+
+ MemoryContextSwitchTo(oldcontext);
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3923,3928 **** DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
--- 3923,3932 ----
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+ DATA(insert OID = 6000 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_step_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 6001 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+ DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1040,1045 **** extern Datum int8_avg(PG_FUNCTION_ARGS);
--- 1040,1047 ----
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
***************
*** 1409,1411 **** select 10.0 ^ 2147483647 as overflows;
--- 1409,1477 ----
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 5.0::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 1.1
+ 2.1
+ 3.1
+ 4.1
+ (5 rows)
+
+ select * from generate_series(0.1::numeric, 1.0::numeric, 0.1::numeric);
+ generate_series
+ -----------------
+ 0.1
+ 0.2
+ 0.3
+ 0.4
+ 0.5
+ 0.6
+ 0.7
+ 0.8
+ 0.9
+ 1.0
+ (10 rows)
+
+ select * from generate_series(0.1::numeric, -2.0::numeric, -0.25::numeric);
+ generate_series
+ -----------------
+ 0.1
+ -0.15
+ -0.40
+ -0.65
+ -0.90
+ -1.15
+ -1.40
+ -1.65
+ -1.90
+ (9 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ generate_series
+ -----------------
+ (0 rows)
+
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ ERROR: step size cannot equal zero
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ ERROR: step size cannot be NaN
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+ ERROR: start and finish cannot be NaN
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(9*(10::numeric ^ 131071) - 1,
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
+ numeric
+ ---------
+ 9
+ (1 row)
+
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
***************
*** 837,839 **** select 10.0 ^ -2147483648 as rounds_to_zero;
--- 837,858 ----
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+ --
+ -- Non persistent generate series
+ --
+ select * from generate_series(0.1::numeric, 5.0::numeric);
+ select * from generate_series(0.1::numeric, 1.0::numeric, 0.1::numeric);
+ select * from generate_series(0.1::numeric, -2.0::numeric, -0.25::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, -5::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ select * from generate_series('nan'::numeric, 100::numeric, 0::numeric);
+ select * from generate_series(0::numeric, 'nan'::numeric, 0::numeric);
+
+ -- big generate (checks the edge of overflow, output truncated)
+ select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(9*(10::numeric ^ 131071) - 1,
+ 9*(10::numeric ^ 131071),
+ (10::numeric ^ 131071))
+ as a(i);
On Tue, Oct 7, 2014 at 8:38 AM, Ali Akbar <the.apaan@gmail.com> wrote:
2014-10-06 22:51 GMT+07:00 Marti Raudsepp <marti@juffo.org>:
the one that tests values just before numeric overflow
Actually I don't know if that's too useful. I think you should add a
test case that causes an error to be thrown.Actually i added the test case because in the code, when adding step into
current for the last value, i expected it to overflow:/* increment current in preparation for next iteration */
add_var(&fctx->current, &fctx->step, &fctx->current);where in the last calculation, current is 9 * 10^131071. Plus 10^131071,
it will be 10^131072, which i expected to overflow numeric type (in the
doc, numeric's range is "up to 131072 digits before the decimal point").In attached patch, i narrowed the test case to produce smaller result.
Well, as things stand now, the logic relies on cmp_var and the sign of the
stop and current values. it is right that it would be better to check for
overflow before going through the next iteration, and the cleanest and
cheapest way to do so would be to move the call to make_result after
add_var and save the result variable in the function context, or something
similar, but honestly I am not sure it is worth the complication as it
would mean some refactoring on what make_result actually already does.
I looked at this patch again a bit and finished with the attached, adding
an example in the docs, refining the error messages and improving a bit the
regression tests. I have nothing more to comment, so I am marking this
patch as "ready for committer".
Regards,
--
Michael
Attachments:
20141014_generate_series_numeric.patchtext/x-patch; charset=US-ASCII; name=20141014_generate_series_numeric.patchDownload
From 4f8591c47bb51ea86b77be645d9c321a1e9fb962 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@vmware.com>
Date: Tue, 14 Oct 2014 14:39:51 +0900
Subject: [PATCH] Implement generate_series for numeric data type
This commit adds two new system functions to generate series of numbers
for the data type numeric, with the possibility to define a custom step
value. Note that Nan is not accepted in output for all the input
variables.
---
doc/src/sgml/func.sgml | 16 +++--
src/backend/utils/adt/numeric.c | 124 ++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 4 ++
src/include/utils/builtins.h | 2 +
src/test/regress/expected/numeric.out | 52 ++++++++++++++
src/test/regress/sql/numeric.sql | 17 +++++
6 files changed, 211 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7e5bcd9..b58cfa5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14076,8 +14076,8 @@ AND
<tbody>
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>)</function></literal></entry>
- <entry><type>int</type> or <type>bigint</type></entry>
- <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
+ <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
+ <entry><type>setof int</type>, <type>setof bigint</type>, or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of one
@@ -14086,8 +14086,8 @@ AND
<row>
<entry><literal><function>generate_series(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</function></literal></entry>
- <entry><type>int</type> or <type>bigint</type></entry>
- <entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>
+ <entry><type>int</type>, <type>bigint</type> or <type>numeric</type></entry>
+ <entry><type>setof int</type>, <type>setof bigint</type> or <type>setof numeric</type> (same as argument type)</entry>
<entry>
Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>
with a step size of <parameter>step</parameter>
@@ -14137,6 +14137,14 @@ SELECT * FROM generate_series(4,3);
-----------------
(0 rows)
+SELECT generate_series(1.1, 4, 1.3);
+ generate_series
+-----------------
+ 1.1
+ 2.4
+ 3.7
+(3 rows)
+
-- this example relies on the date-plus-integer operator
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
dates
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 2d6a4cb..db2f862 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -28,6 +28,7 @@
#include "access/hash.h"
#include "catalog/pg_type.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -261,6 +262,18 @@ typedef struct NumericVar
/* ----------
+ * Data for generate_series
+ * ----------
+ */
+typedef struct
+{
+ NumericVar current;
+ NumericVar stop;
+ NumericVar step;
+} generate_series_numeric_fctx;
+
+
+/* ----------
* Some preinitialized constants
* ----------
*/
@@ -1221,6 +1234,117 @@ numeric_floor(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+
+/*
+ * generate_series_numeric() -
+ *
+ * Generate series of numeric.
+ */
+Datum
+generate_series_numeric(PG_FUNCTION_ARGS)
+{
+ return generate_series_step_numeric(fcinfo);
+}
+
+Datum
+generate_series_step_numeric(PG_FUNCTION_ARGS)
+{
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start_num = PG_GETARG_NUMERIC(0);
+ Numeric stop_num = PG_GETARG_NUMERIC(1);
+ NumericVar steploc = const_one;
+
+ /* handle NaN in start and stop values */
+ if (NUMERIC_IS_NAN(start_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start value cannot be NaN")));
+
+ if (NUMERIC_IS_NAN(stop_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stop value cannot be NaN")));
+
+ /* see if we were given an explicit step size */
+ if (PG_NARGS() == 3)
+ {
+ Numeric step_num = PG_GETARG_NUMERIC(2);
+
+ if (NUMERIC_IS_NAN(step_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+
+ init_var_from_num(step_num, &steploc);
+
+ if (cmp_var(&steploc, &const_zero) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal zero")));
+ }
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * Switch to memory context appropriate for multiple function calls.
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_numeric_fctx *)
+ palloc(sizeof(generate_series_numeric_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with the
+ * original start value.
+ */
+ init_var_from_num(start_num, &fctx->current);
+ init_var_from_num(stop_num, &fctx->stop);
+ init_var(&fctx->step);
+ set_var_from_var(&steploc, &fctx->step);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * Get the saved state and use current state as the result of this
+ * iteration.
+ */
+ fctx = funcctx->user_fctx;
+
+ if ((fctx->step.sign == NUMERIC_POS &&
+ cmp_var(&fctx->current, &fctx->stop) <= 0) ||
+ (fctx->step.sign == NUMERIC_NEG &&
+ cmp_var(&fctx->current, &fctx->stop) >= 0))
+ {
+ Numeric result = make_result(&fctx->current);
+
+ /* switch to memory context appropriate for iteration calculation */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* increment current in preparation for next iteration */
+ add_var(&fctx->current, &fctx->step, &fctx->current);
+ MemoryContextSwitchTo(oldcontext);
+
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(result));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+}
+
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 4736532..bcc02f6 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3923,6 +3923,10 @@ DATA(insert OID = 1068 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("non-persistent series generator");
DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 20 "20 20" _null_ _null_ _null_ _null_ generate_series_int8 _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
+DATA(insert OID = 3259 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1700 "1700 1700 1700" _null_ _null_ _null_ _null_ generate_series_step_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
+DATA(insert OID = 3260 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ generate_series_numeric _null_ _null_ _null_ ));
+DESCR("non-persistent series generator");
DATA(insert OID = 938 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 1114 "1114 1114 1186" _null_ _null_ _null_ _null_ generate_series_timestamp _null_ _null_ _null_ ));
DESCR("non-persistent series generator");
DATA(insert OID = 939 ( generate_series PGNSP PGUID 12 1 1000 0 0 f f f f t t s 3 0 1184 "1184 1184 1186" _null_ _null_ _null_ _null_ generate_series_timestamptz _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index fb1b4a4..523cc79 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1029,6 +1029,8 @@ extern Datum int8_avg(PG_FUNCTION_ARGS);
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
+extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
+extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
/* ri_triggers.c */
extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 5fafdaf..ee6cb50 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -1409,3 +1409,55 @@ select 10.0 ^ 2147483647 as overflows;
ERROR: value overflows numeric format
select 117743296169.0 ^ 1000000000 as overflows;
ERROR: value overflows numeric format
+--
+-- Tests for generate_series
+--
+select * from generate_series(0.0::numeric, 4.0::numeric);
+ generate_series
+-----------------
+ 0.0
+ 1.0
+ 2.0
+ 3.0
+ 4.0
+(5 rows)
+
+select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
+ generate_series
+-----------------
+ 0.1
+ 1.4
+ 2.7
+ 4.0
+(4 rows)
+
+select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
+ generate_series
+-----------------
+ 4.0
+ 1.8
+ -0.4
+(3 rows)
+
+-- Trigger errors
+select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+ERROR: step size cannot equal zero
+select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+ERROR: step size cannot be NaN
+select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
+ERROR: start value cannot be NaN
+select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
+ERROR: stop value cannot be NaN
+-- Checks maximum, output is truncated
+select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(6 * (10::numeric ^ 131071),
+ 9 * (10::numeric ^ 131071),
+ 10::numeric ^ 131071) as a(i);
+ numeric
+---------
+ 6
+ 7
+ 8
+ 9
+(4 rows)
+
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 5c08717..a7e92ac 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -837,3 +837,20 @@ select 10.0 ^ -2147483648 as rounds_to_zero;
select 10.0 ^ -2147483647 as rounds_to_zero;
select 10.0 ^ 2147483647 as overflows;
select 117743296169.0 ^ 1000000000 as overflows;
+
+--
+-- Tests for generate_series
+--
+select * from generate_series(0.0::numeric, 4.0::numeric);
+select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
+select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
+-- Trigger errors
+select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
+select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
+select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
+select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
+-- Checks maximum, output is truncated
+select (i / (10::numeric ^ 131071))::numeric(1,0)
+ from generate_series(6 * (10::numeric ^ 131071),
+ 9 * (10::numeric ^ 131071),
+ 10::numeric ^ 131071) as a(i);
--
2.1.2
On Tue, Oct 14, 2014 at 3:22 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Tue, Oct 7, 2014 at 8:38 AM, Ali Akbar <the.apaan@gmail.com> wrote:
2014-10-06 22:51 GMT+07:00 Marti Raudsepp <marti@juffo.org>:
the one that tests values just before numeric overflow
Actually I don't know if that's too useful. I think you should add a
test case that causes an error to be thrown.Actually i added the test case because in the code, when adding step into
current for the last value, i expected it to overflow:/* increment current in preparation for next iteration */
add_var(&fctx->current, &fctx->step, &fctx->current);where in the last calculation, current is 9 * 10^131071. Plus 10^131071,
it will be 10^131072, which i expected to overflow numeric type (in the doc,
numeric's range is "up to 131072 digits before the decimal point").In attached patch, i narrowed the test case to produce smaller result.
Well, as things stand now, the logic relies on cmp_var and the sign of the
stop and current values. it is right that it would be better to check for
overflow before going through the next iteration, and the cleanest and
cheapest way to do so would be to move the call to make_result after add_var
and save the result variable in the function context, or something similar,
but honestly I am not sure it is worth the complication as it would mean
some refactoring on what make_result actually already does.I looked at this patch again a bit and finished with the attached, adding an
example in the docs, refining the error messages and improving a bit the
regression tests. I have nothing more to comment, so I am marking this patch
as "ready for committer".
The patch looks good to me. Barring any objection I will commit the patch.
Memo for me: CATALOG_VERSION_NO must be changed at the commit.
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Nov 7, 2014 at 3:19 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
On Tue, Oct 14, 2014 at 3:22 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Tue, Oct 7, 2014 at 8:38 AM, Ali Akbar <the.apaan@gmail.com> wrote:
2014-10-06 22:51 GMT+07:00 Marti Raudsepp <marti@juffo.org>:
the one that tests values just before numeric overflow
Actually I don't know if that's too useful. I think you should add a
test case that causes an error to be thrown.Actually i added the test case because in the code, when adding step into
current for the last value, i expected it to overflow:/* increment current in preparation for next iteration */
add_var(&fctx->current, &fctx->step, &fctx->current);where in the last calculation, current is 9 * 10^131071. Plus 10^131071,
it will be 10^131072, which i expected to overflow numeric type (in the doc,
numeric's range is "up to 131072 digits before the decimal point").In attached patch, i narrowed the test case to produce smaller result.
Well, as things stand now, the logic relies on cmp_var and the sign of the
stop and current values. it is right that it would be better to check for
overflow before going through the next iteration, and the cleanest and
cheapest way to do so would be to move the call to make_result after add_var
and save the result variable in the function context, or something similar,
but honestly I am not sure it is worth the complication as it would mean
some refactoring on what make_result actually already does.I looked at this patch again a bit and finished with the attached, adding an
example in the docs, refining the error messages and improving a bit the
regression tests. I have nothing more to comment, so I am marking this patch
as "ready for committer".The patch looks good to me. Barring any objection I will commit the patch.
Memo for me: CATALOG_VERSION_NO must be changed at the commit.
Pushed.
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"Fujii" == Fujii Masao <masao.fujii@gmail.com> writes:
Fujii> Pushed.
Bug found:
regression=# select count(*) from generate_series(1::numeric,10) v, generate_series(1,v) w;
count
-------
99990
(1 row)
regression=# select count(*) from generate_series(1::numeric,10) v, generate_series(1,v+0) w;
count
-------
55
(1 row)
The error is in the use of PG_GETARG_NUMERIC and init_var_from_num
when setting up the multi-call state; init_var_from_num points at the
original num's digits rather than copying them, but start_num and
stop_num have just been (potentially) detoasted in the per-call
context, in which case the storage will have been freed by the next
call.
Obviously this could also be fixed by not detoasting the input until
after switching to the multi-call context, but it looks to me like
that would be unnecessarily complex.
Suggested patch attached.
(Is it also worth putting an explicit warning about this kind of thing
in the SRF docs?)
--
Andrew (irc:RhodiumToad)
Attachments:
ngs.patchtext/x-patchDownload
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index c73f9bc..d841b6f 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -1325,11 +1325,16 @@ generate_series_step_numeric(PG_FUNCTION_ARGS)
/*
* Use fctx to keep state from call to call. Seed current with the
- * original start value.
+ * original start value. We must copy the start_num and stop_num
+ * values rather than pointing to them, since we may have detoasted
+ * them in the per-call context.
*/
- init_var_from_num(start_num, &fctx->current);
- init_var_from_num(stop_num, &fctx->stop);
+ init_var(&fctx->current);
+ init_var(&fctx->stop);
init_var(&fctx->step);
+
+ set_var_from_num(start_num, &fctx->current);
+ set_var_from_num(stop_num, &fctx->stop);
set_var_from_var(&steploc, &fctx->step);
funcctx->user_fctx = fctx;
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index ee6cb50..9d68145 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -1461,3 +1461,41 @@ select (i / (10::numeric ^ 131071))::numeric(1,0)
9
(4 rows)
+-- Check usage with variables
+select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j;
+ i | j
+---+---
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 2 | 2
+ 2 | 3
+ 3 | 3
+(6 rows)
+
+select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j;
+ i | j
+---+---
+ 1 | 1
+ 2 | 1
+ 2 | 2
+ 3 | 1
+ 3 | 2
+ 3 | 3
+(6 rows)
+
+select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j;
+ i | j
+---+---
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 1 | 4
+ 1 | 5
+ 2 | 1
+ 2 | 3
+ 2 | 5
+ 3 | 1
+ 3 | 4
+(10 rows)
+
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index a7e92ac..1633e4c 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -854,3 +854,7 @@ select (i / (10::numeric ^ 131071))::numeric(1,0)
from generate_series(6 * (10::numeric ^ 131071),
9 * (10::numeric ^ 131071),
10::numeric ^ 131071) as a(i);
+-- Check usage with variables
+select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j;
+select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j;
+select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j;
2014-12-15 10:25 GMT+07:00 Andrew Gierth <andrew@tao11.riddles.org.uk>:
"Fujii" == Fujii Masao <masao.fujii@gmail.com> writes:
Fujii> Pushed.
Bug found:
regression=# select count(*) from generate_series(1::numeric,10) v,
generate_series(1,v) w;
count
-------
99990
(1 row)regression=# select count(*) from generate_series(1::numeric,10) v,
generate_series(1,v+0) w;
count
-------
55
(1 row)The error is in the use of PG_GETARG_NUMERIC and init_var_from_num
when setting up the multi-call state; init_var_from_num points at the
original num's digits rather than copying them, but start_num and
stop_num have just been (potentially) detoasted in the per-call
context, in which case the storage will have been freed by the next
call.
Oops.
Obviously this could also be fixed by not detoasting the input until
after switching to the multi-call context, but it looks to me like
that would be unnecessarily complex.Suggested patch attached.
Thanks
(Is it also worth putting an explicit warning about this kind of thing
in the SRF docs?)
I think yes, it will be good. The alternative is restructuring this
paragraph in the SRF docs:
The memory context that is current when the SRF is called is a transient
context that will be cleared between calls. This means that you do not need
to call pfree on everything you allocated using palloc; it will go away
anyway. However, if you want to allocate any data structures to live across
calls, you need to put them somewhere else. The memory context referenced
by multi_call_memory_ctx is a suitable location for any data that needs
to survive until the SRF is finished running. In most cases, this means
that you should switch into multi_call_memory_ctx while doing the
first-call setup.
The important part "However, if you want to allocate any data structures to
live across calls, you need to put them somewhere else." is buried in the
docs.
But i think explicit warning is more noticeable for new developer like me.
Regards,
--
Ali Akbar
"Ali" == Ali Akbar <the.apaan@gmail.com> writes:
Ali> I think yes, it will be good. The alternative is restructuring
Ali> this paragraph in the SRF docs:
The memory context that is current when the SRF is called is a
transient context that will be cleared between calls. This means
that you do not need to call pfree on everything you allocated
using palloc; it will go away anyway. However, if you want to
allocate any data structures to live across calls, you need to put
them somewhere else. The memory context referenced by
multi_call_memory_ctx is a suitable location for any data that
needs to survive until the SRF is finished running. In most cases,
this means that you should switch into multi_call_memory_ctx while
doing the first-call setup.
Ali> The important part "However, if you want to allocate any data
Ali> structures to live across calls, you need to put them somewhere
Ali> else." is buried in the docs.
Ali> But i think explicit warning is more noticeable for new
Ali> developer like me.
I was thinking something like this, added just after that para:
<warning>
<para>
While the actual arguments to the function remain unchanged between
calls, if you detoast the argument values (which is normally done
transparently by the
<function>PG_GETARG_<replaceable>xxx</replaceable></function> macro)
in the transient context then the detoasted copies will be freed on
each cycle. Accordingly, if you keep references to such values in
your <structfield>user_fctx</>, you must either copy them into the
<structfield>multi_call_memory_ctx</> after detoasting, or ensure
that you detoast the values only in that context.
</para>
</warning>
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Dec 15, 2014 at 12:25 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
"Fujii" == Fujii Masao <masao.fujii@gmail.com> writes:
Fujii> Pushed.
Bug found:
regression=# select count(*) from generate_series(1::numeric,10) v, generate_series(1,v) w;
count
-------
99990
(1 row)regression=# select count(*) from generate_series(1::numeric,10) v, generate_series(1,v+0) w;
count
-------
55
(1 row)The error is in the use of PG_GETARG_NUMERIC and init_var_from_num
when setting up the multi-call state; init_var_from_num points at the
original num's digits rather than copying them, but start_num and
stop_num have just been (potentially) detoasted in the per-call
context, in which case the storage will have been freed by the next
call.Obviously this could also be fixed by not detoasting the input until
after switching to the multi-call context, but it looks to me like
that would be unnecessarily complex.Suggested patch attached.
Pushed. Thanks!
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Dec 15, 2014 at 2:38 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
"Ali" == Ali Akbar <the.apaan@gmail.com> writes:
Ali> I think yes, it will be good. The alternative is restructuring
Ali> this paragraph in the SRF docs:The memory context that is current when the SRF is called is a
transient context that will be cleared between calls. This means
that you do not need to call pfree on everything you allocated
using palloc; it will go away anyway. However, if you want to
allocate any data structures to live across calls, you need to put
them somewhere else. The memory context referenced by
multi_call_memory_ctx is a suitable location for any data that
needs to survive until the SRF is finished running. In most cases,
this means that you should switch into multi_call_memory_ctx while
doing the first-call setup.Ali> The important part "However, if you want to allocate any data
Ali> structures to live across calls, you need to put them somewhere
Ali> else." is buried in the docs.Ali> But i think explicit warning is more noticeable for new
Ali> developer like me.I was thinking something like this, added just after that para:
<warning>
<para>
While the actual arguments to the function remain unchanged between
calls, if you detoast the argument values (which is normally done
transparently by the
<function>PG_GETARG_<replaceable>xxx</replaceable></function> macro)
in the transient context then the detoasted copies will be freed on
each cycle. Accordingly, if you keep references to such values in
your <structfield>user_fctx</>, you must either copy them into the
<structfield>multi_call_memory_ctx</> after detoasting, or ensure
that you detoast the values only in that context.
</para>
</warning>
I'm OK with this.
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2014-12-18 19:35 GMT+07:00 Fujii Masao <masao.fujii@gmail.com>:
On Mon, Dec 15, 2014 at 2:38 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:I was thinking something like this, added just after that para:
<warning>
<para>
While the actual arguments to the function remain unchanged between
calls, if you detoast the argument values (which is normally done
transparently by the
<function>PG_GETARG_<replaceable>xxx</replaceable></function>macro)
in the transient context then the detoasted copies will be freed on
each cycle. Accordingly, if you keep references to such values in
your <structfield>user_fctx</>, you must either copy them into the
<structfield>multi_call_memory_ctx</> after detoasting, or ensure
that you detoast the values only in that context.
</para>
</warning>I'm OK with this.
Wrapping the doc changes in a patch. Will add to next commitfest so it
won't be lost.
--
Ali Akbar
Attachments:
srf_add_doc.patchtext/x-diff; charset=US-ASCII; name=srf_add_doc.patchDownload
*** a/doc/src/sgml/xfunc.sgml
--- b/doc/src/sgml/xfunc.sgml
***************
*** 2986,2991 **** SRF_RETURN_DONE(funcctx)
--- 2986,3005 ----
<structfield>multi_call_memory_ctx</> while doing the first-call setup.
</para>
+ <warning>
+ <para>
+ While the actual arguments to the function remain unchanged between
+ calls, if you detoast the argument values (which is normally done
+ transparently by the
+ <function>PG_GETARG_<replaceable>xxx</replaceable></function> macro)
+ in the transient context then the detoasted copies will be freed on
+ each cycle. Accordingly, if you keep references to such values in
+ your <structfield>user_fctx</>, you must either copy them into the
+ <structfield>multi_call_memory_ctx</> after detoasting, or ensure
+ that you detoast the values only in that context.
+ </para>
+ </warning>
+
<para>
A complete pseudo-code example looks like the following:
<programlisting>
On Wed, Jan 14, 2015 at 11:04 AM, Ali Akbar <the.apaan@gmail.com> wrote:
2014-12-18 19:35 GMT+07:00 Fujii Masao <masao.fujii@gmail.com>:
On Mon, Dec 15, 2014 at 2:38 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:I was thinking something like this, added just after that para:
<warning>
<para>
While the actual arguments to the function remain unchanged
between
calls, if you detoast the argument values (which is normally done
transparently by the
<function>PG_GETARG_<replaceable>xxx</replaceable></function>
macro)
in the transient context then the detoasted copies will be freed
on
each cycle. Accordingly, if you keep references to such values in
your <structfield>user_fctx</>, you must either copy them into the
<structfield>multi_call_memory_ctx</> after detoasting, or ensure
that you detoast the values only in that context.
</para>
</warning>I'm OK with this.
Wrapping the doc changes in a patch. Will add to next commitfest so it won't
be lost.
Pushed. Thanks!
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers