pow support for pgbench
Hi,
I've written a small patch to add support for pow() in pgbench.
The main reason behind it is that I'm currently using a shell call to do it
which takes between 2-10 ms that can be a big percentage of the time taken
by the whole transaction. For example (shortened):
latency average = 11.718 ms
- statement latencies in milliseconds:
2.834 \setshell POWER2 awk 'BEGIN {p=2^ARGV[1]; print p }'
:ZOOM_CURRENT
8.846 SELECT
ST_AsBinary(ST_Simplify(ST_SnapToGrid("the_geom_webmercator",:SNAP),
:SIMPLIFY)) AS geom FROM
I've also updated the related docs and added some tests. Please let me know
if I'm missing anything.
Regards,
*Raúl Marín Rodríguez*
carto.com
Attachments:
pgbench_pow_v1.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v1.patchDownload
From 08a4d519e0c73d0f16acd9e5db9e5b547a884902 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 13 Oct 2017 17:42:23 +0200
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 +++++++
src/bin/pgbench/exprparse.y | 3 +++
src/bin/pgbench/pgbench.c | 9 +++++++++
src/bin/pgbench/pgbench.h | 3 ++-
src/bin/pgbench/t/001_pgbench_with_server.pl | 7 ++++++-
5 files changed, 27 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index e509e6c7f62..e12a149c5bb 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1022,6 +1022,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>sqrt(2.0)</literal></entry>
<entry><literal>1.414213562</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double if <replaceable>x</replaceable> or <replaceable>y</replaceable> are doubles, else integer</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0/<literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index b3a2d9bfd37..4db3b215abf 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -191,6 +191,9 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 5d8a01c72cf..a3aef108f84 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -1346,6 +1346,7 @@ evalFunc(TState *thread, CState *st,
case PGBENCH_MUL:
case PGBENCH_DIV:
case PGBENCH_MOD:
+ case PGBENCH_POW:
{
PgBenchValue *lval = &vargs[0],
*rval = &vargs[1];
@@ -1381,6 +1382,10 @@ evalFunc(TState *thread, CState *st,
setDoubleValue(retval, ld / rd);
return true;
+ case PGBENCH_POW:
+ setDoubleValue(retval, pow(ld, rd));
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1442,6 +1447,10 @@ evalFunc(TState *thread, CState *st,
return true;
+ case PGBENCH_POW:
+ setIntValue(retval, pow(li, ri));
+ return true;
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index fd428af274f..e0132b5fcf6 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -75,7 +75,8 @@ typedef enum PgBenchFunction
PGBENCH_SQRT,
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
- PGBENCH_RANDOM_EXPONENTIAL
+ PGBENCH_RANDOM_EXPONENTIAL,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 11bc0fecfef..601c7ee0e4d 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -218,7 +218,9 @@ sub pgbench
qr{command=18.: double 18\b},
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
- qr{command=21.: int 9223372036854775807\b}, ],
+ qr{command=21.: int 9223372036854775807\b},
+ qr{command=23.: int -27\b},
+ qr{command=24.: double 1024\b}, ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -248,6 +250,9 @@ sub pgbench
\set maxint debug(:minint - 1)
-- reset a variable
\set i1 0
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
} });
# backslash commands
On Fri, Oct 27, 2017 at 4:51 PM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:
I've written a small patch to add support for pow() in pgbench.
Cool.
The main reason behind it is that I'm currently using a shell call to do it
which takes between 2-10 ms that can be a big percentage of the time taken
by the whole transaction. For example (shortened):latency average = 11.718 ms
- statement latencies in milliseconds:
2.834 \setshell POWER2 awk 'BEGIN {p=2^ARGV[1]; print p }'
:ZOOM_CURRENT
8.846 SELECT
ST_AsBinary(ST_Simplify(ST_SnapToGrid("the_geom_webmercator",:SNAP),
:SIMPLIFY)) AS geom FROMI've also updated the related docs and added some tests. Please let me know
if I'm missing anything.
Please add this patch to the upcoming commit fest if you would like to
get some feedback:
https://commitfest.postgresql.org/15/
I am adding as well Fabien in CC who worked in getting the internal
function infrastructure in the shape it is now (waaay better) with
commit 86c43f4.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Paquier wrote:
Please add this patch to the upcoming commit fest if you would like to
get some feedback:
https://commitfest.postgresql.org/15/I am adding as well Fabien in CC who worked in getting the internal
function infrastructure in the shape it is now (waaay better) with
commit 86c43f4.
I think Ra�l would do well to review this patch by Fabien
/messages/by-id/alpine.DEB.2.20.1710201835390.15170@lancre
which adds a few functions and operators.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Oct 30, 2017 at 11:07 AM, Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:
Michael Paquier wrote:
Please add this patch to the upcoming commit fest if you would like to
get some feedback:
https://commitfest.postgresql.org/15/I am adding as well Fabien in CC who worked in getting the internal
function infrastructure in the shape it is now (waaay better) with
commit 86c43f4.I think Raúl would do well to review this patch by Fabien
/messages/by-id/alpine.DEB.2.20.1710201835390.15170@lancre
which adds a few functions and operators.
Good idea. pow() is not added by Fabien's patch, but an operator for
pow() could be something to add as well.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
both patches seem complementary. I've rebased my changes on top of that
patch
(v14) in https://git.io/vFtnT and everything seems to be working fine.
On Mon, Oct 30, 2017 at 12:36 PM, Michael Paquier <michael.paquier@gmail.com
wrote:
On Mon, Oct 30, 2017 at 11:07 AM, Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:Michael Paquier wrote:
Please add this patch to the upcoming commit fest if you would like to
get some feedback:
https://commitfest.postgresql.org/15/I am adding as well Fabien in CC who worked in getting the internal
function infrastructure in the shape it is now (waaay better) with
commit 86c43f4.I think Raúl would do well to review this patch by Fabien
/messages/by-id/alpine.DEB.2.20.1710201835390.15170@lancre
which adds a few functions and operators.
Good idea. pow() is not added by Fabien's patch, but an operator for
pow() could be something to add as well.
--
Michael
--
*Raúl Marín Rodríguez*carto.com
On Mon, Oct 30, 2017 at 11:56 AM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:
both patches seem complementary. I've rebased my changes on top of that
patch
(v14) in https://git.io/vFtnT and everything seems to be working fine.
Attaching patches directly to a thread is a better practice as if
github goes away, any Postgres developers can still have an access to
any code you publish using the public archives on postgresql.org.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Paquier wrote:
Attaching patches directly to a thread is a better practice as if
github goes away, any Postgres developers can still have an access to
any code you publish using the public archives on postgresql.org.
Also, by posting to pgsql-hackers indicating intention to integrate to
Postgres you're implicitly making a statement about the license of your
contribution; see the second half of the last paragraph at
https://wiki.postgresql.org/wiki/Archives_Policy
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Sorry about the patch. Attaching it now so it can be considered as
submitted.
--
*Raúl Marín Rodríguez*carto.com
Attachments:
pgbench_pow_v2_pgbench-more-ops-funcs-14.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v2_pgbench-more-ops-funcs-14.patchDownload
From a6eecfe6637bdb0cbb02a9eda7b88d9c4325bb51 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 13 Oct 2017 17:42:23 +0200
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 +++++++
src/bin/pgbench/exprparse.y | 3 +++
src/bin/pgbench/pgbench.c | 9 +++++++++
src/bin/pgbench/pgbench.h | 3 ++-
src/bin/pgbench/t/001_pgbench_with_server.pl | 5 +++++
5 files changed, 26 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 1f55967e40a..2e913dfcfd6 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1233,6 +1233,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>sqrt(2.0)</literal></entry>
<entry><literal>1.414213562</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double if <replaceable>x</replaceable> or <replaceable>y</replaceable> are doubles, else integer</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0/<literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 770be981f06..290bca99d12 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -334,6 +334,9 @@ static const struct
{
"!case_end", -2, PGBENCH_CASE
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index add653bf90c..b2bab9b8239 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -1474,6 +1474,7 @@ evalFunc(TState *thread, CState *st,
case PGBENCH_NE:
case PGBENCH_LE:
case PGBENCH_LT:
+ case PGBENCH_POW:
{
PgBenchValue *lval = &vargs[0],
*rval = &vargs[1];
@@ -1525,6 +1526,10 @@ evalFunc(TState *thread, CState *st,
setBoolValue(retval, ld < rd);
return true;
+ case PGBENCH_POW:
+ setDoubleValue(retval, pow(ld, rd));
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1602,6 +1607,10 @@ evalFunc(TState *thread, CState *st,
return true;
+ case PGBENCH_POW:
+ setIntValue(retval, pow(li, ri));
+ return true;
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index e1277a1dde6..9f26af92bf6 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -94,7 +94,8 @@ typedef enum PgBenchFunction
PGBENCH_LE,
PGBENCH_LT,
PGBENCH_IS,
- PGBENCH_CASE
+ PGBENCH_CASE,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 8e19bbd3f45..81b7ce4dfcc 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -237,6 +237,8 @@ sub pgbench
qr{command=36.: int 36\b},
qr{command=37.: boolean true\b},
qr{command=38.: boolean true\b},
+ qr{command=44.: int -27\b},
+ qr{command=45.: double 1024\b},
],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
@@ -299,6 +301,9 @@ sub pgbench
\set v2 5432
\set v3 -54.21E-2
SELECT :v0, :v1, :v2, :v3;
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
} });
=head
Hello Michaël,
I'm fine with having pow in pgbench.
I am adding as well Fabien in CC who worked in getting the internal
function infrastructure in the shape it is now (waaay better) with
commit 86c43f4.
It might be even better if https://commitfest.postgresql.org/15/985/,
which has been around for over one year (!), get through some day...
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello Raúl,
Sorry about the patch. Attaching it now so it can be considered as
submitted.
There is a typo in the XML doc:
<literal>1024.0/<literal>
Please check that the documentation compiles.
I'm at odds with having the integer version rely on a double pow(), even
if it works. I think that there should be a specific integer version which
does use integer operations. From stack overflow, the following is
suggested:
int ipow(int base, int exp)
{
int result = 1;
while (exp)
{
if (exp & 1)
result *= base;
exp >>= 1;
base *= base;
}
return result;
}
The integer version should be when x & y are integers *AND* y >= 0.
if y is a negative integer, the double version should be used.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi Fabien,
Thanks for the review.
I've fixed the documentation and added an ipow function that handles both
positive and negative ints, having 0^0 == 1 and 0^(negative) == PG_INT64_MAX
since that's what my glibc math.h pow() is returning.
On Sat, Nov 4, 2017 at 12:34 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Hello Raúl,
Sorry about the patch. Attaching it now so it can be considered as
submitted.
There is a typo in the XML doc:
<literal>1024.0/<literal>
Please check that the documentation compiles.
I'm at odds with having the integer version rely on a double pow(), even
if it works. I think that there should be a specific integer version which
does use integer operations. From stack overflow, the following is
suggested:int ipow(int base, int exp)
{
int result = 1;
while (exp)
{
if (exp & 1)
result *= base;
exp >>= 1;
base *= base;
}return result;
}The integer version should be when x & y are integers *AND* y >= 0.
if y is a negative integer, the double version should be used.
--
Fabien.
--
*Raúl Marín Rodríguez*carto.com
Attachments:
pgbench_pow_v3_pgbench-more-ops-funcs-14.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v3_pgbench-more-ops-funcs-14.patchDownload
From 09105f8108e439834510ee5fb53036473f2977d5 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 13 Oct 2017 17:42:23 +0200
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++++
src/bin/pgbench/exprparse.y | 3 ++
src/bin/pgbench/pgbench.c | 54 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 9 +++++
5 files changed, 75 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 1f55967e40a..4e7f75ddf87 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1233,6 +1233,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>sqrt(2.0)</literal></entry>
<entry><literal>1.414213562</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double if <replaceable>x</replaceable> or <replaceable>y</replaceable> are doubles, else integer</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 770be981f06..290bca99d12 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -334,6 +334,9 @@ static const struct
{
"!case_end", -2, PGBENCH_CASE
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index add653bf90c..bdf3e97682a 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -739,6 +739,51 @@ getPoissonRand(TState *thread, int64 center)
}
/*
+ * pow() for integer values
+ */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result;
+
+ if (base == 0)
+ {
+ if (exp > 0)
+ return 0;
+ else if (exp == 0)
+ return 1;
+ return PG_INT64_MAX;
+ }
+
+ /*
+ * For exp > 0 calculate normally
+ * For exp == 0 return 1 * sign of base
+ * For exp < 0 return 0 except when the base is 1 or -1
+ */
+ if (exp > 0)
+ {
+ result = 1;
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+ }
+ else if (exp == 0)
+ result = (base > 0) - (base < 0);
+ else
+ {
+ result = 1 / base;
+ if (exp % 2 == 0)
+ result *= result;
+ }
+
+ return result;
+}
+
+/*
* Initialize the given SimpleStats struct to all zeroes
*/
static void
@@ -1474,6 +1519,7 @@ evalFunc(TState *thread, CState *st,
case PGBENCH_NE:
case PGBENCH_LE:
case PGBENCH_LT:
+ case PGBENCH_POW:
{
PgBenchValue *lval = &vargs[0],
*rval = &vargs[1];
@@ -1525,6 +1571,10 @@ evalFunc(TState *thread, CState *st,
setBoolValue(retval, ld < rd);
return true;
+ case PGBENCH_POW:
+ setDoubleValue(retval, pow(ld, rd));
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1602,6 +1652,10 @@ evalFunc(TState *thread, CState *st,
return true;
+ case PGBENCH_POW:
+ setIntValue(retval, ipow(li, ri));
+ return true;
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index e1277a1dde6..9f26af92bf6 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -94,7 +94,8 @@ typedef enum PgBenchFunction
PGBENCH_LE,
PGBENCH_LT,
PGBENCH_IS,
- PGBENCH_CASE
+ PGBENCH_CASE,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 8e19bbd3f45..c5ecd749c40 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -237,6 +237,10 @@ sub pgbench
qr{command=36.: int 36\b},
qr{command=37.: boolean true\b},
qr{command=38.: boolean true\b},
+ qr{command=44.: int -27\b},
+ qr{command=45.: double 1024\b},
+ qr{command=46.: int 0\b},
+ qr{command=47.: double -0.125\b},
],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
@@ -299,6 +303,11 @@ sub pgbench
\set v2 5432
\set v3 -54.21E-2
SELECT :v0, :v1, :v2, :v3;
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
} });
=head
Hello Raúl,
I've fixed the documentation and added an ipow function that handles both
positive and negative ints, having 0^0 == 1 and 0^(negative) == PG_INT64_MAX
since that's what my glibc math.h pow() is returning.
From the comment:
* For exp < 0 return 0 except when the base is 1 or -1
I think that it should do what POW does in psql, i.e.:
fabien=# SELECT POW(2, -2); # 0.25
that is if exp < 0 the double version should be used, it should
not return 0.
Basically the idea is that the pgbench client-side version should behave
the same as the SQL version.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi Fabien,
Sorry for the confusion, I wasn't aware that SQL pow changed types
depending on
the input value.
I've modified the function to match more closely the behaviour of SQL,
except
that 0^(negative) returns 'double inf'. Do you think there is any value in
raising an error instead?
On Mon, Nov 6, 2017 at 2:12 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Hello Raúl,
I've fixed the documentation and added an ipow function that handles both
positive and negative ints, having 0^0 == 1 and 0^(negative) ==
PG_INT64_MAX
since that's what my glibc math.h pow() is returning.From the comment:
* For exp < 0 return 0 except when the base is 1 or -1
I think that it should do what POW does in psql, i.e.:
fabien=# SELECT POW(2, -2); # 0.25
that is if exp < 0 the double version should be used, it should
not return 0.Basically the idea is that the pgbench client-side version should behave
the same as the SQL version.--
Fabien.
--
*Raúl Marín Rodríguez*carto.com
Attachments:
pgbench_pow_v4_pgbench-more-ops-funcs-14.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v4_pgbench-more-ops-funcs-14.patchDownload
From be2fedcae277f7eede621dcda66b15b08372ce63 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 13 Oct 2017 17:42:23 +0200
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 +++
src/bin/pgbench/exprparse.y | 3 +
src/bin/pgbench/pgbench.c | 84 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 13 +++++
5 files changed, 109 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 1f55967e40a..32c94ba0dc1 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1233,6 +1233,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>sqrt(2.0)</literal></entry>
<entry><literal>1.414213562</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>integer if <replaceable>x</replaceable> and <replaceable>y</replaceable> are integers and <literal><replaceable>y</replaceable> >= 0</literal>, else double</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 770be981f06..290bca99d12 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -334,6 +334,9 @@ static const struct
{
"!case_end", -2, PGBENCH_CASE
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index add653bf90c..d565880b6e2 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -739,6 +739,51 @@ getPoissonRand(TState *thread, int64 center)
}
/*
+ * pow() for integer values
+ */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result;
+
+ if (base == 0)
+ {
+ if (exp > 0)
+ return 0;
+ else if (exp == 0)
+ return 1;
+ return PG_INT64_MAX;
+ }
+
+ /*
+ * For exp > 0 calculate normally
+ * For exp == 0 return 1 * sign of base
+ * For exp < 0 return 0 except when the base is 1 or -1
+ */
+ if (exp > 0)
+ {
+ result = 1;
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+ }
+ else if (exp == 0)
+ result = (base > 0) - (base < 0);
+ else
+ {
+ result = 1 / base;
+ if (exp % 2 == 0)
+ result *= result;
+ }
+
+ return result;
+}
+
+/*
* Initialize the given SimpleStats struct to all zeroes
*/
static void
@@ -1918,6 +1963,45 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+ double ld,
+ rd;
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int and exp >= 0 use
+ * the ipow() function, else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0)
+ {
+ setIntValue(retval, ipow(li, ri));
+ return true;
+ }
+ }
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index e1277a1dde6..9f26af92bf6 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -94,7 +94,8 @@ typedef enum PgBenchFunction
PGBENCH_LE,
PGBENCH_LT,
PGBENCH_IS,
- PGBENCH_CASE
+ PGBENCH_CASE,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 8e19bbd3f45..2a1bb1216d7 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -237,6 +237,12 @@ sub pgbench
qr{command=36.: int 36\b},
qr{command=37.: boolean true\b},
qr{command=38.: boolean true\b},
+ qr{command=44.: int -27\b},
+ qr{command=45.: double 1024\b},
+ qr{command=46.: int 1\b},
+ qr{command=47.: double 1\b},
+ qr{command=48.: double -0.125\b},
+ qr{command=49.: double -0.125\b},
],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
@@ -299,6 +305,13 @@ sub pgbench
\set v2 5432
\set v3 -54.21E-2
SELECT :v0, :v1, :v2, :v3;
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
} });
=head
Hello,
Sorry for the confusion, I wasn't aware that SQL pow changed types
depending on the input value.
Indeed, this is quite strange...
fabien=# SELECT i, POW(2, i) FROM generate_series(-2, 2) AS i;
-2 | 0.25
-1 | 0.5
0 | 1
1 | 2
2 | 4
I've modified the function to match more closely the behaviour of SQL,
except that 0^(negative) returns 'double inf'. Do you think there is any
value in raising an error instead?
fabien=# SELECT POW(0,-1);
ERROR: zero raised to a negative power is undefined
Hmmmm... I'm fine with double inf, because exception in pgbench means the
end of the script, which is not desirable for benchmarking purposes.
I think that:
- you can simplify the ipow function by removing handling of y<0 case,
maybe add an assert to be sure to avoid it.
- you should add more symmetry and simplify the evaluation:
if (int & int)
{
i1, i2 = ...;
if (i2 >= 0)
setIntValue(retval, ipow(i1, i2));
else
// conversion is done by C, no need to coerce again
setDoubleValue(retval, pow(i1, i2));
}
else
{
d1, d2 = ...;
setDoubleValue(retval, pow(d1, d2));
}
Add a test case to show what happens on NULL arguments, hopefully the
result is NULL.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
Indeed, this is quite strange...
I don't want to go too deep into it, but you get stuff like this:
Select pow(2.0, -3)::text = pow(2, -3)::text;
?column?
----------
f
(1 row)
- you can simplify the ipow function by removing handling of y<0 case,
maybe add an assert to be sure to avoid it.
I agree, done.
- you should add more symmetry and simplify the evaluation:
Done too.
Add a test case to show what happens on NULL arguments, hopefully the
result is NULL.
Done and it does.
Thanks again for the review.
On Mon, Nov 6, 2017 at 4:14 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Hello,
Sorry for the confusion, I wasn't aware that SQL pow changed types
depending on the input value.
Indeed, this is quite strange...
fabien=# SELECT i, POW(2, i) FROM generate_series(-2, 2) AS i;
-2 | 0.25
-1 | 0.5
0 | 1
1 | 2
2 | 4I've modified the function to match more closely the behaviour of SQL,
except that 0^(negative) returns 'double inf'. Do you think there is any
value in raising an error instead?fabien=# SELECT POW(0,-1);
ERROR: zero raised to a negative power is undefinedHmmmm... I'm fine with double inf, because exception in pgbench means the
end of the script, which is not desirable for benchmarking purposes.I think that:
- you can simplify the ipow function by removing handling of y<0 case,
maybe add an assert to be sure to avoid it.- you should add more symmetry and simplify the evaluation:
if (int & int)
{
i1, i2 = ...;
if (i2 >= 0)
setIntValue(retval, ipow(i1, i2));
else
// conversion is done by C, no need to coerce again
setDoubleValue(retval, pow(i1, i2));
}
else
{
d1, d2 = ...;
setDoubleValue(retval, pow(d1, d2));
}Add a test case to show what happens on NULL arguments, hopefully the
result is NULL.--
Fabien.
--
*Raúl Marín Rodríguez *carto.com
Attachments:
pgbench_pow_v5_pgbench-more-ops-funcs-14.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v5_pgbench-more-ops-funcs-14.patchDownload
From 47f6ec396d3bc11c39066dbd4b31e75b76102094 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 13 Oct 2017 17:42:23 +0200
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++++
src/bin/pgbench/exprparse.y | 3 ++
src/bin/pgbench/pgbench.c | 62 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 15 +++++++
5 files changed, 89 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 1f55967e40a..32c94ba0dc1 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1233,6 +1233,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>sqrt(2.0)</literal></entry>
<entry><literal>1.414213562</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>integer if <replaceable>x</replaceable> and <replaceable>y</replaceable> are integers and <literal><replaceable>y</replaceable> >= 0</literal>, else double</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 770be981f06..290bca99d12 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -334,6 +334,9 @@ static const struct
{
"!case_end", -2, PGBENCH_CASE
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index add653bf90c..3781e75721d 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -739,6 +739,27 @@ getPoissonRand(TState *thread, int64 center)
}
/*
+ * pow() for integer values with exp >= 0. Matches SQL pow() behaviour
+ */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result = 1;
+
+ Assert(exp >= 0);
+
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+
+ return result;
+}
+
+/*
* Initialize the given SimpleStats struct to all zeroes
*/
static void
@@ -1918,6 +1939,47 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int and exp >= 0 use
+ * the ipow() function, else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0)
+ setIntValue(retval, ipow(li, ri));
+ else
+ setDoubleValue(retval, pow(li, ri));
+ }
+ else
+ {
+ double ld,
+ rd;
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ }
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index e1277a1dde6..9f26af92bf6 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -94,7 +94,8 @@ typedef enum PgBenchFunction
PGBENCH_LE,
PGBENCH_LT,
PGBENCH_IS,
- PGBENCH_CASE
+ PGBENCH_CASE,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 8e19bbd3f45..644add6ea63 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -237,6 +237,13 @@ sub pgbench
qr{command=36.: int 36\b},
qr{command=37.: boolean true\b},
qr{command=38.: boolean true\b},
+ qr{command=44.: int -27\b},
+ qr{command=45.: double 1024\b},
+ qr{command=46.: int 1\b},
+ qr{command=47.: double 1\b},
+ qr{command=48.: double -0.125\b},
+ qr{command=49.: double -0.125\b},
+ qr{command=50.: null\b},
],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
@@ -299,6 +306,14 @@ sub pgbench
\set v2 5432
\set v3 -54.21E-2
SELECT :v0, :v1, :v2, :v3;
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
+\set powernull debug(pow(NULL, NULL))
} });
=head
I don't want to go too deep into it, but you get stuff like this:
Select pow(2.0, -3)::text = pow(2, -3)::text;
Sure. It does so with any overloaded operator or function:
fabien=# SELECT (2.0 + 3)::TEXT = (2 + 3)::TEXT; # f
Patch applies, make check ok in pgbench, doc gen ok.
ipow code is nice and simple.
I switched the patch to "Ready for Committer"
Let's now hope that a committer gets around to consider these patch some
day.
--
Fabien.
--
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, Nov 7, 2017 at 1:34 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Let's now hope that a committer gets around to consider these patch some
day.
Which is not the case yet, so moved to CF 2018-01. Please note that
the patch proposed does not apply anymore, so its status is changed to
"waiting on author" for a rebase.
--
Michael
Hi,
I've rebased the patch so it can be applied cleanly on top of current
master.
On Fri, Dec 1, 2017 at 3:55 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Tue, Nov 7, 2017 at 1:34 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Let's now hope that a committer gets around to consider these patch some
day.Which is not the case yet, so moved to CF 2018-01. Please note that
the patch proposed does not apply anymore, so its status is changed to
"waiting on author" for a rebase.
--
Michael
--
*Raúl Marín Rodríguez *carto.com
Attachments:
pgbench_pow_v6.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v6.patchDownload
From b12a7a37b1af1ede1aa9d5d88d0918808c54e19f Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 1 Dec 2017 10:49:17 +0100
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++++
src/bin/pgbench/exprparse.y | 3 ++
src/bin/pgbench/pgbench.c | 62 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 16 ++++++-
5 files changed, 89 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 94b495e606..769e604dd6 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1099,6 +1099,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>sqrt(2.0)</literal></entry>
<entry><literal>1.414213562</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>integer if <replaceable>x</replaceable> and <replaceable>y</replaceable> are integers and <literal><replaceable>y</replaceable> >= 0</literal>, else double</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index b3a2d9bfd3..4db3b215ab 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -191,6 +191,9 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index bd96eae5e6..5a5197cf63 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -746,6 +746,27 @@ getPoissonRand(TState *thread, int64 center)
return (int64) (-log(uniform) * ((double) center) + 0.5);
}
+ /*
+ * pow() for integer values with exp >= 0. Matches SQL pow() behaviour
+ */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result = 1;
+
+ Assert(exp >= 0);
+
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+
+ return result;
+}
+
/*
* Initialize the given SimpleStats struct to all zeroes
*/
@@ -1673,6 +1694,47 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int and exp >= 0 use
+ * the ipow() function, else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0)
+ setIntValue(retval, ipow(li, ri));
+ else
+ setDoubleValue(retval, pow(li, ri));
+ }
+ else
+ {
+ double ld,
+ rd;
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ }
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index fd428af274..e0132b5fcf 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -75,7 +75,8 @@ typedef enum PgBenchFunction
PGBENCH_SQRT,
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
- PGBENCH_RANDOM_EXPONENTIAL
+ PGBENCH_RANDOM_EXPONENTIAL,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index c095881312..fcb30cdde5 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -231,7 +231,14 @@ pgbench(
qr{command=18.: double 18\b},
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
- qr{command=21.: int 9223372036854775807\b}, ],
+ qr{command=21.: int 9223372036854775807\b},
+ qr{command=23.: int -27\b},
+ qr{command=24.: double 1024\b},
+ qr{command=25.: int 1\b},
+ qr{command=26.: double 1\b},
+ qr{command=27.: double -0.125\b},
+ qr{command=28.: double -0.125\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -261,6 +268,13 @@ pgbench(
\set maxint debug(:minint - 1)
-- reset a variable
\set i1 0
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
} });
# backslash commands
--
2.15.1
Hello Raúl,
I've rebased the patch so it can be applied cleanly on top of current
master.
The idea is that it would be relative to the "more functions and
operators" patch, but I guess this is too much for people checking, so I'm
fine with having it with the current base.
Patch applies cleanly, make check ok.
Back to "ready".
--
Fabien.
Hi Fabien,
The idea is that it would be relative to the "more functions and operators"
patch, but I guess this is too much for people checking, so I'm fine with
having it with the current base.
I tried applying the last "more functions and operators" patch
(pgbench-more-ops-funcs-14.patch) but it also stopped applying cleanly so I
decided to go for master to avoid having too many issues. Let me know if
you rework that patch and I'll be happy to rebase mine on top.
--
*Raúl Marín Rodríguez *carto.com
On Fri, Dec 1, 2017 at 4:57 AM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:
I've rebased the patch so it can be applied cleanly on top of current
master.
Please add the new function into the documentation table in alphabetical order.
The fact that the return type is not consistently of one type bothers
me. I'm not sure pgbench's expression language is a good place to
runtime polymorphism -- SQL doesn't work that way.
+ /*
+ * pow() for integer values with exp >= 0. Matches SQL pow() behaviour
+ */What's the name of the backend function whose behavior this matches?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hello Robert,
The fact that the return type is not consistently of one type bothers
me. I'm not sure pgbench's expression language is a good place to
runtime polymorphism -- SQL doesn't work that way.
Sure.
Pg has a NUMERIC adaptative precision version, which is cheating, because
it can return kind of an "int" or a "float", depending on whether there
are digits after the decimal point or not.
Pgbench does not have support for NUMERIC, just INT & DOUBLE, so the
current version is an approximation of that.
Now it is always possible to just do DOUBLE version, but this won't match
SQL behavior either.
+ /* + * pow() for integer values with exp >= 0. Matches SQL pow() behaviour + */What's the name of the backend function whose behavior this matches?
POW(numeric,numeric) -> numeric, which matches "numeric_power".
--
Fabien.
The fact that the return type is not consistently of one type bothers
me. I'm not sure pgbench's expression language is a good place to
runtime polymorphism -- SQL doesn't work that way.Sure.
Pg has a NUMERIC adaptative precision version, which is cheating, because it
can return kind of an "int" or a "float", depending on whether there are
digits after the decimal point or not.Pgbench does not have support for NUMERIC, just INT & DOUBLE, so the current
version is an approximation of that.Now it is always possible to just do DOUBLE version, but this won't match SQL
behavior either.
Another point I forgot: pgbench functions and operators are notably
interesting to generate/transform keys in tables, which are usually
integers, so having int functions when possible/appropriate is desirable,
which explain why I pushed for having an int version for POW.
Also, pgbench does not have a static typing model because variable types
are not declared "\set i ...", so the type is somehow "guessed" based on
the string values, although if in doubt it is always possible to convert
(with "int" & "double" functions).
So for me the philosophy is to have expression match SQL behavior when
possible, as closely as possible, but it is not an exact match.
--
Fabien.
Hi,
Please add the new function into the documentation table in alphabetical
order.
Fixed in the attached patch.
What's the name of the backend function whose behavior this matches?
As Fabien has mentioned, it tries to behave as "numeric_power". Maybe we
it'd
better if we switch to "dpow" (which is pow with some error handling) and
always
return a double. What do you think?
On Fri, Dec 1, 2017 at 8:02 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Dec 1, 2017 at 4:57 AM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:I've rebased the patch so it can be applied cleanly on top of current
master.Please add the new function into the documentation table in alphabetical
order.The fact that the return type is not consistently of one type bothers
me. I'm not sure pgbench's expression language is a good place to
runtime polymorphism -- SQL doesn't work that way.+ /* + * pow() for integer values with exp >= 0. Matches SQL pow() behaviour + */What's the name of the backend function whose behavior this matches?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
*Raúl Marín Rodríguez *carto.com
Attachments:
pgbench_pow_v7.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v7.patchDownload
From 47eac90ce1eacd3c7c5f4a970d3d16dced6b37ad Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Fri, 1 Dec 2017 10:49:17 +0100
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++++
src/bin/pgbench/exprparse.y | 3 ++
src/bin/pgbench/pgbench.c | 62 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 16 ++++++-
5 files changed, 89 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 94b495e606..af95a8b43a 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1069,6 +1069,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>pi()</literal></entry>
<entry><literal>3.14159265358979323846</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>integer if <replaceable>x</replaceable> and <replaceable>y</replaceable> are integers and <literal><replaceable>y</replaceable> >= 0</literal>, else double</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
<row>
<entry><literal><function>random(<replaceable>lb</replaceable>, <replaceable>ub</replaceable>)</function></literal></entry>
<entry>integer</entry>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index b3a2d9bfd3..4db3b215ab 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -191,6 +191,9 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index bd96eae5e6..5a5197cf63 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -746,6 +746,27 @@ getPoissonRand(TState *thread, int64 center)
return (int64) (-log(uniform) * ((double) center) + 0.5);
}
+ /*
+ * pow() for integer values with exp >= 0. Matches SQL pow() behaviour
+ */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result = 1;
+
+ Assert(exp >= 0);
+
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+
+ return result;
+}
+
/*
* Initialize the given SimpleStats struct to all zeroes
*/
@@ -1673,6 +1694,47 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int and exp >= 0 use
+ * the ipow() function, else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0)
+ setIntValue(retval, ipow(li, ri));
+ else
+ setDoubleValue(retval, pow(li, ri));
+ }
+ else
+ {
+ double ld,
+ rd;
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ }
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index fd428af274..e0132b5fcf 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -75,7 +75,8 @@ typedef enum PgBenchFunction
PGBENCH_SQRT,
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
- PGBENCH_RANDOM_EXPONENTIAL
+ PGBENCH_RANDOM_EXPONENTIAL,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index c095881312..fcb30cdde5 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -231,7 +231,14 @@ pgbench(
qr{command=18.: double 18\b},
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
- qr{command=21.: int 9223372036854775807\b}, ],
+ qr{command=21.: int 9223372036854775807\b},
+ qr{command=23.: int -27\b},
+ qr{command=24.: double 1024\b},
+ qr{command=25.: int 1\b},
+ qr{command=26.: double 1\b},
+ qr{command=27.: double -0.125\b},
+ qr{command=28.: double -0.125\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -261,6 +268,13 @@ pgbench(
\set maxint debug(:minint - 1)
-- reset a variable
\set i1 0
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
} });
# backslash commands
--
2.15.1
Please add the new function into the documentation table in
alphabetical order.Fixed in the attached patch.
Yep. Patch applies cleanly. Make check & pgbench check ok. make html ok.
POW is in the right place in the table, sorry I did not check before.
What's the name of the backend function whose behavior this matches?
As Fabien has mentioned, it tries to behave as "numeric_power". Maybe we
it'd better if we switch to "dpow" (which is pow with some error
handling) and always return a double. What do you think?
My 0.02€: I think that having a integer pow implementation when possible
is a good think for pgbench, because the main use case is to deal with
table keys in a benchmarking scripts, which are expected to be integers.
--
Fabien.
On Mon, Dec 4, 2017 at 10:47 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
What's the name of the backend function whose behavior this matches?
As Fabien has mentioned, it tries to behave as "numeric_power". Maybe we
it'd better if we switch to "dpow" (which is pow with some error handling)
and always return a double. What do you think?My 0.02€: I think that having a integer pow implementation when possible is
a good think for pgbench, because the main use case is to deal with table
keys in a benchmarking scripts, which are expected to be integers.
I'm willing to commit any of the following things:
1. A patch that adds an integer version of pow() but not a double version
2. A patch that adds a double version of pow() but not an integer version
3. A patch that adds both an integer version of pow() and a double
version of pow(), with the two versions having different names
If Raúl is happy with only having an integer version, then I suggest
that he adopt #1 and call it good. Otherwise, given that Fabien wants
the double version, I suggest we call the integer version pow() and
the double version dpow() and go with #3.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Tue, Dec 5, 2017 at 5:38 AM, Robert Haas <robertmhaas@gmail.com> wrote:
I'm willing to commit any of the following things:
1. A patch that adds an integer version of pow() but not a double version
2. A patch that adds a double version of pow() but not an integer version
3. A patch that adds both an integer version of pow() and a double
version of pow(), with the two versions having different namesIf Raúl is happy with only having an integer version, then I suggest
that he adopt #1 and call it good. Otherwise, given that Fabien wants
the double version, I suggest we call the integer version pow() and
the double version dpow() and go with #3.
It seems to me that 1 and 2 have value on their own for the workloads
tried to be emulated, so what you are suggesting in 3 looks good to
me. Now why are two different function names necessary? The parsing
takes care of argument types through PgBenchValue->type so having one
function exposed to the user looks like the most sensible approach to
me.
--
Michael
Michael Paquier <michael.paquier@gmail.com> writes:
On Tue, Dec 5, 2017 at 5:38 AM, Robert Haas <robertmhaas@gmail.com> wrote:
I'm willing to commit any of the following things:
1. A patch that adds an integer version of pow() but not a double version
2. A patch that adds a double version of pow() but not an integer version
3. A patch that adds both an integer version of pow() and a double
version of pow(), with the two versions having different names
It seems to me that 1 and 2 have value on their own for the workloads
tried to be emulated, so what you are suggesting in 3 looks good to
me. Now why are two different function names necessary?
ISTM one key issue here is whether pgbench's expression language is
meant to model SQL (where we have function overloading) or C (where
there is no overloading). I don't think we've really settled on a
fixed policy on that, but maybe now is the time.
If we do think that function overloading is OK, there remains the
question of when the typing is resolved. I think Robert is objecting
to resolving at runtime, and I tend to agree that that's something
we'd regret in the long run. It doesn't match either SQL or C.
regards, tom lane
On Tue, Dec 5, 2017 at 11:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
ISTM one key issue here is whether pgbench's expression language is
meant to model SQL (where we have function overloading) or C (where
there is no overloading). I don't think we've really settled on a
fixed policy on that, but maybe now is the time.
abs() is doing that already. Having some rules in the shape of at
least a comment would be nice.
If we do think that function overloading is OK, there remains the
question of when the typing is resolved. I think Robert is objecting
to resolving at runtime, and I tend to agree that that's something
we'd regret in the long run. It doesn't match either SQL or C.
+1.
--
MichaelHello Tom,
1. A patch that adds an integer version of pow() but not a double version
2. A patch that adds a double version of pow() but not an integer version
3. A patch that adds both an integer version of pow() and a double
version of pow(), with the two versions having different namesIt seems to me that 1 and 2 have value on their own for the workloads
tried to be emulated, so what you are suggesting in 3 looks good to
me. Now why are two different function names necessary?ISTM one key issue here is whether pgbench's expression language is
meant to model SQL (where we have function overloading) or C (where
there is no overloading). I don't think we've really settled on a
fixed policy on that, but maybe now is the time.
Function overloading is implemented for ABS (as noted by Michaï¿œl), but
also LEAST and GREATEST. Typing is dynamic, based on guessing (think
"pgbench -D i=1 -D f=1.0") . These decisions have already been taken, they
were reasonable, the behavior is consistent and useful.
I do not see the point of going backward and breaking compatibility.
The idea is to model after SQL (eg also I've been asked to do that for the
operators & functions extensions, lost in the CF queue), when possible.
When possible is not always.
If we do think that function overloading is OK, there remains the
question of when the typing is resolved.
Dynamic guessing is the only pragmatic option with pgbench.
I think Robert is objecting to resolving at runtime, and I tend to agree
that that's something we'd regret in the long run.
Too late. I do not think that we would come to regret it. I'm rather
regretting that pgbench capabilities are improving so slowly.
It doesn't match either SQL or C.
Sure. A dynamically typed language cannot match a statically typed one. I
do not see this as a significant issue for writing benchmarking scripts.
Now, about POW:
As for approximating NUMERIC, there is a first difficulty, as the type can
either be approximated as an INT or DOUBLE. Ah ah.
Also, POW raises another difficulty, which is that depending on the sign
of the second argument the result is more INT or more DOUBLE. Fun.
So even if we do separate functions (IPOW & DPOW, or POW & DPOW), the
result cannot be typed statically.
Note that type can be enforced if necessary thanks to int() and double()
functions, which is enough of a work around for pgbench simple purpose.
In this context, ISTM that Raul patch is a reasonable, even if imperfect,
compromise.
I can understand that this compromise does not suit Robert. Too bad,
because both POW versions (int & double) have a reasonable use case for
writing benchmarks. Using two names does not resolve the typing issue
anyway. None of the 3 options offered by Robert are really satisfactory,
and the compromise is also rejected.
So basically do whatever you want with the patch (accept, reject, 1, 2,
3). Only "Returned with feedback" with the feedback being "please
implement a statically typed pgbench" does not seem like a useful option.
I can only say that these functions would be useful, so for me it is
better in than out, but that is just my silly opinion.
--
Fabien.
Hi all,
I've been giving a thought about this and I think we could reach the
compromise
of having a single function with 2 overloads:
* pow(double, double) -> double: Uses C pow().
* pow(int, int) -> double: Uses ipow() for positive exponents, and pow()
for negative exponents.
In both cases we'd return a double but we use the fast ipow if it's possible
(which can be 20x faster), so at the cost of an extra cast if you need an
int,
we'd have a consistent API. Would this be acceptable?
I've been giving a thought about this and I think we could reach the
compromise
of having a single function with 2 overloads:
* pow(double, double) -> double: Uses C pow().
* pow(int, int) -> double: Uses ipow() for positive exponents, and pow()
for negative exponents.In both cases we'd return a double but we use the fast ipow if it's
possible (which can be 20x faster), so at the cost of an extra cast if
you need an int, we'd have a consistent API. Would this be acceptable?
This is for Robert to say whether it is more acceptable to him.
My 0.02€: ISTM that it closely equivalent to having just the double
version and using an explicit cast to get an int if needed, which does not
conform anymore to strict SQL behavior than the previous compromise.
Also, probably having something (anything) is better than nothing.
--
Fabien.
On Tue, Dec 5, 2017 at 7:44 AM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:
I've been giving a thought about this and I think we could reach the
compromise
of having a single function with 2 overloads:
* pow(double, double) -> double: Uses C pow().
* pow(int, int) -> double: Uses ipow() for positive exponents, and pow()
for negative exponents.In both cases we'd return a double but we use the fast ipow if it's possible
(which can be 20x faster), so at the cost of an extra cast if you need an
int,
we'd have a consistent API. Would this be acceptable?
It seems OK to me.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
In both cases we'd return a double but we use the fast ipow if it's
possible (which can be 20x faster), so at the cost of an extra cast if
you need an int, we'd have a consistent API. Would this be acceptable?It seems OK to me.
Computing as an int, casting to double and back to int8 can generate a
loss of precision. However for powers of 2 it works exactly, so eg
computing a mask it would be ok.
This proposal does not exactly match SQL behavior, but I do not see this
as a problem, which is why I was happy with the previous proposal.
--
Fabien.
Raúl Marín Rodríguez <rmrodriguez(at)carto(dot)com> wrote:
I don't want to go too deep into it, but you get stuff like this:
Select pow(2.0, -3)::text = pow(2, -3)::text;
?column?
----------
f
Indeed, to me, that has turned out to be the most intriguing part of
the whole thread. Needs to be in some SQL subtleties exam somewhere:
select pow(2.0,-3), pow(2,-3);
pow | pow
--------------------+-------
0.1250000000000000 | 0.125
Looks like the first call resolves to the numeric version, while
the second (with integer arguments) resolves to the double one:
select pow(2.0,-3) is of (numeric), pow(2,-3) is of (double precision);
?column? | ?column?
----------+----------
t | t
Still, a numeric 0.125 doesn't always have those trailing zeros:
select pow(2.0,-3), pow(2,-3)::numeric;
pow | pow
--------------------+-------
0.1250000000000000 | 0.125
What's going on in the representation?
select numeric_send(pow(2.0,-3)), numeric_send(pow(2,-3)::numeric);
numeric_send | numeric_send
------------------------+------------------------
\x0001ffff0000001004e2 | \x0001ffff0000000304e2
I assume the 10 vs. 03 hex in the tails of those things represent
either 'precision' or 'scale' of 16 vs. 3? I don't get much help
from IS OF (numeric(p,s)), which seems to ignore any p,s and just
be true for any numeric. But here, this matches:
select numeric_send(0.125::numeric(16,16));
numeric_send
------------------------
\x0001ffff0000001004e2
How does numeric_power choose the precision and scale of its result?
Is that something the standard dictates?
Given that 0.125 is exact for this answer, at first I wanted to
ask if numeric_power could be made to produce the result with
precision 3, but then I realized that's backwards. A result with
precision 3 would be like saying, eh, it's somewhere between
0.1245 and 0.1255. If a result is known to be exact, it would be
better to go the other way and return it as numeric(huge).
That then led me to wonder if the cast float8_numeric is really
doing the right thing. Is it turning 0.125 (an exact representation
as float8) into numeric(3,3), again hedging as if it might be anything
from 0.1245 to 0.1255? Would it be better for float8_numeric to
produce a numeric with the precision/scale reflecting the actual
limits of float8?
Ok, now I've been driven to UTSL. It looks as if the intent of
the snprintf(..., "%.*g", DBL_DIG, val) in float8_numeric could
have been to accomplish that. It doesn't, though, as (at least
on my platform), %g drops trailing zeros, though there
is a documented 'alternate form' flag # that prevents that.
It works in bash:
bash-4.2$ printf '%.*g\n' 15 0.125
0.125
bash-4.2$ printf '%#.*g\n' 15 0.125
0.125000000000000
Does the standard prescribe how cast(float8 as numeric) ought to
select the precision/scale?
Sorry to drift OT, as this is more about the SQL functions than
pgbench, but it was too puzzling to ignore. :)
-Chap
Fixed in the attached patch.
v7 needs a rebase.
Also, you might try to produce a version which is compatible with
Robert's constraints.
--
Fabien.
Hi,
Rebased the patch on top of current master/HEAD and changed to always
return double.
On Thu, Dec 14, 2017 at 12:48 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Fixed in the attached patch.
v7 needs a rebase.
Also, you might try to produce a version which is compatible with Robert's
constraints.--
Fabien.
--
*Raúl Marín Rodríguez *carto.com
Attachments:
pgbench_pow_v8.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v8.patchDownload
From 0b43116dc68cbe46d5f5caa9ba10f36cda188949 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Thu, 21 Dec 2017 20:28:02 +0100
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++++
src/bin/pgbench/exprparse.y | 3 ++
src/bin/pgbench/pgbench.c | 60 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 16 +++++++-
5 files changed, 87 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 4431fc3eb7..6f7c640db0 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1069,6 +1069,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>pi()</literal></entry>
<entry><literal>3.14159265358979323846</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double</entry>
+ <entry>Numeric exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
<row>
<entry><literal><function>random(<replaceable>lb</replaceable>, <replaceable>ub</replaceable>)</function></literal></entry>
<entry>integer</entry>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 25d5ad48e5..89e1a3b14f 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -194,6 +194,9 @@ static const struct
{
"random_zipfian", 3, PGBENCH_RANDOM_ZIPFIAN
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 7ce6f607f5..f1c1e568e1 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -911,6 +911,25 @@ getZipfianRand(TState *thread, int64 min, int64 max, double s)
: computeHarmonicZipfian(thread, n, s));
}
+/* Faster power function for integer values and exp >= 0 */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result = 1;
+
+ Assert(exp >= 0);
+
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+
+ return result;
+}
+
/*
* Initialize the given SimpleStats struct to all zeroes
*/
@@ -1850,6 +1869,47 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int and exp >= 0 use
+ * the faster ipow() function, else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0)
+ setDoubleValue(retval, ipow(li, ri));
+ else
+ setDoubleValue(retval, pow(li, ri));
+ }
+ else
+ {
+ double ld,
+ rd;
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ }
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 83fee1ae74..0e92882a4c 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -76,7 +76,8 @@ typedef enum PgBenchFunction
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
PGBENCH_RANDOM_EXPONENTIAL,
- PGBENCH_RANDOM_ZIPFIAN
+ PGBENCH_RANDOM_ZIPFIAN,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index e3cdf28628..031b97fc54 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -232,7 +232,14 @@ pgbench(
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
qr{command=21.: int 9223372036854775807\b},
- qr{command=23.: int [1-9]\b}, ],
+ qr{command=23.: int [1-9]\b},
+ qr{command=24.: double -27\b},
+ qr{command=25.: double 1024\b},
+ qr{command=26.: double 1\b},
+ qr{command=27.: double 1\b},
+ qr{command=28.: double -0.125\b},
+ qr{command=29.: double -0.125\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -264,6 +271,13 @@ pgbench(
\set i1 0
-- yet another integer function
\set id debug(random_zipfian(1, 9, 1.3))
+--- pow() operator
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
} });
# backslash commands
--
2.15.1
Hello Raï¿œl,
v7 needs a rebase.
Also, you might try to produce a version which is compatible with Robert's
constraints.
My 0.02ￜ on this new version: Applies cleanly, compiles and works.
I cannot say that I like it more than the previous version.
If a double is always returned, I'm wondering whether keeping the ipow
version makes much sense: In case of double loss of precision, the
precision is lost, too bad, and casting back to int won't bring it back.
In the doc, I'm not sure that "Numeric" brings anything. "Exponentiation"
would be enough.
Also, in pg I just noticed that POW is a shorthand for POWER. Maybe both
should be supported? Or not.
--
Fabien.
Hi Fabien,
Thanks for the review.
If a double is always returned, I'm wondering whether keeping the ipow
version makes much sense: In case of double loss of precision, the
precision is lost, too bad, and casting back to int won't bring it back.
I've kept it because knowing that both are ints enables not making a lot of
checks (done in math.h pow) so it's way faster. In my system it's 2-3ns vs
~40ns. I'm willing to settle for using just pow() if that makes it clearer.
In the doc, I'm not sure that "Numeric" brings anything. "Exponentiation"
would be enough.
Done.
Also, in pg I just noticed that POW is a shorthand for POWER. Maybe both
should be supported? Or not.
I've never used power instead of pow, but I've added for compatibility
shake.
Attached the updated patch.
On Thu, Dec 21, 2017 at 10:48 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
Hello Raúl,
v7 needs a rebase.
Also, you might try to produce a version which is compatible with
Robert's
constraints.My 0.02€ on this new version: Applies cleanly, compiles and works.
I cannot say that I like it more than the previous version.
If a double is always returned, I'm wondering whether keeping the ipow
version makes much sense: In case of double loss of precision, the
precision is lost, too bad, and casting back to int won't bring it back.In the doc, I'm not sure that "Numeric" brings anything. "Exponentiation"
would be enough.Also, in pg I just noticed that POW is a shorthand for POWER. Maybe both
should be supported? Or not.--
Fabien.
--
*Raúl Marín Rodríguez *carto.com
Attachments:
pgbench_pow_v9.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v9.patchDownload
From 58463d2751c016a68410d5357625bf3d7f519ca2 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Thu, 21 Dec 2017 20:28:02 +0100
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++++
src/bin/pgbench/exprparse.y | 6 +++
src/bin/pgbench/pgbench.c | 60 ++++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 18 ++++++++-
5 files changed, 92 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 4431fc3eb7..1519fe78ef 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1069,6 +1069,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>pi()</literal></entry>
<entry><literal>3.14159265358979323846</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function>, <function>power(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double</entry>
+ <entry>exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal>, <literal>power(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
<row>
<entry><literal><function>random(<replaceable>lb</replaceable>, <replaceable>ub</replaceable>)</function></literal></entry>
<entry>integer</entry>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 25d5ad48e5..74ffe5e7a7 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -194,6 +194,12 @@ static const struct
{
"random_zipfian", 3, PGBENCH_RANDOM_ZIPFIAN
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
+ {
+ "power", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 7ce6f607f5..f1c1e568e1 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -911,6 +911,25 @@ getZipfianRand(TState *thread, int64 min, int64 max, double s)
: computeHarmonicZipfian(thread, n, s));
}
+/* Faster power function for integer values and exp >= 0 */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result = 1;
+
+ Assert(exp >= 0);
+
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+
+ return result;
+}
+
/*
* Initialize the given SimpleStats struct to all zeroes
*/
@@ -1850,6 +1869,47 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int and exp >= 0 use
+ * the faster ipow() function, else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0)
+ setDoubleValue(retval, ipow(li, ri));
+ else
+ setDoubleValue(retval, pow(li, ri));
+ }
+ else
+ {
+ double ld,
+ rd;
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ }
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 83fee1ae74..0e92882a4c 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -76,7 +76,8 @@ typedef enum PgBenchFunction
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
PGBENCH_RANDOM_EXPONENTIAL,
- PGBENCH_RANDOM_ZIPFIAN
+ PGBENCH_RANDOM_ZIPFIAN,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index e3cdf28628..b0aeae66f0 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -232,7 +232,15 @@ pgbench(
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
qr{command=21.: int 9223372036854775807\b},
- qr{command=23.: int [1-9]\b}, ],
+ qr{command=23.: int [1-9]\b},
+ qr{command=24.: double -27\b},
+ qr{command=25.: double 1024\b},
+ qr{command=26.: double 1\b},
+ qr{command=27.: double 1\b},
+ qr{command=28.: double -0.125\b},
+ qr{command=29.: double -0.125\b},
+ qr{command=30.: double -0.00032\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -264,6 +272,14 @@ pgbench(
\set i1 0
-- yet another integer function
\set id debug(random_zipfian(1, 9, 1.3))
+--- pow and power
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
+\set powernegd2 debug(power(-5.0,-5.0))
} });
# backslash commands
--
2.15.1
Hello,
If a double is always returned, I'm wondering whether keeping the ipow
version makes much sense: In case of double loss of precision, the
precision is lost, too bad, and casting back to int won't bring it back.I've kept it because knowing that both are ints enables not making a lot of
checks (done in math.h pow) so it's way faster. In my system it's 2-3ns vs
~40ns. I'm willing to settle for using just pow() if that makes it clearer.
Ok, performance is a good argument. I would not have thought that the
double performance would be so bad, but probably no miracle.
As of precision, there is another case where the int computation
overflows, so that the int result is stupid and the double version is a
better approximation. Now that can be controled by providing double or int
arguments to the function, so for me it is ok.
Attached the updated patch.
Ok.
I have marked it as ready to committer.
Basically for me this is an inferior version, not specially behaving that
better with respect to SQL, but if it eventually gets through a committer
maybe it is worth it.
--
Fabien.
On Fri, Dec 22, 2017 at 12:46 AM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:
If a double is always returned, I'm wondering whether keeping the ipow
version makes much sense: In case of double loss of precision, the precision
is lost, too bad, and casting back to int won't bring it back.I've kept it because knowing that both are ints enables not making a lot of
checks (done in math.h pow) so it's way faster. In my system it's 2-3ns vs
~40ns. I'm willing to settle for using just pow() if that makes it clearer.
This version looks good to me, except that I wonder if we should try
to switch to the floating-point version if the integer version
would/does overflow.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hello Robert,
If a double is always returned, I'm wondering whether keeping the ipow
version makes much sense: In case of double loss of precision, the precision
is lost, too bad, and casting back to int won't bring it back.I've kept it because knowing that both are ints enables not making a lot of
checks (done in math.h pow) so it's way faster. In my system it's 2-3ns vs
~40ns. I'm willing to settle for using just pow() if that makes it clearer.This version looks good to me, except that I wonder if we should try to
switch to the floating-point version if the integer version would/does
overflow.
My 0.02€ is that it is under the user control who provides either ints or
doubles as arguments. So I do not think that we should bother, for what my
opinion is worth.
If this is a new requirement, detecting the integer overflow is probably
possible with some testing, eg unexpected changes of sign, but that would
probably add two tests per round, and probably double the computation
cost.
--
Fabien.
Hi,
I've implemented the overflow checks and made some benchmarks and the
ipow() version became slower except with some specific inputs (base 0 for
example). It's true that the new auxiliary functions could be optimized,
but I don't think it makes sense to keep working on them just to match
pow() speed.
I'm attaching both patches in case someone wants to have a look but I would
go with the simpler solution (pgbench_pow_v10.patch).
Regards,
--
*Raúl Marín Rodríguez *carto.com
Attachments:
pgbench_pow_v10.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v10.patchDownload
From 28bc71f657fa967bafc03c015e5e2405c427a711 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Thu, 21 Dec 2017 20:28:02 +0100
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 +++++++
src/bin/pgbench/exprparse.y | 6 ++++++
src/bin/pgbench/pgbench.c | 16 ++++++++++++++++
src/bin/pgbench/pgbench.h | 3 ++-
src/bin/pgbench/t/001_pgbench_with_server.pl | 22 +++++++++++++++++++++-
5 files changed, 52 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 4431fc3eb7..1519fe78ef 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1069,6 +1069,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>pi()</literal></entry>
<entry><literal>3.14159265358979323846</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function>, <function>power(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double</entry>
+ <entry>exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal>, <literal>power(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
<row>
<entry><literal><function>random(<replaceable>lb</replaceable>, <replaceable>ub</replaceable>)</function></literal></entry>
<entry>integer</entry>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 25d5ad48e5..74ffe5e7a7 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -194,6 +194,12 @@ static const struct
{
"random_zipfian", 3, PGBENCH_RANDOM_ZIPFIAN
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
+ {
+ "power", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 7ce6f607f5..5b444765dd 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -1850,6 +1850,22 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 83fee1ae74..0e92882a4c 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -76,7 +76,8 @@ typedef enum PgBenchFunction
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
PGBENCH_RANDOM_EXPONENTIAL,
- PGBENCH_RANDOM_ZIPFIAN
+ PGBENCH_RANDOM_ZIPFIAN,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index e3cdf28628..9cbeb2fc11 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -232,7 +232,17 @@ pgbench(
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
qr{command=21.: int 9223372036854775807\b},
- qr{command=23.: int [1-9]\b}, ],
+ qr{command=23.: int [1-9]\b},
+ qr{command=24.: double -27\b},
+ qr{command=25.: double 1024\b},
+ qr{command=26.: double 1\b},
+ qr{command=27.: double 1\b},
+ qr{command=28.: double -0.125\b},
+ qr{command=29.: double -0.125\b},
+ qr{command=30.: double -0.00032\b},
+ qr{command=31.: double 8.50705917302346e\+37\b},
+ qr{command=32.: double 1e\+30\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -264,6 +274,16 @@ pgbench(
\set i1 0
-- yet another integer function
\set id debug(random_zipfian(1, 9, 1.3))
+--- pow and power
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
+\set powernegd2 debug(power(-5.0,-5.0))
+\set powerov debug(pow(9223372036854775807, 2))
+\set powerov2 debug(pow(10,30))
} });
# backslash commands
--
2.15.1
pgbench_pow_v10_ipow_overflow.patchtext/x-patch; charset=US-ASCII; name=pgbench_pow_v10_ipow_overflow.patchDownload
From 6cb8fb1a3eaffe680b85913b0acf0068dcb0d2a7 Mon Sep 17 00:00:00 2001
From: Raul Marin <rmrodriguez@cartodb.com>
Date: Thu, 21 Dec 2017 20:28:02 +0100
Subject: [PATCH] Add pow() support to pgbench
---
doc/src/sgml/ref/pgbench.sgml | 7 ++
src/bin/pgbench/exprparse.y | 6 ++
src/bin/pgbench/pgbench.c | 100 +++++++++++++++++++++++++++
src/bin/pgbench/pgbench.h | 3 +-
src/bin/pgbench/t/001_pgbench_with_server.pl | 22 +++++-
5 files changed, 136 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 4431fc3eb7..1519fe78ef 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1069,6 +1069,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>pi()</literal></entry>
<entry><literal>3.14159265358979323846</literal></entry>
</row>
+ <row>
+ <entry><literal><function>pow(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function>, <function>power(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</function></literal></entry>
+ <entry>double</entry>
+ <entry>exponentiation</entry>
+ <entry><literal>pow(2.0, 10)</literal>, <literal>power(2.0, 10)</literal></entry>
+ <entry><literal>1024.0</literal></entry>
+ </row>
<row>
<entry><literal><function>random(<replaceable>lb</replaceable>, <replaceable>ub</replaceable>)</function></literal></entry>
<entry>integer</entry>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 25d5ad48e5..74ffe5e7a7 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -194,6 +194,12 @@ static const struct
{
"random_zipfian", 3, PGBENCH_RANDOM_ZIPFIAN
},
+ {
+ "pow", 2, PGBENCH_POW
+ },
+ {
+ "power", 2, PGBENCH_POW
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 7ce6f607f5..07436af5a7 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -911,6 +911,64 @@ getZipfianRand(TState *thread, int64 min, int64 max, double s)
: computeHarmonicZipfian(thread, n, s));
}
+/* Position of the most significant bit */
+static int64
+ffs_i64(int64 i)
+{
+ int64 bits = 0;
+
+ if (i < 0)
+ i = -i;
+ while (i)
+ {
+ i>>=1;
+ bits++;
+ }
+
+ return bits;
+}
+
+/* Heuristic to check for ipow overflow */
+static bool
+ipow_check_overflow(int64 base, int64 exp)
+{
+ int64 msb_base,
+ msb_base_sqr;
+
+ Assert(exp >= 0);
+ if (base == 0 || base == 1 || base == -1 || exp <= 1)
+ return false;
+
+ msb_base = ffs_i64(base);
+ if (msb_base >= 32)
+ return true;
+
+ msb_base_sqr = ffs_i64(base * base);
+ if (msb_base_sqr <= msb_base)
+ return true;
+
+ return (msb_base + (msb_base_sqr - msb_base) * (exp -1)) > 63;
+}
+
+/* Faster power function for integer values with exp >= 0 */
+static int64
+ipow(int64 base, int64 exp)
+{
+ int64 result = 1;
+
+ Assert(exp >= 0);
+
+ while (exp)
+ {
+ if (exp & 1)
+ result *= base;
+ exp >>= 1;
+ base *= base;
+ }
+
+ return result;
+}
+
/*
* Initialize the given SimpleStats struct to all zeroes
*/
@@ -1850,6 +1908,48 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_POW:
+ {
+ PgBenchValue *lval = &vargs[0];
+ PgBenchValue *rval = &vargs[1];
+
+ Assert(nargs == 2);
+
+ /*
+ * If both operands are int, exp >= 0 and the output
+ * fits in an int64 use the faster ipow(), else use pow()
+ */
+ if (lval->type == PGBT_INT &&
+ rval->type == PGBT_INT)
+ {
+
+ int64 li,
+ ri;
+
+ if (!coerceToInt(lval, &li) ||
+ !coerceToInt(rval, &ri))
+ return false;
+
+ if (ri >= 0 &&
+ !ipow_check_overflow(li,ri))
+ setDoubleValue(retval, ipow(li, ri));
+ else
+ setDoubleValue(retval, pow(li, ri));
+ }
+ else
+ {
+ double ld,
+ rd;
+
+ if (!coerceToDouble(lval, &ld) ||
+ !coerceToDouble(rval, &rd))
+ return false;
+
+ setDoubleValue(retval, pow(ld, rd));
+ }
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 83fee1ae74..0e92882a4c 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -76,7 +76,8 @@ typedef enum PgBenchFunction
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
PGBENCH_RANDOM_EXPONENTIAL,
- PGBENCH_RANDOM_ZIPFIAN
+ PGBENCH_RANDOM_ZIPFIAN,
+ PGBENCH_POW
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index e3cdf28628..9cbeb2fc11 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -232,7 +232,17 @@ pgbench(
qr{command=19.: double 19\b},
qr{command=20.: double 20\b},
qr{command=21.: int 9223372036854775807\b},
- qr{command=23.: int [1-9]\b}, ],
+ qr{command=23.: int [1-9]\b},
+ qr{command=24.: double -27\b},
+ qr{command=25.: double 1024\b},
+ qr{command=26.: double 1\b},
+ qr{command=27.: double 1\b},
+ qr{command=28.: double -0.125\b},
+ qr{command=29.: double -0.125\b},
+ qr{command=30.: double -0.00032\b},
+ qr{command=31.: double 8.50705917302346e\+37\b},
+ qr{command=32.: double 1e\+30\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
\set i1 debug(random(1, 100))
@@ -264,6 +274,16 @@ pgbench(
\set i1 0
-- yet another integer function
\set id debug(random_zipfian(1, 9, 1.3))
+--- pow and power
+\set poweri debug(pow(-3,3))
+\set powerd debug(pow(2.0,10))
+\set poweriz debug(pow(0,0))
+\set powerdz debug(pow(0.0,0.0))
+\set powernegi debug(pow(-2,-3))
+\set powernegd debug(pow(-2.0,-3.0))
+\set powernegd2 debug(power(-5.0,-5.0))
+\set powerov debug(pow(9223372036854775807, 2))
+\set powerov2 debug(pow(10,30))
} });
# backslash commands
--
2.15.1
On Tue, Dec 26, 2017 at 11:26:58PM +0100, Fabien COELHO wrote:
This version looks good to me, except that I wonder if we should try to
switch to the floating-point version if the integer version would/does
overflow.My 0.02€ is that it is under the user control who provides either ints or
doubles as arguments. So I do not think that we should bother, for what my
opinion is worth.If this is a new requirement, detecting the integer overflow is probably
possible with some testing, eg unexpected changes of sign, but that would
probably add two tests per round, and probably double the computation cost.
And my 2c on the matter is that switching silently from one version to
the other would be unwelcome. The user should be aware if a test is
overflowing a number when specifying an integer.
--
Michael
Bonjour Michaël,
And my 2c on the matter is that switching silently from one version to
the other would be unwelcome. The user should be aware if a test is
overflowing a number when specifying an integer.
This whole integer pow version is becoming unduly complicated and ugly.
For me, the rational of having the ipow implementation was to have a
precise integer result when possible. Now that it is casted to double and
the precision is lost, the whole point of ipow is moot, even if there is
some performance gain.
So given that committers do not want the int/double version because it is
slightly different from the numeric/double version of SQL (obviously), and
that the integer version is becoming over complicated with checks and
warnings or errors, I'm now in favor of just dropping it, and provide the
double version only.
Too bad for integer computations on keys which is the core of pgbench use
case, but I can't help it.
--
Fabien.
On Tue, Dec 26, 2017 at 4:45 PM, Raúl Marín Rodríguez
<rmrodriguez@carto.com> wrote:
I've implemented the overflow checks and made some benchmarks and the ipow()
version became slower except with some specific inputs (base 0 for example).
It's true that the new auxiliary functions could be optimized, but I don't
think it makes sense to keep working on them just to match pow() speed.I'm attaching both patches in case someone wants to have a look but I would
go with the simpler solution (pgbench_pow_v10.patch).
Committed the simpler solution after fixing it so that it compiles.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company