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:

https://github.com/bitauth/chaingraph/blob/37e226b1aa3bbf9d3c6dd25dea19a944cf254f8c/images/hasura/hasura-data/migrations/default/1616195337538_init/up.sql#L286-L322

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!