Transaction applied partially after failover in postgres

We noticed a data inconsistency in a 3-node asynchronous replication Postgres setup. A single JDBC transaction inserted/updated rows in two tables but we are noticing that one table did not have a few rows inserted. A few hours after the transaction the primary node went down and standby node became master.

Environment

• PostgreSQL 13.x (3 nodes, asynchronous streaming replication)
• JDBC (PostgreSQL driver)
• BoneCP connection pool
• connection.setAutoCommit(false)
• Isolation: REPEATABLE READ
• Explicit connection.commit() after multiple executeBatch() calls
• queryTimeout = 600s (long transaction)
• Application logs show no rollback, no exceptions
• Updates made in a transaction have the same value for column version
• Entries are written to postgres under a global lock
• No entries have been deleted from the table
• data‑checksums were not enabled

Code for inserting:

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    // 1. Insert into main table (table_a)
    // size of updated is around 400000
    if (!updated.isEmpty()) {
        try (PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO table_a (id, version, content) VALUES (?, ?, ?)")) {
            for (Storable s : updated.values()) {
                ps.setString(1, s.getId());
                ps.setLong(2, s.getVersion());
                ps.setBytes(3, s.toBytes());
                ps.addBatch();
            }
            int[] updates = ps.executeBatch();
            int updateCount = IntStream.of(updates).sum();
            if (updateCount != updated.size()) {
                throw new SQLException("Unexpected updateCount=" + updateCount);
            }
        }
    }

    // 2. Update index table (table_b)
    if (!updated.isEmpty()) {
        try (PreparedStatement ps = connection.prepareStatement(
                "UPDATE table_b SET id=?, name=?, version=? WHERE id=?")) {
            for (Storable s : updated.values()) {
                ps.setString(1, s.getId());
                ps.setString(2, s.getName());
                ps.setLong(3, s.getVersion());
                ps.setString(4, s.getId());
                ps.addBatch();
            }
            int[] updates = ps.executeBatch();
            int updateCount = IntStream.of(updates).sum();
            if (updateCount != updated.size()) {
                throw new SQLException("Unexpected updateCount=" + updateCount);
            }
        }
    }

    connection.commit(); // <- Commit succeeds without errors
} catch (SQLException e) {
   connection.rollback();
   ...
}

Observed Behavior

• The transaction completed successfully (no rollback or SQL exception).

• After a failover (a few hours later):

  • The new primary had the updated entries in table_b

  • But for a few IDs(which are present in table_b), table_a had no new rows for that version. None of the postgres nodes have these rows in table_a.

• For most IDs, both tables were updated correctly.

• The tables were first queried after failover, so it’s unclear if the inconsistency existed before failover or appeared later.

• PostgreSQL logs showed no errors during the transaction or failover.

Questions:

• Could this be caused by disk or memory corruption (given data_checksums were disabled)?
• Could it be caused due to an issue with async replication? (Logs for successful syncing of wal are present)
• What specific PostgreSQL or system logs should be checked to confirm whether the WAL for this transaction was fully written and applied?