pg.ddx.io  pgdogdev/pgdog GitHub issues and pull requests  
help / color / mirror / Atom feed
From: jkaczman (@jkaczman) <noreply+jkaczman@github.com>
To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>
Subject: [pgdogdev/pgdog] PR #2060: feat: expand supported col types to all built-in Postgres types (except arrays)
Date: Wed, 18 Nov 2026 16:09:19 +0000
Message-ID: <gh-pgdogdev-pgdog-2060@github.com> (raw)

When we rewrite a cross-shard subquery, we send the rows of the subquery back to the shards as an unnest(..) array per column. Previously, we found the array type of each column through DataType, which only listed 13 types, however, there's quite a few other types that weren't handled, e.g., jsonb, date, inet..

Now, we look up the array type by the OID of the column in BUILTIN_ARRAY_TYPES, which is a generated list (from pg_type) of Postgres's 74 built-in types. 

The lookup iterates over the list once per column in the subquery, which is a bit of a tradeoff. It's about 0.2% of instructions of the entire request to do this; on the one hand, we could create a match for each function, which would be faster (would compile into a jump table), but it'd be about three times the code, and we'd need to worry about keeping three separate matches in sync. Thus, this seems fine for now, and can potentially optimize it better later.

This is partially motivated by Mastodon: the accounts table in Mastodon (which is joined to most other tables) has a jsonb column 

