Soluções para resultados de consultas inconsistentes do GaussDB(DWS)

Os resultados da função de janela são classificados incompletamente

Cenário:

 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álise:

Como mostrado acima, execute sselect c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; duas vezes, os resultados são diferentes. Isso ocorre porque os valores duplicados 1 e 2 existem nas colunas de classificação a e b da função de janela, enquanto seus valores na coluna c são diferentes. Como resultado, quando o primeiro registro é obtido com base no resultado de classificação nas colunas a e b, os dados obtidos na coluna c são aleatórios, como resultado, os conjuntos de resultados são inconsistentes.

Solução:

1
2
3
4
5

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)

Usar a classificação em subvisões/subconsultas

Cenário

1
2
3
4
5
6

CREATE TABLE 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;

SQL de problema:

 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álise:

ORDER BY é inválida para subvisões e subconsultas.

Solução:

Não é aconselhável usar ORDER BY em subvisões e subconsultas. Para garantir que os resultados estejam em ordem, use ORDER BY na consulta mais externa.

LIMIT em subconsultas

Cenário: quando LIMIT é usada em uma subconsulta, os dois resultados da consulta são inconsistentes.

 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álise:

A LIMIT na subconsulta faz com que resultados aleatórios sejam obtidos.

Solução:

Para garantir a estabilidade do resultado final da consulta, não use LIMIT em subconsultas.

Usar String_agg

Cenário: quando string_agg é usada para consultar a tabela employee, os resultados da consulta são 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álise:

A função string_agg é usada para concatenar dados em um grupo em uma linha. No entanto, se você usar string_agg(ename, ','), a ordem dos resultados concatenados precisa ser especificada. Por exemplo, na instrução anterior, select deptno, string_agg(ename, ',') from employee group by deptno;

pode produzir uma das seguintes:

1

30 | ALLEN,MARTIN

Ou:

1

30 |MARTIN,ALLEN

No cenário anterior, o resultado da subconsulta t1 pode ser diferente da subconsulta t2 quando deptno é 30.

Solução:

Adicione ORDER BY a String_agg para garantir que os dados sejam concatenados em sequência.

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 compatibilidade de banco de dados

Cenário: os resultados da consulta de cadeias de caracteres vazias no banco de dados são inconsistentes.

banco de dados1 (compatível com TD):

1
2
3
4
5

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

banco de dados2 (compatível com ORA):

1
2
3
4
5

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

Análise:

Os resultados da consulta de cadeia de caracteres vazia são diferentes porque a sintaxe da cadeia de caracteres vazia é diferente da da cadeia de caracteres nula em compatibilidade de banco de dados diferente.

Atualmente, o GaussDB(DWS) suporta três tipos de compatibilidade de banco de dados: Oracle, TD e MySQL. A sintaxe e o comportamento variam dependendo da compatibilidade. Para obter detalhes sobre as diferenças de compatibilidade, consulte Diferenças de compatibilidade de sintaxe entre Oracle, Teradata e MySQL

Bancos de dados em diferentes modos de compatibilidade têm diferentes problemas de compatibilidade. Você pode executar select datname, datcompatibility from pg_database; para verificar a compatibilidade do banco de dados.

Solução:

O problema é resolvido quando os modos de compatibilidade dos bancos de dados nos dois ambientes são definidos para o mesmo. O atributo DBCOMPATIBILITY de um banco de dados não oferece suporte a ALTER. Você só pode especificar o mesmo atributo DBCOMPATIBILITY ao criar um banco de dados.

O item de configuração behavior_compat_options para comportamentos de compatibilidade de banco de dados é configurado de forma inconsistente.

Cenário: os resultados do cálculo da função add_months são inconsistentes.

banco de dados1:

1
2
3
4
5

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

banco de dados2:

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álise:

Alguns comportamentos variam de acordo com o item de configuração de compatibilidade de banco de dados behavior_compat_options. Para obter detalhes sobre as opções de parâmetro, consulte behavior_compat_options.

O end_month_calculate em behavior_compat_options controla a lógica de cálculo da função add_months. Se este parâmetro for especificado e o Day de param1 indicar o último dia de um mês menor que result, o Day no resultado do cálculo será igual ao result.

Solução:

O parâmetro behavior_compat_options deve ser configurado consistentemente. Este parâmetro é do tipo USERSET e pode ser definido no nível da sessão ou modificado no nível do cluster.

Os atributos da função definida pelo usuário não estão configurados corretamente.

Cenário: quando a função personalizada get_count() é invocada, os resultados são 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;

Chame essa função.

 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álise:

Esta função especifica o atributo SHIPPABLE. Quando um plano é gerado, a função o empurra para DNs para execução. A tabela de teste definida na função é uma tabela de hash. Portanto, cada DN tem apenas parte dos dados da tabela, o resultado retornado por select count(*) from test; não é o resultado de dados completos na tabela de teste. O resultado esperado muda depois que from é adicionado.

Solução:

Use um dos seguintes métodos (o primeiro método é recomendado):

1. Altere a função para não empurrar para baixo: ALTER FUNCTION get_count() not shippable;
2. Altere a tabela usada na função para uma tabela de replicação. Desta forma, os dados completos da tabela são armazenados em cada DN. Mesmo que o plano seja empurrado para DNs para execução, o conjunto de resultados será o esperado.

Usar a tabela não registrada

Cenário:

Depois que uma tabela não registrada é usada e o cluster é reiniciado, o conjunto de resultados de consulta associado é anormal e alguns dados estão ausentes na tabela não registrada.

Análise:

Se max_query_retry_times for definido como 0 e a palavra-chave UNLOGGED for especificada durante a criação da tabela, a tabela criada será uma tabela não registrada. Os dados gravados em tabelas não registradas não são gravados no log de gravação antecipada, o que os torna consideravelmente mais rápidos do que as tabelas comuns. No entanto, uma tabela não registrada é automaticamente truncada após uma falha ou desligamento impuro, incorrendo em riscos de perda de dados. O conteúdo de uma tabela não registrada também não é replicado para servidores em espera. Quaisquer índices criados em uma tabela não registrada também não são registrados automaticamente. Se o cluster for reiniciado inesperadamente (reinicialização do processo, falha do nó ou reinicialização do cluster), alguns dados na memória não serão liberados nos discos em tempo hábil e alguns dados serão perdidos, fazendo com que o conjunto de resultados seja anormal.

Solução:

A segurança das tabelas não registradas não pode ser assegurada se o cluster for defeituoso. Na maioria dos casos, as tabelas não registradas são usadas apenas como tabelas temporárias. Se um cluster estiver com defeito, você precisará reconstruir a tabela não registrada ou fazer backup dos dados e importá-los para o banco de dados novamente para garantir que os dados estejam normais.