Merged PRs

doltgresql

• 2503: Fixed DST handling by moving to timeofday
time.Time serialization and deserialization can cause information to be irreversibly lost due to daylight savings time changes. The location isn't directly serialized due to it being tied to the runtime system information (different operating systems may have different time zone information), and is therefore reconstructed on deserialization.
  This is fine for timestamptz, as we internally process everything as UTC and use the client's timezone when returning values to the client. For timetz, however, the timezone information is embedded in the value itself and is independent of the client, and this was causing information loss during serialization. This PR changes serialization to use the timetz.TimeTZ type, which is already used at most steps (by casting time.Time to timetz.TimeTZ and vice-versa), and therefore the change propagates to all functions that take a timetz value.
  In line with this, the time type was updated to use timeofday.TimeOfDay, which is also already used internally in most functions. Although serialization is fine, this was changed to be more inline with timetz (as timetz.TimeTZ directly uses timeofday.TimeOfDay) and also due to time.Time carrying implicit information that can cause direct value comparisons to be incorrect. For example, '01:00:00'::time should sort before '02:00:00'::time, however adding 24 hours to the first value will still result in the correct hour mark for display, but internally the time.Time value would be on the next day, and would cause the comparison to actually show '02:00:00'::time appearing first (assuming they started on the same day to begin with).
  All of these issues are fixed by using the correct types that are already in Doltgres, and already used by the internal functions. I'm not sure why we decided to use time.Time instead of those anyway (as interval uses the internal type rather than casting to time.Duration), but this fixes it. This does mean that the serialization is incompatible with existing databases, however since serialization was incorrect in the first place, it's a necessary change.
• 2496: /.github/workflows: bump actions checkout
• 2494: add is distinct and not distinct from exprs
• 2490: resolve user defined type in drop routine
• 2487: Added proper support for receiving binary formatted values
  This is the receive counterpart to the following PR:
  • #2394
    All new tests were validated against a live Postgres 15 server to ensure that we're processing client input properly. In addition, I've added a little more testing on the send side as well, as there were a couple of small gaps in coverage.
• 2478: remove ValidateTagUniqueness
  companion pr: dolthub/dolt#10707
• 2476: Misc Fixes for Prisma
  3 things in this PR:
  1. Makes version numbers consistent. I used 15.17 which is the latest version of postgresql 15.
  2. Make the col_description, obj_description, and shobj_description return the correct value for "no comments", rather than "empty comments". They are still no-op functions but now return NULL instead of empty strings. More detailed information on these functions can be found here
  3. generate_subscripts is a set returning function that returns the indices of the array you give it. So for instance the array [10, 40, 23] will return {1, 2, 3}. Most postgresql array types are 1-indexed, except for the types oidvector and int2vector. I added a fix so that generate_subscripts respects those two's unique indexing. More on generate_subscripts here. oidvector and int2vector don't seem to have standard docs pages but this site provides basic details.
• 2472: add fors plpgsql statement
• 2464: Support creating/dropping DB with hypens in name
  Adds functionality and testing for creating databases like test-db.
  Currently: create database "test-db" will create a database called "test-db" (with the escaped quotes).
  drop database "test-db" will try to look for a database called "test-db" to drop (with the escaped quotes).
• 2458: Moved the binary wire format for receiving to the receive func
• 2454: resolve type with database provided
• 2447: support fori plpgsql statement

Closed Issues

• 2333: UPDATE SET date_column = '2026-02-14' fails without explicit ::date cast via simple protocol

Merged PRs

doltgresql

• 2443: Added proper dolt_branch_control functionality
  Fixes:
  • #2435
    This fixes two primary issues with dolt_branch_control. The first is that we'd error when encountering a destination GMS type, as we make the assumption that all GMS types have been handled once we reach a certain point in the analyzer. This is false for Dolt system tables that may be modified, so now cast those types to their closest Doltgres type for analysis.
    The second fix deals with how superusers are handled between Doltgres and GMS. Doltgres uses its own permission model since it's fundamentally different than GMS' model, but dolt_branch_control internally checks for a superuser (or pseudo-superuser) to allow some commands without explicit privileges given. This adds a layer of indirection so that checks for superusers specifically will work as expected.
• 2439: check for 'check_function_bodies' configuration parameter…
  … when creating functions
  Depends on: dolthub/go-mysql-server#3465
