Soluciones a los resultados incoherentes de las consultas a GaussDB(DWS)

Los resultados de la función de ventana están incompletos

Escenario:

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

select * from t3 order by 1,2,3;
 a | b | c 
---+---+--- 
 1 | 2 | 1 
 1 | 2 | 2 
 1 | 2 | 3
(3 rows) 

select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; 
 c | rn 
---+---- 
1  |  1
(1 row) 
select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1;
 c | rn 
---+---- 
 3 |  1
(1 row)

Análisis:

Como se muestra anteriormente, ejecutar select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; dos veces, los resultados son diferentes. Esto se debe a que existen valores duplicados 1 y 2 en las columnas de ordenación a y b de la función ventana, mientras que sus valores en la columna c son diferentes. Como resultado, cuando el primer registro se obtiene basándose en el resultado de la clasificación en las columnas a y b los datos obtenidos en la columna c son aleatorios, como resultado, los conjuntos de resultados son incoherentes.

Solución:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

select c,rn from (select c,row_number() over(order by a,b,c) as rn from t3) where rn = 1;
 c | rn 
---+----
 1 |  1
(1 row)

select c,rn from (select c,row_number() over(order by a,b,c) as rn from t3) where rn = 1;
 c | rn 
---+----
 1 |  1
(1 row)

Uso de la ordenación en subvistas/subconsultas

Escenario

1
2
3
4
5
6

CREATE TBALE test(a serial ,b int);
INSERT INTO test(b) VALUES(1);
INSERT INTO test(b) SELECT b FROM test;
...
INSERT INTO test(b) SELECT b FROM test;
CREATE VIEW v as SELECT * FROM test ORDER BY a;

Problema de SQL:

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

select * from v limit 1;
 a | b
---+---
 3 | 1
(1 row)

select * from (select *from test order by a) limit 10;
 a  | b
----+---
 14 | 1
(1 row)

select * from test order by a limit 10;
 a | b
---+---
 1 | 1
(1 row)

Análisis:

ORDER BY no es válido para subconsultas y subconsultas.

Solución:

No se recomienda utilizar ORDER BY en subvistas y subconsultas. Para asegurarse de que los resultados están en orden, utilice ORDER BY en la consulta más externa.

LIMIT en subconsultas

Escenario: Cuando se utiliza LIMIT en una subconsulta, los dos resultados de la consulta son incoherentes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

select * from (select a from test limit 1 ) order by 1;
 a
---
 5
(1 row)

select * from (select a from test limit 1 ) order by 1;
 a
---
 1
(1 row)

Análisis:

El LIMIT en la subconsulta provoca que se obtengan resultados aleatorios.

Solución:

Para garantizar la estabilidad del resultado final de la consulta, no utilice LIMIT en subconsultas.

Using String_agg

Escenario: cuando se utiliza string_agg para consultar la tabla employee, los resultados de la consulta son inconsistentes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

select * from employee;
 empno | ename  |   job   | mgr  |      hiredate       |  sal  | comm | deptno 
-------+--------+---------+------+---------------------+-------+------+--------
  7654 | MARTIN | SALEMAN | 7698 | 2022-11-08 00:00:00 | 12000 | 1400 |     30
  7566 | JONES  | MANAGER | 7839 | 2022-11-08 00:00:00 | 32000 |    0 |     20
  7499 | ALLEN  | SALEMAN | 7698 | 2022-11-08 00:00:00 | 16000 |  300 |     30
(3 rows)

select count(*) from (select deptno, string_agg(ename, ',') from employee group by deptno) t1, (select deptno, string_agg(ename, ',') from employee group by deptno) t2 where t1.string_agg = t2.string_agg;
 count 
-------
     2
(1 row)

select count(*) from (select deptno, string_agg(ename, ',') from employee group by deptno) t1, (select deptno, string_agg(ename, ',') from employee group by deptno) t2 where t1.string_agg = t2.string_agg;
 count 
-------
     1
(1 row)

Análisis:

La función string_agg se utiliza para concatenar datos de un grupo en una fila. Sin embargo, si utiliza string_agg(ename, ',') debe especificarse el orden de los resultados concatenados. Por ejemplo, en la instrucción anterior select deptno, string_agg(ename, ',') from employee group by deptno;

Puede generar cualquiera de las siguientes opciones:

1

30 | ALLEN,MARTIN

O:

1

30 |MARTIN,ALLEN

En el escenario anterior, el resultado de la subconsulta t1 puede ser diferente de la subconsulta t2 cuando deptno es de 30.

Solución:

Agregue ORDER BY a String_agg para asegurarse de que los datos están concatenados en secuencia.

1

