Rewrite stored procedure example with SQL refactoring

Stored procedures are popular with monolithic databases, but with some of the NEWSQL flavors such as CockroachDB it is not a feature. Perhaps one day it will be. Many may argue that stored procedures and triggers which rely on a database procedural language are a database lock-in. One point of view is that databases should be used in ANSI SQL mode enabling portability for applications across any database technology so that organizations can adopt modern cloud native databases and not be held hostage to any one product. Moving on, this article will look at an alternative using SQL refactoring to help migrate away from procedural approach. Not to pick on any one vendor stored procedure language, I will use Cockroach Labs TPCC harness go routine for payments processing and refactor using a common table expression which would yield the same positive in-database processing of a transaction. This routine could easily have been a stored procedure.

Looking at the code for payment.go, we can see this is a great example of logic in a code / SQL flow interaction with a database.

https://github.com/cockroachdb/cockroach/blob/master/pkg/workload/tpcc/payment.go

Which is described as “

The Payment business transaction updates the customer’s balance and reflects the payment on the district and warehouse sales statistics. It represents a light-weight, read-write transaction with a high frequency of execution and stringent response time requirements to satisfy on-line users.

“

Looking at snippets of the code

https://github.com/cockroachdb/cockroach/blob/master/pkg/workload/tpcc/payment.go#L91

And

https://github.com/cockroachdb/cockroach/blob/master/pkg/workload/tpcc/payment.go#L152

the logic is easy to follow.

Here is the transaction from the harness before rewriting..

Reading through the code, I am able to rewrite the process as one CTE ~

passing in my parameters to a inline table and using that to pass the values to predicate portioin of DML operations.

WITH

vars AS ( SELECT 101.25 AS amount, 0 AS wid, 1 AS did, ‘BARPRIEING’ AS in_c_last, 40 AS cid),

upd_w AS ( UPDATE warehouse AS w SET w_ytd = w_ytd + v.amount FROM vars AS v WHERE w_id = v.wid RETURNING w_name, w_street_1, w_street_2, w_city, w_state, w_zip),

upd_d AS ( UPDATE district AS d SET d_ytd = d_ytd + v.amount FROM vars AS v WHERE (d_w_id = v.wid) AND (d_id = v.did) RETURNING d_name, d_street_1, d_street_2, d_city, d_state, d_zip),

sel_c AS ( SELECT c_id FROM customer, vars AS v WHERE ((c_w_id = v.wid) AND (c_d_id = v.did)) AND (c_last = v.in_c_last) ORDER BY c_first ASC),

upd_c AS ( UPDATE customer AS c SET ( c_balance, c_ytd_payment, c_payment_cnt, c_data) = ( c_balance — v.amount, c_ytd_payment + v.amount, c_payment_cnt + 1, CASE c_credit WHEN ‘BC’ THEN “left”( c_id::STRING || c_d_id::STRING || c_w_id::STRING || c_d_id:::INT8::STRING || c_w_id:::INT8::STRING || v.amount::STRING || c_data, 500) ELSE c_data END) FROM vars AS v WHERE c_w_id = v.wid AND c_d_id = v.did AND c_id = v.cid RETURNING c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, CASE c_credit WHEN ‘BC’ THEN “left”(c_data, 200) ELSE ‘’ END)

INSERT INTO history ( h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_amount, h_date, h_data) SELECT vars.cid, vars.did, vars.wid, vars.did, vars.wid, vars.amount, now(), upd_w.w_name || upd_d.d_name FROM vars, upd_w, upd_d

Resulting in the following implicit statement execution

So, its possible in some cases to move procedural logic to set based SQL and still enjoy some of the positive benefits of in-database processing. There will be cases where large convoluted code which will be complex to unravel will leave you stuck with the past product.