Whether we retain the custom SQL, which applies only to the finance Cash contract, is still an open question. The generic vault function tryLockFungibleStatesForSpending
doesn’t have custom SQL and is portable, I believe. Which is more efficient depends on the amount of contention for states (coins) vs. how many states are needed to fulfil a spend (or rather, the variability of the number of states required. More testing of scenarios required!
From: Mike Hearn <mike@...>
We do have database testing infrastructure internally. This sort of thing boils down to lots of scripts and little tools that are very specific to our setup. It’s not product-ized at all.
The incompatibilities are things like obscure table name limitations, queries that “work” but trash performance if they aren’t configured right and so on. ANSI SQL is a very leaky abstraction. Our original hope was that databases could be plugged in by end users on demand, but enough things broke each time we tried it that we backed away from that. Now we do nightly regression and performance test runs against each supported database to flush out issues.
Rick is currently reworking coin selection. The current DB query is deadlock prone when run in parallel. We talked about not using the custom query anymore, but then again doing it in the DB does have some advantages. I’m not sure where we ended up with that – Rick?