github.com/aakash4dev/cometbft@v0.38.2/state/indexer/sink/psql/psql.go (about)

     1  // Package psql implements an event sink backed by a PostgreSQL database.
     2  package psql
     3  
     4  import (
     5  	"context"
     6  	"database/sql"
     7  	"errors"
     8  	"fmt"
     9  	"strings"
    10  	"time"
    11  
    12  	"github.com/cosmos/gogoproto/proto"
    13  
    14  	abci "github.com/aakash4dev/cometbft/abci/types"
    15  	"github.com/aakash4dev/cometbft/libs/pubsub/query"
    16  	"github.com/aakash4dev/cometbft/types"
    17  )
    18  
    19  const (
    20  	tableBlocks     = "blocks"
    21  	tableTxResults  = "tx_results"
    22  	tableEvents     = "events"
    23  	tableAttributes = "attributes"
    24  	driverName      = "postgres"
    25  )
    26  
    27  // EventSink is an indexer backend providing the tx/block index services.  This
    28  // implementation stores records in a PostgreSQL database using the schema
    29  // defined in state/indexer/sink/psql/schema.sql.
    30  type EventSink struct {
    31  	store   *sql.DB
    32  	chainID string
    33  }
    34  
    35  // NewEventSink constructs an event sink associated with the PostgreSQL
    36  // database specified by connStr. Events written to the sink are attributed to
    37  // the specified chainID.
    38  func NewEventSink(connStr, chainID string) (*EventSink, error) {
    39  	db, err := sql.Open(driverName, connStr)
    40  	if err != nil {
    41  		return nil, err
    42  	}
    43  
    44  	return &EventSink{
    45  		store:   db,
    46  		chainID: chainID,
    47  	}, nil
    48  }
    49  
    50  // DB returns the underlying Postgres connection used by the sink.
    51  // This is exported to support testing.
    52  func (es *EventSink) DB() *sql.DB { return es.store }
    53  
    54  // runInTransaction executes query in a fresh database transaction.
    55  // If query reports an error, the transaction is rolled back and the
    56  // error from query is reported to the caller.
    57  // Otherwise, the result of committing the transaction is returned.
    58  func runInTransaction(db *sql.DB, query func(*sql.Tx) error) error {
    59  	dbtx, err := db.Begin()
    60  	if err != nil {
    61  		return err
    62  	}
    63  	if err := query(dbtx); err != nil {
    64  		_ = dbtx.Rollback() // report the initial error, not the rollback
    65  		return err
    66  	}
    67  	return dbtx.Commit()
    68  }
    69  
    70  // queryWithID executes the specified SQL query with the given arguments,
    71  // expecting a single-row, single-column result containing an ID. If the query
    72  // succeeds, the ID from the result is returned.
    73  func queryWithID(tx *sql.Tx, query string, args ...interface{}) (uint32, error) {
    74  	var id uint32
    75  	if err := tx.QueryRow(query, args...).Scan(&id); err != nil {
    76  		return 0, err
    77  	}
    78  	return id, nil
    79  }
    80  
    81  // insertEvents inserts a slice of events and any indexed attributes of those
    82  // events into the database associated with dbtx.
    83  //
    84  // If txID > 0, the event is attributed to the transaction with that
    85  // ID; otherwise it is recorded as a block event.
    86  func insertEvents(dbtx *sql.Tx, blockID, txID uint32, evts []abci.Event) error {
    87  	// Populate the transaction ID field iff one is defined (> 0).
    88  	var txIDArg interface{}
    89  	if txID > 0 {
    90  		txIDArg = txID
    91  	}
    92  
    93  	// Add each event to the events table, and retrieve its row ID to use when
    94  	// adding any attributes the event provides.
    95  	for _, evt := range evts {
    96  		// Skip events with an empty type.
    97  		if evt.Type == "" {
    98  			continue
    99  		}
   100  
   101  		eid, err := queryWithID(dbtx, `
   102  INSERT INTO `+tableEvents+` (block_id, tx_id, type) VALUES ($1, $2, $3)
   103    RETURNING rowid;
   104  `, blockID, txIDArg, evt.Type)
   105  		if err != nil {
   106  			return err
   107  		}
   108  
   109  		// Add any attributes flagged for indexing.
   110  		for _, attr := range evt.Attributes {
   111  			if !attr.Index {
   112  				continue
   113  			}
   114  			compositeKey := evt.Type + "." + attr.Key
   115  			if _, err := dbtx.Exec(`
   116  INSERT INTO `+tableAttributes+` (event_id, key, composite_key, value)
   117    VALUES ($1, $2, $3, $4);
   118  `, eid, attr.Key, compositeKey, attr.Value); err != nil {
   119  				return err
   120  			}
   121  		}
   122  	}
   123  	return nil
   124  }
   125  
   126  // makeIndexedEvent constructs an event from the specified composite key and
   127  // value. If the key has the form "type.name", the event will have a single
   128  // attribute with that name and the value; otherwise the event will have only
   129  // a type and no attributes.
   130  func makeIndexedEvent(compositeKey, value string) abci.Event {
   131  	i := strings.Index(compositeKey, ".")
   132  	if i < 0 {
   133  		return abci.Event{Type: compositeKey}
   134  	}
   135  	return abci.Event{Type: compositeKey[:i], Attributes: []abci.EventAttribute{
   136  		{Key: compositeKey[i+1:], Value: value, Index: true},
   137  	}}
   138  }
   139  
   140  // IndexBlockEvents indexes the specified block header, part of the
   141  // indexer.EventSink interface.
   142  func (es *EventSink) IndexBlockEvents(h types.EventDataNewBlockEvents) error {
   143  	ts := time.Now().UTC()
   144  
   145  	return runInTransaction(es.store, func(dbtx *sql.Tx) error {
   146  		// Add the block to the blocks table and report back its row ID for use
   147  		// in indexing the events for the block.
   148  		blockID, err := queryWithID(dbtx, `
   149  INSERT INTO `+tableBlocks+` (height, chain_id, created_at)
   150    VALUES ($1, $2, $3)
   151    ON CONFLICT DO NOTHING
   152    RETURNING rowid;
   153  `, h.Height, es.chainID, ts)
   154  		if err == sql.ErrNoRows {
   155  			return nil // we already saw this block; quietly succeed
   156  		} else if err != nil {
   157  			return fmt.Errorf("indexing block header: %w", err)
   158  		}
   159  
   160  		// Insert the special block meta-event for height.
   161  		if err := insertEvents(dbtx, blockID, 0, []abci.Event{
   162  			makeIndexedEvent(types.BlockHeightKey, fmt.Sprint(h.Height)),
   163  		}); err != nil {
   164  			return fmt.Errorf("block meta-events: %w", err)
   165  		}
   166  		// Insert all the block events. Order is important here,
   167  		if err := insertEvents(dbtx, blockID, 0, h.Events); err != nil {
   168  			return fmt.Errorf("finalizeblock events: %w", err)
   169  		}
   170  		return nil
   171  	})
   172  }
   173  
   174  func (es *EventSink) IndexTxEvents(txrs []*abci.TxResult) error {
   175  	ts := time.Now().UTC()
   176  
   177  	for _, txr := range txrs {
   178  		// Encode the result message in protobuf wire format for indexing.
   179  		resultData, err := proto.Marshal(txr)
   180  		if err != nil {
   181  			return fmt.Errorf("marshaling tx_result: %w", err)
   182  		}
   183  
   184  		// Index the hash of the underlying transaction as a hex string.
   185  		txHash := fmt.Sprintf("%X", types.Tx(txr.Tx).Hash())
   186  
   187  		if err := runInTransaction(es.store, func(dbtx *sql.Tx) error {
   188  			// Find the block associated with this transaction. The block header
   189  			// must have been indexed prior to the transactions belonging to it.
   190  			blockID, err := queryWithID(dbtx, `
   191  SELECT rowid FROM `+tableBlocks+` WHERE height = $1 AND chain_id = $2;
   192  `, txr.Height, es.chainID)
   193  			if err != nil {
   194  				return fmt.Errorf("finding block ID: %w", err)
   195  			}
   196  
   197  			// Insert a record for this tx_result and capture its ID for indexing events.
   198  			txID, err := queryWithID(dbtx, `
   199  INSERT INTO `+tableTxResults+` (block_id, index, created_at, tx_hash, tx_result)
   200    VALUES ($1, $2, $3, $4, $5)
   201    ON CONFLICT DO NOTHING
   202    RETURNING rowid;
   203  `, blockID, txr.Index, ts, txHash, resultData)
   204  			if err == sql.ErrNoRows {
   205  				return nil // we already saw this transaction; quietly succeed
   206  			} else if err != nil {
   207  				return fmt.Errorf("indexing tx_result: %w", err)
   208  			}
   209  
   210  			// Insert the special transaction meta-events for hash and height.
   211  			if err := insertEvents(dbtx, blockID, txID, []abci.Event{
   212  				makeIndexedEvent(types.TxHashKey, txHash),
   213  				makeIndexedEvent(types.TxHeightKey, fmt.Sprint(txr.Height)),
   214  			}); err != nil {
   215  				return fmt.Errorf("indexing transaction meta-events: %w", err)
   216  			}
   217  			// Index any events packaged with the transaction.
   218  			if err := insertEvents(dbtx, blockID, txID, txr.Result.Events); err != nil {
   219  				return fmt.Errorf("indexing transaction events: %w", err)
   220  			}
   221  			return nil
   222  		}); err != nil {
   223  			return err
   224  		}
   225  	}
   226  	return nil
   227  }
   228  
   229  // SearchBlockEvents is not implemented by this sink, and reports an error for all queries.
   230  func (es *EventSink) SearchBlockEvents(_ context.Context, _ *query.Query) ([]int64, error) {
   231  	return nil, errors.New("block search is not supported via the postgres event sink")
   232  }
   233  
   234  // SearchTxEvents is not implemented by this sink, and reports an error for all queries.
   235  func (es *EventSink) SearchTxEvents(_ context.Context, _ *query.Query) ([]*abci.TxResult, error) {
   236  	return nil, errors.New("tx search is not supported via the postgres event sink")
   237  }
   238  
   239  // GetTxByHash is not implemented by this sink, and reports an error for all queries.
   240  func (es *EventSink) GetTxByHash(_ []byte) (*abci.TxResult, error) {
   241  	return nil, errors.New("getTxByHash is not supported via the postgres event sink")
   242  }
   243  
   244  // HasBlock is not implemented by this sink, and reports an error for all queries.
   245  func (es *EventSink) HasBlock(_ int64) (bool, error) {
   246  	return false, errors.New("hasBlock is not supported via the postgres event sink")
   247  }
   248  
   249  // Stop closes the underlying PostgreSQL database.
   250  func (es *EventSink) Stop() error { return es.store.Close() }