• 2415: Support text array parameters
  Prisma blocking bug. Right now if you try to bind a query with a text array we scan it incorrectly and error out.
• 2413: Added additional testing for TIMESTAMP
  This adds testing for TIMESTAMP and TIMESTAMPTZ, which were missing. This also adds a step in CI that changes the locale to the west coast since we already had such assumptions in other tests (that were eventually changed), and there's no other way to test timezones otherwise.
• 2408: support int2vector and oidvector types
• 2402: #2373 fix VALUES subquery unwrap from dropping clauses

  Summary

  • Replace overly permissive len(inSelect.From) == 1 guard in nodeAliasedTableExpr with isTrivialSelectStar, which only unwraps bare SELECT * FROM (VALUES ...)
    subqueries with no additional clauses (projections, LIMIT, ORDER BY, DISTINCT, WHERE, GROUP BY, HAVING)
  • Add *plan.Offset guard to TypeSanitizer to prevent OFFSET literals inside subqueries from being converted to Doltgres types, which caused GMS validateOffsetAndLimit
    to reject them with "invalid type: bigint"
  • Un-skip mixed-type VALUES-in-subquery tests and add mixed-type variants plus OFFSET-specific assertions
    Closes: #2373
• 2384: support privileges for routines and sequences
  Depends on: dolthub/go-mysql-server#3450

Closed Issues

• 2435: INSERT INTO dolt_branch_control fails with "non-Doltgres type found in destination
• 2388: Cannot call unnest on pg_index column indoption.
• 2373: Bug: VALUES inside subquery ignores projections, LIMIT, and ORDER BY

Merged PRs

doltgresql

• 2395: information schema sequences
  Adds sequences to information_schema, following the specification outlined by Postgresql here.
• 2394: Implemented the binary wire format
  Fixes:
  • #2317
  • #2347
  • #2348
  • #2360
  • #2362
    In a nutshell, we did not respect a client's wishes when they requested that the results for some columns are returned in their binary format (obtained from their send function). Instead, we had hardcoded that some types, in certain circumstances, would only return their binary format, while others would strictly return their text format. This PR implements the binary wire format for every type that is currently supported. This also fixes numerous bugs that were hidden due to our incorrect behavior.
    In addition to the above fixes, this PR introduces a new test harness for analyzing our compatibility with the wire messages returned directly to Postgres clients. Thousands of lines of new tests have been added that ensure we're returning the exact correct results for a given set of messages (not just queries but the actual client messages), and the test harness allows for quick iteration when comparing what a Postgres server sends versus a Doltgres server, so we can ensure that we're returning the same bytes, not just the same overall structure.
• 2187: #1648: fix VALUES clause type inference

  Summary

  VALUES clause type inference now uses PostgreSQL's common type resolution algorithm instead of using only the first row's type.
  existing issue: SELECT * FROM (VALUES(1),(2.01),(3)) v(n) failed with integer: unhandled type: decimal.Decimal

  Changes

  • Add ResolveValuesTypes analyzer rule that:
  • Collects types from all VALUES rows (not just the first)
  • Uses FindCommonType() to resolve per PostgreSQL's [UNION/CASE type resolution](https://www.postgresql.org/docs/15/typeconv-union-case.html; per FindCommonType doc comment)
  • Applies implicit casts to convert values to the common type
  • Updates GetField expressions in parent nodes (handles aggregates like SUM)
  • Add UnknownCoercion expression for unknown to target type coercion
  • add go and bats tests

  Questions for Reviewers

  1. Analyzer rule approach: Is adding this as an analyzer rule (after TypeSanitizer) the right approach? I considered alternatives but this seemed cleanest for handling the two-pass transformation needed for GetField updates. Open to feedback if there's a better pattern.
  2. PostgreSQL version: The code references PostgreSQL 15 docs. Should this stay as-is since doltgresql targets PG 15, or should it use /docs/current/?
     Fixes: #1648

Closed Issues

• 2362: COUNT(*) returns wrong value via binary protocol (extended query)
• 2360: Extended protocol sends JSONB as raw JSON text instead of binary JSONB format (missing 0x01 version prefix)
• 2348: Extended protocol sends incorrect binary format for ENUM columns, causing client-side decode panic
• 2347: Extended protocol sends empty binary payload for NUMERIC/DECIMAL columns, causing client panic
• 2317: Extended protocol SELECT fails with 'permission denied for table pg_type'
• 1648: the first value in VALUES should not determine the column type

