Postgres: Appropriate Handling of Multiple Settlements in Single Block

[nlordell]

At this moment some of our queries are fragile to the fact that, technically, multiple settlements could happen in a single block. For example, the recent query for fetching orders by transaction hash (introduced in #1194) whose raw SQL query assumes block number is unique per settlement.

We have included a todo with a rough sketch of how to handle this which I will also paste here.

with target_block_number as (
    SELECT block_number from settlements where tx_hash = $1
),

with next_log_index as (
    SELECT log_index from settlements
    WHERE block_number > target_block_number
    ORDER BY block_number asc
    LIMIT 1
)

"SELECT ", ORDERS_SELECT,
"FROM ", ORDERS_FROM,
"JOIN trades t \
    ON t.order_uid = o.uid \
 JOIN settlements s \
    ON s.block_number = t.block_number \
 WHERE s.tx_hash = $1 "
 AND t.log_index BETWEEN s.log_index AND next_log_index,

Up until this moment, we do not yet have a single instance of multiple settlements in a single block (cf Settlements Table) so this issue is not of immediate concern. However, with the introduction of multiple solver accounts (#1010) we may see this sooner than expected.

Original issue gnosis/gp-v2-services#1214 by @bh2smith