add modulo (%) operator to pgbench

This patch is pretty trivial.

Another slightly less trivial but more useful version.

The issue is that there are 3 definitions of modulo, two of which are fine
(Knuth floored division and Euclidian), and the last one much less useful.
Alas, C (%) & SQL (MOD) choose the bad definition:-( I really need any of
the other two. The attached patch adds all versions, with "%" and "mod"
consistent with their C and SQL unfortunate counterparts, and "fmod" and
"emod" the sane ones.

Add modulo operator to pgbench.

This is useful to compute a permutation for tests with non uniform
accesses (exponential or gaussian), so as to avoid trivial correlations
between neighbour keys.

--
Fabien.

On Mon, Aug 4, 2014 at 5:20 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

This patch is pretty trivial.

Another slightly less trivial but more useful version.

The issue is that there are 3 definitions of modulo, two of which are fine
(Knuth floored division and Euclidian), and the last one much less useful.
Alas, C (%) & SQL (MOD) choose the bad definition:-( I really need any of
the other two. The attached patch adds all versions, with "%" and "mod"
consistent with their C and SQL unfortunate counterparts, and "fmod" and
"emod" the sane ones.

Three different modulo operators seems like a lot for a language that
doesn't even have a real expression syntax, but I'll yield to whatever
the consensus is on this one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas wrote:

On Mon, Aug 4, 2014 at 5:20 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

This patch is pretty trivial.

Another slightly less trivial but more useful version.

The issue is that there are 3 definitions of modulo, two of which are fine
(Knuth floored division and Euclidian), and the last one much less useful.
Alas, C (%) & SQL (MOD) choose the bad definition:-( I really need any of
the other two. The attached patch adds all versions, with "%" and "mod"
consistent with their C and SQL unfortunate counterparts, and "fmod" and
"emod" the sane ones.

Three different modulo operators seems like a lot for a language that
doesn't even have a real expression syntax, but I'll yield to whatever
the consensus is on this one.

I wonder if it would be necessary to offer the division operator
semantics corresponding to whatever additional modulo operator we choose
to offer. That is, if we add emod, do we need "ediv" as well?

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Robert,

The issue is that there are 3 definitions of modulo, two of which are fine
(Knuth floored division and Euclidian), and the last one much less useful.
Alas, C (%) & SQL (MOD) choose the bad definition:-( I really need any of
the other two. The attached patch adds all versions, with "%" and "mod"
consistent with their C and SQL unfortunate counterparts, and "fmod" and
"emod" the sane ones.

Three different modulo operators seems like a lot for a language that
doesn't even have a real expression syntax, but I'll yield to whatever
the consensus is on this one.

I agree that it is overkill.

In fact there is a link: if there was a real expression syntax, the
remainder sign could be fixed afterwards, so the standard C/SQL version
would do. If it is not available, the modulo operator must be right.

If there is only one modulo added, I would rather have the Knuth version.
However I was afraid that someone would object if "%" does not return the
same result than the C/PostgreSQL versions (even if I think that nearly
nobody has a clue about what % returns when arguments are negative), hence
the 3 modulo version to counter this potential critic.

But I would prefer just one version with the Knuth (or Euclidian)
definitions.

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

I wonder if it would be necessary to offer the division operator
semantics corresponding to whatever additional modulo operator we choose
to offer. That is, if we add emod, do we need "ediv" as well?

I would make sense, however I do not need it, and I'm not sure of a use
case where it would be needed, so I do not think that it is "necessary".
If it happens to be, it could be added then quite easily.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Three different modulo operators seems like a lot for a language that
doesn't even have a real expression syntax, but I'll yield to whatever
the consensus is on this one.

Here is a third simpler patch which only implements the Knuth's modulo,
where the remainder has the same sign as the divisor.

I would prefer this version 3 (one simple modulo based on Knuth
definition) or if there is a problem version 2 (all 3 modulos). Version 1
which provides a modulo compatible with C & SQL is really useless to me.

--
Fabien.

On Tue, Aug 5, 2014 at 5:53 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Robert Haas wrote:

On Mon, Aug 4, 2014 at 5:20 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

This patch is pretty trivial.

Another slightly less trivial but more useful version.

The issue is that there are 3 definitions of modulo, two of which are fine
(Knuth floored division and Euclidian), and the last one much less useful.
Alas, C (%) & SQL (MOD) choose the bad definition:-( I really need any of
the other two. The attached patch adds all versions, with "%" and "mod"
consistent with their C and SQL unfortunate counterparts, and "fmod" and
"emod" the sane ones.

Three different modulo operators seems like a lot for a language that
doesn't even have a real expression syntax, but I'll yield to whatever
the consensus is on this one.

I wonder if it would be necessary to offer the division operator
semantics corresponding to whatever additional modulo operator we choose
to offer. That is, if we add emod, do we need "ediv" as well?

Maybe we ought to break down and support a real expression syntax.
Sounds like that would be better all around.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Maybe we ought to break down and support a real expression syntax.
Sounds like that would be better all around.

Adding operators is more or less orthogonal with providing a new
expression syntax. I'm not sure that there is currently a crying need for
it (a syntax). It would be a significant project. It would raise the
question "where to stop"... And I just really need a few more
functions/operators which can be fitted in the current implementation
quite easily.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2014-08-06 23:38 GMT+09:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Three different modulo operators seems like a lot for a language that

doesn't even have a real expression syntax, but I'll yield to whatever
the consensus is on this one.

Here is a third simpler patch which only implements the Knuth's modulo,
where the remainder has the same sign as the divisor.

I would prefer this version 3 (one simple modulo based on Knuth
definition) or if there is a problem version 2 (all 3 modulos). Version 1
which provides a modulo compatible with C & SQL is really useless to me.

I like version 3, it is simple and practical. And it's enough in pgbench.
If someone wants to use other implementation of modulo algorithm, he just
changes his source code.

Best regards,
--
Mitsumasa KONDO

Hi,

Here is the review result.

#1. Patch compatibility
Little bit hunk, but it can patch to latest master.

#2. Functionality
No problem.

#3. Documentation
I think modulo operator explanation should put at last at the doc line.
Because the others are more frequently used.

#4. Algorithm
You proposed three modulo algorithm, that are
1. general modulo, 2. floor modulo and 3. euclidian modulo.

They calculate different value when modulo2 or reminder is negative number.
Calculate examples are here,

1. general modulo (patch1)
5 % 3 = 2
5 % -3 = 1
-5 % 3 = -1

2. floor modulo (patch2, 3)
5 % 3 = 2
5 % -3 = -2
-5 % 3 = 2

3. euclidian modulo (patch2)
5 % 3 = 2
5 % -3 = 4
-5 % 3 = 2

That's all.

I think if we want to create equal possibility and inutuitive random
generator, we select floor modulo, as you see the upper example. It can
create contrapositive random number. 1 and 2 method cannot.

I think euclidian modulo doesn't need by a lot of people. If we add it,
many people will confuse, because they doesn't know the mathematic
algorithms.

So I like patch3 which is simple and practical.

If you agree or reply my comment, I will mark ready for commiter.

Best Regards,
--
Mitsumsasa KONDO

Hello Mutsumara-san,

#3. Documentation
I think modulo operator explanation should put at last at the doc line.
Because the others are more frequently used.

So I like patch3 which is simple and practical.

Ok.

If you agree or reply my comment, I will mark ready for commiter.

Please find attached v4, which is v3 plus an improved documentation
which is clearer about the sign of the remainder.

--
Fabien.

Hi Fabien-san,

Thank you for your fast work!

2014-09-08 23:08 GMT+09:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Mutsumara-san,

#3. Documentation

I think modulo operator explanation should put at last at the doc line.
Because the others are more frequently used.

So I like patch3 which is simple and practical.

Ok.

If you agree or reply my comment, I will mark ready for commiter.

Please find attached v4, which is v3 plus an improved documentation
which is clearer about the sign of the remainder.

The attached is seemed no problem. But I'd like to say about order of
explanation in five formulas.

Fix version is here. Please confirm, and I mark it for ready for commiter.

Best regards,
--
Mitsumasa KONDO

The attached is seemed no problem. But I'd like to say about order of
explanation in five formulas.

Fix version is here. Please confirm, and I mark it for ready for
commiter.

I'm ok with this 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

On Wed, Aug 6, 2014 at 3:22 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Maybe we ought to break down and support a real expression syntax.
Sounds like that would be better all around.

Adding operators is more or less orthogonal with providing a new expression
syntax. I'm not sure that there is currently a crying need for it (a
syntax). It would be a significant project. It would raise the question
"where to stop"... And I just really need a few more functions/operators
which can be fitted in the current implementation quite easily.

Writing a simple expression parser for pgbench using flex and bison
would not be an inordinately difficult problem. And it would let you
add abs() and ?: and thereby avoid the need to hard-code multiple
versions of the modulo operator in the patch. The fact that you're
agonizing about which modulo to add is a sign that the language is too
impoverished to let you do anything non-trivial. That's why C only
has one modulo operator: as the patch demonstrates, the differences
between the version can be patched over with a very short C
expression. If we had real expressions in pgbench, you could do the
same thing there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Robert,

Writing a simple expression parser for pgbench using flex and bison
would not be an inordinately difficult problem.

Sure. Note that there is not only the parsing but also the evaluating to
think of, which mean a data structure to represent the expressions which
would be more complex than the current one. Although it is not difficult
as such, it would mean breaking pgbench heavily, which would mean
more time and energy than I have available.

And it would let you add abs() and ?: and thereby avoid the need to
hard-code multiple versions of the modulo operator in the patch.

It would also mean to *implement* abs and ?: in the "execution" code to
compute the parsed expression.

The fact that you're agonizing about which modulo to add is a sign that
the language is too impoverished to let you do anything non-trivial.

I'm not agonizing about which modulo to use:-) I know I do not want the
C/SQL version which is never really useful if you have signed data. I
overlooked this detail when submitting the first patch, and produced a
stupid patch with all 3 possible modulos, before going to the sane
solution which is to use the floored division Knuth version. If I had
thought a bit, I would have sent v3 as v1 directly.

That's why C only has one modulo operator: as the patch demonstrates,
the differences between the version can be patched over with a very
short C expression. If we had real expressions in pgbench, you could do
the same thing there.

Sure. I agree that pgbench is limited and that real expressions would have
helped, but it is also quite useful and easy to extend "as is". Maybe the
"add an expression parser to pgbench" could be added in the postgresql
todo wiki?

--
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, Sep 9, 2014 at 11:07 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

The fact that you're agonizing about which modulo to add is a sign that
the language is too impoverished to let you do anything non-trivial.

I'm not agonizing about which modulo to use:-) I know I do not want the
C/SQL version which is never really useful if you have signed data. I
overlooked this detail when submitting the first patch, and produced a
stupid patch with all 3 possible modulos, before going to the sane solution
which is to use the floored division Knuth version. If I had thought a bit,
I would have sent v3 as v1 directly.

Sure, and I would have looked at that patch and complained that you
were implementing a modulo operator with different semantics than C.
And then we'd be right back where we are now.

The problem here isn't that I don't know what you want, or that what
you want is unreasonable. The problem is that we can't cater to every
slightly-different thing that somebody wants to do with pgbench. If
we try, we'll end up with a neverending jumble of features most of
which never get used by 1 or 2 people. Features for any part of
PostgreSQL need to be of reasonably general interest, not something
that is super-specialized to one particular set of needs. If I commit
what you're asking me to commit here, I think that's exactly what I'll
be doing, and I don't think that's a good idea.

In all seriousness, sometimes the right solution to these problems is
just to patch your own copy. I've done that with pgbench at least
once and with PostgreSQL in general more times than I can conveniently
count. I've learned a lot of useful things that way, but I can't
expect all of the debugging instrumentation and trial features that
I've created to get incorporated into the product. It's not
reasonable, and it's not worth the time it would take me to make the
code general and maintainable, so the code just gets dropped on the
floor. In a perfect world, that wouldn't happen, but in a perfect
world they'd pay me the same salary they pay Linus Torvalds.

In this particular instance, we got onto this topic in the first place
because of the Gaussian and exponential patches, and the desire to
have the "hot" portion of the keys distributed in some random way
through the data set rather than having everything be clustered at the
front. As you yourself said, the best case for this patch is to allow
a poor-man's approximation of that. Adding a weird non-standard
operator for a poor approximation of the feature we're really looking
for just doesn't feel like the right call. I recognize that you have
a different view, and if enough people agree with you, that argument
may win the day. But my opinion is that we are too far down in the
weeds. We should be trying to create features that will have general
appeal to pgbench users, not features that solve narrow problems for
individual developers.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Robert,

Sure, and I would have looked at that patch and complained that you
were implementing a modulo operator with different semantics than C.
And then we'd be right back where we are now.
[...]

Probably.

To be clear about my intent, which is a summary of what you already know:
I would like to be able to generate a reasonable non uniform throttled
workload with pgbench.

I do think such a feature is worth having for anybody who would like to do
something realistic with pgbench, and that it is in the "general
interest" of postgresql to have such features.

In particular, given the current state of abysmal performance under some
trivial load with pg, I really think that it is really worth having a
better tool, and I think that my effort with rate and progress helped to
put these hidden problems into the light, and I do hope that they are
going to be solved quickly.

Now if I submitted a big patch with all the necessary features in it,
someone would ask to break it into pieces. So they are submitted one by
one (progress, throttling, limit, modulo, ...).

Note I did not start with the non uniform stuff, but Mitsumasa-san sent a
gaussian distribution patch and I jumped onto the wagon to complement it
with an exponential distribution patch. I knew when doing it that is was
not enough, but as I said "one piece at a time", given the effort required
to pass simple patch.

What is still needed for the overall purpose is the ability to scatter the
distribution. This is really:

(1) a positive remainder modulo, which is the trivial patch under
discussion

(2) a fast but good quality for the purpose hash function
also a rather small patch, not submitted yet.

(3) maybe the '|' operator to do a TP*-like non-uniform load,
which is really periodic so I do not like it.
a trivial patch, not submitted yet.

If you do not want one of these pieces (1 & 2), basically the interest of
gaussian/exponential addition is much reduced, and this is just a half
baked effort aborted because you did not want what was required to make it
useful. Well, I can only disagree, but you are a committer and I'm not!

--
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 Wed, Sep 10, 2014 at 4:48 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Note I did not start with the non uniform stuff, but Mitsumasa-san sent a
gaussian distribution patch and I jumped onto the wagon to complement it
with an exponential distribution patch. I knew when doing it that is was not
enough, but as I said "one piece at a time", given the effort required to
pass simple patch.

What is still needed for the overall purpose is the ability to scatter the
distribution. This is really:

(1) a positive remainder modulo, which is the trivial patch under
discussion

(2) a fast but good quality for the purpose hash function
also a rather small patch, not submitted yet.

(3) maybe the '|' operator to do a TP*-like non-uniform load,
which is really periodic so I do not like it.
a trivial patch, not submitted yet.

If you do not want one of these pieces (1 & 2), basically the interest of
gaussian/exponential addition is much reduced, and this is just a half baked
effort aborted because you did not want what was required to make it useful.
Well, I can only disagree, but you are a committer and I'm not!

I am not objecting to the functionality; I'm objecting to bolting on
ad-hoc operators one at a time. I think an expression syntax would
let us do this in a much more scalable way. If I had time, I'd go do
that, but I don't. We could add abs(x) and hash(x) and it would all
be grand.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Robert,

I am not objecting to the functionality; I'm objecting to bolting on
ad-hoc operators one at a time. I think an expression syntax would
let us do this in a much more scalable way. If I had time, I'd go do
that, but I don't. We could add abs(x) and hash(x) and it would all
be grand.

Ok. I do agree that an expression syntax would be great!

However, that would not diminish nor change much the amount and kind of
code necessary to add an operator or a function: the parser would have to
be updated, and the expression structure, and the executor. Currently the
pgbench "parser" and expression are very limited, but they are also very
generic so that nothing is needed to add a new operator there, only the
execution code needs to be updated, and the update would be basically the
same (if this is this function or operator, actually do it...) if the
execution part of a real expression syntax would have to be updated.

So although I agree that a real expression syntax would be great "nice to
have", I do not think that it is valid objection to block this patch.

Moreover, upgrading pgbench to handle an actual expression syntax is not
so trivial, because for instance some parts of the text needs to be parsed
(set syntax) while others would need not to be pased (SQL commands), so
some juggling would be needed in the lexer, or maybe only call the parser
on some lines and not others... Everything is possible, but this one would
require some careful thinking.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers