Backend: Use postgres transactions for queries to guarantee consistency between tables

Use a transaction to store all the information related to one block in db: block, extrinsics, signed_extrinsics, events, account updates, etc
Modify block harvester to just fill the gaps in db and should not check that all blocks have the correct number of block, extrinsics and events (this is guarantee by the usage of a transaction)

node-pg doc: https://node-postgres.com/features/transactions

Async/await example:

const { Pool } = require('pg')
const pool = new Pool()
;(async () => {
  // note: we don't try/catch this because if connecting throws an exception
  // we don't need to dispose of the client (it will be undefined)
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
    const res = await client.query(queryText, ['brianc'])
    const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
    const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
    await client.query(insertPhotoText, insertPhotoValues)
    await client.query('COMMIT')
  } catch (e) {
    await client.query('ROLLBACK')
    throw e
  } finally {
    client.release()
  }
})().catch(e => console.error(e.stack))

xx-labs / xxstats

Backend: Use postgres transactions for queries to guarantee consistency between tables #9