Hi Fernando, My guess is that this is the query: https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java#L5015 <https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java#L5015>
And this is the rollback: https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java#L5032 <https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java#L5032> It might worth to take a look at the MetaStore logs, to check if the corresponding log lines are printed or not. Thanks, Peter > On Oct 9, 2019, at 15:30, Antunes, Fernando De Souza > <fernando.antu...@arcelormittal.com.br> wrote: > > 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 <mailto:pv...@cloudera.com>> > Reply-To: "user@hive.apache.org <mailto:user@hive.apache.org>" > <user@hive.apache.org <mailto:user@hive.apache.org>> > Date: Wednesday, 9 October 2019 08:32 > To: "user@hive.apache.org <mailto:user@hive.apache.org>" > <user@hive.apache.org <mailto: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."
