24841644892

Committed 23 Apr 2026 02:44PM UTC coverage: 85.279% (-4.0%) from 89.306%

Build # 24841644892

Build Type

push

gh-ci

Committed by

web-flow

Commit Message

feat: v1.11.0 PostgreSQL compatibility and SQL feature expansion (#2090)

* Add structured audit logging with immutable audit trail

Introduces a new --audit-log flag that records all gRPC operations as
structured JSON events in immudb's tamper-proof KV store. Events are
stored under the audit: key prefix in systemdb, queryable via Scan and
verifiable via VerifiableGet. An async buffered writer ensures minimal
latency impact. Configurable event filtering (all/write/admin) via
--audit-log-events flag.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

* Add PostgreSQL ORM compatibility layer and verification functions

Extend the pgsql wire protocol with immudb verification functions
(immudb_state, immudb_verify_row, immudb_verify_tx, immudb_history,
immudb_tx) accessible via standard SQL SELECT statements.

Add pg_catalog resolvers (pg_attribute, pg_index, pg_constraint,
pg_type, pg_settings, pg_description) and information_schema
resolvers (tables, columns, schemata, key_column_usage) to support
ORM introspection from Django, SQLAlchemy, GORM, and ActiveRecord.

Add PostgreSQL compatibility functions: current_database,
current_schema, current_user, format_type, pg_encoding_to_char,
pg_get_expr, pg_get_constraintdef, obj_description, col_description,
has_table_privilege, has_schema_privilege, and others.

Add SHOW statement emulation for common ORM config queries and
schema-qualified name stripping for information_schema and public
schema references.

* Implement EXISTS and IN subquery support in SQL engine

Replace the previously stubbed ExistsBoolExp and InSubQueryExp
implementations with working non-correlated subquery execution.

EXISTS subqueries resolve the inner SELECT and check if any rows
are returned. IN subqueries resolve the inner SELECT, iterate the
result set, and compare each value against the outer expression.
Both support NOT variants (NOT EXISTS, NOT IN).

Correlated subqueries (referencing outer query columns) ar... (continued)

Coverage Stats

7254 of 10471 new or added lines in 124 files covered. (69.28%)

115 existing lines in 18 files now uncovered.

44599 of 52298 relevant lines covered (85.28%)

127676.6 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

73.91

/pkg/pgsql/server/stmts_handler.go

/*
Copyright 2026 Codenotary Inc. All rights reserved.

SPDX-License-Identifier: BUSL-1.1
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://mariadb.com/bsl11/

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package server

import (
        "regexp"
        "strings"

        pserr "github.com/codenotary/immudb/pkg/pgsql/errors"
)

var (
        // Match only top-level PG session SET commands (SET timezone=..., SET
        // client_encoding=..., etc.), NOT the SET clause inside `UPDATE ... SET
        // col = val WHERE ...`. Without the start-of-statement anchor every
        // UPDATE silently matched this regex and was dropped by the blacklist,
        // so UPDATEs "succeeded" but never wrote anything.
        set           = regexp.MustCompile(`(?is)^\s*set\s+\S+`)
        selectVersion = regexp.MustCompile(`(?i)select\s+version\(\s*\)`)
        dealloc       = regexp.MustCompile(`(?i)deallocate\s+\"([^\"]+)\"`)

        // immudb verification functions exposed via PG wire protocol
        immudbStateRe     = regexp.MustCompile(`(?i)select\s+immudb_state\(\s*\)`)
        immudbVerifyRowRe = regexp.MustCompile(`(?i)select\s+immudb_verify_row\(\s*(.+)\s*\)`)
        immudbVerifyTxRe  = regexp.MustCompile(`(?i)select\s+immudb_verify_tx\(\s*(.+)\s*\)`)
        immudbHistoryRe   = regexp.MustCompile(`(?i)select\s+immudb_history\(\s*(.+)\s*\)`)
        immudbTxRe        = regexp.MustCompile(`(?i)select\s+immudb_tx\(\s*(.+)\s*\)`)

        // SHOW statement patterns for ORM compatibility
        showRe = regexp.MustCompile(`(?i)^\s*show\s+(\w+)\s*;?\s*$`)

        // regtype OID lookup: Rails (and other ORMs) resolve custom type OIDs with
        // queries like:   SELECT 'jsonb'::regtype::oid
        //                 SELECT 'decimal(19,4)'::regtype::oid
        // immudb's SQL engine does not implement regtype; without interception it
        // silently returns the literal string. Rails then stores the string into
        // its OID map and blows up later ("can't quote Hash") when it tries to
        // bind a Hash parameter against a column typed by that non-integer OID.
        regtypeOidRe = regexp.MustCompile(`(?i)^\s*select\s+'([^']+)'::regtype::oid\s*;?\s*$`)

        // Rails column introspection: drives ActiveRecord's knowledge of each
        // table's columns. Without real data here, models that declare
        // `enum :role, ...` fail with "Undeclared attribute type for enum 'role'".
        // Match the canonical Rails 7 form of this query (multi-line, joins on
        // pg_attrdef / pg_type / pg_collation, filter on attrelid = '"t"'::regclass).
        // Literal form — simple query protocol.
        pgAttributeForTableRe = regexp.MustCompile(`(?is)SELECT\s+a\.attname\s*,\s*format_type\s*\([^)]*\).*?FROM\s+pg_attribute\s+a.*?WHERE\s+a\.attrelid\s*=\s*'"?([^"']+)"?'::regclass`)

        // Parameterised form — Extended Query protocol (Rails's default).
        // Table name arrives as the bound value of $1 at Execute time; the
        // caller is responsible for substituting the parameter value for
        // the empty tableName slot in pgAttributeForTableCmd. Note the
        // `::regclass` cast is stripped by removePGCatalogReferences before
        // this regex runs, so we match on bare `$1` here.
        pgAttributeForTableParamRe = regexp.MustCompile(`(?is)SELECT\s+a\.attname\s*,\s*format_type\s*\([^)]*\).*?FROM\s+pg_attribute\s+a.*?WHERE\s+a\.attrelid\s*=\s*\$1\b`)

        // Rails's db:migrate takes a Postgres advisory lock to serialise
        // concurrent migrations. immudb has no advisory-lock subsystem; return
        // true unconditionally to let the single Rails container proceed.
        // Matches: SELECT pg_try_advisory_lock(...), pg_advisory_unlock(...)
        pgAdvisoryLockRe = regexp.MustCompile(`(?i)^\s*select\s+pg_(?:try_)?advisory_(?:lock|unlock)\s*\(`)

        // Blanket intercept for all PostgreSQL system catalog queries.
        // pgAdmin, DBeaver, ORMs etc. send dozens of these after connecting.
        // immudb can't execute them, so we return canned responses.
        pgSystemQueryRe = regexp.MustCompile(`(?i)pg_catalog\.|information_schema\.|pg_roles\b|pg_database\b|pg_settings\b|pg_extension\b|pg_tablespace\b|pg_replication_slots\b|pg_stat_activity\b|pg_authid\b|pg_shdescription\b|pg_description\b|pg_am\b|pg_stat_replication\b|pg_auth_members\b|pg_namespace\b|pg_class\b|pg_attribute\b|pg_type\b|pg_proc\b|pg_constraint\b|pg_index\b|pg_depend\b|pg_stat_user_tables\b|pg_statio_user_tables\b|pg_locks\b|pg_shadow\b|pg_user\b|pg_tables\b|pg_indexes\b|pg_views\b|pg_matviews\b|pg_sequences\b|current_setting\s*\(|has_database_privilege\s*\(|has_table_privilege\s*\(|has_schema_privilege\s*\(|pg_encoding_to_char\s*\(|pg_get_userbyid\s*\(`)

        // pgVirtualTableFromRe matches a query whose FROM clause names one of the
        // virtual catalog tables the SQL engine implements natively. Covers
        // everything registered by pkg/pgsql/sys/: the relation catalog
        // (pg_class, pg_attribute, pg_index, pg_namespace, pg_am, pg_type),
        // auxiliary catalogs (pg_database, pg_roles, pg_settings,
        // pg_constraint, pg_description, pg_proc), and the A5 compat views
        // (pg_tables, pg_indexes, pg_views, pg_sequences). A match flags
        // the query as a candidate for engine passthrough;
        // allPgRefsRegistered decides whether it actually qualifies.
        pgVirtualTableFromRe = regexp.MustCompile(`(?i)\bfrom\s+(?:pg_catalog\.)?(?:pg_class|pg_attribute|pg_index|pg_namespace|pg_am|pg_type|pg_settings|pg_constraint|pg_database|pg_roles|pg_description|pg_proc|pg_tables|pg_indexes|pg_views|pg_sequences|pg_attrdef|pg_collation|pg_inherits)\b`)

        // pgAnyTableRe finds every reference to a pg_ table in the statement.
        // Used by allPgRefsRegistered to decide whether a JOIN-containing
        // query only touches tables the engine can actually serve.
        pgAnyTableRe = regexp.MustCompile(`(?i)\b(pg_[a-z_]+)\b`)

        // registeredPgTables is the set of pg_catalog objects the SQL engine
        // can serve via the catalog-level system-table registry
        // (pkg/pgsql/sys/ — installed at engine-init time). Keep in sync
        // with pgVirtualTableFromRe and the init()s in pkg/pgsql/sys/.
        registeredPgTables = map[string]bool{
                // pkg/pgsql/sys/ — core relation catalog (A2)
                "pg_class":     true,
                "pg_attribute": true,
                "pg_index":     true,
                "pg_namespace": true,
                "pg_am":        true,
                "pg_type":      true,
                // pkg/pgsql/sys/ — auxiliary catalogs (A3)
                "pg_database":    true,
                "pg_roles":       true,
                "pg_settings":    true,
                "pg_constraint":  true,
                "pg_description": true,
                "pg_proc":        true,
                // pkg/pgsql/sys/ — compat views (A5)
                "pg_tables":    true,
                "pg_indexes":   true,
                "pg_views":     true,
                "pg_sequences": true,
                // pkg/pgsql/sys/ — empty stubs for psql \d subqueries (A6)
                // psql's \d <table> has correlated subqueries joining
                // pg_attrdef (column defaults) and pg_collation (collation
                // metadata). immudb has no natural row source for either,
                // but registering them empty keeps the engine-passthrough
                // dispatcher happy and the subqueries return NULL which is
                // what psql expects for "no default / no collation".
                "pg_attrdef":   true,
                "pg_collation": true,
                // pkg/pgsql/sys/ — empty stubs for psql \d tail sections.
                // Only pg_inherits is allowlisted — the psql probe for it is
                // a simple `SELECT count(*) FROM pg_inherits WHERE …` that
                // the engine handles cleanly. pg_policy / pg_publication /
                // pg_statistic_ext are intentionally NOT allowlisted: psql
                // emits `array(SELECT …)` subquery-array constructors and
                // UNION joins against unregistered pg_publication_rel in
                // those probes — neither is in immudb's grammar, so routing
                // to the engine returns a red "syntax error" psql. Better
                // to let them fall to the canned pgAdminProbe which returns
                // cosmetically-noisy but harmless rows (the "Policies:
                // POLICY ''" / "Publications:" sections at the bottom of
                // `\d` output). Upstream fix for that noise lives in the
                // engine grammar, not here.
                "pg_inherits": true,
        }

        // pgBuiltinFunctions are known pg_catalog-qualified functions built
        // into embedded/sql/functions.go. A reference to one of these does
        // not disqualify a query from engine passthrough even though it
        // matches pgAnyTableRe's identifier shape. Keep in sync with
        // embedded/sql/functions.go's builtinFunctions map.
        pgBuiltinFunctions = map[string]bool{
                "pg_table_is_visible":     true,
                "pg_get_userbyid":         true,
                "pg_get_expr":             true,
                "pg_get_constraintdef":    true,
                "pg_get_indexdef":         true,
                "pg_get_serial_sequence":  true,
                "pg_encoding_to_char":     true,
                "pg_total_relation_size":  true,
                "pg_relation_size":        true,
        }

        // xormColumnsRe matches XORM's distinctive column-introspection query
        // (SELECT column_name, column_default, is_nullable, … FROM pg_attribute
        // JOIN pg_class …). XORM selects info_schema-flavoured column names
        // against pg_attribute, which the SQL engine can't serve directly
        // (pg_attribute's own columns are attname/attnotnull/…). The handler
        // walks immudb's catalog and emits one row per column.
        xormColumnsRe = regexp.MustCompile(`(?is)\bcolumn_name\s*,\s*column_default\b.*\bfrom\s+pg_attribute\b`)

        // infoSchemaVirtualTableFromRe flags queries whose FROM clause
        // names one of the information_schema tables the SQL engine
        // implements natively via pkg/pgsql/sys/ (A4). When the dispatcher
        // spots one, the query is forwarded to the engine so JOINs and
        // complex WHEREs work correctly — the legacy infoSchemaColumnsRe
        // canned handler stays wired as a fallback for the tiny number of
        // information_schema views we haven't registered yet.
        infoSchemaVirtualTableFromRe = regexp.MustCompile(`(?is)\bfrom\s+information_schema[._](tables|columns|schemata|key_column_usage|table_constraints)\b`)
)

// CREATE INDEX / CREATE UNIQUE INDEX are SUPPORTED by immudb's engine
// (see embedded/sql/sql_grammar.y:390-408). PG-style statements arrive
// with an index name (`CREATE INDEX idx_foo ON t(c)`) which immudb's
// grammar rejects; the name-strip regex in query_machine.go's
// pgTypeReplacements handles that before the statement reaches the
// engine. Do NOT re-add them to this blacklist — blacklisting turns
// them into silent no-ops, which destroys uniqueness guarantees.
var pgUnsupportedDDL = regexp.MustCompile(`(?i)^\s*(CREATE\s+TYPE|CREATE\s+FUNCTION|CREATE\s+OR\s+REPLACE\s+FUNCTION|CREATE\s+TRIGGER|CREATE\s+RULE|CREATE\s+EXTENSION|CREATE\s+CAST|CREATE\s+OPERATOR|CREATE\s+AGGREGATE|CREATE\s+DOMAIN|ALTER\s+TABLE\s+\S+\s+OWNER\s+TO|ALTER\s+TABLE\s+\S+\s+ALTER\s+COLUMN|ALTER\s+TABLE\s+ONLY|ALTER\s+TABLE\s+\S+\s+DISABLE|ALTER\s+TABLE\s+\S+\s+ENABLE|ALTER\s+TABLE\s+\S+\s+ADD\s+(?:CONSTRAINT\s+\S+\s+)?FOREIGN\s+KEY|ALTER\s+TABLE\s+\S+\s+ADD\b|ALTER\s+SEQUENCE|ALTER\s+FUNCTION|ALTER\s+TYPE|GRANT\s|REVOKE\s|COMMENT\s+ON|SELECT\s+pg_catalog\.|SELECT\s+setval|SET\s+default_tablespace|SET\s+default_table_access_method|SET\s+transaction_timeout|DROP\s+INDEX)`)

// allPgRefsRegistered returns true when every pg_* identifier in
// statement is either a registered system table (registeredPgTables)
// or a registered built-in function (pgBuiltinFunctions). Used by the
// dispatcher to decide whether a query can safely execute against the
// SQL engine instead of being shunted to a canned handler.
//
// Strips single-quoted string literals before the scan so that
// identifiers appearing *inside* literals don't disqualify the query.
// This matters for psql's `\d`, which emits
//
//        WHERE n.nspname !~ '^pg_toast'
//
// The bare `pg_toast` inside the pattern literal would otherwise be
// treated as an unregistered table reference and shunt the whole
// query to the canned pgAdminProbe handler.
func allPgRefsRegistered(statement string) bool {
        stripped := stripSingleQuotedLiterals(statement)
        matches := pgAnyTableRe.FindAllStringSubmatch(stripped, -1)
        for _, m := range matches {
                name := strings.ToLower(m[1])
                // pg_catalog is a namespace qualifier, not a table — it gets
                // stripped by removePGCatalogReferences before the engine
                // sees the statement. Skip it here so queries that reference
                // `pg_catalog.pg_class` aren't wrongly disqualified.
                if name == "pg_catalog" {
                        continue
                }
                if registeredPgTables[name] || pgBuiltinFunctions[name] {
                        continue
                }
                return false
        }
        return true
}

// stripSingleQuotedLiterals replaces the body of every `'…'` string
// literal with spaces so identifier-matching regexes can't trip on
// substrings of user-supplied text. Preserves length so error
// positions downstream (if any) still point at the right character
// offsets. Escaped `''` inside a literal is handled.
func stripSingleQuotedLiterals(s string) string {
        b := make([]byte, len(s))
        inStr := false
        for i := 0; i < len(s); i++ {
                c := s[i]
                if c == '\'' {
                        b[i] = c
                        if inStr && i+1 < len(s) && s[i+1] == '\'' {
                                // Escaped '' — stay inside the literal, copy the second ' too.
                                b[i+1] = '\''
                                i++
                                continue
                        }
                        inStr = !inStr
                        continue
                }
                if inStr {
                        b[i] = ' '
                } else {
                        b[i] = c
                }
        }
        return string(b)
}

func (s *session) isInBlackList(statement string) bool {
        if set.MatchString(statement) {
                return true
        }

        if statement == ";" {
                return true
        }

        // Silently ignore unsupported PostgreSQL DDL statements
        if pgUnsupportedDDL.MatchString(statement) {
                return true
        }

        return false
}

func (s *session) isEmulableInternally(statement string) interface{} {
        if selectVersion.MatchString(statement) {
                return &version{}
        }

        if dealloc.MatchString(statement) {
                matches := dealloc.FindStringSubmatch(statement)
                if len(matches) == 2 {
                        return &deallocate{plan: matches[1]}
                }
        }

        if immudbStateRe.MatchString(statement) {
                return &immudbStateCmd{}
        }

        if m := immudbVerifyRowRe.FindStringSubmatch(statement); len(m) == 2 {
                return &immudbVerifyRowCmd{args: m[1]}
        }

        if m := immudbVerifyTxRe.FindStringSubmatch(statement); len(m) == 2 {
                return &immudbVerifyTxCmd{args: m[1]}
        }

        if m := immudbHistoryRe.FindStringSubmatch(statement); len(m) == 2 {
                return &immudbHistoryCmd{args: m[1]}
        }

        if m := immudbTxRe.FindStringSubmatch(statement); len(m) == 2 {
                return &immudbTxCmd{args: m[1]}
        }

        if m := showRe.FindStringSubmatch(statement); len(m) == 2 {
                return &showCmd{param: m[1]}
        }

        if m := regtypeOidRe.FindStringSubmatch(statement); len(m) == 2 {
                return &regtypeOidCmd{typeName: m[1]}
        }

        if m := pgAttributeForTableRe.FindStringSubmatch(statement); len(m) == 2 {
                s.log.Infof("pgcompat: pg_attribute intercept for table %q", m[1])
                return &pgAttributeForTableCmd{tableName: m[1]}
        }

        if pgAttributeForTableParamRe.MatchString(statement) {
                // Parameterised form: table name is bound via $1 at Execute time.
                // Signal with an empty tableName — the caller pulls the value out
                // of the bind parameters and passes it in.
                s.log.Infof("pgcompat: pg_attribute intercept (parameterised, $1)")
                return &pgAttributeForTableCmd{tableName: ""}
        }

        if pgAdvisoryLockRe.MatchString(statement) {
                return &pgAdvisoryLockCmd{}
        }

        // XORM column-introspection: the one ORM shape where the query
        // selects info_schema-flavoured column names (column_name,
        // column_default, is_nullable, …) but FROMs pg_attribute. The
        // engine would reject this because pg_attribute doesn't expose
        // those column names — the canned handler synthesises a result set
        // that matches what XORM expects. Must run BEFORE the engine
        // passthrough below or the dispatcher sends it to the engine and
        // the client gets "column does not exist".
        if xormColumnsRe.MatchString(statement) {
                return &xormColumnsCmd{sql: statement}
        }

        // Engine passthrough for queries targeting registered pg_catalog
        // or information_schema system tables. The A2-A5 sys/ tables cover
        // every common ORM / psql introspection shape; JOINs, ORDER BY,
        // complex WHERE clauses, and sub-queries all work because the SQL
        // engine handles them against the live catalog.
        if pgVirtualTableFromRe.MatchString(statement) && allPgRefsRegistered(statement) {
                return nil
        }
        if infoSchemaVirtualTableFromRe.MatchString(statement) && allPgRefsRegistered(statement) {
                return nil
        }

        // Safety net for pg_catalog / information_schema probes that
        // reference tables we haven't registered (pg_extension,
        // pg_tablespace, pg_replication_slots, pg_locks, …). Returns
        // a canned one-row response with column names extracted from the
        // query's SELECT list — avoids "table does not exist" errors on
        // clients that probe every possible system view at connect time.
        if pgSystemQueryRe.MatchString(statement) {
                return &pgAdminProbe{sql: statement}
        }

        return nil
}

func (s *session) tryToHandleInternally(command interface{}) error {
        switch cmd := command.(type) {
        case *version:
                if err := s.writeVersionInfo(); err != nil {
                        return err
                }
        case *deallocate:
                delete(s.statements, cmd.plan)
                return nil
        case *immudbStateCmd:
                return s.immudbState()
        case *immudbVerifyRowCmd:
                return s.immudbVerifyRow(cmd.args)
        case *immudbVerifyTxCmd:
                return s.immudbVerifyTx(cmd.args)
        case *immudbHistoryCmd:
                return s.immudbHistory(cmd.args)
        case *immudbTxCmd:
                return s.immudbTxByID(cmd.args)
        case *showCmd:
                return s.handleShow(cmd.param)
        case *regtypeOidCmd:
                return s.handleRegtypeOid(cmd.typeName)
        case *pgAttributeForTableCmd:
                // Simple query path — no Describe beforehand, so always emit RowDescription.
                return s.handlePgAttributeForTable(cmd.tableName, false)
        case *pgAdvisoryLockCmd:
                return s.handlePgAdvisoryLock()
        case *pgAdminProbe:
                return s.handlePgSystemQuery(cmd.sql)
        case *xormColumnsCmd:
                return s.handleXormColumnsQuery(cmd.sql, nil, false)
        default:
                return pserr.ErrMessageCannotBeHandledInternally
        }
        return nil
}

type version struct{}

type deallocate struct {
        plan string
}

type immudbStateCmd struct{}

type immudbVerifyRowCmd struct {
        args string
}

type immudbVerifyTxCmd struct {
        args string
}

type immudbHistoryCmd struct {
        args string
}

type immudbTxCmd struct {
        args string
}

type regtypeOidCmd struct {
        typeName string
}

type pgAttributeForTableCmd struct {
        tableName string
}

type pgAdvisoryLockCmd struct{}

type showCmd struct {
        param string
}

type pgAdminProbe struct {
        sql string
}

// xormColumnsCmd is XORM's column-introspection query: a multi-table
// JOIN of pg_attribute / pg_class / pg_type / information_schema.columns
// where the SELECT list uses info_schema-flavoured column names
// (column_name, column_default, is_nullable, …) against pg_attribute.
// The SQL engine can't serve this shape directly — pg_attribute's own
// columns are attname / attnotnull / … — so the handler synthesises
// one row per column with the fields XORM expects (column_name,
// column_default, is_nullable, data_type, character_maximum_length,
// description, primarykey, uniquekey).
type xormColumnsCmd struct {
        sql string
}

1	/*
2	Copyright 2026 Codenotary Inc. All rights reserved.
3
4	SPDX-License-Identifier: BUSL-1.1
5	you may not use this file except in compliance with the License.
6	You may obtain a copy of the License at
7
8	https://mariadb.com/bsl11/
9
10	Unless required by applicable law or agreed to in writing, software
11	distributed under the License is distributed on an "AS IS" BASIS,
12	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13	See the License for the specific language governing permissions and
14	limitations under the License.
15	*/
16
17	package server
18
19	import (
20	"regexp"
21	"strings"
22
23	pserr "github.com/codenotary/immudb/pkg/pgsql/errors"
24	)
25
26	var (
27	// Match only top-level PG session SET commands (SET timezone=..., SET
28	// client_encoding=..., etc.), NOT the SET clause inside `UPDATE ... SET
29	// col = val WHERE ...`. Without the start-of-statement anchor every
30	// UPDATE silently matched this regex and was dropped by the blacklist,
31	// so UPDATEs "succeeded" but never wrote anything.
32	set = regexp.MustCompile(`(?is)^\s*set\s+\S+`)
33	selectVersion = regexp.MustCompile(`(?i)select\s+version\(\s*\)`)
34	dealloc = regexp.MustCompile(`(?i)deallocate\s+\"([^\"]+)\"`)
35
36	// immudb verification functions exposed via PG wire protocol
37	immudbStateRe = regexp.MustCompile(`(?i)select\s+immudb_state\(\s*\)`)
38	immudbVerifyRowRe = regexp.MustCompile(`(?i)select\s+immudb_verify_row\(\s(.+)\s\)`)
39	immudbVerifyTxRe = regexp.MustCompile(`(?i)select\s+immudb_verify_tx\(\s(.+)\s\)`)
40	immudbHistoryRe = regexp.MustCompile(`(?i)select\s+immudb_history\(\s(.+)\s\)`)
41	immudbTxRe = regexp.MustCompile(`(?i)select\s+immudb_tx\(\s(.+)\s\)`)
42
43	// SHOW statement patterns for ORM compatibility
44	showRe = regexp.MustCompile(`(?i)^\sshow\s+(\w+)\s;?\s*$`)
45
46	// regtype OID lookup: Rails (and other ORMs) resolve custom type OIDs with
47	// queries like: SELECT 'jsonb'::regtype::oid
48	// SELECT 'decimal(19,4)'::regtype::oid
49	// immudb's SQL engine does not implement regtype; without interception it
50	// silently returns the literal string. Rails then stores the string into
51	// its OID map and blows up later ("can't quote Hash") when it tries to
52	// bind a Hash parameter against a column typed by that non-integer OID.
53	regtypeOidRe = regexp.MustCompile(`(?i)^\sselect\s+'([^']+)'::regtype::oid\s;?\s*$`)
54
55	// Rails column introspection: drives ActiveRecord's knowledge of each
56	// table's columns. Without real data here, models that declare
57	// `enum :role, ...` fail with "Undeclared attribute type for enum 'role'".
58	// Match the canonical Rails 7 form of this query (multi-line, joins on
59	// pg_attrdef / pg_type / pg_collation, filter on attrelid = '"t"'::regclass).
60	// Literal form — simple query protocol.
61	pgAttributeForTableRe = regexp.MustCompile(`(?is)SELECT\s+a\.attname\s,\sformat_type\s\([^)]\).?FROM\s+pg_attribute\s+a.?WHERE\s+a\.attrelid\s=\s'"?([^"']+)"?'::regclass`)
62
63	// Parameterised form — Extended Query protocol (Rails's default).
64	// Table name arrives as the bound value of $1 at Execute time; the
65	// caller is responsible for substituting the parameter value for
66	// the empty tableName slot in pgAttributeForTableCmd. Note the
67	// `::regclass` cast is stripped by removePGCatalogReferences before
68	// this regex runs, so we match on bare `$1` here.
69	pgAttributeForTableParamRe = regexp.MustCompile(`(?is)SELECT\s+a\.attname\s,\sformat_type\s\([^)]\).?FROM\s+pg_attribute\s+a.?WHERE\s+a\.attrelid\s=\s\$1\b`)
70
71	// Rails's db:migrate takes a Postgres advisory lock to serialise
72	// concurrent migrations. immudb has no advisory-lock subsystem; return
73	// true unconditionally to let the single Rails container proceed.
74	// Matches: SELECT pg_try_advisory_lock(...), pg_advisory_unlock(...)
75	pgAdvisoryLockRe = regexp.MustCompile(`(?i)^\sselect\s+pg_(?:try_)?advisory_(?:lock\|unlock)\s\(`)
76
77	// Blanket intercept for all PostgreSQL system catalog queries.
78	// pgAdmin, DBeaver, ORMs etc. send dozens of these after connecting.
79	// immudb can't execute them, so we return canned responses.
80	pgSystemQueryRe = regexp.MustCompile(`(?i)pg_catalog\.\|information_schema\.\|pg_roles\b\|pg_database\b\|pg_settings\b\|pg_extension\b\|pg_tablespace\b\|pg_replication_slots\b\|pg_stat_activity\b\|pg_authid\b\|pg_shdescription\b\|pg_description\b\|pg_am\b\|pg_stat_replication\b\|pg_auth_members\b\|pg_namespace\b\|pg_class\b\|pg_attribute\b\|pg_type\b\|pg_proc\b\|pg_constraint\b\|pg_index\b\|pg_depend\b\|pg_stat_user_tables\b\|pg_statio_user_tables\b\|pg_locks\b\|pg_shadow\b\|pg_user\b\|pg_tables\b\|pg_indexes\b\|pg_views\b\|pg_matviews\b\|pg_sequences\b\|current_setting\s\(\|has_database_privilege\s\(\|has_table_privilege\s\(\|has_schema_privilege\s\(\|pg_encoding_to_char\s\(\|pg_get_userbyid\s\(`)
81
82	// pgVirtualTableFromRe matches a query whose FROM clause names one of the
83	// virtual catalog tables the SQL engine implements natively. Covers
84	// everything registered by pkg/pgsql/sys/: the relation catalog
85	// (pg_class, pg_attribute, pg_index, pg_namespace, pg_am, pg_type),
86	// auxiliary catalogs (pg_database, pg_roles, pg_settings,
87	// pg_constraint, pg_description, pg_proc), and the A5 compat views
88	// (pg_tables, pg_indexes, pg_views, pg_sequences). A match flags
89	// the query as a candidate for engine passthrough;
90	// allPgRefsRegistered decides whether it actually qualifies.
91	pgVirtualTableFromRe = regexp.MustCompile(`(?i)\bfrom\s+(?:pg_catalog\.)?(?:pg_class\|pg_attribute\|pg_index\|pg_namespace\|pg_am\|pg_type\|pg_settings\|pg_constraint\|pg_database\|pg_roles\|pg_description\|pg_proc\|pg_tables\|pg_indexes\|pg_views\|pg_sequences\|pg_attrdef\|pg_collation\|pg_inherits)\b`)
92
93	// pgAnyTableRe finds every reference to a pg_ table in the statement.
94	// Used by allPgRefsRegistered to decide whether a JOIN-containing
95	// query only touches tables the engine can actually serve.
96	pgAnyTableRe = regexp.MustCompile(`(?i)\b(pg_[a-z_]+)\b`)
97
98	// registeredPgTables is the set of pg_catalog objects the SQL engine
99	// can serve via the catalog-level system-table registry
100	// (pkg/pgsql/sys/ — installed at engine-init time). Keep in sync
101	// with pgVirtualTableFromRe and the init()s in pkg/pgsql/sys/.
102	registeredPgTables = map[string]bool{
103	// pkg/pgsql/sys/ — core relation catalog (A2)
104	"pg_class": true,
105	"pg_attribute": true,
106	"pg_index": true,
107	"pg_namespace": true,
108	"pg_am": true,
109	"pg_type": true,
110	// pkg/pgsql/sys/ — auxiliary catalogs (A3)
111	"pg_database": true,
112	"pg_roles": true,
113	"pg_settings": true,
114	"pg_constraint": true,
115	"pg_description": true,
116	"pg_proc": true,
117	// pkg/pgsql/sys/ — compat views (A5)
118	"pg_tables": true,
119	"pg_indexes": true,
120	"pg_views": true,
121	"pg_sequences": true,
122	// pkg/pgsql/sys/ — empty stubs for psql \d subqueries (A6)
123	// psql's \d <table> has correlated subqueries joining
124	// pg_attrdef (column defaults) and pg_collation (collation
125	// metadata). immudb has no natural row source for either,
126	// but registering them empty keeps the engine-passthrough
127	// dispatcher happy and the subqueries return NULL which is
128	// what psql expects for "no default / no collation".
129	"pg_attrdef": true,
130	"pg_collation": true,
131	// pkg/pgsql/sys/ — empty stubs for psql \d tail sections.
132	// Only pg_inherits is allowlisted — the psql probe for it is
133	// a simple `SELECT count(*) FROM pg_inherits WHERE …` that
134	// the engine handles cleanly. pg_policy / pg_publication /
135	// pg_statistic_ext are intentionally NOT allowlisted: psql
136	// emits `array(SELECT …)` subquery-array constructors and
137	// UNION joins against unregistered pg_publication_rel in
138	// those probes — neither is in immudb's grammar, so routing
139	// to the engine returns a red "syntax error" psql. Better
140	// to let them fall to the canned pgAdminProbe which returns
141	// cosmetically-noisy but harmless rows (the "Policies:
142	// POLICY ''" / "Publications:" sections at the bottom of
143	// `\d` output). Upstream fix for that noise lives in the
144	// engine grammar, not here.
145	"pg_inherits": true,
146	}
147
148	// pgBuiltinFunctions are known pg_catalog-qualified functions built
149	// into embedded/sql/functions.go. A reference to one of these does
150	// not disqualify a query from engine passthrough even though it
151	// matches pgAnyTableRe's identifier shape. Keep in sync with
152	// embedded/sql/functions.go's builtinFunctions map.
153	pgBuiltinFunctions = map[string]bool{
154	"pg_table_is_visible": true,
155	"pg_get_userbyid": true,
156	"pg_get_expr": true,
157	"pg_get_constraintdef": true,
158	"pg_get_indexdef": true,
159	"pg_get_serial_sequence": true,
160	"pg_encoding_to_char": true,
161	"pg_total_relation_size": true,
162	"pg_relation_size": true,
163	}
164
165	// xormColumnsRe matches XORM's distinctive column-introspection query
166	// (SELECT column_name, column_default, is_nullable, … FROM pg_attribute
167	// JOIN pg_class …). XORM selects info_schema-flavoured column names
168	// against pg_attribute, which the SQL engine can't serve directly
169	// (pg_attribute's own columns are attname/attnotnull/…). The handler
170	// walks immudb's catalog and emits one row per column.
171	xormColumnsRe = regexp.MustCompile(`(?is)\bcolumn_name\s,\scolumn_default\b.*\bfrom\s+pg_attribute\b`)
172
173	// infoSchemaVirtualTableFromRe flags queries whose FROM clause
174	// names one of the information_schema tables the SQL engine
175	// implements natively via pkg/pgsql/sys/ (A4). When the dispatcher
176	// spots one, the query is forwarded to the engine so JOINs and
177	// complex WHEREs work correctly — the legacy infoSchemaColumnsRe
178	// canned handler stays wired as a fallback for the tiny number of
179	// information_schema views we haven't registered yet.
180	infoSchemaVirtualTableFromRe = regexp.MustCompile(`(?is)\bfrom\s+information_schema[._](tables\|columns\|schemata\|key_column_usage\|table_constraints)\b`)
181	)
182
183	// CREATE INDEX / CREATE UNIQUE INDEX are SUPPORTED by immudb's engine
184	// (see embedded/sql/sql_grammar.y:390-408). PG-style statements arrive
185	// with an index name (`CREATE INDEX idx_foo ON t(c)`) which immudb's
186	// grammar rejects; the name-strip regex in query_machine.go's
187	// pgTypeReplacements handles that before the statement reaches the
188	// engine. Do NOT re-add them to this blacklist — blacklisting turns
189	// them into silent no-ops, which destroys uniqueness guarantees.
190	var pgUnsupportedDDL = regexp.MustCompile(`(?i)^\s*(CREATE\s+TYPE\|CREATE\s+FUNCTION\|CREATE\s+OR\s+REPLACE\s+FUNCTION\|CREATE\s+TRIGGER\|CREATE\s+RULE\|CREATE\s+EXTENSION\|CREATE\s+CAST\|CREATE\s+OPERATOR\|CREATE\s+AGGREGATE\|CREATE\s+DOMAIN\|ALTER\s+TABLE\s+\S+\s+OWNER\s+TO\|ALTER\s+TABLE\s+\S+\s+ALTER\s+COLUMN\|ALTER\s+TABLE\s+ONLY\|ALTER\s+TABLE\s+\S+\s+DISABLE\|ALTER\s+TABLE\s+\S+\s+ENABLE\|ALTER\s+TABLE\s+\S+\s+ADD\s+(?:CONSTRAINT\s+\S+\s+)?FOREIGN\s+KEY\|ALTER\s+TABLE\s+\S+\s+ADD\b\|ALTER\s+SEQUENCE\|ALTER\s+FUNCTION\|ALTER\s+TYPE\|GRANT\s\|REVOKE\s\|COMMENT\s+ON\|SELECT\s+pg_catalog\.\|SELECT\s+setval\|SET\s+default_tablespace\|SET\s+default_table_access_method\|SET\s+transaction_timeout\|DROP\s+INDEX)`)
191
192	// allPgRefsRegistered returns true when every pg_* identifier in
193	// statement is either a registered system table (registeredPgTables)
194	// or a registered built-in function (pgBuiltinFunctions). Used by the
195	// dispatcher to decide whether a query can safely execute against the
196	// SQL engine instead of being shunted to a canned handler.
197	//
198	// Strips single-quoted string literals before the scan so that
199	// identifiers appearing inside literals don't disqualify the query.
200	// This matters for psql's `\d`, which emits
201	//
202	// WHERE n.nspname !~ '^pg_toast'
203	//
204	// The bare `pg_toast` inside the pattern literal would otherwise be
205	// treated as an unregistered table reference and shunt the whole
206	// query to the canned pgAdminProbe handler.
207	func allPgRefsRegistered(statement string) bool {	97✔
208	stripped := stripSingleQuotedLiterals(statement)	97✔
209	matches := pgAnyTableRe.FindAllStringSubmatch(stripped, -1)	97✔
210	for _, m := range matches {	224✔
211	name := strings.ToLower(m[1])	127✔
212	// pg_catalog is a namespace qualifier, not a table — it gets	127✔
213	// stripped by removePGCatalogReferences before the engine	127✔
214	// sees the statement. Skip it here so queries that reference	127✔
215	// `pg_catalog.pg_class` aren't wrongly disqualified.	127✔
216	if name == "pg_catalog" {	167✔
217	continue	40✔
218	}
219	if registeredPgTables[name] \|\| pgBuiltinFunctions[name] {	173✔
220	continue	86✔
221	}
222	return false	1✔
223	}
224	return true	96✔
225	}
226
227	// stripSingleQuotedLiterals replaces the body of every `'…'` string
228	// literal with spaces so identifier-matching regexes can't trip on
229	// substrings of user-supplied text. Preserves length so error
230	// positions downstream (if any) still point at the right character
231	// offsets. Escaped `''` inside a literal is handled.
232	func stripSingleQuotedLiterals(s string) string {	97✔
233	b := make([]byte, len(s))	97✔
234	inStr := false	97✔
235	for i := 0; i < len(s); i++ {	8,929✔
236	c := s[i]	8,832✔
237	if c == '\'' {	8,976✔
238	b[i] = c	144✔
239	if inStr && i+1 < len(s) && s[i+1] == '\'' {	144✔
NEW 240	// Escaped '' — stay inside the literal, copy the second ' too.	×
NEW 241	b[i+1] = '\''	×
NEW 242	i++	×
NEW 243	continue	×
244	}
245	inStr = !inStr	144✔
246	continue	144✔
247	}
248	if inStr {	9,277✔
249	b[i] = ' '	589✔
250	} else {	8,688✔
251	b[i] = c	8,099✔
252	}	8,099✔
253	}
254	return string(b)	97✔
255	}
256
257	func (s *session) isInBlackList(statement string) bool {	549✔
258	if set.MatchString(statement) {	567✔
259	return true	18✔
260	}	18✔
261
262	if statement == ";" {	533✔
263	return true	2✔
264	}	2✔
265
266	// Silently ignore unsupported PostgreSQL DDL statements
267	if pgUnsupportedDDL.MatchString(statement) {	531✔
268	return true	2✔
269	}	2✔
270
271	return false	527✔
272	}
273
274	func (s *session) isEmulableInternally(statement string) interface{} {	736✔
275	if selectVersion.MatchString(statement) {	740✔
276	return &version{}	4✔
277	}	4✔
278
279	if dealloc.MatchString(statement) {	733✔
280	matches := dealloc.FindStringSubmatch(statement)	1✔
281	if len(matches) == 2 {	2✔
282	return &deallocate{plan: matches[1]}	1✔
283	}	1✔
284	}
285
286	if immudbStateRe.MatchString(statement) {	736✔
287	return &immudbStateCmd{}	5✔
288	}	5✔
289
290	if m := immudbVerifyRowRe.FindStringSubmatch(statement); len(m) == 2 {	729✔
291	return &immudbVerifyRowCmd{args: m[1]}	3✔
292	}	3✔
293
294	if m := immudbVerifyTxRe.FindStringSubmatch(statement); len(m) == 2 {	725✔
295	return &immudbVerifyTxCmd{args: m[1]}	2✔
296	}	2✔
297
298	if m := immudbHistoryRe.FindStringSubmatch(statement); len(m) == 2 {	722✔
299	return &immudbHistoryCmd{args: m[1]}	1✔
300	}	1✔
301
302	if m := immudbTxRe.FindStringSubmatch(statement); len(m) == 2 {	721✔
303	return &immudbTxCmd{args: m[1]}	1✔
304	}	1✔
305
306	if m := showRe.FindStringSubmatch(statement); len(m) == 2 {	727✔
307	return &showCmd{param: m[1]}	8✔
308	}	8✔
309
310	if m := regtypeOidRe.FindStringSubmatch(statement); len(m) == 2 {	711✔
NEW 311	return &regtypeOidCmd{typeName: m[1]}	×
NEW 312	}	×
313
314	if m := pgAttributeForTableRe.FindStringSubmatch(statement); len(m) == 2 {	711✔
NEW 315	s.log.Infof("pgcompat: pg_attribute intercept for table %q", m[1])	×
NEW 316	return &pgAttributeForTableCmd{tableName: m[1]}	×
NEW 317	}	×
318
319	if pgAttributeForTableParamRe.MatchString(statement) {	711✔
NEW 320	// Parameterised form: table name is bound via $1 at Execute time.	×
NEW 321	// Signal with an empty tableName — the caller pulls the value out	×
NEW 322	// of the bind parameters and passes it in.	×
NEW 323	s.log.Infof("pgcompat: pg_attribute intercept (parameterised, $1)")	×
NEW 324	return &pgAttributeForTableCmd{tableName: ""}	×
NEW 325	}	×
326
327	if pgAdvisoryLockRe.MatchString(statement) {	717✔
328	return &pgAdvisoryLockCmd{}	6✔
329	}	6✔
330
331	// XORM column-introspection: the one ORM shape where the query
332	// selects info_schema-flavoured column names (column_name,
333	// column_default, is_nullable, …) but FROMs pg_attribute. The
334	// engine would reject this because pg_attribute doesn't expose
335	// those column names — the canned handler synthesises a result set
336	// that matches what XORM expects. Must run BEFORE the engine
337	// passthrough below or the dispatcher sends it to the engine and
338	// the client gets "column does not exist".
339	if xormColumnsRe.MatchString(statement) {	708✔
340	return &xormColumnsCmd{sql: statement}	3✔
341	}	3✔
342
343	// Engine passthrough for queries targeting registered pg_catalog
344	// or information_schema system tables. The A2-A5 sys/ tables cover
345	// every common ORM / psql introspection shape; JOINs, ORDER BY,
346	// complex WHERE clauses, and sub-queries all work because the SQL
347	// engine handles them against the live catalog.
348	if pgVirtualTableFromRe.MatchString(statement) && allPgRefsRegistered(statement) {	759✔
349	return nil	57✔
350	}	57✔
351	if infoSchemaVirtualTableFromRe.MatchString(statement) && allPgRefsRegistered(statement) {	675✔
352	return nil	30✔
353	}	30✔
354
355	// Safety net for pg_catalog / information_schema probes that
356	// reference tables we haven't registered (pg_extension,
357	// pg_tablespace, pg_replication_slots, pg_locks, …). Returns
358	// a canned one-row response with column names extracted from the
359	// query's SELECT list — avoids "table does not exist" errors on
360	// clients that probe every possible system view at connect time.
361	if pgSystemQueryRe.MatchString(statement) {	615✔
NEW 362	return &pgAdminProbe{sql: statement}	×
NEW 363	}	×
364
365	return nil	615✔
366	}
367
368	func (s *session) tryToHandleInternally(command interface{}) error {	11✔
369	switch cmd := command.(type) {	11✔
370	case *version:	3✔
371	if err := s.writeVersionInfo(); err != nil {	4✔
372	return err	1✔
373	}	1✔
374	case *deallocate:	1✔
375	delete(s.statements, cmd.plan)	1✔
376	return nil	1✔
377	case *immudbStateCmd:	1✔
378	return s.immudbState()	1✔
NEW 379	case *immudbVerifyRowCmd:	×
NEW 380	return s.immudbVerifyRow(cmd.args)	×
NEW 381	case *immudbVerifyTxCmd:	×
NEW 382	return s.immudbVerifyTx(cmd.args)	×
NEW 383	case *immudbHistoryCmd:	×
NEW 384	return s.immudbHistory(cmd.args)	×
NEW 385	case *immudbTxCmd:	×
NEW 386	return s.immudbTxByID(cmd.args)	×
387	case *showCmd:	4✔
388	return s.handleShow(cmd.param)	4✔
NEW 389	case *regtypeOidCmd:	×
NEW 390	return s.handleRegtypeOid(cmd.typeName)	×
NEW 391	case *pgAttributeForTableCmd:	×
NEW 392	// Simple query path — no Describe beforehand, so always emit RowDescription.	×
NEW 393	return s.handlePgAttributeForTable(cmd.tableName, false)	×
394	case *pgAdvisoryLockCmd:	2✔
395	return s.handlePgAdvisoryLock()	2✔
NEW 396	case *pgAdminProbe:	×
NEW 397	return s.handlePgSystemQuery(cmd.sql)	×
NEW 398	case *xormColumnsCmd:	×
NEW 399	return s.handleXormColumnsQuery(cmd.sql, nil, false)	×
400	default:	×
401	return pserr.ErrMessageCannotBeHandledInternally	×
402	}
403	return nil	2✔
404	}
405
406	type version struct{}
407
408	type deallocate struct {
409	plan string
410	}
411
412	type immudbStateCmd struct{}
413
414	type immudbVerifyRowCmd struct {
415	args string
416	}
417
418	type immudbVerifyTxCmd struct {
419	args string
420	}
421
422	type immudbHistoryCmd struct {
423	args string
424	}
425
426	type immudbTxCmd struct {
427	args string
428	}
429
430	type regtypeOidCmd struct {
431	typeName string
432	}
433
434	type pgAttributeForTableCmd struct {
435	tableName string
436	}
437
438	type pgAdvisoryLockCmd struct{}
439
440	type showCmd struct {
441	param string
442	}
443
444	type pgAdminProbe struct {
445	sql string
446	}
447
448	// xormColumnsCmd is XORM's column-introspection query: a multi-table
449	// JOIN of pg_attribute / pg_class / pg_type / information_schema.columns
450	// where the SELECT list uses info_schema-flavoured column names
451	// (column_name, column_default, is_nullable, …) against pg_attribute.
452	// The SQL engine can't serve this shape directly — pg_attribute's own
453	// columns are attname / attnotnull / … — so the handler synthesises
454	// one row per column with the fields XORM expects (column_name,
455	// column_default, is_nullable, data_type, character_maximum_length,
456	// description, primarykey, uniquekey).
457	type xormColumnsCmd struct {
458	sql string
459	}

codenotary / immudb / 24841644892

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous