#![cfg(feature = "postgres-admin")]
use std::time::Duration;
use footing::db::postgres::admin::{PostgresResetError, PostgresResetSettings, reset_database};
use footing::db::postgres::{
PostgresBootstrapError, PostgresBootstrapSettings, PostgresConnectionSettings,
PostgresMigrationRunError, PostgresPoolSettings, advisory_lock_key, bootstrap_database,
connect_pool, database_name, ensure_database_exists, run_migrations_with_timeouts,
schema_maintenance_advisory_lock_key,
};
use sqlx::migrate::{MigrateDatabase, Migrator};
const POSTGRES_TEST_DATABASE: &str = "footing_test";
const REQUIRE_POSTGRES_TESTS_ENV: &str = "FOOTING_REQUIRE_POSTGRES_TESTS";
const ALLOW_DESTRUCTIVE_POSTGRES_TESTS_ENV: &str = "FOOTING_ALLOW_DESTRUCTIVE_POSTGRES_TESTS";
fn test_database_url() -> Option<String> {
let required = std::env::var(REQUIRE_POSTGRES_TESTS_ENV).as_deref() == Ok("1");
let database_url = match std::env::var("FOOTING_TEST_POSTGRES_URL") {
Ok(database_url) => database_url,
Err(error) => {
assert!(
!required,
"{REQUIRE_POSTGRES_TESTS_ENV}=1 requires FOOTING_TEST_POSTGRES_URL: {error}"
);
return None;
}
};
assert_eq!(
std::env::var(ALLOW_DESTRUCTIVE_POSTGRES_TESTS_ENV).as_deref(),
Ok("1"),
"PostgreSQL integration tests can reset the public schema; set \
{ALLOW_DESTRUCTIVE_POSTGRES_TESTS_ENV}=1 only for the disposable test database"
);
assert_eq!(
database_name(&database_url).as_deref(),
Some(POSTGRES_TEST_DATABASE),
"destructive PostgreSQL integration tests must target the fixed disposable database \
{POSTGRES_TEST_DATABASE:?}"
);
Some(database_url)
}
#[tokio::test]
async fn database_creation_quotes_identifiers_and_tolerates_competing_creators() {
let Some(database_url) = test_database_url() else {
return;
};
let name = format!(
"footing_ensure_{}_{}\"quoted",
std::process::id(),
std::time::SystemTime::now()
.duration_since(std::time::UNIX_EPOCH)
.unwrap()
.as_nanos()
);
let mut url = url::Url::parse(&database_url).unwrap();
url.set_path(&name);
url.query_pairs_mut().append_pair("dbname", &name);
let (first, second) = tokio::join!(
ensure_database_exists(url.as_str(), Duration::from_secs(30)),
ensure_database_exists(url.as_str(), Duration::from_secs(30))
);
first.expect("first creator should succeed");
second.expect("competing creator should succeed");
ensure_database_exists(url.as_str(), Duration::from_secs(30))
.await
.expect("an existing database must remain usable");
let pool = connect_pool(
&PostgresConnectionSettings::new(url.as_str())
.build()
.unwrap(),
PostgresPoolSettings::default().build().unwrap(),
)
.await
.unwrap();
let actual: String = sqlx::query_scalar("SELECT current_database()")
.fetch_one(&pool)
.await
.unwrap();
pool.close().await;
sqlx::Postgres::drop_database(url.as_str()).await.unwrap();
assert_eq!(actual, name);
url.set_path("/postgres");
url.query_pairs_mut().append_pair("dbname", "postgres");
ensure_database_exists(url.as_str(), Duration::from_secs(30))
.await
.expect("the postgres database must use template1 for maintenance");
}
#[tokio::test]
async fn database_creation_rejects_names_the_server_would_truncate() {
let Some(database_url) = test_database_url() else {
return;
};
let pool = connect_pool(
&PostgresConnectionSettings::new(&database_url)
.build()
.unwrap(),
PostgresPoolSettings::default().build().unwrap(),
)
.await
.unwrap();
let max_length: i32 =
sqlx::query_scalar("SELECT current_setting('max_identifier_length')::int4")
.fetch_one(&pool)
.await
.unwrap();
let max_length = usize::try_from(max_length).unwrap();
for multibyte in [false, true] {
let prefix = format!(
"footing_limit_{}_{}_",
std::process::id(),
std::time::SystemTime::now()
.duration_since(std::time::UNIX_EPOCH)
.unwrap()
.as_nanos()
);
let name = if multibyte {
format!("{prefix}{}界", "x".repeat(max_length - prefix.len() - 1))
} else {
format!("{prefix}{}", "x".repeat(max_length + 1 - prefix.len()))
};
let truncated = &name[..name.floor_char_boundary(max_length)];
let mut url = url::Url::parse(&database_url).unwrap();
url.query_pairs_mut().append_pair("dbname", &name);
let result = ensure_database_exists(url.as_str(), Duration::from_secs(30)).await;
let unintended_database: bool =
sqlx::query_scalar("SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname::text = $1)")
.bind(truncated)
.fetch_one(&pool)
.await
.unwrap();
if unintended_database {
let statement = format!("DROP DATABASE \"{}\"", truncated.replace('"', "\"\""));
sqlx::query(sqlx::AssertSqlSafe(statement))
.execute(&pool)
.await
.unwrap();
}
assert!(
result.is_err(),
"an overlong database name must be rejected"
);
assert!(
!unintended_database,
"database creation must not target a truncated alias"
);
}
pool.close().await;
}
#[tokio::test]
async fn schema_maintenance_rejects_truncated_database_aliases() {
let Some(database_url) = test_database_url() else {
return;
};
let admin = connect_pool(
&PostgresConnectionSettings::new(&database_url)
.build()
.unwrap(),
PostgresPoolSettings::default().build().unwrap(),
)
.await
.unwrap();
let max_length: i32 =
sqlx::query_scalar("SELECT current_setting('max_identifier_length')::int4")
.fetch_one(&admin)
.await
.unwrap();
let prefix = format!(
"footing_alias_{}_{}_",
std::process::id(),
std::time::SystemTime::now()
.duration_since(std::time::UNIX_EPOCH)
.unwrap()
.as_nanos()
);
let name = format!("{prefix}{}", "x".repeat(max_length as usize - prefix.len()));
let mut url = url::Url::parse(&database_url).unwrap();
url.query_pairs_mut().append_pair("dbname", &name);
ensure_database_exists(url.as_str(), Duration::from_secs(30))
.await
.unwrap();
let mut alias_url = url.clone();
let alias = format!("{name}_overflow");
alias_url.query_pairs_mut().append_pair("dbname", &alias);
let pool = connect_pool(
&PostgresConnectionSettings::new(alias_url.as_str())
.build()
.unwrap(),
PostgresPoolSettings::default().build().unwrap(),
)
.await
.unwrap();
let actual: String = sqlx::query_scalar("SELECT current_database()")
.fetch_one(&pool)
.await
.unwrap();
assert_eq!(
actual, name,
"PostgreSQL truncates the startup database name"
);
let exists: bool =
sqlx::query_scalar("SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname = $1)")
.bind(&alias)
.fetch_one(&admin)
.await
.unwrap();
assert!(
!exists,
"SQLx text parameters must not alias the database name"
);
sqlx::query("CREATE TABLE alias_guard (id integer)")
.execute(&pool)
.await
.unwrap();
let mut holder = pool.acquire().await.unwrap();
let lock_key = schema_maintenance_advisory_lock_key(&name);
sqlx::query("SELECT pg_advisory_lock($1)")
.bind(lock_key)
.execute(&mut *holder)
.await
.unwrap();
let directory = tempfile::tempdir().unwrap();
let migrator = Migrator::new(directory.path()).await.unwrap();
let bootstrap = bootstrap_database(
PostgresBootstrapSettings::new(alias_url.as_str())
.with_migration_lock_timeout(Duration::from_millis(100))
.build()
.unwrap(),
Some(&migrator),
)
.await;
let bootstrap_error = match bootstrap {
Ok(pool) => {
pool.close().await;
None
}
Err(error) => Some(error),
};
let migrations_created: bool =
sqlx::query_scalar("SELECT to_regclass('public._sqlx_migrations') IS NOT NULL")
.fetch_one(&pool)
.await
.unwrap();
let reset = reset_database(
PostgresResetSettings::new(alias_url.as_str())
.with_reset_lock_timeout(Duration::from_millis(100))
.build()
.unwrap(),
None,
)
.await;
let sentinel_exists: bool =
sqlx::query_scalar("SELECT to_regclass('public.alias_guard') IS NOT NULL")
.fetch_one(&pool)
.await
.unwrap();
sqlx::query("SELECT pg_advisory_unlock($1)")
.bind(lock_key)
.execute(&mut *holder)
.await
.unwrap();
drop(holder);
pool.close().await;
sqlx::Postgres::drop_database(url.as_str()).await.unwrap();
admin.close().await;
assert!(
matches!(&bootstrap_error, Some(PostgresBootstrapError::MigrationLock { source, .. }) if matches!(source.as_ref(), sqlx::Error::Configuration(_))),
"bootstrap error: {bootstrap_error:?}; migrations created: {migrations_created}; reset failed: {}; sentinel survived: {sentinel_exists}",
reset.is_err(),
);
assert!(
matches!(reset, Err(PostgresResetError::ResetLock { source, .. }) if matches!(*source, sqlx::Error::Configuration(_)))
);
assert!(
!migrations_created,
"migrations must not modify a truncated alias"
);
assert!(sentinel_exists, "reset must not modify a truncated alias");
}
#[tokio::test]
async fn migration_lock_and_execution_respect_deadlines() {
let Some(database_url) = test_database_url() else {
return;
};
let pool = connect_pool(
&PostgresConnectionSettings::new(&database_url)
.build()
.unwrap(),
PostgresPoolSettings::default()
.build()
.expect("default pool settings are valid"),
)
.await
.expect("integration database must be available");
let database_name = database_name(&database_url).expect("test URL must name a database");
let coordinator_key = advisory_lock_key("footing.test", "postgres-integration-suite");
let lock_key = schema_maintenance_advisory_lock_key(&database_name);
let mut holder = pool.acquire().await.expect("lock holder must connect");
sqlx::query("SELECT pg_advisory_lock($1)")
.bind(coordinator_key)
.execute(&mut *holder)
.await
.expect("test coordinator lock must be acquired");
sqlx::query("SELECT pg_advisory_lock($1)")
.bind(lock_key)
.execute(&mut *holder)
.await
.expect("test lock must be acquired");
let directory = tempfile::tempdir().expect("migration directory must be created");
let migrator = Migrator::new(directory.path())
.await
.expect("empty migration directory is valid");
let error = run_migrations_with_timeouts(
&pool,
&migrator,
None,
Duration::from_millis(100),
Duration::from_secs(2),
)
.await
.expect_err("a held advisory lock must hit the configured deadline");
assert!(matches!(
error,
PostgresMigrationRunError::LockTimeout { .. }
));
let unlocked: bool = sqlx::query_scalar("SELECT pg_advisory_unlock($1)")
.bind(lock_key)
.fetch_one(&mut *holder)
.await
.expect("test lock must be released");
assert!(unlocked);
std::fs::write(
directory.path().join("20260831000000_slow.sql"),
"SELECT pg_sleep(1);",
)
.expect("slow migration must be written");
let slow_migrator = Migrator::new(directory.path())
.await
.expect("slow migration directory must be valid");
let error = run_migrations_with_timeouts(
&pool,
&slow_migrator,
None,
Duration::from_secs(2),
Duration::from_millis(100),
)
.await
.expect_err("a slow migration must hit the execution deadline");
assert!(matches!(
error,
PostgresMigrationRunError::MigrationTimeout { .. }
));
let coordinator_unlocked: bool = sqlx::query_scalar("SELECT pg_advisory_unlock($1)")
.bind(coordinator_key)
.fetch_one(&mut *holder)
.await
.expect("test coordinator lock must be released");
assert!(coordinator_unlocked);
}
#[tokio::test]
async fn reset_is_exclusive_and_recreates_schema_atomically() {
let Some(database_url) = test_database_url() else {
return;
};
let pool = connect_pool(
&PostgresConnectionSettings::new(&database_url)
.build()
.unwrap(),
PostgresPoolSettings::default()
.build()
.expect("default pool settings are valid"),
)
.await
.expect("integration database must be available");
let name = database_name(&database_url).expect("test URL must name a database");
let coordinator_key = advisory_lock_key("footing.test", "postgres-integration-suite");
let reset_key = schema_maintenance_advisory_lock_key(&name);
let mut holder = pool.acquire().await.expect("lock holder must connect");
sqlx::query("SELECT pg_advisory_lock($1)")
.bind(coordinator_key)
.execute(&mut *holder)
.await
.expect("test coordinator lock must be acquired");
sqlx::query("SELECT pg_advisory_lock($1)")
.bind(reset_key)
.execute(&mut *holder)
.await
.expect("test reset lock must be acquired");
let blocked = PostgresResetSettings::new(&database_url)
.with_reset_lock_timeout(Duration::from_millis(100))
.build()
.expect("reset settings must be valid");
let error = reset_database(blocked, None)
.await
.expect_err("concurrent reset must not pass the exclusive lock");
assert!(matches!(error, PostgresResetError::ResetLockTimeout { .. }));
let unlocked: bool = sqlx::query_scalar("SELECT pg_advisory_unlock($1)")
.bind(reset_key)
.fetch_one(&mut *holder)
.await
.expect("test reset lock must be released");
assert!(unlocked);
sqlx::query("CREATE TABLE IF NOT EXISTS public.footing_reset_marker (id integer)")
.execute(&pool)
.await
.expect("marker table must be created");
let mut blocker = pool.acquire().await.expect("DDL blocker must connect");
sqlx::query("BEGIN")
.execute(&mut *blocker)
.await
.expect("DDL blocker transaction must begin");
sqlx::query("LOCK TABLE public.footing_reset_marker IN ACCESS SHARE MODE")
.execute(&mut *blocker)
.await
.expect("DDL blocker must hold an incompatible schema-reset lock");
let timed_reset = PostgresResetSettings::new(&database_url)
.with_reset_lock_timeout(Duration::from_secs(2))
.with_reset_schema_timeout(Duration::from_millis(100))
.build()
.expect("timed reset settings must be valid");
let error = reset_database(timed_reset, None)
.await
.expect_err("blocked schema reset must hit its execution deadline");
assert!(matches!(
error,
PostgresResetError::ResetSchemaTimeout { .. }
));
sqlx::query("ROLLBACK")
.execute(&mut *blocker)
.await
.expect("DDL blocker transaction must roll back");
drop(blocker);
let marker_after_timeout: Option<String> =
sqlx::query_scalar("SELECT to_regclass('public.footing_reset_marker')::text")
.fetch_one(&pool)
.await
.expect("marker lookup after timed-out reset must succeed");
assert_eq!(
marker_after_timeout.as_deref(),
Some("footing_reset_marker"),
"timed-out transactional reset must leave the original schema intact"
);
let reset = PostgresResetSettings::new(&database_url)
.with_reset_lock_timeout(Duration::from_secs(2))
.build()
.expect("reset settings must be valid");
reset_database(reset, None)
.await
.expect("isolated test database must reset successfully");
let marker: Option<String> =
sqlx::query_scalar("SELECT to_regclass('public.footing_reset_marker')::text")
.fetch_one(&pool)
.await
.expect("marker lookup must succeed");
assert!(marker.is_none());
let public_schema: bool =
sqlx::query_scalar("SELECT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'public')")
.fetch_one(&pool)
.await
.expect("public schema lookup must succeed");
assert!(public_schema);
let coordinator_unlocked: bool = sqlx::query_scalar("SELECT pg_advisory_unlock($1)")
.bind(coordinator_key)
.fetch_one(&mut *holder)
.await
.expect("test coordinator lock must be released");
assert!(coordinator_unlocked);
}