25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
use dbx_core::db::postgres::*;
use std::time::Duration;
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_owned_serial_table_ddl_round_trips_without_external_sequences() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let suffix = uuid::Uuid::new_v4().simple();
let schema = format!("dbx 6405 \"{suffix}");
let schema_ident = pg_quote_ident(&schema);
let table_name = "order\"items";
let table_ident = pg_quote_ident(table_name);
let table = format!("{schema_ident}.{table_ident}");
let custom_sequence_name = "custom\"source";
let custom_sequence = format!("{schema_ident}.{}", pg_quote_ident(custom_sequence_name));
let custom_table_name = "custom defaults";
let custom_table = format!("{schema_ident}.{}", pg_quote_ident(custom_table_name));
let owned_custom_sequence_name = "owned custom source";
let owned_custom_sequence = format!("{schema_ident}.{}", pg_quote_ident(owned_custom_sequence_name));
let owned_custom_table_name = "owned custom defaults";
let owned_custom_table = format!("{schema_ident}.{}", pg_quote_ident(owned_custom_table_name));
let dropped_default_table_name = "dropped default";
let dropped_default_table = format!("{schema_ident}.{}", pg_quote_ident(dropped_default_table_name));
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE SCHEMA {schema_ident}; \
CREATE TABLE {table} (\
\"small\"\"id\" smallserial, \
\"regular id\" serial, \
\"large\"\"id\" bigserial, \
identity_id bigint GENERATED BY DEFAULT AS IDENTITY, \
base bigint DEFAULT 2, \
generated bigint GENERATED ALWAYS AS (base * 2) STORED\
); \
CREATE SEQUENCE {custom_sequence}; \
CREATE TABLE {custom_table} (id bigint DEFAULT nextval({}::regclass)); \
CREATE TABLE {owned_custom_table} (id bigint NOT NULL); \
CREATE SEQUENCE {owned_custom_sequence} OWNED BY {owned_custom_table}.id; \
ALTER TABLE {owned_custom_table} ALTER COLUMN id \
SET DEFAULT nextval({}::regclass) + 7; \
CREATE TABLE {dropped_default_table} (id serial); \
ALTER TABLE {dropped_default_table} ALTER COLUMN id DROP DEFAULT",
pg_quote_literal(&custom_sequence),
pg_quote_literal(&owned_custom_sequence),
))
.await
.expect("create serial probe objects");
let ddl = dbx_core::schema::pg_ddl(&pool, &schema, table_name).await.expect("read serial table ddl");
let custom_ddl =
dbx_core::schema::pg_ddl(&pool, &schema, custom_table_name).await.expect("read custom-default table ddl");
let owned_custom_ddl = dbx_core::schema::pg_ddl(&pool, &schema, owned_custom_table_name)
.await
.expect("read owned custom-default table ddl");
let dropped_default_ddl = dbx_core::schema::pg_ddl(&pool, &schema, dropped_default_table_name)
.await
.expect("read dropped-default table ddl");
assert!(ddl.contains("\"small\"\"id\" smallserial NOT NULL"), "ddl: {ddl}");
assert!(ddl.contains("\"regular id\" serial NOT NULL"), "ddl: {ddl}");
assert!(ddl.contains("\"large\"\"id\" bigserial NOT NULL"), "ddl: {ddl}");
assert!(ddl.contains("\"identity_id\" bigint generated by default as identity"), "ddl: {ddl}");
assert!(ddl.contains("\"generated\" bigint generated always as ((base * 2)) stored"), "ddl: {ddl}");
assert!(!ddl.contains("nextval("), "ddl: {ddl}");
assert!(custom_ddl.contains("bigint DEFAULT nextval("), "ddl: {custom_ddl}");
assert!(!custom_ddl.contains("bigserial"), "ddl: {custom_ddl}");
assert!(owned_custom_ddl.contains("bigint NOT NULL DEFAULT (nextval("), "ddl: {owned_custom_ddl}");
assert!(owned_custom_ddl.contains(" + 7)"), "ddl: {owned_custom_ddl}");
assert!(!owned_custom_ddl.contains("bigserial"), "ddl: {owned_custom_ddl}");
assert!(dropped_default_ddl.contains("\"id\" integer NOT NULL"), "ddl: {dropped_default_ddl}");
assert!(!dropped_default_ddl.contains("serial"), "ddl: {dropped_default_ddl}");
assert!(!dropped_default_ddl.contains("DEFAULT"), "ddl: {dropped_default_ddl}");
client.batch_execute(&format!("DROP TABLE {table} CASCADE; {ddl}")).await.expect("replay copied table ddl");
let inserted = client
.query_one(
&format!(
"INSERT INTO {table} DEFAULT VALUES RETURNING \"small\"\"id\", \"regular id\", \"large\"\"id\", identity_id, generated"
),
&[],
)
.await
.expect("insert with replayed sequence defaults");
assert_eq!(inserted.get::<_, i16>(0), 1);
assert_eq!(inserted.get::<_, i32>(1), 1);
assert_eq!(inserted.get::<_, i64>(2), 1);
assert_eq!(inserted.get::<_, i64>(3), 1);
assert_eq!(inserted.get::<_, i64>(4), 4);
client.batch_execute(&format!("DROP SCHEMA {schema_ident} CASCADE")).await.expect("drop serial probe schema");
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_column_metadata_dependency_joins_return_each_column_once() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let suffix = uuid::Uuid::new_v4().simple();
let schema = format!("dbx 6547 \"{suffix}");
let schema_ident = pg_quote_ident(&schema);
let table_name = "issue6547_repro";
let table_ident = pg_quote_ident(table_name);
let table = format!("{schema_ident}.{table_ident}");
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE SCHEMA {schema_ident}; \
CREATE TABLE {table} (\
id serial PRIMARY KEY, \
value text, \
small_id smallserial, \
big_id bigserial, \
ident_id bigint GENERATED BY DEFAULT AS IDENTITY, \
plain_int integer, \
vc varchar(32), \
num numeric(10, 2), \
ts timestamp\
); \
CREATE SEQUENCE {schema_ident}.serial_decoy OWNED BY {table}.id; \
CREATE SEQUENCE {schema_ident}.identity_decoy OWNED BY {table}.ident_id; \
CREATE INDEX idx_issue6547_repro_id ON {table}(id); \
CREATE INDEX idx_issue6547_repro_big ON {table}(big_id); \
CREATE UNIQUE INDEX uniq_issue6547_repro_vc ON {table}(vc);"
))
.await
.expect("create repro table");
let columns = get_columns(&pool, &schema, table_name).await.expect("get columns");
// One pg_attribute row must produce exactly one ColumnInfo even when
// the column has multiple sequence dependencies and several indexes.
// Only the sequence used by the default, or the internal identity
// sequence, is semantic metadata for the column.
let mut seen: std::collections::HashSet<&str> = std::collections::HashSet::new();
for column in &columns {
assert!(seen.insert(column.name.as_str()), "duplicate column {:?} in {columns:?}", column.name);
}
assert_eq!(columns.len(), 9, "expected 9 columns, got {columns:?}");
for (name, expected_extra) in [("id", "serial"), ("small_id", "smallserial"), ("big_id", "bigserial")] {
let column = columns.iter().find(|c| c.name == name).unwrap_or_else(|| panic!("missing {name}"));
assert_eq!(column.extra.as_deref(), Some(expected_extra), "{name} extra mismatch");
}
let ident = columns.iter().find(|c| c.name == "ident_id").expect("ident_id column");
assert!(
ident.extra.as_deref().is_some_and(|extra| extra.starts_with("generated by default as identity")),
"ident_id extra: {:?}",
ident.extra
);
// Ordinary columns keep their plain metadata.
let vc = columns.iter().find(|c| c.name == "vc").expect("vc column");
assert_eq!(vc.data_type, "character varying(32)");
let num = columns.iter().find(|c| c.name == "num").expect("num column");
assert_eq!(num.data_type, "numeric(10,2)");
assert_eq!(num.numeric_precision, Some(10));
assert_eq!(num.numeric_scale, Some(2));
let ts = columns.iter().find(|c| c.name == "ts").expect("ts column");
assert_eq!(ts.data_type, "timestamp without time zone");
// The rendered column section of the DDL must contain each column
// definition exactly once. Before the dependency-join fix these
// queries could return "id" (serial + PRIMARY KEY + secondary index
// = several pg_depend rows) any number of times.
let ddl = dbx_core::schema::pg_ddl(&pool, &schema, table_name).await.expect("read repro table ddl");
for needle in [r#" "id" serial NOT NULL"#, r#" "value" text"#, r#" "vc" character varying(32)"#] {
assert_eq!(ddl.matches(needle).count(), 1, "needle {needle:?} not unique in ddl:\n{ddl}");
}
drop(client);
execute_query(&pool, &format!("DROP SCHEMA {schema_ident} CASCADE")).await.expect("drop repro schema");
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL 9.x database"]
async fn postgres_legacy_table_ddl_uses_compatible_partition_probe() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let version = execute_query(&pool, "SHOW server_version_num").await.expect("query PostgreSQL version");
let version_num = version.rows[0][0]
.as_str()
.expect("server_version_num should be text")
.parse::<u32>()
.expect("server_version_num should be numeric");
assert!((90000..100000).contains(&version_num), "expected PostgreSQL 9.x, got {version_num}");
let schema = format!("dbx_legacy_ddl_{}", uuid::Uuid::new_v4().simple());
let schema_ident = pg_quote_ident(&schema);
let parent = format!("{schema_ident}.parent");
let child = format!("{schema_ident}.child");
let function = format!("{schema_ident}.fill_child_name");
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE SCHEMA {schema_ident}; \
CREATE TABLE {parent} (id integer PRIMARY KEY); \
CREATE TABLE {child} (parent_id integer REFERENCES {parent}(id), name text) INHERITS ({parent}); \
ALTER TABLE {child} ADD PRIMARY KEY (id); \
CREATE INDEX child_name_idx ON {child}(name); \
CREATE FUNCTION {function}() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN NEW.name := COALESCE(NEW.name, chr(120)); RETURN NEW; END$$; \
CREATE TRIGGER child_bi BEFORE INSERT ON {child} FOR EACH ROW EXECUTE PROCEDURE {function}()"
))
.await
.expect("create PostgreSQL 9.x DDL fixtures");
drop(client);
let partition_info =
get_table_partition_info(&pool, &schema, "child").await.expect("classify PostgreSQL 9.x inherited table");
let ddl = dbx_core::schema::pg_ddl(&pool, &schema, "child").await;
// 查看表 DDL(DISPLAY 路径)在 9.x 上不能因 relispartition 报错,
// INHERITS 子表也不能被当成声明式分区渲染。
// The view-DDL path must not fail on the missing relispartition
// column pre-10, and INHERITS children must render as plain tables.
let ddl_with_partitions = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "child").await;
let parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "parent").await;
execute_query(&pool, &format!("DROP SCHEMA {schema_ident} CASCADE"))
.await
.expect("drop PostgreSQL 9.x DDL fixtures");
let ddl = ddl.expect("generate PostgreSQL 9.x table DDL");
let ddl_with_partitions = ddl_with_partitions.expect("generate PostgreSQL 9.x partition-tree DDL");
let parent_ddl = parent_ddl.expect("generate PostgreSQL 9.x partition-tree DDL for parent");
assert!(ddl.contains("CREATE TABLE"), "ddl: {ddl}");
assert!(ddl.contains("PRIMARY KEY"), "ddl: {ddl}");
assert!(ddl.contains("FOREIGN KEY"), "ddl: {ddl}");
assert!(ddl.contains("CREATE INDEX \"child_name_idx\""), "ddl: {ddl}");
assert!(ddl.contains("CREATE TRIGGER child_bi"), "ddl: {ddl}");
// The 9.x child uses traditional `INHERITS`, so it is not a declarative
// partition, but it must carry its parent in `inherits_parents` and the
// rendered DDL must emit `INHERITS (parent)` (issue #10803).
assert!(!partition_info.is_partition, "partition_info: {partition_info:?}");
assert!(!partition_info.is_foreign, "partition_info: {partition_info:?}");
assert_eq!(partition_info.inherits_parents.len(), 1, "partition_info: {partition_info:?}");
assert_eq!(partition_info.inherits_parents[0].table, "parent", "partition_info: {partition_info:?}");
assert!(partition_info.parent_schema.is_none(), "partition_info: {partition_info:?}");
assert!(partition_info.bound.is_none(), "partition_info: {partition_info:?}");
assert!(ddl.contains("INHERITS"), "ddl: {ddl}");
assert!(ddl.contains("\"parent\""), "ddl: {ddl}");
assert!(!ddl.contains("PARTITION OF"), "ddl: {ddl}");
assert!(ddl_with_partitions.contains("CREATE TABLE"), "ddl_with_partitions: {ddl_with_partitions}");
assert!(!ddl_with_partitions.contains("PARTITION OF"), "ddl_with_partitions: {ddl_with_partitions}");
// 9.x 没有声明式分区:父表的分区树只含父表自身(子表按普通表)。
assert_eq!(parent_ddl.matches("CREATE TABLE").count(), 1, "parent_ddl: {parent_ddl}");
assert!(!parent_ddl.contains("PARTITION OF"), "parent_ddl: {parent_ddl}");
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_legacy_batched_ddl_metadata_preserves_relation_pairs_across_compat_tiers() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let schema = format!("dbx_batch_fkeys_{}", uuid::Uuid::new_v4().simple());
let schema_ident = pg_quote_ident(&schema);
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE SCHEMA {schema_ident}; \
CREATE TABLE {schema_ident}.parent_a (id integer PRIMARY KEY); \
CREATE TABLE {schema_ident}.parent_b (id integer PRIMARY KEY); \
CREATE TABLE {schema_ident}.child_a ( \
id integer PRIMARY KEY, parent_id integer, \
CONSTRAINT child_a_parent_fk FOREIGN KEY (parent_id) REFERENCES {schema_ident}.parent_a(id) \
); \
CREATE TABLE {schema_ident}.child_b ( \
id integer PRIMARY KEY, parent_id integer, \
CONSTRAINT child_b_parent_fk FOREIGN KEY (parent_id) REFERENCES {schema_ident}.parent_b(id) \
); \
CREATE TABLE {schema_ident}.without_fk (id integer PRIMARY KEY)"
))
.await
.expect("create batched foreign-key fixtures");
let version_num = client
.query_one("SHOW server_version_num", &[])
.await
.expect("query PostgreSQL version")
.get::<_, String>(0)
.parse::<u32>()
.expect("server_version_num should be numeric");
let has_conparentid = client
.query_one(
"SELECT EXISTS ( \
SELECT 1 FROM pg_catalog.pg_attribute \
WHERE attrelid = 'pg_catalog.pg_constraint'::pg_catalog.regclass \
AND attname = 'conparentid' AND NOT attisdropped \
)",
&[],
)
.await
.expect("probe pg_constraint.conparentid")
.get::<_, bool>(0);
if version_num < 110000 {
assert!(!has_conparentid, "pre-11 PostgreSQL must not have parent-linked constraint clones");
}
let empty = list_foreign_keys_for_relations(&pool, &[]).await.expect("empty relation batch");
assert!(empty.is_empty());
let single_relations = vec![(schema.clone(), "child_a".to_string())];
let single = list_foreign_keys_for_relations(&pool, &single_relations).await.expect("single relation batch");
let single_fk = &single[&(schema.clone(), "child_a".to_string())][0];
assert_eq!(single_fk.name, "child_a_parent_fk");
assert_eq!(single_fk.ref_table, "parent_a");
// Keep the pairs deliberately out of name order. A cross product or
// independently unnested array would duplicate or misattribute keys.
let relations = vec![
(schema.clone(), "child_b".to_string()),
(schema.clone(), "without_fk".to_string()),
(schema.clone(), "child_a".to_string()),
];
let batched = list_foreign_keys_for_relations(&pool, &relations).await.expect("multi-relation batch");
assert_eq!(batched.len(), 2, "batched keys: {batched:?}");
assert!(!batched.contains_key(&(schema.clone(), "without_fk".to_string())));
assert_eq!(batched[&(schema.clone(), "child_a".to_string())][0].ref_table, "parent_a");
assert_eq!(batched[&(schema.clone(), "child_b".to_string())][0].ref_table, "parent_b");
let schemas: Vec<&str> = relations.iter().map(|(schema, _)| schema.as_str()).collect();
let tables: Vec<&str> = relations.iter().map(|(_, table)| table.as_str()).collect();
let compat = list_foreign_keys_for_relations_with_sql(
&client,
postgres_foreign_keys_for_relations_compat_sql(),
&schemas,
&tables,
)
.await
.expect("subscript-paired compatibility query");
assert_eq!(compat[&(schema.clone(), "child_a".to_string())][0].ref_table, "parent_a");
assert_eq!(compat[&(schema.clone(), "child_b".to_string())][0].ref_table, "parent_b");
let modern =
list_foreign_keys_for_relations_with_sql(&client, postgres_foreign_keys_for_relations_sql(), &schemas, &tables)
.await;
if version_num >= 90400 {
let modern = modern.expect("multi-array unnest primary query");
assert_eq!(modern[&(schema.clone(), "child_a".to_string())][0].ref_table, "parent_a");
assert_eq!(modern[&(schema.clone(), "child_b".to_string())][0].ref_table, "parent_b");
} else {
modern.expect_err("pre-9.4 server must reject multi-array unnest");
}
let relation_oids: Vec<i64> = client
.query(
"SELECT c.oid::bigint \
FROM pg_catalog.pg_class c \
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
WHERE n.nspname = $1 ORDER BY c.relname",
&[&schema],
)
.await
.expect("query fixture relation oids")
.into_iter()
.map(|row| row.get(0))
.collect();
get_table_partition_local_objects_for_relations_with_sql(
&client,
postgres_table_partition_local_objects_for_relations_compat_sql(),
&relation_oids,
)
.await
.expect("pre-parent-clone local-object query");
let modern_local_objects = get_table_partition_local_objects_for_relations_with_sql(
&client,
postgres_table_partition_local_objects_for_relations_sql(),
&relation_oids,
)
.await;
if version_num >= 90300 {
modern_local_objects.expect("JSON-extraction local-object query");
} else {
modern_local_objects.expect_err("PostgreSQL 9.2 must reject the JSON ->> operator");
}
drop(client);
let child_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "child_a")
.await
.expect("display DDL with foreign key");
let without_fk_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "without_fk")
.await
.expect("display DDL without foreign key");
execute_query(&pool, &format!("DROP SCHEMA {schema_ident} CASCADE"))
.await
.expect("drop batched foreign-key fixtures");
assert!(child_ddl.contains("FOREIGN KEY"), "child ddl: {child_ddl}");
assert!(!without_fk_ddl.contains("FOREIGN KEY"), "without-fk ddl: {without_fk_ddl}");
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL 15+ database"]
async fn postgres_foreign_key_options_round_trip_through_single_and_partition_tree_ddl() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let suffix = uuid::Uuid::new_v4().simple().to_string();
let source = format!("dbx_fk_options_{}", &suffix[..10]);
let references = format!("{source}_refs");
let source_ident = pg_quote_ident(&source);
let references_ident = pg_quote_ident(&references);
let client = pool.get().await.expect("get postgres client");
client.batch_execute(&format!(
"CREATE SCHEMA {source_ident}; CREATE SCHEMA {references_ident};
CREATE TABLE {references_ident}.\"parent(table\" (\"id)\" integer, version integer, PRIMARY KEY (\"id)\", version));
CREATE TABLE {source_ident}.children (
id integer, \"parent)a\" integer, \"parent\"\"b\" integer,
CONSTRAINT fk_parent FOREIGN KEY (\"parent)a\", \"parent\"\"b\")
REFERENCES {references_ident}.\"parent(table\" (\"id)\", version)
MATCH FULL ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
) PARTITION BY RANGE (id);
CREATE TABLE {source_ident}.children_default PARTITION OF {source_ident}.children DEFAULT;
ALTER TABLE {source_ident}.children_default ADD CONSTRAINT fk_local
FOREIGN KEY (\"parent)a\", \"parent\"\"b\") REFERENCES {references_ident}.\"parent(table\" (\"id)\", version)
ON UPDATE CASCADE ON DELETE SET NULL (\"parent)a\") DEFERRABLE INITIALLY IMMEDIATE NOT VALID;"
)).await.expect("create foreign key option fixtures");
for include_tree in [false, true] {
let replay = format!("{source}_{}", if include_tree { "tree" } else { "single" });
let replay_ident = pg_quote_ident(&replay);
let ddl = if include_tree {
dbx_core::schema::pg_ddl_with_partitions(&pool, &source, "children").await
} else {
dbx_core::schema::pg_ddl(&pool, &source, "children").await
}
.expect("read foreign key DDL");
client
.batch_execute(&format!("CREATE SCHEMA {replay_ident}; {}", ddl.replace(&source_ident, &replay_ident)))
.await
.unwrap_or_else(|error| panic!("replay foreign key DDL: {error:?}; DDL: {ddl}"));
let mut snapshots = Vec::new();
for schema in [&source, &replay] {
let result = execute_query(
&pool,
&format!(
"SELECT c.relname::text, con.conname::text, con.condeferrable, con.condeferred,
con.confupdtype::text, con.confdeltype::text, con.convalidated,
pg_catalog.pg_get_constraintdef(con.oid, true) AS definition
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_class c ON c.oid = con.conrelid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = {} AND con.contype = 'f'
AND (c.relname = 'children' OR {})
ORDER BY c.relname, con.conname",
pg_quote_literal(schema),
include_tree
),
)
.await
.expect("snapshot restored constraint properties");
assert!(!result.rows.is_empty());
snapshots.push(result.rows);
}
assert_eq!(snapshots[0], snapshots[1], "foreign key options changed; DDL: {ddl}");
client.batch_execute(&format!("DROP SCHEMA {replay_ident} CASCADE")).await.expect("drop replay schema");
}
client
.batch_execute(&format!("DROP SCHEMA {source_ident} CASCADE; DROP SCHEMA {references_ident} CASCADE"))
.await
.expect("drop fixture schemas");
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_ddl_emits_not_valid_check_constraints_as_separate_replayable_alter() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let pid = std::process::id();
let schema = format!("dbx_not_valid_check_{pid}");
let schema_ident = pg_quote_ident(&schema);
let table = format!("{schema_ident}.events");
execute_query(&pool, &format!("CREATE SCHEMA {schema_ident}")).await.expect("create schema");
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE TABLE {table} (id integer, amount integer); \
ALTER TABLE {table} ADD CONSTRAINT c_pos CHECK (amount > 0) NOT VALID"
))
.await
.expect("create table with unvalidated check constraint");
let ddl = dbx_core::schema::pg_ddl(&pool, &schema, "events").await.expect("events ddl");
// A `NOT VALID` CHECK constraint is illegal inline in a `CREATE
// TABLE` column list — Postgres only accepts it after `ALTER TABLE
// ADD CONSTRAINT`. Replaying the generated DDL onto a fresh schema
// exercises that this is real, executable syntax, not just a string
// match.
let replay_schema = format!("{schema}_replay");
let replay_schema_ident = pg_quote_ident(&replay_schema);
execute_query(&pool, &format!("CREATE SCHEMA {replay_schema_ident}")).await.expect("create replay schema");
client
.batch_execute(&ddl.replace(&schema, &replay_schema))
.await
.unwrap_or_else(|error| panic!("replay not-valid-check ddl failed: {error:?}; ddl: {ddl}"));
client
.batch_execute(&format!("DROP SCHEMA {schema_ident} CASCADE; DROP SCHEMA {replay_schema_ident} CASCADE"))
.await
.expect("drop schemas");
assert!(!ddl.contains("NOT VALID)"), "NOT VALID must not appear inline in the column list: {ddl}");
assert!(ddl.contains("CREATE TABLE"), "ddl: {ddl}");
assert!(!ddl.contains("\"amount\" integer,"), "check constraint must not be inlined into columns: {ddl}");
assert!(ddl.contains("ALTER TABLE"), "ddl: {ddl}");
assert!(
ddl.contains("ADD CONSTRAINT \"c_pos\" CHECK (amount > 0) NOT VALID;"),
"expected a separate ALTER TABLE ADD CONSTRAINT ... NOT VALID statement: {ddl}"
);
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_partition_metadata_renders_replayable_children_and_subpartitions() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let pid = std::process::id();
let schema = format!("dbx_partition_meta_{pid}");
let schema_ident = pg_quote_ident(&schema);
let replay_schema = format!("{schema}_replay");
let replay_schema_ident = pg_quote_ident(&replay_schema);
let parent = format!("{schema_ident}.parent");
let child = format!("{schema_ident}.child");
let default_child = format!("{schema_ident}.child_default");
let subpartition = format!("{schema_ident}.subpartition");
let inherited_parent = format!("{schema_ident}.inherited_parent");
let inherited_child = format!("{schema_ident}.inherited_child");
// Declared in "public" (not the per-test schema) so the same
// qualified name keeps resolving after `schema_ident` substrings are
// rewritten to `replay_schema_ident` for the replay pass below.
let trigger_fn = format!("public.dbx_partition_meta_trigger_fn_{pid}");
execute_query(&pool, &format!("CREATE SCHEMA {schema_ident}")).await.expect("create schema");
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE OR REPLACE FUNCTION {trigger_fn}() RETURNS trigger LANGUAGE plpgsql AS \
$$ BEGIN RETURN NEW; END $$; \
CREATE TABLE {parent} (id integer, payload text, status text DEFAULT 'pending') PARTITION BY RANGE (id); \
CREATE TABLE {child} PARTITION OF {parent} ( \
PRIMARY KEY (id), \
CONSTRAINT child_payload_not_blank CHECK (payload <> ''), \
status WITH OPTIONS DEFAULT 'child-status' \
) FOR VALUES FROM (1) TO (10); \
CREATE INDEX child_payload_idx ON {child} (payload); \
CREATE TABLE {default_child} PARTITION OF {parent} DEFAULT; \
CREATE TABLE {subpartition} PARTITION OF {parent} FOR VALUES FROM (10) TO (20) PARTITION BY HASH (payload); \
ALTER TABLE {parent} ADD CONSTRAINT parent_id_positive CHECK (id > 0); \
CREATE TRIGGER parent_biu BEFORE INSERT ON {parent} FOR EACH ROW EXECUTE FUNCTION {trigger_fn}(); \
CREATE TABLE {inherited_parent} (id integer, bucket integer, PRIMARY KEY (id, bucket)) PARTITION BY RANGE (bucket); \
CREATE TABLE {inherited_child} PARTITION OF {inherited_parent} FOR VALUES FROM (1) TO (10)"
))
.await
.expect("create partitioned tables");
let parent_info = get_table_partition_info(&pool, &schema, "parent").await.expect("parent metadata");
let child_info = get_table_partition_info(&pool, &schema, "child").await.expect("child metadata");
let default_info = get_table_partition_info(&pool, &schema, "child_default").await.expect("default metadata");
let subpartition_info =
get_table_partition_info(&pool, &schema, "subpartition").await.expect("subpartition metadata");
let child_local_objects =
get_table_partition_local_objects(&pool, &schema, "child").await.expect("child local objects");
let inherited_child_local_objects = get_table_partition_local_objects(&pool, &schema, "inherited_child")
.await
.expect("inherited child local objects");
let parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "parent").await.expect("parent ddl");
let child_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "child").await.expect("child ddl");
let default_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "child_default").await.expect("default ddl");
let subpartition_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "subpartition").await.expect("subpartition ddl");
let inherited_parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "inherited_parent")
.await
.expect("inherited parent ddl");
let inherited_child_ddl =
dbx_core::schema::pg_ddl(&pool, &schema, "inherited_child").await.expect("inherited child ddl");
// The bare (non-recursive) parent DDL, exactly what database export
// and table transfer produce per-relation — used below to give the
// single-relation partition ddls (child_ddl, default_ddl,
// subpartition_ddl, inherited_child_ddl) a real parent shell to
// replay against, so their syntax is actually exercised by
// PostgreSQL rather than only checked with string assertions.
let bare_parent_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "parent").await.expect("bare parent ddl");
let bare_inherited_parent_ddl =
dbx_core::schema::pg_ddl(&pool, &schema, "inherited_parent").await.expect("bare inherited parent ddl");
// `parent_ddl`/`inherited_parent_ddl` already embed their full
// partition subtree (that's the fix under test), so replaying the
// per-partition ddl strings on top would recreate the same relations
// twice. Only the two top-level requests need replaying; the
// individually-fetched partition ddls above are replayed separately
// below, on top of a bare (childless) copy of their parent.
execute_query(&pool, &format!("CREATE SCHEMA {replay_schema_ident}")).await.expect("create replay schema");
for ddl in [&parent_ddl, &inherited_parent_ddl] {
client
.batch_execute(&ddl.replace(&schema, &replay_schema))
.await
.unwrap_or_else(|error| panic!("replay partition ddl failed: {error:?}; ddl: {ddl}"));
}
// Single-relation partition ddls are now a real production code
// path (database export/table transfer fetch each partition this
// way), so replay them for real too: a fresh schema gets just the
// bare parents, then each single-relation partition ddl on top.
let bare_schema = format!("{schema}_bare");
let bare_schema_ident = pg_quote_ident(&bare_schema);
execute_query(&pool, &format!("CREATE SCHEMA {bare_schema_ident}")).await.expect("create bare schema");
for ddl in [&bare_parent_ddl, &bare_inherited_parent_ddl] {
client
.batch_execute(&ddl.replace(&schema, &bare_schema))
.await
.unwrap_or_else(|error| panic!("replay bare parent ddl failed: {error:?}; ddl: {ddl}"));
}
for ddl in [&child_ddl, &default_ddl, &subpartition_ddl, &inherited_child_ddl] {
client
.batch_execute(&ddl.replace(&schema, &bare_schema))
.await
.unwrap_or_else(|error| panic!("replay single-relation partition ddl failed: {error:?}; ddl: {ddl}"));
}
client
.batch_execute(&format!(
"DROP SCHEMA {schema_ident} CASCADE; DROP SCHEMA {replay_schema_ident} CASCADE; \
DROP SCHEMA {bare_schema_ident} CASCADE; DROP FUNCTION IF EXISTS {trigger_fn}()"
))
.await
.expect("drop schemas and trigger function");
assert_eq!(parent_info.key.as_deref(), Some("RANGE (id)"));
assert!(!parent_info.is_partition);
assert_eq!(child_info.parent_schema.as_deref(), Some(schema.as_str()));
assert_eq!(child_info.parent_table.as_deref(), Some("parent"));
assert_eq!(child_info.bound.as_deref(), Some("FOR VALUES FROM (1) TO (10)"));
assert!(child_local_objects.has_primary_key);
assert!(child_local_objects.indexes.contains("child_payload_idx"));
assert!(child_local_objects.check_constraints.contains("child_payload_not_blank"));
assert!(!child_local_objects.check_constraints.contains("parent_id_positive"));
assert_eq!(child_local_objects.column_defaults.get("status"), Some(&PostgresColumnDefaultState::Overridden));
assert!(!inherited_child_local_objects.has_primary_key);
assert!(inherited_child_local_objects.indexes.is_empty());
assert_eq!(default_info.bound.as_deref(), Some("DEFAULT"));
assert_eq!(subpartition_info.key.as_deref(), Some("HASH (payload)"));
assert!(parent_ddl.contains(") PARTITION BY RANGE (id);"), "ddl: {parent_ddl}");
assert!(parent_ddl.contains("CONSTRAINT \"parent_id_positive\" CHECK (id > 0)"), "ddl: {parent_ddl}");
// The parent's trigger is automatically cloned onto every partition
// by PostgreSQL itself when the partition is created/attached, so it
// must appear exactly once in the combined DDL, not once per relation.
assert_eq!(parent_ddl.matches("CREATE TRIGGER").count(), 1, "ddl: {parent_ddl}");
assert!(child_ddl.contains("CREATE TABLE"), "ddl: {child_ddl}");
assert!(child_ddl.contains("PARTITION OF"), "ddl: {child_ddl}");
assert!(child_ddl.contains("PRIMARY KEY (\"id\")"), "ddl: {child_ddl}");
assert!(child_ddl.contains("FOR VALUES FROM (1) TO (10);"), "ddl: {child_ddl}");
assert!(child_ddl.contains("CREATE INDEX \"child_payload_idx\""), "ddl: {child_ddl}");
assert!(child_ddl.contains("CONSTRAINT \"child_payload_not_blank\" CHECK (payload"), "ddl: {child_ddl}");
assert!(!child_ddl.contains("parent_id_positive"), "ddl: {child_ddl}");
assert!(child_ddl.contains("\"status\" WITH OPTIONS DEFAULT 'child-status'"), "ddl: {child_ddl}");
assert!(!child_ddl.contains("CREATE TRIGGER"), "child must not redeclare the parent's cloned trigger: {child_ddl}");
assert!(default_ddl.contains(" DEFAULT;"), "ddl: {default_ddl}");
assert!(
subpartition_ddl.contains("FOR VALUES FROM (10) TO (20) PARTITION BY HASH (payload);"),
"ddl: {subpartition_ddl}"
);
assert!(!inherited_child_ddl.contains("PRIMARY KEY"), "ddl: {inherited_child_ddl}");
assert!(!inherited_child_ddl.contains("CREATE INDEX"), "ddl: {inherited_child_ddl}");
}
#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database with postgres_fdw available"]
async fn postgres_partition_ddl_uses_foreign_table_syntax_for_foreign_partitions() {
let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
let pid = std::process::id();
let schema = format!("dbx_partition_fdw_{pid}");
let schema_ident = pg_quote_ident(&schema);
let server = format!("dbx_partition_fdw_server_{pid}");
let server_ident = pg_quote_ident(&server);
let replay_schema = format!("{schema}_replay");
let replay_schema_ident = pg_quote_ident(&replay_schema);
let parent = format!("{schema_ident}.parent");
let local_child = format!("{schema_ident}.local_child");
let foreign_child = format!("{schema_ident}.foreign_child");
execute_query(&pool, &format!("CREATE SCHEMA {schema_ident}")).await.expect("create schema");
let client = pool.get().await.expect("get postgres client");
client
.batch_execute(&format!(
"CREATE EXTENSION IF NOT EXISTS postgres_fdw; \
CREATE SERVER {server_ident} FOREIGN DATA WRAPPER postgres_fdw \
OPTIONS (host 'localhost', dbname 'postgres', port '5432'); \
CREATE USER MAPPING FOR CURRENT_USER SERVER {server_ident} \
OPTIONS (user 'postgres', password 'postgres'); \
CREATE TABLE {parent} (id integer, payload text) PARTITION BY RANGE (id); \
CREATE TABLE {local_child} PARTITION OF {parent} FOR VALUES FROM (1) TO (10); \
CREATE FOREIGN TABLE {foreign_child} PARTITION OF {parent} \
FOR VALUES FROM (10) TO (20) \
SERVER {server_ident} OPTIONS (schema_name 'public', table_name 'foreign_child_data')"
))
.await
.expect("create partitioned tables with a foreign partition");
let foreign_info = get_table_partition_info(&pool, &schema, "foreign_child").await.expect("foreign child info");
let local_info = get_table_partition_info(&pool, &schema, "local_child").await.expect("local child info");
let parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "parent").await.expect("parent ddl");
let foreign_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "foreign_child").await.expect("foreign child ddl");
execute_query(&pool, &format!("CREATE SCHEMA {replay_schema_ident}")).await.expect("create replay schema");
client
.batch_execute(&parent_ddl.replace(&schema, &replay_schema))
.await
.unwrap_or_else(|error| panic!("replay foreign partition ddl failed: {error:?}; ddl: {parent_ddl}"));
client
.batch_execute(&format!(
"DROP SCHEMA {schema_ident} CASCADE; DROP SCHEMA {replay_schema_ident} CASCADE; \
DROP SERVER {server_ident} CASCADE"
))
.await
.expect("drop schemas and foreign server");
assert!(!local_info.is_foreign);
assert!(foreign_info.is_foreign);
assert_eq!(foreign_info.foreign_server.as_deref(), Some(server.as_str()));
assert!(
foreign_info.foreign_options.contains(&("schema_name".to_string(), "public".to_string())),
"options: {:?}",
foreign_info.foreign_options
);
assert!(parent_ddl.contains("CREATE FOREIGN TABLE"), "ddl: {parent_ddl}");
assert!(foreign_ddl.starts_with("CREATE FOREIGN TABLE"), "ddl: {foreign_ddl}");
assert!(foreign_ddl.contains("PARTITION OF"), "ddl: {foreign_ddl}");
assert!(foreign_ddl.contains(&format!("SERVER {}", pg_quote_ident(&server))), "ddl: {foreign_ddl}");
assert!(foreign_ddl.contains("\"schema_name\" 'public'"), "ddl: {foreign_ddl}");
assert!(foreign_ddl.contains("\"table_name\" 'foreign_child_data'"), "ddl: {foreign_ddl}");
assert!(!foreign_ddl.contains("PRIMARY KEY"), "ddl: {foreign_ddl}");
assert!(!foreign_ddl.contains("CREATE INDEX"), "ddl: {foreign_ddl}");
}