Cockroach: sql: support stored procedures

[knz]

CockroachDB needs stored procedures in the long term.
This is a placeholder issue to group action items:

• [x] support name scopes properly (work: planning)
• [x] support the Apply relational operator to be used by corr. subqueries, user-defined functions and proceudres (work: planning, execution)
• [x] support CTEs (work: planning)
• [ ] support query compilation (work: planning, execution)
• [x] advance the deployment of auto-generated IR code in CockroachDB (work: language, planning)
• [ ] research an encoding in KV for stored procedure descriptors, define versioning semantics and whether they are editable
• [ ] research and design an AST for stored procedures. Ancillary questions: common AST for multiple languages? API if languages are provided externally (Lua, others)? Compile this AST. (work: language, planning)

[knz]

user @camuel has written a PDF about why it makes sense from a PM perspective: https://u23433218.dl.dropboxusercontent.com/u/23433218/PSM4CRDB%20EARLY%20DAFT%20BY%20CAMUEL%20GILYADOV.pdf

[camuel]

Thanks @knz, I have updated the document and here is the second version: https://tinyurl.com/ybq4tvqw

I'm impatiently waiting for a feedback and further guidance and I'm interested in implementing this feature. I do have a few questions:

1. Is there any big decisions which are already made? Such as commitment to support SQL/PSM or perhaps a firm decision not to. This will immediately narrow the scope of work for me.
2. Regarding 'support query compilation' mentioned above, what is meant here? Compiling the query to vectorized native code with perhaps LLVM for performance considerations? Impala did this for example. Or something entirely different?
3. How could common AST exist for multiple languages? Well some languages are similar but some are pretty different. Doing a common AST for Lua, SQL/PSM and Python is not feasible, does it? Will appreciate some elaboration.
4. It seems a lot of work, how we will go about it? Depth first or breadth first. I mean one way is to implement minimal UDF right now and then UDAF and a bit more constructs of PSM language, then SP, then triggers and etc.. Breadth first is to initially research the whole topic, write a design, will be a small book easily, do some experiments here and there, and then carry out the implementation. Given multiple existing SQL/PSM implementations in the open, almost zero requirement risk, perhaps waterfall approach is not too crazy. Also, is this the only RFC or some other RFCs would be created for a smaller tasks?

Sorry for nooby questions and thanks again

[knz]

Hold your horses! We are welcoming input but it is just too soon. If you really want to help with this, please wait at least until January 2018.

If you and your team would like to help improve CockroachDB in the mean time, especially in areas that will enable later work on stored procedures, please let us know. In particular it would be great if you could get in touch with Nate Stewart [e-mail] to discuss your overall strategy, how and why you plan to contribute to CockroachDB, and see how well your plans/intents/interests match our roadmap. (Also we can take input from you!)

[tbg]

If we introduce stored procedures, we'll also have to change FmtAnonymize to strip at least user-defined function names.

[awoods187]

In Navicat

SELECT r.routine_schema, r.routine_name, string_agg(p.udt_name, ', ') AS object_info FROM information_schema.routines AS r LEFT JOIN information_schema.parameters AS p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema

I180409 21:52:10.217696 380 sql/conn_executor.go:1805 [n1,client=[::1]:56172,user=root] execution error: relation "information_schema.routines" does not exist

[fire]

Is there a status update on this?

[knz]

Yes: "not yet".