Caracteres Estranhos em Tabelas do Coopcred:
Problema: Existência de registros de caracteres "Estranhos" ou "Inválidos" em tabelas do Coopcred;
Mensagem de Erro:
O que são caracteres estranhos: Todo e qualquer caractere diferente dos caracteres conhecidos do teclado e que causem erro quando uma aplicação lê uma tabela e não consegue interpretar este caractere. Quando um caractere não consegue ser interpretado, geralmente o erro exibido mostra o caractere em formato hexadecimal.
Exemplos de caracteres estranhos e sua equivalência em hexadecimal (para converter de hex para text ou vice-versa basta acessar o google e buscar os conversores online).
- Caractere: ć Hex: 0xc4 0x87;
- Caractere: þ Hex: 0xc3 0xbe;
- Caractere: Þ Hex: 0xc3 0x9e;
Solução: Buscar na(s) tabela(s), a(s) linha(s) e a(s) coluna(s) onde se encontra(m) o(s) caractere(s) utilizando o conceito de expressões regulares (REGEX).
Procedimento para Postgres versão 13
(se no ambiente do cliente não for esta versão de postgres, basta baixar o backup e restaurar em uma instalação em sua máquina pessoal)
1 - Conectado ao BD do cliente através do Dbeaver ou PGAdmin, abrir o bloco de comandos de execução sql e verificar a versão do postgres com o comando:
select version();2 - Criar a função a seguir na base de dados a ser “varrida”:
CREATE OR REPLACE FUNCTION public.encontra_tabelas(needle text, haystack_tables name[] DEFAULT '{}'::name[], haystack_schema name[] DEFAULT '{}'::name[])
RETURNS TABLE(schemaname text, tablename text, columnname text, rowctid text) LANGUAGE plpgsql
AS $function$ begin
FOR schemaname,tablename,columnname IN
SELECT c.table_schema,c.table_name,c.column_name FROM information_schema.columns c
JOIN information_schema.tables t ON
(t.table_name=c.table_name AND t.table_schema=c.table_schema) WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}') AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}') AND t.table_type in ('BASE TABLE', 'FOREIGN')
--and c.table_schema !~ '^(pg_|information_schema)' LOOP
FOR rowctid IN EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) ~ %L', schemaname,
tablename, columnname, needle
) loop
IF rowctid is not null THEN RETURN NEXT;
END IF;
end loop; END LOOP; END;
$function$
;3 - Executar a query a seguir, informando em "{public}", o esquema no qual será realizada a busca. No caso, será necessário executar a query duas vezes, sendo uma para o schema public e outra para o schema da cooperativa:
select * from encontra_tabelas('[^A-Za-zÀ-Úà-ú0-9çÇ\ \(\)\.\,\;\:\?\-
\\\/\_\*\`\´\n\r''ºª²\<\>\{\}\[\]°\&\$\%\#\"\"\=\@\+Üü\ \|\!]+','{}','{public}');4 - A expressão regular acima (que começa em [ e termina em +) diz para a consulta retornar todos os registros cujos quais existam caracteres diferentes do que os acima supracitados.
A saída do comando será mais ou menos assim:
Na imagem acima observa-se o campo denominado "rowctid". Este campo faz referência a um conceito da arquitetura do postgres na qual toda e qualquer tabela existente possui um identificador único chamado de "ctid" que nada mais é do que uma coluna oculta que existe em cada tabela e que guarda o identificador único temporário do registro como um ponteiro para o registro no BD. Ele é temporário pois ele muda toda vez que é realizado um update ou insert na tabela.
No caso do exemplo, retornaram 2 linhas com o mesmo id ou seja, apontando para o mesmo registro na tabela pessoa e nos campos nome e "nome_para_impressao" os quais ambos contém um registro de caractere inválido.
Munido deste "ctid" (identificador) iremos consultar a tabela pessoa do exemplo acima para pegar o id da chave primária do registro da tabela para realizarmos o update.
ATENÇÃO, poderíamos utilizar o "ctid" no update mas não o faremos pois sempre devemos supor o pior caso, ou seja, estando nós conectados ao banco de produção do cliente, não há garantia que no exato momento em que estamos realizando esta intervenção, não ocorrerá alguma alteração na tabela em questão pelo Coopcred pois os clientes estão utilizando o sistema.
Vamos dar um select nos campos retornados no resultado da query anterior:
select id, nome, nome_para_impressao from pessoa where ctid='(760,67)';Resultado:
Note que nos campos nome e "nome_para_impressao", consta um caractere estranho (um interrogação invertido). Este caractere provavelmente é o causador do erro de conversão de encoding mencionado na tela de erro.
5 - Iremos realizar um update utilizando a função replace para substituir o caractere estranho por outro caractere que no caso será o vazio, e utilizaremos no filtro where o "id primary key" da tabela pessoa:
begin;
update pessoa set nome=replace(nome, '¿', '') where id=469579;select id, nome, nome_para_impressao from pessoa where id=469579;7 - Se a alteração tiver sido realizada com sucesso, basta finalizar com o end:
end;IMPORTANTE LEMBRAR:
SEMPRE antes de qualquer intervenção manual em banco de dados postgres, utilizar o begin; primeiramente para evitar equívocos.
O que o begin faz: abre uma sessão e coloca a tabela a ser alterada em lock, o que significa que enquanto o begin não for fechado, nenhuma outra sessão de banco será capaz de alterar qualquer dado na tabela (para evitar inconsistências) e, além do mais, se caso ocorrer algum engano, é possível desfazer utilizando rollback;.