Unconfirmed transactions query returns some confirmed transactions

[elderapo]

The following graphq query:

query GetUnconfirmedTransactions {
  node(where: { internal_id: { _eq: 3 } }) {
    unconfirmed_transaction_count
    unconfirmed_transactions(order_by: { validated_at: desc }) {
      validated_at
      transaction {
        hash
      }
    }
  }
}

returned the following results:

{
  "data": {
    "node": [
      {
        "unconfirmed_transaction_count": "13",
        "unconfirmed_transactions": [
          {
            "validated_at": "2023-03-21T00:38:27.5",
            "transaction": {
              "hash": "\\x0691d550e69e6d426bec3fc6ceeacabba2144ea21108ec90f702f45d86d5f485"
            }
          },
          {
            "validated_at": "2023-03-21T00:38:23.736",
            "transaction": {
              "hash": "\\x7e867e99a299d363956f6548a2925585124f56d278b443972085279e34c47049"
            }
          },
          {
            "validated_at": "2023-03-21T00:38:17.905",
            "transaction": {
              "hash": "\\x77f75f419ac011ce583ce88c212288db87dfc051d50951e256fb4b33a62e8359"
            }
          },
          {
            "validated_at": "2023-03-06T17:37:43.711",
            "transaction": {
              "hash": "\\x45f10a77d34650365bebc3b115ba4721a7a8f1d559563edc7d73282294c87faa"
            }
          },
          {
            "validated_at": "2023-03-06T17:24:04.098",
            "transaction": {
              "hash": "\\x9c00b3f6d7208b2dae69aafb2577baa5c563a90d3f985d189bdf3c299385c6a9"
            }
          },
          {
            "validated_at": "2023-03-01T11:24:09.349",
            "transaction": {
              "hash": "\\x1eede4e0bcb98b0433ead09faaeb2b6e46695a464f2e9e31187d4d49454b8e12"
            }
          },
          {
            "validated_at": "2023-03-01T11:24:07.293",
            "transaction": {
              "hash": "\\x663c60dde3a0e95f780a5afa6c6a1ebcfefff1d16cbafa7c391ee4f8d212fe06"
            }
          },
          {
            "validated_at": "2023-02-28T13:35:40.464",
            "transaction": {
              "hash": "\\x580f8e3ff5d048ecb306ff717377a51b4843d815b2a43c3efb0f6bb5ddfe9606"
            }
          },
          {
            "validated_at": "2023-02-28T13:34:12.278",
            "transaction": {
              "hash": "\\xbcc4f8e61c00ab0b5e0a6dee73cbdd9f7ac0962b2d97c4973deafc43bb63f129"
            }
          },
          {
            "validated_at": "2023-02-28T03:14:28.411",
            "transaction": {
              "hash": "\\x306a83ee418b4c175f2f798ca5d2d83ce885ac0a1dd45d22957cb286caf10c16"
            }
          },
          {
            "validated_at": "2023-02-08T22:58:17.565",
            "transaction": {
              "hash": "\\x52bc9ef0b974bb61b421e404104a1b7f4584c64fe20383c96ad9966b5d36fc07"
            }
          },
          {
            "validated_at": "2023-02-03T18:19:31.516",
            "transaction": {
              "hash": "\\xe9478c66cc6c890df73ed39a59feec823c9f3690cb9172f6bfa37e3fababdd0c"
            }
          },
          {
            "validated_at": "2023-01-29T05:10:09.224",
            "transaction": {
              "hash": "\\xa1543eb5fdf7b2247a0922b4fb9e5be31ddf7a9b63b4be2f0654cdab3d64acad"
            }
          }
        ]
      }
    ]
  }
}

Transaction 45f10a77d34650365bebc3b115ba4721a7a8f1d559563edc7d73282294c87faa and below are all confirmed already.

[bitjson]

Thanks for opening an issue! This is definitely a problem, I haven't tracked down exactly why it happens yet. I suspect there's a subtle bug in this trigger causing it to miss these confirmed transactions:

chaingraph/images/hasura/hasura-data/migrations/default/1616195337538_init/up.sql

Lines 286 to 322 in 37e226b

	CREATE OR REPLACE FUNCTION trigger_node_block_insert() RETURNS trigger
	LANGUAGE plpgsql
	AS $$
	BEGIN
	WITH accepted_transactions AS (
	SELECT node_internal_id, transaction_internal_id, accepted_at FROM block_transaction INNER JOIN new_table ON block_transaction.block_internal_id = new_table.block_internal_id
	),
	newly_spent AS (
	SELECT accepted_transactions.node_internal_id, accepted_transactions.transaction_internal_id, accepted_transactions.accepted_at, input.outpoint_transaction_hash, input.outpoint_index
	FROM input INNER JOIN accepted_transactions ON input.transaction_internal_id = accepted_transactions.transaction_internal_id
	),
	accepted_and_replaced_transactions AS (
	SELECT newly_spent.node_internal_id, input.transaction_internal_id, CASE WHEN input.transaction_internal_id != newly_spent.transaction_internal_id THEN newly_spent.accepted_at ELSE NULL END AS replaced_at
	FROM input INNER JOIN newly_spent ON input.outpoint_transaction_hash = newly_spent.outpoint_transaction_hash AND input.outpoint_index = newly_spent.outpoint_index
	WHERE input.outpoint_transaction_hash != '\x0000000000000000000000000000000000000000000000000000000000000000'::bytea
	-- GROUP BY newly_spent.node_internal_id, input.transaction_internal_id, replaced -- doesn't improve performance
	),
	deleted_node_transactions AS (
	DELETE FROM node_transaction
	USING accepted_and_replaced_transactions
	WHERE node_transaction.node_internal_id = accepted_and_replaced_transactions.node_internal_id
	AND node_transaction.transaction_internal_id = accepted_and_replaced_transactions.transaction_internal_id
	RETURNING node_transaction.node_internal_id, node_transaction.transaction_internal_id, validated_at, accepted_and_replaced_transactions.replaced_at
	)
	INSERT INTO node_transaction_history (node_internal_id, transaction_internal_id, validated_at, replaced_at)
	SELECT node_internal_id, transaction_internal_id, validated_at, replaced_at FROM deleted_node_transactions;
	RETURN NEW;
	END;
	$$;
	CREATE TRIGGER trigger_public_node_block_insert
	AFTER INSERT ON node_block
	REFERENCING NEW TABLE AS new_table
	FOR EACH STATEMENT EXECUTE FUNCTION trigger_node_block_insert();
	COMMENT ON TRIGGER trigger_public_node_block_insert ON node_block IS 'A trigger which removes previously-accepted transactions from node_transaction when nodes accept blocks containing transactions which would double-spend those previously-accepted transactions. This only occurs if a node intentionally replaces previously-heard transactions (e.g. replace-by-fee, replace-by-ZCE, manual replacements, etc.).';
	-- disabled until initial sync is complete (when mempool transactions begin to be accepted)
	ALTER TABLE node_block DISABLE TRIGGER trigger_public_node_block_insert;

I won't be able to come back to focus on this for a little while, but I'd love to merge a PR if you figure it out!