{"id":3473,"date":"2024-04-05T09:33:32","date_gmt":"2024-04-05T16:33:32","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/improved-debuggability-for-sql-user-defined-functions\/"},"modified":"2024-04-05T09:33:32","modified_gmt":"2024-04-05T16:33:32","slug":"improved-debuggability-for-sql-user-defined-functions","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/pt\/improved-debuggability-for-sql-user-defined-functions\/","title":{"rendered":"Improved Debuggability for SQL++ User-Defined Functions"},"content":{"rendered":"\n

User-defined functions (UDFs)<\/a> are a very useful feature supported in SQL++.\u00a0<\/span>Couchbase 7.6 introduces improvements that allow for more debuggability and visibility into UDF execution.<\/span><\/p>\n\n\n\n

This blog will explore two new features in Couchbase 7.6 in the world of UDFs.<\/span><\/p>\n\n\n\n

    \n
  1. Profiling for SQL++ statements executed in JavaScript UDFs<\/span><\/li>\n\n\n
  2. EXPLAIN FUNCTION to access query plans of SQL++ statements within UDFs<\/span><\/li>\n\n<\/ol>\n\n\n\n

    The examples in this post require the travel-sample dataset<\/a> to be installed.<\/span><\/p>\n\n\n\n

    Profiling SQL++ Executed in JavaScript UDFs<\/h2>\n\n\n\n

    Query profiling is a debuggability feature that SQL++ offers.<\/span><\/p>\n\n\n\n

    When profiling is enabled for a statement\u2019s execution, the result of the request includes a detailed execution tree with timing and metrics of each step of the statement\u2019s execution. In addition to the profiling information being returned in the results of the statement, it can also be accessed for the request in the system:active_requests<\/em> and system:completed_requests<\/em> system keyspaces.<\/span><\/p>\n\n\n\n

    Here is a post that delves deeper into request profiling<\/a>.<\/span><\/p>\n\n\n\n

    In Couchbase 7.0, profiling was included for subqueries, including profiling subqueries from Inline UDFs<\/em>.<\/span><\/p>\n\n\n\n

    However, with new features in Couchbase 7.6, profiling was extended to SQL++ statements within JavaScript UDFs<\/em>.<\/span><\/p>\n\n\n\n

    In earlier versions, to profile statements within a JavaScript UDF, the user would be required to open up the function\u2019s definition, individually run each statement within the UDF and collect their profiles. This additional step will no longer be needed in 7.6.0!<\/span><\/p>\n\n\n\n

    Now, when profiling is enabled, if the statement contains JavaScript UDF execution, profiles for all SQL++ statements executed in the UDF will also be collected. And this UDF related profiling information will be available in the request output, system:active_requests<\/em> and system:completed_requests<\/em> system keyspaces as well.<\/span><\/p>\n\n\n\n

    Example 1:<\/span><\/h3>\n\n\n\n

    Create a JavaScript UDF js1<\/em> in a global library lib1<\/em>\u00a0via the REST endpoint or via the UI.<\/span><\/p>\n\n\n

    [crayon nums=”false” lang=”js” decode=”true”]function js1() {<\/p>\n

    \u00a0\u00a0\u00a0\u00a0var query = SELECT * FROM default:`travel-sample`.inventory.airline LIMIT 1;<\/p>\n

    \u00a0\u00a0\u00a0\u00a0var res = [];
    \n\u00a0\u00a0\u00a0\u00a0for (const row of query) {
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0res.push(row);
    \n\u00a0\u00a0\u00a0}
    \n\u00a0\u00a0\u00a0\u00a0query.close()<\/p>\n

    \u00a0\u00a0\u00a0\u00a0return res;
    \n}[\/crayon]<\/p>\n\n\n\n

    Create the corresponding SQL++ function:<\/span><\/p>\n\n\n

    [crayon nums=”false” lang=”default” decode=”true”]CREATE FUNCTION js1() LANGUAGE JAVASCRIPT AS “js1” AT “lib1”;[\/crayon]<\/p>\n\n\n\n

    Execute the UDF with profiling enabled:<\/span><\/p>\n\n\n

    [crayon nums=”false” lang=”default” decode=”true”]EXECUTE FUNCTION js1();
    \n[\/crayon]<\/p>\n\n\n\n

    In the <\/span>profile<\/b> section of the returned response, the <\/span>executionTimings<\/b> subsection contains a field <\/span>~udfStatements<\/b>.<\/span><\/p>\n\n\n\n

    ~udfStatements<\/b>\u00a0is an array of profiling information that contains an entry for every SQL++ statement within the JavaScript UDF.<\/span><\/p>\n\n\n\n

    Every entry within the <\/span>~udfStatements<\/b> section contains:<\/span><\/p>\n\n\n\n