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> 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."
