mirror of
https://github.com/tendermint/tendermint.git
synced 2026-02-11 06:11:07 +00:00
7fe3e78a38
Update the schema and implementation of the Postgres event indexer to improve certain types of queries against the index. These changes address the use cases raised by #6843, and are partly inspired by the prototype schema in that issue. In the old schema, events were flattened, making it difficult to find all the events associated with a particular block or transaction. In addition, events with no key/value attributes were entirely lost, since entries were generated only for attributes. To address these issues, this new schema records blocks, transactions, events, and attributes in separate tables, and provides views that join these tables to give a more convenient query surface for block and transaction events. - All events for a given block can be queried from the `block_events` view. - All events for a given transaction can be queried from the `tx_events` view. - Multiple events for the same key can be indexed for both blocks and transactions. The tests have been reworked, but all of the existing test cases for the old schema still pass with the new implementation. Various other minor cleanups are included, ADR-065 is also updated to reflect the updated schema.
86 lines
3.2 KiB
SQL
86 lines
3.2 KiB
SQL
/*
|
|
This file defines the database schema for the PostgresQL ("psql") event sink
|
|
implementation in Tendermint. The operator must create a database and install
|
|
this schema before using the database to index events.
|
|
*/
|
|
|
|
-- The blocks table records metadata about each block.
|
|
-- The block record does not include its events or transactions (see tx_results).
|
|
CREATE TABLE blocks (
|
|
rowid BIGSERIAL PRIMARY KEY,
|
|
|
|
height BIGINT NOT NULL,
|
|
chain_id VARCHAR NOT NULL,
|
|
|
|
-- When this block header was logged into the sink, in UTC.
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
|
|
UNIQUE (height, chain_id)
|
|
);
|
|
|
|
-- Index blocks by height and chain, since we need to resolve block IDs when
|
|
-- indexing transaction records and transaction events.
|
|
CREATE INDEX idx_blocks_height_chain ON blocks(height, chain_id);
|
|
|
|
-- The tx_results table records metadata about transaction results. Note that
|
|
-- the events from a transaction are stored separately.
|
|
CREATE TABLE tx_results (
|
|
rowid BIGSERIAL PRIMARY KEY,
|
|
|
|
-- The block to which this transaction belongs.
|
|
block_id BIGINT NOT NULL REFERENCES blocks(rowid),
|
|
-- The sequential index of the transaction within the block.
|
|
index INTEGER NOT NULL,
|
|
-- When this result record was logged into the sink, in UTC.
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
-- The hex-encoded hash of the transaction.
|
|
tx_hash VARCHAR NOT NULL,
|
|
-- The protobuf wire encoding of the TxResult message.
|
|
tx_result BYTEA NOT NULL,
|
|
|
|
UNIQUE (block_id, index)
|
|
);
|
|
|
|
-- The events table records events. All events (both block and transaction) are
|
|
-- associated with a block ID; transaction events also have a transaction ID.
|
|
CREATE TABLE events (
|
|
rowid BIGSERIAL PRIMARY KEY,
|
|
|
|
-- The block and transaction this event belongs to.
|
|
-- If tx_id is NULL, this is a block event.
|
|
block_id BIGINT NOT NULL REFERENCES blocks(rowid),
|
|
tx_id BIGINT NULL REFERENCES tx_results(rowid),
|
|
|
|
-- The application-defined type label for the event.
|
|
type VARCHAR NOT NULL
|
|
);
|
|
|
|
-- The attributes table records event attributes.
|
|
CREATE TABLE attributes (
|
|
event_id BIGINT NOT NULL REFERENCES events(rowid),
|
|
key VARCHAR NOT NULL, -- bare key
|
|
composite_key VARCHAR NOT NULL, -- composed type.key
|
|
value VARCHAR NULL,
|
|
|
|
UNIQUE (event_id, key)
|
|
);
|
|
|
|
-- A joined view of events and their attributes. Events that do not have any
|
|
-- attributes are represented as a single row with empty key and value fields.
|
|
CREATE VIEW event_attributes AS
|
|
SELECT block_id, tx_id, type, key, composite_key, value
|
|
FROM events LEFT JOIN attributes ON (events.rowid = attributes.event_id);
|
|
|
|
-- A joined view of all block events (those having tx_id NULL).
|
|
CREATE VIEW block_events AS
|
|
SELECT blocks.rowid as block_id, height, chain_id, type, key, composite_key, value
|
|
FROM blocks JOIN event_attributes ON (blocks.rowid = event_attributes.block_id)
|
|
WHERE event_attributes.tx_id IS NULL;
|
|
|
|
-- A joined view of all transaction events.
|
|
CREATE VIEW tx_events AS
|
|
SELECT height, index, chain_id, type, key, composite_key, value, tx_results.created_at
|
|
FROM blocks JOIN tx_results ON (blocks.rowid = tx_results.block_id)
|
|
JOIN event_attributes ON (tx_results.rowid = event_attributes.tx_id)
|
|
WHERE event_attributes.tx_id IS NOT NULL;
|