pg.ddx.io  pgdogdev/pgdog GitHub issues and pull requests  
help / color / mirror / Atom feed
From: murex971 (@murex971) <noreply+murex971@github.com>
To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>
Subject: [pgdogdev/pgdog] PR #2072: refactor(cross-shard): sharding key lookup for aggregate rewrite
Date: Thu, 19 Nov 2026 13:21:44 +0000
Message-ID: <gh-pgdogdev-pgdog-2072@github.com> (raw)

### Summary

Groundwork for cross-shard `DISTINCT` aggregates. No behaviour change.

When `DISTINCT` is on the sharding key, or `GROUP BY` includes it, shards
never share a value or a group, so per-shard results can be added up.
The aggregate rewrite needs to know when that is the case.

- Move the router's sharding key lookup to `ShardedTables::sharding_key`
  so the rewrite can reuse it. The router calls the same method.
- Add `ShardingKey`, which answers whether an expression or the
  `GROUP BY` is the sharding key of the table a `SELECT` reads. It only
  answers for a single plain table and a bare column reference; joins,
  subqueries, CTEs and expressions over the key are treated as "no".



inline patch 1: pgdog/src/backend/replication/sharded_tables.rs, pgdog/src/frontend/router/parser/rewrite/statement/aggregate/mod.rs, pgdog/src/frontend/router/parser/rewrite/statement/aggregate/sharding_key.rs and 1 more (+262/-26 lines) download inline-1.patchdiff --git a/pgdog/src/backend/replication/sharded_tables.rs b/pgdog/src/backend/replication/sharded_tables.rs index b8cbb358f..46b387e61 100644 --- a/pgdog/src/backend/replication/sharded_tables.rs +++ b/pgdog/src/backend/replication/sharded_tables.rs @@ -247,6 +247,32 @@ impl ShardedTables { None } + + /// Sharded table config for a column, if the column is a sharding key. + /// + /// Configs that name a table match that table's column. Configs with only + /// a column match that column on any table. + pub(crate) fn sharding_key(&self, column: Column<'_>) -> Option<&ShardedTable> { + // Omnisharded config takes priority over sharded tables: + // a sharding key on an omnisharded table doesn't route. + if column + .table + .is_some_and(|name| self.omnishards().contains_key(name)) + { + return None; + } + + if let Some(table) = self.get_table(column) + && table.name.is_some() + { + return Some(table); + } + + // Column-only config: user explicitly wants any table with this column to be sharded + self.tables() + .iter() + .find(|table| table.name.is_none() && table.column == column.name) + } } #[cfg(test)] diff --git a/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/mod.rs b/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/mod.rs index 5709b4589..119a8f468 100644 --- a/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/mod.rs +++ b/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/mod.rs @@ -1,4 +1,7 @@ mod engine; +// Used by the DISTINCT aggregate rewrite. +#[cfg_attr(not(test), expect(dead_code))] +mod sharding_key; pub(crate) use super::projection::AggregateHelper; diff --git a/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/sharding_key.rs b/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/sharding_key.rs new file mode 100644 index 000000000..83f358d52 --- /dev/null +++ b/pgdog/src/frontend/router/parser/rewrite/statement/aggregate/sharding_key.rs @@ -0,0 +1,228 @@ +//! Sharding key of the table a SELECT reads. +//! +//! A sharding key value lives on one shard, so shards never share one. Each +//! shard's `DISTINCT` aggregate over the key covers values no other shard +//! has, and a group that includes the key is complete on its shard. + +use itertools::Itertools; +use pg_raw_parse::{Node, nodes}; + +use crate::backend::replication::ShardedTables; +use crate::frontend::router::parser::{Column, Table}; + +/// Answers whether an expression is the sharding key of the queried table. +pub(crate) struct ShardingKey<'a> { + table: Table<'a>, + tables: &'a ShardedTables, +} + +impl<'a> ShardingKey<'a> { + /// `None` unless the SELECT reads exactly one plain table. Joins, + /// subqueries and functions in `FROM` can repeat a key on several shards, + /// and a CTE can shadow the table's name. + pub(crate) fn new(select: &'a nodes::SelectStmt, tables: &'a ShardedTables) -> Option<Self> { + if select.with_clause().is_some() { + return None; + } + let Ok(Node::RangeVar(range_var)) = select.from_clause().iter().exactly_one() else { + return None; + }; + + Some(Self { + table: Table::from(range_var), + tables, + }) + } + + /// Whether `node` is a bare reference to the sharding key column. + /// Expressions over the key, e.g. `user_id % 2`, don't count: + /// two shards can produce the same result. + pub(crate) fn matches(&self, node: Node<'_>) -> bool { + let Node::ColumnRef(column) = node else { + return false; + }; + let Ok(mut column) = Column::try_from(column) else { + return false; + }; + if !self.qualifier_matches(&column) { + return false; + } + column.table = Some(self.table.name); + column.schema = self.table.schema; + + self.tables.sharding_key(column).is_some() + } + + /// Whether `GROUP BY` includes the sharding key, by name or by SELECT + /// list position, so that no group spans shards. + pub(crate) fn in_group_by(&self, select: &nodes::SelectStmt) -> bool { + select.group_clause().iter().any(|item| match item { + // Postgres counts SELECT list positions from 1. + Node::A_Const(ordinal) => ordinal + .val() + .and_then(|value| value.numeric_value::<i32>()) + .and_then(|ordinal| usize::try_from(ordinal).ok()?.checked_sub(1)) + .and_then(|position| select.target_list().iter().nth(position)) + .is_some_and(|target| self.matches(target.val())), + item => self.matches(item), + }) + } + + /// An aliased table is only known by its alias. + fn qualifier_matches(&self, column: &Column<'_>) -> bool { + match (column.schema, column.table, self.table.alias) { + (None, None, _) => true, + (None, Some(qualifier), Some(alias)) => qualifier == alias, + (None, Some(qualifier), None) => qualifier == self.table.name, + (Some(schema), Some(qualifier), None) => { + qualifier == self.table.name && self.table.schema == Some(schema) + } + _ => false, + } + } +} + +#[cfg(test)] +mod tests { + use super::*; + use crate::frontend::router::sharding::ShardedTable; + use pg_raw_parse::{Owned, make}; + use pgdog_config::{OmnishardedTable, SystemCatalogsBehavior}; + + fn select(sql: &str) -> Owned<nodes::SelectStmt> { + match pg_raw_parse::parse(sql) + .expect("valid SQL") + .stmts() + .next() + .expect("one statement") + { + Node::SelectStmt(stmt) => make::owned(|mem| mem.make_unique(stmt)), + _ => panic!("not a select"), + } + } + + fn config(name: Option<&str>, column: &str) -> ShardedTable { + ShardedTable { + name: name.map(Into::into), + column: column.into(), + ..Default::default() + } + } + + fn orders_by_user() -> ShardedTables { + ShardedTables::from([config(Some("orders"), "user_id")].as_slice()) + } + + /// Whether the first SELECT list item and the GROUP BY are on the sharding key. + fn check(sql: &str, tables: &ShardedTables) -> Option<(bool, bool)> { + let select = select(sql); + let key = ShardingKey::new(&select, tables)?; + let first = select.target_list().iter().next().expect("one target"); + + Some((key.matches(first.val()), key.in_group_by(&select))) + } + + #[test] + fn matches_bare_key_column() { + let tables = orders_by_user(); + + for (sql, expected) in [ + ("SELECT user_id FROM orders", true), + ("SELECT orders.user_id FROM orders", true), + ("SELECT public.orders.user_id FROM public.orders", true), + ("SELECT o.user_id FROM orders o", true), + ("SELECT product_id FROM orders", false), + // Two shards can produce the same value. + ("SELECT user_id % 2 FROM orders", false), + ("SELECT user_id::text FROM orders", false), + // The alias hides the table name. + ("SELECT orders.user_id FROM orders o", false), + ("SELECT other.user_id FROM orders", false), + ("SELECT other.orders.user_id FROM public.orders", false), + ("SELECT user_id FROM users", false), + ] { + assert_eq!( + check(sql, &tables).map(|(first, _)| first), + Some(expected), + "{sql}" + ); + } + } + + #[test] + fn requires_one_plain_table() { + let tables = orders_by_user(); + + for sql in [ + "SELECT user_id FROM orders JOIN users ON users.id = orders.user_id", + "SELECT user_id FROM orders, users", + "SELECT user_id FROM (SELECT user_id FROM orders) AS orders", + "SELECT user_id FROM generate_series(1, 3) AS orders(user_id)", + "WITH orders AS (SELECT 1 AS user_id) SELECT user_id FROM orders", + "SELECT user_id FROM orders UNION SELECT user_id FROM orders", + "SELECT 1", + ] { + assert!(check(sql, &tables).is_none(), "{sql}"); + } + } + + #[test] + fn finds_key_in_group_by() { + let tables = orders_by_user(); + + for (sql, expected) in [ + ("SELECT count(*) FROM orders GROUP BY user_id", true), + ( + "SELECT count(*) FROM orders GROUP BY status, o.user_id", + false, + ), + ( + "SELECT count(*) FROM orders o GROUP BY status, o.user_id", + true, + ), + ( + "SELECT status, user_id, count(*) FROM orders GROUP BY 1, 2", + true, + ), + ("SELECT status, count(*) FROM orders GROUP BY 1", false), + ("SELECT count(*) FROM orders GROUP BY status", false), + ("SELECT count(*) FROM orders GROUP BY user_id % 2", false), + ("SELECT count(*) FROM orders", false), + // The grand total group spans shards. + ( + "SELECT count(*) FROM orders GROUP BY ROLLUP (user_id)", + false, + ), + ] { + assert_eq!( + check(sql, &tables).map(|(_, group)| group), + Some(expected), + "{sql}" + ); + } + } + + #[test] + fn follows_sharded_table_config() { + // A config without a table name covers the column on every table. + let column_only = ShardedTables::from([config(None, "user_id")].as_slice()); + assert_eq!( + check("SELECT user_id FROM anything", &column_only), + Some((true, false)) + ); + + let omnisharded = ShardedTables::new( + vec![config(None, "user_id")], + vec![OmnishardedTable { + name: "settings".into(), + sticky_routing: false, + }], + false, + SystemCatalogsBehavior::default(), + ); + assert_eq!( + check("SELECT user_id FROM settings", &omnisharded), + Some((false, false)) + ); + } +} diff --git a/pgdog/src/frontend/router/parser/statement.rs b/pgdog/src/frontend/router/parser/statement.rs index 74fe69df8..e810b0062 100644 --- a/pgdog/src/frontend/router/parser/statement.rs +++ b/pgdog/src/frontend/router/parser/statement.rs @@ -660,32 +660,11 @@ impl<'a, 'b: 'a> StatementParser<'a, 'b> { table_name: Option<&str>, schema: Option<&str>, ) -> Option<&'b ShardedTable> { - // Omnisharded config takes priority over sharded tables: - // a sharding key on an omnisharded table doesn't route. - if table_name.is_some_and(|name| self.schema.tables.omnishards().contains_key(name)) { - return None; - } - - // Try named table configs first - if let Some(table_name) = table_name { - let column = Column { - name: column_name, - table: Some(table_name), - schema, - }; - if let Some(sharded_table) = self.schema.tables().get_table(column) - && sharded_table.name.is_some() - { - return Some(sharded_table); - } - } - - // Column-only config: user explicitly wants any table with this column to be sharded - self.schema - .tables - .tables() - .iter() - .find(|t| t.name.is_none() && t.column == column_name) + self.schema.tables().sharding_key(Column { + name: column_name, + table: table_name, + schema, + }) } fn compute_shard(

view thread (5+ messages)  latest in thread

Message-ID: <gh-pgdogdev-pgdog-2072@github.com>
Permalink:  ../gh-pgdogdev-pgdog-2072@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox