Converting Common Syntax and Functions Using the Database sql_dialect Plug-in

Binding a Dialect Plug-in

1. Ensure that the sql_dialect plug-in has been correctly installed.
2. Start automatic execution of the sql_dialect_mysql.sql file in the plug-in and ensure that the built-in functions of the plug-in are contained in the __mysql__ schema. These plug-in functions can be directly used in the current session.
3. Run the following command to bind the dialect plug-in:

   1	ALTER DATABASE database_name SET sql_dialect='mysql';

4. Send a message to instruct other online sessions to preferentially use the functions in the __mysql__ schema, which have a higher priority than pg_catalog.

   1	SELECT pg_reload_conf();

5. Use the PG_EXTENSION system catalog to query the automatically installed sql_dialect plug-in.

   1 2 3 4 5

   SELECT * FROM pg_extension WHERE extname='sql_dialect'; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -------------------+----------+--------------+----------------+------------+-----------+-------------- sql_dialect | 10 | 11 | f | 1.0.0 | | (1 row)

6. Use the PG_NAMESPACE system catalog to query the automatically created schema.

   1 2 3 4 5

   SELECT * FROM pg_namespace WHERE nspname='__dialect_mysql__'; nspname | nspowner | nsptimeline | nspacl | permspace | usedspace | nsptype -------------------+----------+-------------+--------+-----------+-----------+--------- __dialect_mysql__ | 10 | 0 | | -1 | 0 | i (1 row)

7. Use the PG_PROC system catalog to query the functions provided by the dialect plug-in, which have a higher priority than the database kernel functions. When using the functions provided by the dialect plug-in, you do not need to specify a schema.

   1 2 3 4

   SELECT proname,nspname,prosrc FROM pg_proc,pg_namespace n WHERE pronamespace=n.oid and nspname='__dialect_mysql__'; proname | nspname | prosrc ---------------+-------------------+--------------------------------------------- rlike | __dialect_mysql__ | select $1 ~ $2

Uninstalling a Dialect Plug-in

Use DROP EXTENSION to uninstall the dialect plug-in of the current database.

1

DROP EXTENSION sql_dialect;

Upgrading or Downgrading a Dialect Plug-in

1. Check the extension version supported by the current database.

   1	SELECT * FROM pg_extension;

2. Upgrade the extension to the latest version.

   1	ALTER EXTENSION extension_name UPDATE;

3. Upgrade the extension to a specified version.

   1	ALTER EXTENSION extension_name UPDATE TO 'x.x.x';

Function Attributes

For details about function attributes and whether functions can be pushed down, see CREATE FUNCTION.

Performance Enhancement Through Inline Optimization

Inline optimization is a function of the database query optimizer and is similar to the inline capability in C++. If a short calculation or conversion function meets the inline optimization conditions, the optimizer replaces the function call with an expression execution. This technique avoids extra overhead of function calls and significantly improves execution performance.

Inline optimization example:

1
2
3

CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL IMMUTABLE;

This function performs an addition operation and meets the inline optimization conditions. The optimizer replaces it with the "$1 + $2" expression.

As shown in the execution plan, the Output field is the optimized expression (a + b) instead of a function call.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

EXPLAIN VERBOSE SELECT func_add_sql(a, b) FROM t1;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
  id |          operation           | E-rows | E-distinct | E-memory | E-width | E-costs 
 ----+------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER) |      1 |            |          |       8 | 9.01    
   2 |    ->  Seq Scan on public.t1 |      1 |            | 1MB      |       8 | 1.01    

      Targetlist Information (identified by plan id)     
 --------------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: ((a + b))
         Node/s: All datanodes (node_group, bucket:16384)
   2 --Seq Scan on public.t1
         Output: (a + b)

Optimization for Input Parameters Containing NULL

Functions whose input parameters contain NULL return NULL. The STRICT attribute must be explicitly defined for such functions so that the execution of the function body can be reduced when the input parameters contain NULL.

Function Type Selection

For better performance and stability, choose the function type in the following sequence: SQL function, C function, and PL/pgSQL function.

Optimizing GUC Variable Settings Within Functions

Avoid setting environment variables within functions to prevent unnecessary communication overhead.

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer
SET autoanalyze=off --This does not affect other CNs.
AS $$
BEGIN
SET autoanalyze=off; --This affects other CNs and incurs communication overhead.
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

VOLATILE SQL Function Optimization

SQL functions with the VOLATILE attribute frequently access the GTM, resulting in poor performance and high pressure on the GTM. Avoid defining the VOLATILE attribute for SQL functions.

Other Notes

• Data types

  The types of function parameters and return values must be strictly verified to prevent cluster exceptions or result set errors caused by type conversion. The following are some examples:

  • timestamptz should be returned, but text is returned. The results appear consistent, but they cannot be used for time zone conversion or time calculation.
  • timestamptz should be returned, but timestamp is returned. As a result, the time zone information is lost, causing deviation in time calculation.
  • Forcible conversion of incompatible types causes errors in C function calls.
  • During the call to a memory C function, a timestamptz parameter is required, but a timestamp parameter is passed. This results in an error in time zone conversion.
• Function reloading

  For types that support automatic implicit conversion (such as from text or timestamp to timestamptz), define a unified function to avoid redundant implementation.

• Function development

  You are advised to use native DWS functions to define new functions. This is because the result sets of new functions defined using compatible functions may be unstable due to compatibility issues such as version updates.

• Function change

  The number and types of function parameters, once defined, cannot be changed. This avoids errors in user service logic.

  Incompatible changes to function behavior are also prohibited. If such changes are necessary, they must be controlled by database GUC parameters to avoid conflicts with database dependencies.

Function Development Example

New functions must be added to the dialect file corresponding to the dialects directory and processed in the upgrade script.