Postgres: Appropriate Handling of Multiple Settlements in Single Block

[bh2smith]

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.

[nlordell]

Looks like this is actually happening in the while and found by our FE team.

Specifically these two transactions:

• https://barn.api.cow.fi/mainnet/api/v1/transactions/0xc2eb04b549d68c770062591e2561c5b1534ea665c6179d33a779c52376c9161d/orders
• https://barn.api.cow.fi/mainnet/api/v1/transactions/0x56c47505eddbbe1b1ad8ad61d3c8c2232d0c11811799b003e029a0074547ba15/orders

Are on the same block and return the same list of orders.

An alternative to the solution proposed above would be to:

• Add a tx_hash field to our trades table
• Add indexes on tx_hash on both our settlements and trades tables
• Modify the query to s.tx_hash = t.tx_hash

[bh2smith]

Seems like a reasonable alternative. The only reason we we didn't include it before is because it was considered redundant (which is no longer the case). I'm surprised the same order were included in two separate settlements... were they partially fillable orders?

It will be a bit of a task to populate all the existing records with their corresponding transaction hash, but at least we already have the values!

[nlordell]

I'm surprised the same order were included in two separate settlements... were they partially fillable orders?

Orders weren't included in different settlements, there were just two settlements on the same block which caused the query to return incorrect data (i.e. the same list of orders for two separate transactions).