inline patch 1: integration/rust/tests/integration/subqueries/mod.rs, integration/rust/tests/integration/subqueries/tests/select.sql, pgdog-postgres-types/src/datum.rs and 2 more (+135/-87 lines) download inline-1.patchdiff --git a/integration/rust/tests/integration/subqueries/mod.rs b/integration/rust/tests/integration/subqueries/mod.rs index d29758e5d..b20969b98 100644 --- a/integration/rust/tests/integration/subqueries/mod.rs +++ b/integration/rust/tests/integration/subqueries/mod.rs @@ -61,7 +61,7 @@ async fn setup_subqueries_suite(suffix: &str, first_time_running: bool) { "DROP TABLE IF EXISTS {subpackages_table}, {suborders_table}, {subusers_table}, {substates_table}" ), format!( - "CREATE TABLE {subusers_table} (id BIGINT PRIMARY KEY, username text, status text)" + "CREATE TABLE {subusers_table} (id BIGINT PRIMARY KEY, username text, status text, profile jsonb, signed_up date, last_ip inet)" ), format!("CREATE TABLE {substates_table} (state text PRIMARY KEY, shipping text)"), format!( @@ -80,11 +80,15 @@ async fn setup_subqueries_suite(suffix: &str, first_time_running: bool) { let setup_sql_inserts = [ format!("INSERT INTO {substates_table}(state, shipping) VALUES ('CA', 'quick')"), format!("INSERT INTO {substates_table}(state, shipping) VALUES ('NC', 'not quick')"), - format!("INSERT INTO {subusers_table}(id, username, status) VALUES (1, 'test', 'VIP')"), format!( - "INSERT INTO {subusers_table}(id, username, status) VALUES (2, 'test2', 'regular')" + "INSERT INTO {subusers_table}(id, username, status, profile, signed_up, last_ip) VALUES (1, 'test', 'VIP', '{{\"plan\": \"pro\"}}', '2026-01-01', '10.0.0.1')" + ), + format!( + "INSERT INTO {subusers_table}(id, username, status, profile, signed_up, last_ip) VALUES (2, 'test2', 'regular', '{{\"plan\": \"free\"}}', '2026-02-01', '192.168.0.2')" + ), + format!( + "INSERT INTO {subusers_table}(id, username, status, profile, signed_up, last_ip) VALUES (3, 'test3', 'VIP', '{{\"plan\": \"pro\"}}', '2026-03-01', '10.0.0.3')" ), - format!("INSERT INTO {subusers_table}(id, username, status) VALUES (3, 'test3', 'VIP')"), format!("INSERT INTO {subusers_table}(id, username, status) VALUES (4, 'test4', NULL)"), format!( "INSERT INTO {suborders_table}(id, user_id, state, created_at) VALUES (1, 1, 'CA', '2026-10-12 14:30:00 America/New_York')" diff --git a/integration/rust/tests/integration/subqueries/tests/select.sql b/integration/rust/tests/integration/subqueries/tests/select.sql index e39881a0b..78c0dd61d 100644 --- a/integration/rust/tests/integration/subqueries/tests/select.sql +++ b/integration/rust/tests/integration/subqueries/tests/select.sql @@ -204,8 +204,8 @@ SELECT s.id, s.username FROM ( -- what: SELECT .. FROM (<sq>) s ORDER BY .. LIMIT <n>, <sq> is cross-shard -- route select: 0,1 -- route select: any -SELECT s.id, s.status FROM ( - SELECT id, status FROM subusers WHERE status IS NOT NULL +SELECT * FROM ( + SELECT * FROM subusers WHERE status IS NOT NULL ) s ORDER BY s.id DESC LIMIT 2; -- case diff --git a/pgdog-postgres-types/src/datum.rs b/pgdog-postgres-types/src/datum.rs index d392f9c85..15fcf29bd 100644 --- a/pgdog-postgres-types/src/datum.rs +++ b/pgdog-postgres-types/src/datum.rs @@ -376,78 +376,30 @@ impl DataType { } } - // Returns None for arrays, Vector and Other, since we can't send those types as one-dimensional arrays (unnest would flatten arrays of arrays) - pub fn into_array_type(&self) -> Option<DataType> { - match self { - DataType::Bigint => Some(DataType::Array(1016)), - DataType::Integer => Some(DataType::Array(1007)), - DataType::Text => Some(DataType::Array(1009)), - DataType::Interval => Some(DataType::Array(1187)), - DataType::Timestamp => Some(DataType::Array(1115)), - DataType::TimestampTz => Some(DataType::Array(1185)), - DataType::Real => Some(DataType::Array(1021)), - DataType::DoublePrecision => Some(DataType::Array(1022)), - DataType::Bool => Some(DataType::Array(1000)), - DataType::SmallInt => Some(DataType::Array(1005)), - DataType::Numeric => Some(DataType::Array(1231)), - DataType::Uuid => Some(DataType::Array(2951)), - DataType::Oid => Some(DataType::Array(1028)), - DataType::Array(_) | DataType::Vector | DataType::Other(_) => None, - DataType::Cidr => todo!(), - DataType::Money => todo!(), - DataType::Inet => todo!(), + /// Returns the OID of the array type of the built-in type `type_oid`, e.g., 3807 (jsonb[]) for jsonb (3802). + /// + /// Returns None for + /// - array types (e.g., bigint[]), since Postgres has no array type for bigint[] (in pg_type, typarray is 0 for bigint[]). + /// - types that aren't built into Postgres (e.g., enums or vector from pgvector), which aren't in `BUILTIN_ARRAY_TYPES`. + pub fn array_type_oid(type_oid: i32) -> Option<i32> { + for (element_oid, array_oid, _) in BUILTIN_ARRAY_TYPES { + if element_oid == type_oid { + return Some(array_oid); + } } + + None } - /// Format the `DataType` into a type that can be - /// expressed as pg_catalog.<TYPE> - /// - /// Manually checked via `SELECT oid, typname, typnamespace::regnamespace AS schema_name - /// FROM pg_catalog.pg_type ORDER BY oid;` against `DataType::from_oid` - /// - /// <https://www.postgresql.org/docs/current/datatype.html; - pub fn as_pg_catalog_type(&self) -> &'static str { - match self { - DataType::Bigint => "int8", - DataType::Integer => "int4", - DataType::Text => "text", - DataType::Interval => "interval", - DataType::Timestamp => "timestamp", - DataType::TimestampTz => "timestamptz", - DataType::Real => "float4", - DataType::DoublePrecision => "float8", - DataType::Bool => "bool", - DataType::SmallInt => "int2", - DataType::Numeric => "numeric", - DataType::Uuid => "uuid", - DataType::Oid => "oid", - DataType::Vector => todo!( - "We don't have a type OID for this within from_oid; not sure what this maps to" - ), - DataType::Array(inner_oid) => match inner_oid { - 1000 => "_bool", - 1005 => "_int2", - 1007 => "_int4", - 1009 => "_text", - 1015 => "_varchar", - 1016 => "_int8", - 1021 => "_float4", - 1022 => "_float8", - 1028 => "_oid", - 1115 => "_timestamp", - 1185 => "_timestamptz", - 1187 => "_interval", - 1231 => "_numeric", - 2951 => "_uuid", - _ => panic!("We should have all the array types covered (this matches from_oid)"), - }, - DataType::Other(_) => { - panic!("This needs to be handled properly with a client-facing error") + /// Returns the name of the built-in array type `array_oid` in pg_catalog, e.g., "_jsonb" for jsonb[] (3807). + pub fn array_type_name(array_oid: i32) -> Option<&'static str> { + for (_, oid, name) in BUILTIN_ARRAY_TYPES { + if oid == array_oid { + return Some(name); } - DataType::Money => todo!(), - DataType::Inet => todo!(), - DataType::Cidr => todo!(), } + + None } pub fn pg_catalog_type_to_datatype(pg_catalog_type: &str) -> Option<Self> { @@ -511,6 +463,94 @@ impl fmt::Display for DataType { } } +/// Types that are built into Postgres, as well as the array type of each type, e.g., (3802, 3807, "_jsonb") for jsonb and jsonb[] +/// Each entry tuple represents (the OID of the type, the OID of the array type, the name of the array type) +/// +/// Generated on Postgres 18 with the query below. The query lists the types in pg_catalog that +/// - are built in (`oid < 10000`). Postgres assigns these OIDs in its source code +/// - have a binary format (`typsend <> 0 and typreceive <> 0`), to ensure that we can copy values of these types into arrays in binary format without decoding the values +/// - are base types (e.g., int8), range types (e.g., int4range) or multirange types (e.g., int4multirange) (`typtype in ('b','r','m')`) +/// +/// `select oid, typarray, (select typname from pg_type a where a.oid = t.typarray) from pg_type t where typnamespace = 'pg_catalog'::regnamespace and typarray <> 0 and oid < 10000 and typtype in ('b','r','m') and typsend <> 0 and typreceive <> 0 order by oid` +/// +/// <https://www.postgresql.org/docs/current/datatype.html; +const BUILTIN_ARRAY_TYPES: [(i32, i32, &str); 74] = [ + (16, 1000, "_bool"), + (17, 1001, "_bytea"), + (18, 1002, "_char"), + (19, 1003, "_name"), + (20, 1016, "_int8"), + (21, 1005, "_int2"), + (22, 1006, "_int2vector"), + (23, 1007, "_int4"), + (24, 1008, "_regproc"), + (25, 1009, "_text"), + (26, 1028, "_oid"), + (27, 1010, "_tid"), + (28, 1011, "_xid"), + (29, 1012, "_cid"), + (30, 1013, "_oidvector"), + (114, 199, "_json"), + (142, 143, "_xml"), + (600, 1017, "_point"), + (601, 1018, "_lseg"), + (602, 1019, "_path"), + (603, 1020, "_box"), + (604, 1027, "_polygon"), + (628, 629, "_line"), + (650, 651, "_cidr"), + (700, 1021, "_float4"), + (701, 1022, "_float8"), + (718, 719, "_circle"), + (774, 775, "_macaddr8"), + (790, 791, "_money"), + (829, 1040, "_macaddr"), + (869, 1041, "_inet"), + (1042, 1014, "_bpchar"), + (1043, 1015, "_varchar"), + (1082, 1182, "_date"), + (1083, 1183, "_time"), + (1114, 1115, "_timestamp"), + (1184, 1185, "_timestamptz"), + (1186, 1187, "_interval"), + (1266, 1270, "_timetz"), + (1560, 1561, "_bit"), + (1562, 1563, "_varbit"), + (1700, 1231, "_numeric"), + (1790, 2201, "_refcursor"), + (2202, 2207, "_regprocedure"), + (2203, 2208, "_regoper"), + (2204, 2209, "_regoperator"), + (2205, 2210, "_regclass"), + (2206, 2211, "_regtype"), + (2950, 2951, "_uuid"), + (2970, 2949, "_txid_snapshot"), + (3220, 3221, "_pg_lsn"), + (3614, 3643, "_tsvector"), + (3615, 3645, "_tsquery"), + (3734, 3735, "_regconfig"), + (3769, 3770, "_regdictionary"), + (3802, 3807, "_jsonb"), + (3904, 3905, "_int4range"), + (3906, 3907, "_numrange"), + (3908, 3909, "_tsrange"), + (3910, 3911, "_tstzrange"), + (3912, 3913, "_daterange"), + (3926, 3927, "_int8range"), + (4072, 4073, "_jsonpath"), + (4089, 4090, "_regnamespace"), + (4096, 4097, "_regrole"), + (4191, 4192, "_regcollation"), + (4451, 6150, "_int4multirange"), + (4532, 6151, "_nummultirange"), + (4533, 6152, "_tsmultirange"), + (4534, 6153, "_tstzmultirange"), + (4535, 6155, "_datemultirange"), + (4536, 6157, "_int8multirange"), + (5038, 5039, "_pg_snapshot"), + (5069, 271, "_xid8"), +]; + #[cfg(test)] mod tests { use super::*; diff --git a/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs b/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs index 374254b10..b4603bc52 100644 --- a/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs +++ b/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs @@ -6,7 +6,7 @@ use pg_raw_parse::{ nodes::{self, Alias, RangeFunction, SelectStmt}, transform, }; -use pgdog_postgres_types::{Array, Format}; +use pgdog_postgres_types::{Array, DataType, Format}; use crate::{ frontend::client::query_engine::multi_step::SubqueryMulti, @@ -154,16 +154,9 @@ impl<'a> SubqueryMulti<'a> { unnest_column_names.push(mem.make_string(Some(unnest_column_name.as_str())).uncast()); - let data_type = field.data_type(); - - let pg_catalog_type: &str = data_type - .into_array_type() - .expect("evaluate_and_rewrite_outer_statement checked the column types") - .as_pg_catalog_type(); - - let element_oid = data_type - .to_oid() - .expect("all data types with an array type have an OID"); + let array_type_name = DataType::array_type_oid(field.type_oid) + .and_then(DataType::array_type_name) + .expect("evaluate_and_rewrite_outer_statement checked the column types"); let node = { let mut node = mem.make_node::<nodes::ParamRef>(); @@ -184,7 +177,7 @@ impl<'a> SubqueryMulti<'a> { .map(|value| value.data.as_ref()) }); - let array = Array::binary_from_encoded_elements(element_oid, elements); + let array = Array::binary_from_encoded_elements(field.type_oid, elements); old_bind.push_param(Parameter::from_bytes(array), Format::Binary); @@ -193,7 +186,7 @@ impl<'a> SubqueryMulti<'a> { node.uncast(), mem.make_list(&[ mem.make_string(Some("pg_catalog")), - mem.make_string(Some(pg_catalog_type)), + mem.make_string(Some(array_type_name)), ]), ) .uncast(); diff --git a/pgdog/src/frontend/client/query_engine/multi_step/subquery/mod.rs b/pgdog/src/frontend/client/query_engine/multi_step/subquery/mod.rs index 073762588..26cf31a64 100644 --- a/pgdog/src/frontend/client/query_engine/multi_step/subquery/mod.rs +++ b/pgdog/src/frontend/client/query_engine/multi_step/subquery/mod.rs @@ -14,6 +14,8 @@ mod evaluate; mod request; mod rewrite; +use pgdog_postgres_types::DataType; + use super::Error; use crate::{ frontend::{ @@ -190,12 +192,21 @@ impl<'ast> SubqueryMulti<'ast> { // We send the rows of subqueries as unnest(..) arrays, so we check the column types before rewriting to ensure that all columns have array types. // EXISTS is the only exception to this, since we only read whether or not the subquery returned rows (exists just needs 1 row to be replaced w/ TRUE) + // + // Specifically, we return the error below for the columns of the types that `DataType::array_type_oid` doesn't return an array type for: + // - array columns (e.g., bigint[]), since 2D arrays need inner arrays of the same length, but the arrays of the rows can have different lengths (e.g., {a,b} and {c}). + // - columns of types that extensions or users create (e.g., enums) (see below) + // + // TODO(jkaczman): Support columns who have enum type e.g., mood (CREATE TYPE mood AS ENUM ('sad', 'happy')) or citext. + // The RowDescription gives us the OID of the type (e.g., 16385). However, the OID can vary between shards. The good thing is that the name of the type (mood) is the same on all shards. This allows us to first look up the name of the type in pg_type (on the shard that returned the OID) + // Then, we could send the values as a text[], and cast that array with the name of the type instead of the OID, e.g., $1::pg_catalog._text::mood[]. + // How can the values go into the text[] as-is? In binary format, enum or citext value is the same bytes as the text value (e.g., 'happy' is the 5 bytes h, a, p, p, y). if !self.is_exists_in_where(subquery) && let (_, Some(row_description)) = &results && row_description .fields .iter() - .any(|field| field.data_type().into_array_type().is_none()) + .any(|field| DataType::array_type_oid(field.type_oid).is_none()) { return Err(Error::Subquery( SubqueryError::SubqueryReturningUnsupportedColumn,

view thread (6+ messages)  latest in thread

Message-ID: <gh-pgdogdev-pgdog-2060@github.com>
Permalink:  ../gh-pgdogdev-pgdog-2060@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