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

94.87

/pkg/pgsql/server/types.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 (
        "encoding/binary"
        "encoding/hex"
        "fmt"
        "strconv"
        "strings"
        "time"

        "github.com/codenotary/immudb/embedded/sql"
        "github.com/codenotary/immudb/pkg/api/schema"
)

// pgTextBool mirrors Postgres' text-format boolean accepted values:
// t/true/y/yes/on/1 for true; f/false/n/no/off/0 for false.
// Rails' pg gem sends "t"/"f" by default, not "true"/"false".
func pgTextBool(s string) (bool, bool) {
        switch strings.ToLower(strings.TrimSpace(s)) {
        case "t", "true", "y", "yes", "on", "1":
                return true, true
        case "f", "false", "n", "no", "off", "0":
                return false, true
        }
        return false, false
}

// pgTextTimestamp parses the common text formats Postgres/Rails send
// for TIMESTAMP binds: "YYYY-MM-DD HH:MM:SS[.fff][Z|±HH:MM]" or RFC3339.
var pgTimestampLayouts = []string{
        "2006-01-02 15:04:05.999999999",
        "2006-01-02 15:04:05.999999",
        "2006-01-02 15:04:05.999",
        "2006-01-02 15:04:05",
        "2006-01-02 15:04:05.999999-07",
        "2006-01-02 15:04:05-07",
        time.RFC3339Nano,
        time.RFC3339,
        "2006-01-02",
}

func pgTextTimestamp(s string) (time.Time, bool) {
        s = strings.TrimSpace(s)
        for _, layout := range pgTimestampLayouts {
                if t, err := time.Parse(layout, s); err == nil {
                        return t, true
                }
        }
        return time.Time{}, false
}

func buildNamedParams(paramsType []sql.ColDescriptor, paramsVal []interface{}) ([]*schema.NamedParam, error) {
        pMap := make(map[string]interface{})
        for index, param := range paramsType {
                name := param.Column

                val := paramsVal[index]
                // NULL parameter (Bind protocol pLen == -1).
                if val == nil {
                        pMap[name] = nil
                        continue
                }
                // text param
                if p, ok := val.(string); ok {
                        switch param.Type {

                        case sql.IntegerType:
                                int, err := strconv.Atoi(p)
                                if err != nil {
                                        return nil, err
                                }
                                pMap[name] = int64(int)
                        case sql.VarcharType:
                                pMap[name] = p
                        case sql.BooleanType:
                                // Postgres text-format booleans: "t"/"f" (Rails),
                                // plus the long forms. Anything else is an error,
                                // not a silent false, so we don't round-trip a
                                // corrupted bool (the old `p == "true"` check stored
                                // Rails' "t" as false).
                                if b, ok := pgTextBool(p); ok {
                                        pMap[name] = b
                                } else {
                                        return nil, fmt.Errorf("invalid boolean text bind value %q for parameter %s", p, name)
                                }
                        case sql.Float64Type:
                                f, err := strconv.ParseFloat(p, 64)
                                if err != nil {
                                        return nil, fmt.Errorf("invalid float bind value %q for parameter %s: %w", p, name, err)
                                }
                                pMap[name] = f
                        case sql.TimestampType:
                                // Rails sends timestamps as "YYYY-MM-DD HH:MM:SS.ffffff".
                                // Must become time.Time so EncodeParams emits a Ts
                                // SQLValue — otherwise the engine coerces the leading
                                // digits as an integer and stores the year only.
                                if t, ok := pgTextTimestamp(p); ok {
                                        pMap[name] = t
                                } else {
                                        return nil, fmt.Errorf("invalid timestamp bind value %q for parameter %s", p, name)
                                }
                        case sql.BLOBType:
                                // lib/pq / pgx text-format for BYTEA is PG's canonical
                                // `\x<hex>` since PG 9.0 (default bytea_output=hex).
                                // Strip the leading `\x` if present so the hex decoder
                                // sees just the hex digits. Falls back to raw hex for
                                // clients that encode without the prefix.
                                s := p
                                if len(s) >= 2 && s[0] == '\\' && s[1] == 'x' {
                                        s = s[2:]
                                }
                                d, err := hex.DecodeString(s)
                                if err != nil {
                                        return nil, err
                                }
                                pMap[name] = d
                        default:
                                // AnyType / unrecognised: keep as string. ORMs that
                                // don't introspect parameter types ahead of binding
                                // (Rails sends text-format params for everything
                                // when the inferred type is unknown) rely on the
                                // engine's downstream coercion, not on us forcing
                                // a specific type here.
                                pMap[name] = p
                        }
                }
                // binary param
                if p, ok := val.([]byte); ok {
                        switch param.Type {
                        case sql.IntegerType:
                                i, err := getInt64(p)
                                if err != nil {
                                        return nil, err
                                }
                                pMap[name] = i
                        case sql.VarcharType:
                                pMap[name] = string(p)
                        case sql.BooleanType:
                                v := false
                                if p[0] == byte(1) {
                                        v = true
                                }
                                pMap[name] = v
                        case sql.BLOBType:
                                pMap[name] = p
                        default:
                                // AnyType: pass raw bytes through; downstream
                                // coercion picks the right value.
                                pMap[name] = p
                        }
                }
        }
        return schema.EncodeParams(pMap)
}

func getInt64(p []byte) (int64, error) {
        switch len(p) {
        case 8:
                return int64(binary.BigEndian.Uint64(p)), nil
        case 4:
                return int64(binary.BigEndian.Uint32(p)), nil
        case 2:
                return int64(binary.BigEndian.Uint16(p)), nil
        default:
                return 0, fmt.Errorf("cannot convert a slice of %d byte in an INTEGER parameter", len(p))
        }
}

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	"encoding/binary"
21	"encoding/hex"
22	"fmt"
23	"strconv"
24	"strings"
25	"time"
26
27	"github.com/codenotary/immudb/embedded/sql"
28	"github.com/codenotary/immudb/pkg/api/schema"
29	)
30
31	// pgTextBool mirrors Postgres' text-format boolean accepted values:
32	// t/true/y/yes/on/1 for true; f/false/n/no/off/0 for false.
33	// Rails' pg gem sends "t"/"f" by default, not "true"/"false".
34	func pgTextBool(s string) (bool, bool) {	42✔
35	switch strings.ToLower(strings.TrimSpace(s)) {	42✔
36	case "t", "true", "y", "yes", "on", "1":	17✔
37	return true, true	17✔
38	case "f", "false", "n", "no", "off", "0":	16✔
39	return false, true	16✔
40	}
41	return false, false	9✔
42	}
43
44	// pgTextTimestamp parses the common text formats Postgres/Rails send
45	// for TIMESTAMP binds: "YYYY-MM-DD HH:MM:SS[.fff][Z\|±HH:MM]" or RFC3339.
46	var pgTimestampLayouts = []string{
47	"2006-01-02 15:04:05.999999999",
48	"2006-01-02 15:04:05.999999",
49	"2006-01-02 15:04:05.999",
50	"2006-01-02 15:04:05",
51	"2006-01-02 15:04:05.999999-07",
52	"2006-01-02 15:04:05-07",
53	time.RFC3339Nano,
54	time.RFC3339,
55	"2006-01-02",
56	}
57
58	func pgTextTimestamp(s string) (time.Time, bool) {	8✔
59	s = strings.TrimSpace(s)	8✔
60	for _, layout := range pgTimestampLayouts {	52✔
61	if t, err := time.Parse(layout, s); err == nil {	50✔
62	return t, true	6✔
63	}	6✔
64	}
65	return time.Time{}, false	2✔
66	}
67
68	func buildNamedParams(paramsType []sql.ColDescriptor, paramsVal []interface{}) ([]*schema.NamedParam, error) {	195✔
69	pMap := make(map[string]interface{})	195✔
70	for index, param := range paramsType {	275✔
71	name := param.Column	80✔
72		80✔
73	val := paramsVal[index]	80✔
74	// NULL parameter (Bind protocol pLen == -1).	80✔
75	if val == nil {	80✔
NEW 76	pMap[name] = nil	×
NEW 77	continue	×
78	}
79	// text param
80	if p, ok := val.(string); ok {	134✔
81	switch param.Type {	54✔
82
83	case sql.IntegerType:	7✔
84	int, err := strconv.Atoi(p)	7✔
85	if err != nil {	8✔
86	return nil, err	1✔
87	}	1✔
88	pMap[name] = int64(int)	6✔
89	case sql.VarcharType:	8✔
90	pMap[name] = p	8✔
91	case sql.BooleanType:	21✔
92	// Postgres text-format booleans: "t"/"f" (Rails),	21✔
93	// plus the long forms. Anything else is an error,	21✔
94	// not a silent false, so we don't round-trip a	21✔
95	// corrupted bool (the old `p == "true"` check stored	21✔
96	// Rails' "t" as false).	21✔
97	if b, ok := pgTextBool(p); ok {	38✔
98	pMap[name] = b	17✔
99	} else {	21✔
100	return nil, fmt.Errorf("invalid boolean text bind value %q for parameter %s", p, name)	4✔
101	}	4✔
102	case sql.Float64Type:	5✔
103	f, err := strconv.ParseFloat(p, 64)	5✔
104	if err != nil {	6✔
105	return nil, fmt.Errorf("invalid float bind value %q for parameter %s: %w", p, name, err)	1✔
106	}	1✔
107	pMap[name] = f	4✔
108	case sql.TimestampType:	5✔
109	// Rails sends timestamps as "YYYY-MM-DD HH:MM:SS.ffffff".	5✔
110	// Must become time.Time so EncodeParams emits a Ts	5✔
111	// SQLValue — otherwise the engine coerces the leading	5✔
112	// digits as an integer and stores the year only.	5✔
113	if t, ok := pgTextTimestamp(p); ok {	9✔
114	pMap[name] = t	4✔
115	} else {	5✔
116	return nil, fmt.Errorf("invalid timestamp bind value %q for parameter %s", p, name)	1✔
117	}	1✔
118	case sql.BLOBType:	5✔
119	// lib/pq / pgx text-format for BYTEA is PG's canonical	5✔
120	// `\x<hex>` since PG 9.0 (default bytea_output=hex).	5✔
121	// Strip the leading `\x` if present so the hex decoder	5✔
122	// sees just the hex digits. Falls back to raw hex for	5✔
123	// clients that encode without the prefix.	5✔
124	s := p	5✔
125	if len(s) >= 2 && s[0] == '\\' && s[1] == 'x' {	6✔
126	s = s[2:]	1✔
127	}	1✔
128	d, err := hex.DecodeString(s)	5✔
129	if err != nil {	6✔
130	return nil, err	1✔
131	}	1✔
132	pMap[name] = d	4✔
133	default:	3✔
134	// AnyType / unrecognised: keep as string. ORMs that	3✔
135	// don't introspect parameter types ahead of binding	3✔
136	// (Rails sends text-format params for everything	3✔
137	// when the inferred type is unknown) rely on the	3✔
138	// engine's downstream coercion, not on us forcing	3✔
139	// a specific type here.	3✔
140	pMap[name] = p	3✔
141	}
142	}
143	// binary param
144	if p, ok := val.([]byte); ok {	98✔
145	switch param.Type {	26✔
146	case sql.IntegerType:	19✔
147	i, err := getInt64(p)	19✔
148	if err != nil {	20✔
149	return nil, err	1✔
150	}	1✔
151	pMap[name] = i	18✔
152	case sql.VarcharType:	1✔
153	pMap[name] = string(p)	1✔
154	case sql.BooleanType:	5✔
155	v := false	5✔
156	if p[0] == byte(1) {	9✔
157	v = true	4✔
158	}	4✔
159	pMap[name] = v	5✔
160	case sql.BLOBType:	1✔
161	pMap[name] = p	1✔
NEW 162	default:	×
NEW 163	// AnyType: pass raw bytes through; downstream	×
NEW 164	// coercion picks the right value.	×
NEW 165	pMap[name] = p	×
166	}
167	}
168	}
169	return schema.EncodeParams(pMap)	186✔
170	}
171
172	func getInt64(p []byte) (int64, error) {	23✔
173	switch len(p) {	23✔
174	case 8:	19✔
175	return int64(binary.BigEndian.Uint64(p)), nil	19✔
176	case 4:	1✔
177	return int64(binary.BigEndian.Uint32(p)), nil	1✔
178	case 2:	1✔
179	return int64(binary.BigEndian.Uint16(p)), nil	1✔
180	default:	2✔
181	return 0, fmt.Errorf("cannot convert a slice of %d byte in an INTEGER parameter", len(p))	2✔
182	}
183	}

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