75 lines
2.1 KiB
PL/PgSQL
75 lines
2.1 KiB
PL/PgSQL
/**
|
|
* This plugin implementation for PostgreSQL requires the following tables
|
|
*/
|
|
|
|
CREATE TYPE "SlotStatus" AS ENUM (
|
|
'Rooted',
|
|
'Confirmed',
|
|
'Processed'
|
|
);
|
|
|
|
CREATE TABLE monitoring (
|
|
name TEXT PRIMARY KEY,
|
|
last_update TIMESTAMP WITH TIME ZONE,
|
|
last_slot_write TIMESTAMP WITH TIME ZONE,
|
|
last_account_write_write TIMESTAMP WITH TIME ZONE,
|
|
slot_queue BIGINT,
|
|
account_write_queue BIGINT
|
|
);
|
|
|
|
CREATE TABLE pubkey (
|
|
pubkey_id BIGSERIAL PRIMARY KEY,
|
|
pubkey VARCHAR(44) NOT NULL UNIQUE
|
|
);
|
|
|
|
-- Returns a pubkey_id for a pubkey, by getting it from the table or inserting it.
|
|
-- Getting this fully correct is complex, see:
|
|
-- https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324
|
|
-- and currently this function assumes there are no deletions in the pubkey table!
|
|
CREATE OR REPLACE FUNCTION map_pubkey(_pubkey varchar(44), OUT _pubkey_id bigint)
|
|
LANGUAGE plpgsql AS
|
|
$func$
|
|
BEGIN
|
|
LOOP
|
|
SELECT pubkey_id
|
|
FROM pubkey
|
|
WHERE pubkey = _pubkey
|
|
INTO _pubkey_id;
|
|
|
|
EXIT WHEN FOUND;
|
|
|
|
INSERT INTO pubkey AS t
|
|
(pubkey) VALUES (_pubkey)
|
|
ON CONFLICT (pubkey) DO NOTHING
|
|
RETURNING t.pubkey_id
|
|
INTO _pubkey_id;
|
|
|
|
EXIT WHEN FOUND;
|
|
END LOOP;
|
|
END
|
|
$func$;
|
|
|
|
-- The table storing account writes, keeping only the newest write_version per slot
|
|
CREATE TABLE account_write (
|
|
pubkey_id BIGINT NOT NULL REFERENCES pubkey,
|
|
slot BIGINT NOT NULL,
|
|
write_version BIGINT NOT NULL,
|
|
is_selected BOOL NOT NULL,
|
|
owner_id BIGINT REFERENCES pubkey,
|
|
lamports BIGINT NOT NULL,
|
|
executable BOOL NOT NULL,
|
|
rent_epoch BIGINT NOT NULL,
|
|
data BYTEA,
|
|
PRIMARY KEY (pubkey_id, slot, write_version)
|
|
);
|
|
CREATE INDEX account_write_searchkey on account_write(pubkey_id, slot DESC, write_version DESC);
|
|
CREATE INDEX account_write_pubkey_id_idx on account_write(pubkey_id);
|
|
|
|
-- The table storing slot information
|
|
CREATE TABLE slot (
|
|
slot BIGINT PRIMARY KEY,
|
|
parent BIGINT,
|
|
status "SlotStatus" NOT NULL,
|
|
uncle BOOL NOT NULL
|
|
);
|
|
CREATE INDEX ON slot (parent); |