Merged PRs

doltgresql

• 2353: allow cte in CREATE VIEW
  Also fixes:
  • string_agg with separator cast with text type
  • fix parse procedure and sequence name in GRANT privileges ON statement
• 2350: add plpgsql-dynexecute statement for CREATE FUNCTION
  Also fixes:
  • type resolution in DECLARE
  • add support for TG_OP special variable usage for triggers
• 2349: Checking for table relation respects temporary tables
  Fixes #2326
  When checking for whether a root has a relation, we should respect temporary tables in addition to our existing checks for regular tables.
• 2346: Added test framework for testing wire data
  This is a precursor for:
  • #2317
    Currently, we have no in-engine way of ensuring that the proper data is received on the wire for any given sequence of messages that were sent. This means that we must rely on client tests to ensure that the correct data is sent, with the expectation that they'll fail if we change something. This isn't conducive to our standard debugging workflow, so this adds the ability to perform wire tests and verification from within the Go testing environment, which is resistant to change (client tests could fail due to a library update that is outside of our control) and easily debuggable.
    For most of these tests, I'll be using WireShark to ensure that our output is exactly the same as a standard Postgres server.

Closed Issues

• 2326: Cannot create keyless temporary tables with serial columns

Merged PRs

doltgresql

• 2327: bugfix: support records containing non-DoltgresType values
• 2324: resolve type with empty search path

Closed Issues

Merged PRs

doltgresql

• 2323: Fixed issues 2316 and 2318
  This fixes:
  • #2316
  • #2318
    For 2316, clients may only send a single format code that should apply to multiple values, but we assumed that their lengths always matched. This is fixed by extending the format code slice length to the same as the value slice if there is a single format code provided.
    For 2318, it appears that Rust wants these values in binary format, so now we're returning them in binary format. I'm not sure why Rust doesn't accept text format like everything else does, but perhaps it's the default in Postgres so some ORMs only work with the default.
    It's worth mentioning that tests for these exist only within the client tests, as I could not replicate the errors within our standard testing framework. As mentioned with the binary format, it seems that Rust isn't as permissive as other ORMs, so we have to test with Rust specifically.

Closed Issues

• 2318: Extended protocol sends TIMESTAMPTZ and DATE as text instead of binary format
• 2316: Extended protocol INSERT/UPDATE/DELETE crashes with 'index out of range [1] with length 1'

Merged PRs

doltgresql

• 2308: Fixed issues 2299, 2301, and 2307
  This fixes:
  • #2299
  • #2307
    For 2299, we were only resolving the column type, and not the types within default values, so we ended up with an unresolved type that was persisted in the schema. Since we assume that every deserialized type is resolved, this would cause a panic when fields were missing.
    For 2307, we return the schema from the unanalyzed node obtained right after parsing the node. Our analysis passes handle type conversions between GMS types (with EXISTS being a node defined in GMS), and therefore we were returning a GMS type (which was being automatically converted to the wrong type). We now attempt an analysis pass before determining the schema, which should work in most cases, but may fail for some bindings. In those cases, we ignore the analysis error and return the original schema. This isn't a complete solution, but it at least gets us further in all other cases.
    This most likely fixes:
  • #2301
    2301 seems related to 2299. We don't appear to have any intrinsic problem with many CREATE TABLE statements within Rust's sqlx library (as used in the issue filed, 30 statements work fine), so it appears tied to the other issue. It's possible that there are further issues masked by the aforementioned one, but that cannot be determined without the user's migration data.
• 2291: add nightly ORM test

Closed Issues

• 2301: Panic when executing large CREATE TABLE migration via extended query protocol (SQLx)
• 2307: SELECT EXISTS(...) returns INT2 instead of BOOL, breaking typed PostgreSQL clients
• 2299: Panic: nil pointer dereference when using DEFAULT on ENUM column
• 2206: Support for LOWER() function (ERROR: at or near "(": syntax error)

Merged PRs

doltgresql

• 2289: Table types are handled by all relevant ALTER TABLE statements
  This adds the remaining ALTER TABLE statements that have special hooks for table types.
• 2272: Correctly support foreign key constraint violation checks during merge
  This PR is mostly tests. See dolthub/dolt#10418 for most of the logic.
• 2270: add information_schema.constraint_column_usage view
  Depends on dolthub/go-mysql-server#3416
  • adds information_schema.constraint_column_usage system view as table
  • fix parsing schema table with double quotes in functions
  • allow int to be casted in regtype as int4
