System Stored Procedures for PartiQL

[dlurton]

PartiQL needs a method to allow customers to quickly define and implement their own administrative commands. Examples of administrative commands include:

• Undeleting a database object that was previously dropped
• Forcing recalculation of table statistics
• Flushing an execution plan cache

We anticipate that PartiQL will need to support many such commands as existing PartiQL implementations continue to mature and new implementations come online. However, our current approach to support these implementation specific commands is not at all scalable because:

• Since these features require modifications to the grammar of PartiQL, approval of the Ion steering committee must be obtained, which does not often have a fast turn-around time.
• Support for the new grammar must be added to the parser, the AST, serialization, and test cases must be added to the parser’s test suite to ensure thorough testing. Even for simple features, this is not-trivial developer effort.
• Over time, these grammars will greatly add to the complexity and maintenance cost of the PartiQL parser.
• These features are often specific to the implementation and only make sense in the context of the implementation's back-end storage system. As such, these features should not be added to the main PartiQL reference implementation.
  • This greatly complicates source control branch management as these features must be manually removed when feature branches are merged back to GitHub’s master branch. Each new implementation specific feature further complicates this.

In the coming years, we expect that dozens or even hundreds of such implementation specific administrative commands will be needed as the number of PartiQL implementations increases.

A Scalable Approach to Adding Administrative Commands

An alternative to adding grammar to PartiQL for each of these features is to utilize common syntax for all of them, such as stored procedure invocation. SQL Server for example has hundreds of such procedures for everything from configuring database replication to sending e-mail. This proposal covers the invocation of stored procedures and intentionally excludes their definition.

Similar to ExprFunction of today, systems that implement PartiQL will be able to provide stored procedures to the interpreter which can be invoked with an SQL statement. The proposed grammar follows, with two variations: one variation allows for named arguments while the other does not:

<named-argument> ::= <symbol> = <expr>
<named-arg-list>::= <named-argument> [',' <named-argument>]...
<arg-list> ::= <expr> [',' <expr> ]...
<stored procedure call> ::= 'EXEC' <symbol> [ <named-arg-list> | <arg-list> ]

Examples

-- nameless arguments
EXEC deallocate_storage '<storage id>'
EXEC undrop_table '<unique_id>'
EXEC get_query_plan 'select * from foo where bar = 123'

-- named arguments
EXEC rebuild_index index_name = '<index name>'
EXEC add_user user_id = 'bob', groups = ['read_customers', 'invoice_create']

Differences between stored procedures and functions

• A stored procedure is allowed to have side-effects while functions are not.
• A stored procedure is only allowed at the top level of a query and cannot be used as an expression. i.e. this is illegal and will be rejected by the PartiQL parser: SELECT * FROM (EXEC undrop 'foo')

Pros of stored procedures

• Customers do not need to get approval from the steering committee to add their own stored procedures.
• Customers do not need to add support to the parser for new stored procedures.
• Customers do not need to involve the PartiQL team to add new stored procedures.
• Eliminates the need to remove these administrative commands during the merge to GitHub.
• Provides a simple syntax that is easy to understand due to its consistency across administrative commands.

Cons of stored procedures

• For complex administrative commands, specialized grammar may:
  • be easier to read and write than stored procedure invocation.
  • be easier to constrain the data types of arguments.

These cons are not major, considering that most stored procedures will likely require only a very small number of arguments. It will of course still be possible to create custom syntax for new administrative commands if the implementation team is willing to obtain the approval of the steering committee.