Support upserts

[iand]

Description

Currently all Visor models are inserted using ON CONFLICT DO NOTHING which discards the data if it conflicts with existing data. This is fine for appending new data but unsatisfactory when backfilling after changes to extraction logic or bug fixes. Visor should support upserting of data if a conflict is encountered. We may want to make this an optional flag for use when backfilling since in normal operation all writes are appends.

Acceptance criteria

• Visor can be run in a mode where rows are updated if their primary key already exists in the database

Where to begin

• Investigate go-pg support for upserting. If no built in support then change the OnConflict clause call in each model's persist method to update the non primary key columns.

[frrist]

From the looks of go-pg documentation: https://pkg.go.dev/github.com/go-pg/pg/v10#example-DB.Model-InsertOnConflictDoUpdate it doesn't have "easy" support for upsert.

For comparison, GORM allows upserts to be performed via: https://gorm.io/docs/create.html#Upsert-On-Conflict

[frrist]

As an example, will probably go with something like?

func (a *Actor) PersistWithTx(ctx context.Context, tx *pg.Tx, policy model.ConflictPolicy) error {
	ctx, span := global.Tracer("").Start(ctx, "Actor.PersistWithTx")
	defer span.End()

	q := tx.ModelContext(ctx, a)

	if policy == model.DoNothing {
		if _, err := q.OnConflict("do nothing").
			Insert(); err != nil {
			return err
		}
	} else if policy == model.Upset {
		if _, err := q.OnConflict("(state_root)").
			Set("code = ?code, head = ?head, balance = ?balance, nonce = ?nonce").
			Insert(); err != nil {
			return err
		}
	} else {
		return xerrors.Errorf("unknown conflict policy: %d", policy)
	}

	return nil
}

ConflictPolicy is just a constant we set when instantiating the database.