Stellar postgres errors: canceling statement due to user request

Question

I am using Stellar (mainnet) and after latest update got errors in postgresql:

canceling statement due to user request
UTC [64] STATEMENT:  delete from history_assets where id IN (select id from (select id, (select 1 from history_trades where base_asset_id = hcb.id limit 1) as c0, (select 1 from history_trades where counter_asset_id = hcb.id limit 1) as c1, (select 1 from history_trades_60000 where base_asset_id = hcb.id limit 1) as c2, (select 1 from history_trades_60000 where counter_asset_id = hcb.id limit 1) as c3, 1 as cx from history_assets hcb where id >= 0 order by id limit 1000) as sub where c0 IS NULL and c1 IS NULL and c2 IS NULL and c3 IS NULL and 1=1);

Version before update: horizon-version: 2.19.0 core-version: 19.3.0

Current versions: "horizon_version": "2.21.0-d771e05ef417c7f12f06f25d2a8166aabc8af75a", "core_version": "stellar-core 19.3.0 (9ce6dc4e9889ce86083c0e3ba2e773e0ff2ced3a)"

Postgres: version: postgres (PostgreSQL) 11.12 (Debian 11.12-1.pgdg90+1), disk size: 4TB, used disk space: 3.2TB

It seems Stellar is working fine. Still I store 75 days of history and I am expecting Stellar to remove history automatically.

Errors spotted after Stellar lag becomes 0.

Answer 1

I have the same problem. It looks like the error started after catching up to the latest block. I'm worried about the load because the query is executed every 5-6 seconds.

Oct 7 05:30:59 ip-10-10-0-230 stellar-horizon[54272]: time="2022-10-07T05:30:59.335Z" level=warning msg="Error reaping lookup tables" err="error running query: delete from history_assets where id IN (select id from (select id, (select 1 from history_trades where base_asset_id = hcb.id limit 1) as c0, (select 1 from history_trades where counter_asset_id = hcb.id limit 1) as c1, (select 1 from history_trades_60000 where base_asset_id = hcb.id limit 1) as c2, (select 1 from history_trades_60000 where counter_asset_id = hcb.id limit 1) as c3, 1 as cx from history_assets hcb where id >= 0 order by id limit 1000) as sub where c0 IS NULL and c1 IS NULL and c2 IS NULL and c3 IS NULL and 1=1);: canceling statement due to lack of response within timeout period" pid=54272 service=ingest