Web3 acceptance test failed in testnet-citus

[xin-hedera]

Description

Some web3 acceptance tests failed in testnet-citus, potentially due to certain SQL query timing out in citus db.

Acceptance tests log:

 org.springframework.web.client.ResourceAccessException: I/O error on POST request for "http://mirror-web3/api/v1/contracts/call": Read timed out
	at org.springframework.web.client.DefaultRestClient$DefaultRequestBodyUriSpec.createResourceAccessException(DefaultRestClient.java:557)
	at org.springframework.web.client.DefaultRestClient$DefaultRequestBodyUriSpec.exchangeInternal(DefaultRestClient.java:482)
	at org.springframework.web.client.DefaultRestClient$DefaultRequestBodyUriSpec.retrieve(DefaultRestClient.java:444)
	at com.hedera.mirror.test.e2e.acceptance.client.MirrorNodeClient.lambda$callPostRestEndpoint$4(MirrorNodeClient.java:393)
	at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:335)
	at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:211)
	at com.hedera.mirror.test.e2e.acceptance.client.MirrorNodeClient.callPostRestEndpoint(MirrorNodeClient.java:392)
	at com.hedera.mirror.test.e2e.acceptance.client.MirrorNodeClient.contractsCall(MirrorNodeClient.java:270)
	at com.hedera.mirror.test.e2e.acceptance.steps.AbstractFeature.callContract(AbstractFeature.java:179)
	at com.hedera.mirror.test.e2e.acceptance.steps.AbstractFeature.callContract(AbstractFeature.java:175)
	at com.hedera.mirror.test.e2e.acceptance.steps.HistoricalFeature.getHistoricalDataForAllowance(HistoricalFeature.java:463)
	at ✽.I verify historical data for "FUNGIBLEHISTORICAL" is returned for allowance(classpath:features/contract/historical.feature:46)
Caused by: java.net.SocketTimeoutException: Read timed out
	at java.base/sun.nio.ch.NioSocketImpl.timedRead(Unknown Source)

web3 log:

