Add client-side SQL option to PostgreSQL TPROC-C

[shrimpy56]

Is your feature request related to a problem? Please describe. It is glad to see that MySQL/MariaDB now can enable client-side SQL execution without stored procedure. We want to use the feature on PostgreSQL.

Describe the solution you'd like This would be a similar change as MySQL/MariaDB. Please correct me if I am wrong. Is there any specific reason that PostgreSQL was not in the scope of the feature?

Describe alternatives you've considered N/A

Additional context N/A

[sm-shaw]

It's great to see interest in this feature and a good background to why this was added for MySQL/MariaDB is explained here: https://www.hammerdb.com/blog/uncategorized/why-you-should-benchmark-your-database-using-stored-procedures/

Essentially, there was a community discussion around client SQL vs stored procedures for MySQL/MariaDB (this has not been the case for commercial databases) and therefore this feature enables comparison and shows definitively that stored procedures are faster than client SQL. Of course looking at the official full disclosure reports on https://www.tpc.org/ shows that this is how the commercial databases have always been benchmarked (and stored procedures will be faster than client SQL for PostgreSQL as well).

Nevertheless, this feature is also very useful when we want to test accelerating networking features such as encryption or compression, as this is the way we can ensure higher levels of network traffic.

Also, some (not all) derived open source databases especially distributed ones may not support stored procedures with the trade-off of being distributed by having lower performance and therefore this could be useful for derived PostgreSQL databases as well in this scenario.

Therefore, this is a perfect opportunity for a community contribution to add this feature. If you wish to work on this, there is a template here for what was added for MySQL/MariaDB https://github.com/TPC-Council/HammerDB/pull/602/commits

[shrimpy56]

Hi Steve, thanks for pointing me the background of the feature!

I totally agree with the value of it and I am currently working on it, taking the template as a reference.

[JoshInnis]

What about Prepared Statements? Since Prepared Statements only parse, analyze, and rewrite a query once, it should be even faster.

[sm-shaw]

We already use prepared statements for PostgreSQL functions, but prepared statements are not supported for PostgreSQL stored procedures (so with the XML connect pool option you can't use stored procedures). However, it should be clear that stored procedures/functions are always faster than client SQL. https://www.hammerdb.com/blog/uncategorized/why-you-should-benchmark-your-database-using-stored-procedures/ So any interest in this option is to illustrate the difference in performance, as we do for MySQL/MariaDB or to implement a solution for testing PostgreSQL derived databases that do not support stored procedures.