select count(*) from (select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t1 ,(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t2 where t1.string_agg = t2.string_agg;

Modo de compatibilidad de bases de datos

Scenario: Los resultados de la consulta de cadenas vacías en la base de datos son inconsistentes.

database1 (compatible con TD):

1
2
3
4
5

td=# select '' is null;
 isnull 
--------
 f
(1 row)

database 2 (compatible con ORA):

1
2
3
4
5

ora=# select '' is null;
 isnull 
--------
 t
(1 row)

Análisis:

Los resultados de la consulta de cadenas vacías son diferentes porque la sintaxis de la cadena vacía es diferente de la de la cadena nula en diferentes compatibilidades de base de datos.

Actualmente, GaussDB(DWS) soporta tres tipos de compatibilidad de bases de datos: Oracle, TD y MySQL. La sintaxis y el comportamiento varían dependiendo de la compatibilidad. Para obtener detalles sobre las diferencias de compatibilidad, consulte Diferencias de compatibilidad de sintaxis entre Oracle, Teradata y MySQL.

Las bases de datos en diferentes modos de compatibilidad tienen diferentes problemas de compatibilidad. Puede ejecutar select datname, datcompatibility from pg_database; para comprobar la compatibilidad de la base de datos.

Solución:

El problema se resuelve cuando los modos de compatibilidad de las bases de datos en los dos entornos se establecen en el mismo. El atributo DBCOMPATIBILITY de una base de datos no es compatible con ALTER. Solo se puede especificar el mismo atributo DBCOMPATIBILITY al crear una base de datos.

El elemento de configuración behavior_compat_options para comportamientos de compatibilidad de bases de datos se configura de manera inconsistente.

Escenario: Los resultados del cálculo de la función add_months son inconsistentes.

database1:

1
2
3
4
5

select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)

database2:

1
2
3
4
5

select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-31 00:00:00
(1 row)

Análisis:

Algunos comportamientos varían según el elemento behavior_compat_options de configuración de compatibilidad de bases de datos. Para obtener más información sobre las opciones de parámetros, consulte behavior_compat_options.

El end_month_calculate de behavior_compat_options controla la lógica de cálculo de la función add_months. Si se especifica este parámetro, y el Day de param1 indica el último día de un mes inferior a result el Day en el resultado del cálculo será igual al de result.

Solución:

El parámetro behavior_compat_options debe configurarse de forma coherente. Este parámetro es del tipo USERSET y se puede establecer en el nivel de sesión o modificar en el nivel de clúster.

Los atributos de la función definida por el usuario no están configurados correctamente.

Escenario: Cuando se invoca la función personalizada get_count() los resultados son inconsistentes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE FUNCTION get_count() returns int
SHIPPABLE
as $$
declare
    result int;
begin
result = (select count(*) from test); --test table is a hash table.
	return result;
end;
$$
language plpgsql;

Invocar a esta función.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT get_count();
 get_count 
-----------
      2106
(1 row)

SELECT get_count() FROM t_src;
 get_count 
-----------
      1032
(1 row)

Análisis:

Esta función especifica el atributo SHIPPABLE. Cuando se genera un plan, la función lo empuja hacia abajo a los DN para su ejecución. La tabla de prueba definida en la función es una tabla de hash. Por lo tanto, cada DN tiene solo parte de los datos en la tabla, el resultado devuelto por select count(*) from test; no es el resultado de los datos completos en la tabla de prueba. El resultado esperado cambia después de agregar from.

Solución:

Utilice cualquiera de los siguientes métodos (se recomienda el primer método):

1. Cambie la función para que no empujes hacia abajo: ALTER FUNCTION get_count() not shippable;
2. Cambie la tabla utilizada en la función a una tabla de replicación. De esta manera, los datos completos de la tabla se almacenan en cada DN. Incluso si el plan se empuja hacia abajo a los DN para su ejecución, el conjunto de resultados será el esperado.

Uso de la tabla sin registro

Escenario:

Una vez que se utiliza una tabla sin registro y se reinicia el clúster, el conjunto de resultados de consulta asociado es anormal y faltan algunos datos en la tabla sin registro.

Análisis:

Si max_query_retry_times se establece en 0 y la palabra clave UNLOGGED se especifica durante la creación de la tabla, la tabla creada será una tabla sin registro. Los datos escritos en tablas no registradas no se escriben en el log de escritura anticipada, lo que los hace considerablemente más rápidos que las tablas ordinarias. Sin embargo, una tabla no registrada se trunca automáticamente después de un bloqueo o un apagado sucio, incurriendo en riesgos de pérdida de datos. El contenido de una tabla no registrada tampoco se replica en los servidores en espera. Los índices creados en una tabla no registrada tampoco se registran automáticamente. Si el clúster se reinicia inesperadamente (reinicio del proceso, fallo del nodo o reinicio del clúster), algunos datos de la memoria no se vacían en los discos de manera oportuna y se pierden algunos datos, lo que provoca que el conjunto de resultados sea anormal.

Solución:

No se puede garantizar la seguridad de las tablas no registradas si el clúster falla. En la mayoría de los casos, las tablas no registradas solo se utilizan como tablas temporales. Si un clúster es defectuoso, debe reconstruir la tabla no registrada o hacer una copia de respaldo de los datos e importarlos a la base de datos de nuevo para asegurarse de que los datos son normales.