From d3028db00568e22135fd388f833875d4b57ae4d5 Mon Sep 17 00:00:00 2001 From: Arik Sosman Date: Thu, 17 Aug 2023 13:14:18 -0700 Subject: [PATCH] Create indices for schema version 12. --- src/config.rs | 11 +++++++++-- 1 file changed, 9 insertions(+), 2 deletions(-) diff --git a/src/config.rs b/src/config.rs index 804b0f8..db6d2ef 100644 --- a/src/config.rs +++ b/src/config.rs @@ -15,7 +15,7 @@ use lightning::util::ser::Readable; use lightning_block_sync::http::HttpEndpoint; use tokio_postgres::Config; -pub(crate) const SCHEMA_VERSION: i32 = 11; +pub(crate) const SCHEMA_VERSION: i32 = 12; pub(crate) const SNAPSHOT_CALCULATION_INTERVAL: u32 = 3600 * 24; // every 24 hours, in seconds /// If the last update in either direction was more than six days ago, we send a reminder /// That reminder may be either in the form of a channel announcement, or in the form of empty @@ -117,11 +117,12 @@ pub(crate) fn db_channel_update_table_creation_query() -> &'static str { pub(crate) fn db_index_creation_query() -> &'static str { " - CREATE INDEX IF NOT EXISTS channel_updates_seen_with_id_direction_blob ON channel_updates(seen) INCLUDE (id, direction, blob_signed); CREATE INDEX IF NOT EXISTS channel_updates_seen_scid ON channel_updates(seen, short_channel_id); CREATE INDEX IF NOT EXISTS channel_updates_scid_dir_seen_asc ON channel_updates(short_channel_id, direction, seen); CREATE INDEX IF NOT EXISTS channel_updates_scid_dir_seen_desc_with_id ON channel_updates(short_channel_id ASC, direction ASC, seen DESC) INCLUDE (id); CREATE UNIQUE INDEX IF NOT EXISTS channel_updates_key ON channel_updates (short_channel_id, direction, timestamp); + CREATE INDEX IF NOT EXISTS channel_updates_seen ON channel_updates(seen); + CREATE INDEX IF NOT EXISTS channel_updates_timestamp_desc ON channel_updates(timestamp DESC); " } @@ -254,6 +255,12 @@ pub(crate) async fn upgrade_db(schema: i32, client: &mut tokio_postgres::Client) tx.execute("UPDATE config SET db_schema = 11 WHERE id = 1", &[]).await.unwrap(); tx.commit().await.unwrap(); } + if schema >= 1 && schema <= 11 { + let tx = client.transaction().await.unwrap(); + tx.execute("DROP INDEX IF EXISTS channel_updates_seen_with_id_direction_blob", &[]).await.unwrap(); + tx.execute("UPDATE config SET db_schema = 12 WHERE id = 1", &[]).await.unwrap(); + tx.commit().await.unwrap(); + } if schema <= 1 || schema > SCHEMA_VERSION { panic!("Unknown schema in db: {}, we support up to {}", schema, SCHEMA_VERSION); } -- 2.39.5