Very helpful. Observing the code and the Hive metastore DEBUG level log :
1) Going to execute query <select count(*) from TXNS where txn_state = 'o'> 2019-10-10T11:50:00,121 DEBUG [pool-4-thread-6]: pool.ProxyConnection (ProxyConnection.java:close(235)) - HikariPool-3 - Executed rollback on connection org.postgresql.jdbc.PgConnection@40a18eb5 due to dirty commit state on close(). 1. dirty commit state on close() => Hive in the database connection function set dbConn.setAutoCommit(false); 3) AutoCommit = false => HikariCP treats a non-explicit commit when autocommit is false as an application error. Some other pools support configuring "commit-on-close", but HikariCP considers that risky, and a hack to support applications that were never properly written. The JDBC specification is explicitly silent on whether a Connection without auto commit should automatically commit or rollback. That is an implementation detail left up the the driver developers. HikariCP treats a non-explicit commit when autocommit is false as an application error. Some other pools support configuring "commit-on-close", but HikariCP considers that risky, and a hack to support applications that were never properly written. The JDBC specification is explicitly silent on whether a Connection without auto commit should automatically commit or rollback. That is an implementation detail left up the the driver developers. My conclusion, it is OK. Thanks Peter. From: Peter Vary <pv...@cloudera.com> Reply-To: "user@hive.apache.org" <user@hive.apache.org> Date: Thursday, 10 October 2019 04:37 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, 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://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_hive_blob_master_standalone-2Dmetastore_metastore-2Dserver_src_main_java_org_apache_hadoop_hive_metastore_txn_TxnHandler.java-23L5015&d=DwMFaQ&c=y5LGzd1hT50ruE_IlUH7x8VGgWz9W0tFVWT6rSvPUKA&r=DG-yyggEeDDj0vPKrcPwzAPjq3r7k5xcx-lyRJMIzdUcgrfcgsklQfuqQSHxihJ_&m=tXAZdtiFGJvbW03zw3QhvgKPuq3_8EeI4ES2uH_IXhQ&s=QvWPZDn89OKIgYEfBxa_orhYnydMEACebV9-lnpiGrQ&e=> 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://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_hive_blob_master_standalone-2Dmetastore_metastore-2Dserver_src_main_java_org_apache_hadoop_hive_metastore_txn_TxnHandler.java-23L5032&d=DwMFaQ&c=y5LGzd1hT50ruE_IlUH7x8VGgWz9W0tFVWT6rSvPUKA&r=DG-yyggEeDDj0vPKrcPwzAPjq3r7k5xcx-lyRJMIzdUcgrfcgsklQfuqQSHxihJ_&m=tXAZdtiFGJvbW03zw3QhvgKPuq3_8EeI4ES2uH_IXhQ&s=4qdxbG5IU485JPS1Dpz8oZs5Lk-t4z6XkGWbylrYgCo&e=> 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<mailto: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." 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."
