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() }