• 2268: sqlAlchemy integration tests
  Integration tests for sqlAlchemy.
  Pretty similar to our standard dolt sqlAlchemy tests, with a couple of differences. I used the newer version of sqlAlchemy (2.0.46) and had to make some setup changes using features that were introduced in 2.x builds.
  psycopg2 seems to convert row results into python lists as well so the expected results had to be changed. I couldn't find confirmation but it seems like that would be the expected behavior (from what claude found).
• 2254: Issue #2197 Part 2
  This fixes the other issues brought up in:
  • #2197
    This also adds support for column access, which is used by the UPDATE statement when a new column is added.
• 2235: Fix windows binary name in build script
  I found that starting from version 0.54.4 (after #2061), binaries for Windows are built with empty names. This PR should fix that.
• 2231: Add client tests for psycopg2 python client
  Adds regression tests using psycopg2 for fix from #2216
• 2227: Fixed issue 2206
  This is a fix for:
  • #2206
    I've added a skipped test to verify that the syntax error portion is fixed, however we don't yet support expression index attributes, so the test overall fails. However, the issue only covers the syntax portion which we're addressing here.
• 2226: Bump Dolt version
  I accidentally merged #2196 while it contained a replace directive pointing to a fork of Dolt.
  This PR contains the actual dolt version bump.
• 2223: put pg_catalog implicitly on the search_path when it isn't there already
  pg_catalog is always implicitly the first element on the search_path, unless it's explicitly included elsewhere:
  https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-CATALOG
  We weren't implementing this behavior, causing us to fail to resolve ::regclass expressions in some cases with a "no such relation" error for all pg_catalog tables.
  This fix isn't complete, because the same logic should also apply to all table lookups. i.e. users should be able to create a table named pg_class, and what the name pg_class resolves to depends on whether the pg_catalog schema is explicitly present in the search_path and where it is relative to the user's other schemas. That's a pretty obscure bug and is left unaddressed for now.
  Fixes #2217
• 2219: additional tests for merging foreign keys and constraint violations
• 2216: Bug fix: don't send RowDescription message with each spooled batch of rows
  Ensure that RowDescription messages are sent only once, at the beginning of query results. Sending multiple times, at the start of each batch of spooled rows, breaks some clients, such as psycopg2.
  Note that this wasn't causing a problem in the other clients we use for testing. To properly regression test this, we should follow up with adding a test suite for psycopg2.
  Related to #2143
• 2198: Support for UDFs with RETURNS TABLE
• 2196: dolthub/dolt#10227: add dolt_status_ignored dolt system table

  Summary

  Add adapter and test expectations for the new dolt_status_ignored system table from dolthub/dolt#10227.
  This table provides the same information as dolt_status plus an additional ignored boolean column.

  Changes

  • Add DoltgresDoltStatusIgnoredTableAdapter in server/tables/dtables/status_ignored.go
  • Register adapter in server/tables/dtables/init.go
  • Add new table to test expectations in:
  • testing/go/pgcatalog_test.go
  • testing/go/sequences_test.go

  Dependencies

  • Requires dolthub/dolt#10227

Closed Issues

• 2197: Bugs with table composite types
• 1465: SQLAlchemy unable to fetch type information
• 2143: psycopg2 support
• 2217: filter with 'pg_class'::regclass produces relation "pg_class" does not exist error when Eval is called during simplifyFilters
• 2175: Error: only a single statement at a time is currently supported (transaction support?)
• 2174: Error: DISTINCT ON is not yet supported

Merged PRs

doltgresql

• 2199: Support multiple statements
  This allows for handling multiple statements within a single Query message. This fixes:
  • #2175
    This also tests the contents of the messages returned, which were verified using Wireshark on a local Postgres instance as the source-of-truth.
• 2190: Initial support for UDFs with RETURNS SETOF
  Adds support for user-defined functions that use RETURNS SETOF to return values of a composite type.
  Support for UDFs that use RETURNS TABLE to return an anonymous composite type will be added as a follow up.

Closed Issues

• 2174: Error: DISTINCT ON is not yet supported

Merged PRs

doltgresql

• 2181: Added DISTINCT ON handling
  This adds support for:
  • #2174
    Related PRs:
  • dolthub/vitess#446
  • dolthub/go-mysql-server#3369

Closed Issues