2024-05-14T23:24:19.917Z  WARN tomcat-handler-35525 o.h.e.j.s.SqlExceptionHelper SQL Error: 0, SQLState: 57014 
2024-05-14T23:24:19.917Z ERROR tomcat-handler-35525 o.h.e.j.s.SqlExceptionHelper ERROR: canceling statement due to statement timeout 
2024-05-14T23:24:19.918Z  WARN tomcat-handler-35525 c.h.s.s.c.p.HTSPrecompiledContract Internal precompile failure org.springframework.dao.QueryTimeoutException: JDBC exception executing SQL [with token_allowances as (
    select *
    from
    (
        select *, row_number() over (
            partition by token_id, spender
            order by lower(timestamp_range) desc
        ) as row_number
        from
        (
            (
                select *
                from token_allowance
                where owner = ?
                    and lower(timestamp_range) <= ?
            )
            union all
            (
                select *
                from token_allowance_history
                where owner = ?
                    and lower(timestamp_range) <= ?
            )
        ) as all_token_allowances
    ) as grouped_token_allowances
    where row_number = 1 and amount_granted > 0
), transfers as (
    select tt.token_id, tt.payer_account_id, tt.consensus_timestamp, sum(tt.amount) as amount
    from token_transfer tt
        join token_allowances ta on tt.account_id = ta.owner
            and tt.payer_account_id = ta.spender
            and tt.token_id = ta.token_id
    where is_approval is true
        and consensus_timestamp <= ?
        and consensus_timestamp > lower(ta.timestamp_range)
    group by tt.token_id, tt.payer_account_id, tt.consensus_timestamp
), contract_results_filtered as (
    select sender_id, consensus_timestamp
    from contract_result cr
    where cr.consensus_timestamp <= ?
        and cr.consensus_timestamp in (
            select consensus_timestamp
            from token_transfer
        )
), contract_call_transfers as (
    select cr.sender_id, tt.consensus_timestamp, tt.token_id, sum(tt.amount) as amount
    from token_transfer tt
        join token_allowances ta on tt.account_id = ta.owner
            and tt.token_id = ta.token_id,
            contract_results_filtered cr
    where tt.is_approval is true
        and cr.sender_id = ta.spender
        and tt.consensus_timestamp = cr.consensus_timestamp
        and tt.consensus_timestamp <= ?
        and tt.consensus_timestamp > lower(ta.timestamp_range)
    group by cr.sender_id, tt.token_id, tt.consensus_timestamp
)
select *
from (
    select amount_granted, owner, payer_account_id, spender, timestamp_range, token_id, amount_granted + coalesce(
        (
            select sum(amount)
            from contract_call_transfers cct
            where cct.token_id = ta.token_id
                and cct.sender_id = ta.spender
        ),
        0)
        +  coalesce(
        (
            select sum(amount)
            from transfers tr
            where tr.token_id = ta.token_id
                and tr.payer_account_id = ta.spender
                and tr.consensus_timestamp not in (
                    select consensus_timestamp
                    from contract_call_transfers
                )
        ),
        0
    ) as amount
    from token_allowances ta
) result
where amount > 0
] [ERROR: canceling statement due to statement timeout] [n/a]; SQL [n/a]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy2/jdk.proxy2.$Proxy222.findByOwnerAndTimestamp(Unknown Source)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:216)
	at jdk.proxy2/jdk.proxy2.$Proxy222.findByOwnerAndTimestamp(Unknown Source)
	at com.hedera.mirror.web3.evm.store.accessor.AccountDatabaseAccessor.lambda$getFungibleTokenAllowances$14(AccountDatabaseAccessor.java:166)
	at java.base/java.util.Optional.map(Unknown Source)
	at com.hedera.mirror.web3.evm.store.accessor.AccountDatabaseAccessor.lambda$getFungibleTokenAllowances$17(AccountDatabaseAccessor.java:166)
	at com.hedera.mirror.web3.utils.Suppliers.lambda$memoize$0(Suppliers.java:48)
	at com.hedera.services.store.models.Account.getFungibleTokenAllowances(Account.java:682)
	at com.hedera.mirror.web3.evm.token.TokenAccessorImpl.staticAllowanceOf(TokenAccessorImpl.java:172)
	at com.hedera.services.store.contracts.precompile.impl.AllowancePrecompile.run(AllowancePrecompile.java:78)
	at com.hedera.services.store.contracts.precompile.HTSPrecompiledContract.computeInternal(HTSPrecompiledContract.java:252)
	at com.hedera.services.store.contracts.precompile.HTSPrecompiledContract.computePrecompile(HTSPrecompiledContract.java:200)
	at com.hedera.services.store.contracts.precompile.HTSPrecompiledContract.computeCosted(HTSPrecompiledContract.java:158)
	at com.hedera.mirror.web3.evm.contracts.execution.AbstractEvmMessageCallProcessor.calculatePrecompileGasAndOutput(AbstractEvmMessageCallProcessor.java:64)
	at com.hedera.mirror.web3.evm.contracts.execution.AbstractEvmMessageCallProcessor.executeHederaPrecompile(AbstractEvmMessageCallProcessor.java:50)
	at com.hedera.node.app.service.evm.contracts.execution.HederaEvmMessageCallProcessor.start(HederaEvmMessageCallProcessor.java:62)
	at org.hyperledger.besu.evm.processor.AbstractMessageProcessor.process(AbstractMessageProcessor.java:196)
	at com.hedera.node.app.service.evm.contracts.execution.HederaEvmTxProcessor.process(HederaEvmTxProcessor.java:203)
	at com.hedera.node.app.service.evm.contracts.execution.HederaEvmTxProcessor.execute(HederaEvmTxProcessor.java:148)
	at com.hedera.mirror.web3.evm.contracts.execution.MirrorEvmTxProcessorImpl.execute(MirrorEvmTxProcessorImpl.java:105)
	at com.hedera.mirror.web3.service.ContractCallService.doProcessCall(ContractCallService.java:147)
	at com.hedera.mirror.web3.service.ContractCallService.lambda$processCall$0(ContractCallService.java:98)
	at com.hedera.mirror.web3.common.ContractCallContext.lambda$run$0(ContractCallContext.java:55)
	at java.base/java.lang.ScopedValue$Carrier$CallableAdapter.call(Unknown Source)
	at java.base/jdk.internal.vm.ScopedValueContainer.callWithoutScope(Unknown Source)
	at java.base/jdk.internal.vm.ScopedValueContainer.call(Unknown Source)
	at java.base/java.lang.ScopedValue$Carrier.get(Unknown Source)
	at java.base/java.lang.ScopedValue.getWhere(Unknown Source)
	at com.hedera.mirror.web3.common.ContractCallContext.run(ContractCallContext.java:55)
2024-05-14T23:24:19.921Z  WARN tomcat-handler-35525 c.h.m.w.c.ContractController Mirror EVM transaction error: CONTRACT_REVERT_EXECUTED 
2024-05-14T23:24:19.921Z  INFO tomcat-handler-35525 c.h.m.w.c.LoggingFilter 10.4.3.32 POST /api/v1/contracts/call in 10152 ms: 400 

Steps to reproduce

check the description

Additional context

No response

Hedera network

testnet

Version

v0.104.0

Operating system

None