Erro no Postgresql após atualizar o banco de dados

Question

Recentemente, atualizamos o POSTGRESQL para o 9.4.4 e, agora, quando meu aplicativo tenta executar uma consulta, estou recebendo esse erro.

LOGS:

2016-03-15 16:34:51 ERROR http-nio-8282-exec-2:FeedRestController:224 - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ SELECT ji.JOB_INSTANCE_ID, ji.JOB_NAME, ji.JOB_KEY, e.JOB_EXEC,  e.START_TIME, COALESCE(childJobs.END_TIME,e.END_TIME) as END_TIME, EXTRACT(EPOCH FROM (COALESCE(childJobs.END_TIME,COALESCE(e.END_TIME,NOW())) - e.START_Tas RUN_TIME, COALESCE(childJobs.STATUS,e.STATUS) as STATUS, COALESCE(childJobs.EXIT_CODE,e.EXIT_CODE) as EXIT_CODE, e.EXIT_MESSAGE, e.CREATE_TIME, e.LAST_UP.VERSION, e.JOB_CONFIGURATION_LOCATION, feed.STRING_VAL as FEED_NAME, 'FEED' as JOB_TYPE FROM BATCH_JOB_EXECUTION e INNER JOIN ( SELECT b.STRING_VAL , MAEXECUTION_ID) AS JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION a INNER JOIN BATCH_JOB_EXECUTION_PARAMS b on a.JOB_EXECUTION_ID = b.JOB_EXECUTION_ID AND b.KEY_NAME = 'feed' AND a.STATUS NOT IN('STOPPED','STARTING') AND a.EXITOT IN('STOPPED','NOOP') INNER JOIN BATCH_JOB_EXECUTION_PARAMS c on c.KEY_NAME='jobType' AND c.STRING_VAL = 'FEED' c.JOB_EXECUTION_ID = b.JOB_EXECUTION_ID GROUP BY b.STRING_VAL ) feed on feed.JOB_EXECUTION_ID = e.JOB_EXECUTION_ID inner join BATCH_JOCE ji on ji.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID LEFT JOIN ( SELECT MAX(e.END_TIME) as END_TIME ,e.STATUS, e.EXIT_CODE, p.STRING_VAL as PAREXECUTION_ID FROM BATCH_JOB_EXECUTION e INNER JOIN BATCH_JOB_INSTANCE ji on ji.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID INNER JOIN BATCH_JOB_EXECUTION_PARAMS p on p.JOB_EXECUTION_ID = e.JOB_EXECUTION_ID AND p.KEY_NAME = 'parentJobExecutionId' ROUP BY e.STATUS, e.EXIT_CODE, p.STRING_VAL ) childJobs on childJobs.PARENT_JOB_EXECUTION_ID = e.JOB_EXECUTION_ID ORDER BY e. JOB_EXECUTION_ID DESC ]; neeption is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bigint
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 1805

Versão que temos atualmente:

postgres@abc:~ $ psql postgres -c 'SELECT version();'
PostgreSQL 9.4.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.4.7    20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)

    

Answer 1

Seu comando SQL tem um erro de digitação: ... 'parentJobExecutionId' ROUP BY ... . Deve ser GROUP BY .

(isso foi particularmente difícil de ver porque exigia rolagem horizontal. No futuro, eu recomendo quebrar linhas longas para evitar barras de rolagem horizontais, usar \ no final de cada quebra de linha adicionada e indentar a linha a seguir para indicar que eles deveriam estar ligados à linha anterior).

Parei de procurar por identificar esse erro de digitação. Pode ou não haver outros. Verifique seu comando SQL com cuidado.