Conversión de sintaxis y funciones comunes mediante el complemento sql_dialect de la base de datos

Vinculación de un complemento de dialecto

1. Asegúrese de que el complemento sql_dialect se haya instalado correctamente.
2. Inicie la ejecución automática del archivo sql_dialect_mysql.sql en el complemento y asegúrese de que las funciones integradas del complemento estén contenidas en el __mysql__ schema. Estas funciones del complemento se pueden utilizar directamente en la sesión actual.
3. Ejecute el siguiente comando para vincular el complemento de dialecto:

   1	ALTER DATABASE database_name SET sql_dialect='mysql';

4. Envíe un mensaje para indicar a otras sesiones en línea que utilicen preferentemente las funciones del __mysql__ schema, que tienen una prioridad más alta que las de pg_catalog.

   1	SELECT pg_reload_conf();

5. Utilice el catálogo del sistema PG_EXTENSION para consultar el complemento sql_dialect instalado automáticamente.

   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. Utilice el catálogo del sistema PG_NAMESPACE para consultar el esquema creado automáticamente.

   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. Utilice el catálogo del sistema PG_PROC para consultar las funciones proporcionadas por el complemento de dialecto, que tienen una prioridad más alta que las funciones del kernel de la base de datos. Al utilizar las funciones proporcionadas por el complemento de dialecto, no es necesario especificar un esquema.

   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

Desinstalación de un complemento de dialecto

Utilice DROP EXTENSION para desinstalar el complemento de dialecto de la base de datos actual.

1

DROP EXTENSION sql_dialect;

Actualización o degradación de un complemento de dialecto

1. Verifique la versión de la extensión admitida por la base de datos actual.

   1	SELECT * FROM pg_extension;

2. Actualice la extensión a la versión más reciente.

   1	ALTER EXTENSION extension_name UPDATE;

3. Actualice la extensión a una versión especificada.

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

Atributos de funciones

Para obtener más información sobre los atributos de las funciones y si las funciones se pueden enviar, consulte CREATE FUNCTION.

Mejora del rendimiento mediante optimización en línea

La optimización en línea es una función del optimizador de consultas de la base de datos y es similar a la capacidad de línea en C++. Si una función de cálculo o conversión corta cumple las condiciones de optimización en línea, el optimizador reemplaza la invocación de la función con una ejecución de expresión. Esta técnica evita la sobrecarga adicional de las invocaciones de funciones y mejora significativamente el rendimiento de la ejecución.

Ejemplo de optimización en línea:

1
2
3

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

Esta función realiza una operación de suma y cumple las condiciones de optimización en línea. El optimizador lo reemplaza con la expresión "$1 + $2".

Como se muestra en el plan de ejecución, el campo Output es la expresión optimizada (a + b) en lugar de una invocación de función.

 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)

Optimización para parámetros de entrada que contienen NULL

Las funciones cuyos parámetros de entrada contienen NULL devuelven NULL. El atributo STRICT debe definirse explícitamente para dichas funciones para que se pueda reducir la ejecución del cuerpo de la función cuando los parámetros de entrada contengan NULL.

Selección del tipo de función

Para un mejor rendimiento y estabilidad, elija el tipo de función en el siguiente orden: función SQL, función C y función PL/pgSQL.

Optimización de la configuración de variables GUC dentro de las funciones

Evite configurar variables de entorno dentro de las funciones para evitar sobrecargas innecesarias de comunicación.

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;

Optimización de funciones SQL de VOLATILE

Las funciones SQL con el atributo VOLATILE acceden con frecuencia al GTM, lo que genera un rendimiento deficiente y una alta presión sobre el GTM. Evite definir el atributo VOLATILE para las funciones SQL.

Otras notas

• Tipos de datos

  Los tipos de parámetros de función y los valores de retorno deben verificarse estrictamente para evitar excepciones de clúster o errores de conjunto de resultados causados por la conversión de tipos. Estos son algunos ejemplos:

  • Se debería devolver timestamptz, pero se devuelve text. Los resultados parecen consistentes, pero no se pueden utilizar para la conversión de zonas horarias o el cálculo de la hora.
  • Se debería devolver timestamptz, pero se devuelve timestamp. Como resultado, se pierde la información de la zona horaria, lo que provoca una desviación en el cálculo de la hora.
  • La conversión forzosa de tipos incompatibles provoca errores en las invocaciones de la función C.
  • Durante la invocación de una función C de memoria, se requiere un parámetro timestamptz, pero se pasa un parámetro timestamp. Esto genera un error en la conversión de la zona horaria.
• Recarga de funciones

  Para los tipos que admiten la conversión implícita automática (como de text o timestamp a timestamptz), defina una función unificada para evitar la implementación redundante.

• Desarrollo de funciones

  Se recomienda utilizar funciones nativas de DWS para definir nuevas funciones. Esto se debe a que los conjuntos de resultados de las nuevas funciones definidas mediante funciones compatibles pueden ser inestables debido a problemas de compatibilidad, como actualizaciones de versiones.

• Cambio de función

  La cantidad y los tipos de parámetros de la función, una vez definidos, no se pueden cambiar. Esto evita errores en la lógica del servicio del usuario.

  También se prohíben los cambios incompatibles en el comportamiento de la función. Si tales cambios son necesarios, deben ser controlados por los parámetros GUC de la base de datos para evitar conflictos con las dependencias de la base de datos.

Ejemplo de desarrollo de funciones

Las nuevas funciones deben agregarse al archivo de dialecto correspondiente al directorio dialects y procesarse en el script de actualización.