Hi Peter, thanks for the support. Every select count(*) from TXNS where txn_state = 'o' runs fine if I run it from psql. No ROLLBACK happens after.
Maybe the result , I’m only seeing 0 (zero), trigger the ROLLBACK command from Hive program. (???) From: Peter Vary <pv...@cloudera.com> Reply-To: "user@hive.apache.org" <user@hive.apache.org> Date: Wednesday, 9 October 2019 08:32 To: "user@hive.apache.org" <user@hive.apache.org> Subject: Re: So many SQL ROLLBACK commands on the Hive PostgreSQL table **This Message originated from a Non-ArcelorMittal source** Hi Fernando, Checked the comapaction_queue related one, and that is definitely normal. Checked the txn related one, and that seems more interesting. I would try to run the query above against you HMS DB - my guess that is failing with some error. Peter On Oct 9, 2019, at 12:56, Antunes, Fernando De Souza <fernando.antu...@arcelormittal.com.br<mailto:fernando.antu...@arcelormittal.com.br>> wrote: Hi, I took some time to observe what Hive is doing with its PostgreSQL tables the figure out what is the impact in the case of PostgreSQL failure. In logs, something catches me up. There are many ROLLBACK commands for SELECT commands (sample below). Is it normal or something is wrong? I am using Hive 3.1.0 (Hortonworks HDP) and Postgresql 10.9 on CentOS 7.6. 5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:22.401 -03 [5262] LOG: execute <unnamed>: select cq_id, cq_database, cq_table, cq_partition, cq_type, cq_tblproperties from COMPACTION_QUEUE where cq_state = 'i' 2019-10-09 07:11:22.401 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:22.473 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:22.475 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:22.475 -03 [5262] LOG: execute <unnamed>: select cq_id, cq_database, cq_table, cq_partition, cq_type, cq_tblproperties from COMPACTION_QUEUE where cq_state = 'i' 2019-10-09 07:11:22.476 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:22.819 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:22.820 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:22.821 -03 [5262] LOG: execute <unnamed>: select count(*) from TXNS where txn_state = 'o' 2019-10-09 07:11:22.821 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:23.820 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:23.821 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:23.821 -03 [5262] LOG: execute <unnamed>: select count(*) from TXNS where txn_state = 'o' 2019-10-09 07:11:23.822 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:24.825 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:24.826 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:24.826 -03 [5262] LOG: execute <unnamed>: select count(*) from TXNS where txn_state = 'o' 2019-10-09 07:11:24.827 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:25.820 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:25.821 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:25.822 -03 [5262] LOG: execute <unnamed>: select count(*) from TXNS where txn_state = 'o' 2019-10-09 07:11:25.822 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:26.820 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:26.821 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:26.821 -03 [5262] LOG: execute <unnamed>: select count(*) from TXNS where txn_state = 'o' 2019-10-09 07:11:26.822 -03 [5262] LOG: execute S_1: ROLLBACK 2019-10-09 07:11:27.403 -03 [5262] LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED 2019-10-09 07:11:27.403 -03 [5262] LOG: execute <unnamed>: BEGIN 2019-10-09 07:11:27.404 -03 [5262] LOG: execute <unnamed>: select cq_id, cq_database, cq_table, cq_partition, cq_type, cq_tblproperties from COMPACTION_QUEUE where cq_state = 'i' 2019-10-09 07:11:27.404 -03 [5262] LOG: execute S_1: ROLLBACK Fernando de Souza Antunes ArcelorMittal Sistemas Avenida Brasil, 1654 – Funcionários – 30140-004 - Belo Horizonte M + 55 31 98751-3841 e 3217 4357 | www.arcelormittal.com.br<http://www.arcelormittal.com.br/> AVISO LEGAL "As informações existentes nesta mensagem e nos arquivos anexados têm caráter confidencial e são para uso restrito. A utilização, divulgação, cópia ou distribuição desta mensagem, ou parte dela, por qualquer pessoa diferente do destinatário é proibida, sujeitando o infrator às sanções legais. Se esta mensagem foi recebida por engano, favor excluí-la e informar ao remetente pelo endereço eletrônico acima. Agradecemos sua cooperação." DISCLAIMER "This email and its attachments may contain privileged and/or confidential information. Use, disclosure, copying or distribution of this message, or part thereof, by anyone other than the intended recipient is strictly prohibited, and will submit the infractor to the legal sanctions. If you have received this email in error, please notify the sender by reply email and destroy all copies of this message. Thank you for your cooperation." AVISO LEGAL "As informações existentes nesta mensagem e nos arquivos anexados têm caráter confidencial e são para uso restrito. A utilização, divulgação, cópia ou distribuição desta mensagem, ou parte dela, por qualquer pessoa diferente do destinatário é proibida, sujeitando o infrator às sanções legais. Se esta mensagem foi recebida por engano, favor excluí-la e informar ao remetente pelo endereço eletrônico acima. Agradecemos sua cooperação." DISCLAIMER "This email and its attachments may contain privileged and/or confidential information. Use, disclosure, copying or distribution of this message, or part thereof, by anyone other than the intended recipient is strictly prohibited, and will submit the infractor to the legal sanctions. If you have received this email in error, please notify the sender by reply email and destroy all copies of this message. Thank you for your cooperation."
