pqxx:broken connection causing haf to crash

Dies when trying to write a block.

Looks like we either have a bad memory chip, we have a buggy extension, or is there is a bug in postgres:

2025-03-15 03:38:14 UTC [39354]: app=hive-mind-postgrest WARNING:  HIVE EXTENSION: The query was terminated due to a timeout being reached.
 2025-03-15 03:38:14 UTC [39354]: app=hive-mind-postgrest CONTEXT:  SQL statement "SELECT -- get_full_post_view_by_id
             hp.id,
             hp.community_id,
             hp.root_id,
             hp.parent_id,
             ha_a.name AS author,
             hp.active,
             hp.author_rewards,
             hp.author_id,
             hpd_p.permlink,
             hpd.title,
             hpd.body,
             hivemind_app.extract_img_url_from_json_metadata(hpd.json)::character varying(1024) as img_url,
             hcd.category,
             hp.category_id,
             hp.depth,
             hp.payout,
             hp.pending_payout,
             hp.payout_at,
             hp.last_payout_at,
             hp.cashout_time,
             hp.is_paidout,
             hp.children,
             0 AS votes,
             0 AS active_votes,
             hp.created_at,
             hp.updated_at,
             hp.vote_rshares AS rshares,
             hp.abs_rshares,
             hp.total_votes,
             hp.net_votes,
             hpd.json,
             ha_a.reputation AS author_rep,
             hp.is_hidden,
             ha_a.is_grayed,
             hp.total_vote_weight,
             ha_pp.name AS parent_author,
             ha_pp.id AS parent_author_id,
                 CASE hp.depth > 0
                     WHEN true THEN hpd_pp.permlink
                     ELSE hcd.category
                 END AS parent_permlink_or_category,
             hp.curator_payout_value,
             ha_rp.name AS root_author,
             hpd_rp.permlink AS root_permlink,
             rcd.category AS root_category,
             hp.max_accepted_payout,
             hp.percent_hbd,
             true AS allow_replies,
             hp.allow_votes,
             hp.allow_curation_rewards,
             hp.beneficiaries,
            concat('/', rcd.category, '/@', ha_rp.name, '/', hpd_rp.permlink,
                 CASE rp.id
                     WHEN hp.id THEN ''::text
                     ELSE concat('#@', ha_a.name, '/', hpd_p.permlink)
                 END) AS url,
             rpd.title AS root_title,
             hp.sc_trend,
             hp.sc_hot,
             hp.is_pinned,
             hp.is_muted,
             hp.is_nsfw,
             hp.is_valid,
             hr.title AS role_title,
             hr.role_id,
             hc.title AS community_title,
             hc.name AS community_name,
             hp.block_num,
             hp.muted_reasons,
             blacklist.source
            FROM hivemind_app.hive_posts hp
              -- post data (6 joins)
              JOIN hivemind_app.hive_accounts_view ha_a ON ha_a.id = hp.author_id
              JOIN hivemind_app.hive_category_data hcd ON hcd.id = hp.category_id
              JOIN hivemind_app.hive_permlink_data hpd_p ON hpd_p.id = hp.permlink_id
              LEFT JOIN hivemind_app.hive_communities hc ON hp.community_id = hc.id
              LEFT JOIN hivemind_app.hive_roles hr ON hp.author_id = hr.account_id AND hp.community_id = hr.community_id
              -- parent post data
              JOIN hivemind_app.hive_posts pp ON pp.id = hp.parent_id -- parent post (0 or 1 parent)
              JOIN hivemind_app.hive_accounts ha_pp ON ha_pp.id = pp.author_id
              JOIN hivemind_app.hive_permlink_data hpd_pp ON hpd_pp.id = pp.permlink_id
              -- root post data
              JOIN hivemind_app.hive_posts rp ON rp.id = hp.root_id -- root_post (0 or 1 root)
              JOIN hivemind_app.hive_accounts ha_rp ON ha_rp.id = rp.author_id
              JOIN hivemind_app.hive_permlink_data hpd_rp ON hpd_rp.id = rp.permlink_id
              JOIN hivemind_app.hive_category_data rcd ON rcd.id = rp.category_id
              JOIN hivemind_app.hive_post_data rpd ON rpd.id = rp.id
              LEFT JOIN hivemind_app.get_blacklisted_by_observer(_observer_id) blacklist ON (blacklist.blacklisted_id = hp.author_id)
              -- largest joined data
              JOIN hivemind_app.hive_post_data hpd ON hpd.id = hp.id
           WHERE hp.id = _id AND hp.counter_deleted = 0"
         PL/pgSQL function hivemind_app.get_full_post_view_by_id(integer,integer) line 3 at RETURN QUERY
         PL/pgSQL assignment "_result = (
             SELECT jsonb_agg (
             (
               CASE
                 WHEN _called_from_bridge_api THEN hivemind_postgrest_utilities.create_bridge_post_object(row, _truncate_body, (CASE WHEN row.author <> _account THEN ARRAY[_account] ELSE NULL END), False,   True)
                 ELSE hivemind_postgrest_utilities.create_condenser_post_object(row, _truncate_body, False)
               END
             )
             ) FROM (
              WITH blog AS MATERIALIZED -- get_account_posts_by_blog
               (
                 SELECT
                   hfc.post_id,
                   hfc.created_at,
                   hfc.account_id
                 FROM hivemind_app.hive_feed_cache hfc
                 WHERE hfc.account_id = _account_id         -- use hive_feed_cache_account_id_created_at_post_id_idx
                   AND ( _post_id = 0 OR hfc.created_at < _created_at
                                      OR (hfc.created_at = _created_at AND hfc.post_id < _post_id) )
                   AND ( NOT _called_from_bridge_api OR
                     NOT EXISTS (SELECT NULL FROM hivemind_app.live_posts_view hp1
                                 WHERE hp1.id = hfc.post_id AND hp1.community_id IS NOT NULL
                                 AND NOT EXISTS (SELECT NULL FROM hivemind_app.hive_reblogs hr WHERE hr.blogger_id = _account_id AND hr.post_id = hp1.id)
                                )
                   )
                 ORDER BY hfc.created_at DESC, hfc.post_id DESC
                 LIMIT _limit
               )
               SELECT
                 hp.id,
                 hp.author,
                 hp.parent_author,
                 hp.author_rep,
                 hp.root_title,
                 hp.beneficiaries,
                 hp.max_accepted_payout,
                 hp.percent_hbd,
                 hp.url,
                 hp.permlink,
                 hp.parent_permlink_or_category,
                 hp.title,
                 hp.body,
                 hp.category,
                 hp.depth,
                 hp.payout,
                 hp.pending_payout,
                 hp.payout_at,
                 hp.is_paidout,
                 hp.children,
                 hp.votes,
                 hp.created_at,
                 hp.updated_at,
                 hp.rshares,
                 hp.abs_rshares,
                 hp.json,
                 hp.is_hidden,
                 -- is grayed - if author is muted by observer, make post gray
                 hp.is_grayed OR _posts_should_be_grayed AS is_grayed,
                 hp.total_votes,
                 hp.sc_trend,
                 hp.role_title,
                 hp.community_title,
                 hp.role_id,
                 hp.is_pinned,
                 hp.curator_payout_value,
                 hp.is_muted,
                 hp.source AS blacklists,
                 hp.muted_reasons
               FROM blog,
               LATERAL hivemind_app.get_full_post_view_by_id(blog.post_id, _observer_id) hp
               ORDER BY blog.created_at DESC, blog.post_id DESC
               LIMIT _limit
             ) row
           )"
         PL/pgSQL function hivemind_postgrest_utilities.get_account_posts_by_blog(text,integer,integer,integer,integer,integer,boolean) line 19 at assignment
         PL/pgSQL function bridge_api_get_account_posts(jsonb) line 42 at RETURN
         PL/pgSQL function hivemind_postgrest_utilities.get_bridge_method(text,jsonb) line 15 at assignment
         PL/pgSQL function hivemind_postgrest_utilities.dispatch(text,text,jsonb) line 11 at assignment
         PL/pgSQL function home(json) line 55 at RETURN
 malloc(): unsorted double linked list corrupted

Crashed again shortly after a malloc error resulting from a timeout of a different function. Seems like there is a bug in postgres version we are using for timeout of queries. Or maybe we are using different timeout mechanism, need to check it more: 2025-03-15 18:10:31 UTC [263117]: app=hive-mind-postgrest WARNING: HIVE EXTENSION: The query was terminated due to a timeout being reached.

Current suspect is timeouts by query_supervisor

We updated to Postgres 17.4, but the problem persisted. Next we disabled query_supervisor.

With query_supervisor disabled, but with similar timeouts (both at 2s and 300ms) using set statement_timout, we've had no problem running for more than 48 hours, so it seems fairly certain the bug is in query_supervisor. Simple solution for now is to disable query_supervisor.

pqxx:broken connection causing haf to crash

Designs

Child items ...

Activity