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