Skip to content

Commit d487c53

Browse files
ninglinLiucopybara-github
authored andcommitted
fix: store DatabaseSessionService JSON columns as json on PostgreSQL
Merge #7857 DynamicJSON now creates PostgreSQL columns as `json` instead of `jsonb`, so state and event values keep key order, floats such as `-0.0` and `1e23`, and NUL characters, as on SQLite. Only newly created tables change; existing `jsonb` tables are not altered and still read and write; the PostgreSQL sample README shows how to convert them. Queries outside ADK that use jsonb-only operators or GIN indexes will not work on new tables. Part of #7854 PiperOrigin-RevId: 1013154584
1 parent 14236ff commit d487c53

3 files changed

Lines changed: 27 additions & 11 deletions

File tree

‎contributing/samples/context_management/postgres_session_service/README.md‎

Lines changed: 19 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,7 @@ pip install google-adk asyncpg greenlet
3535
| app_name | VARCHAR(128) | Application identifier (PK) |
3636
| user_id | VARCHAR(128) | User identifier (PK) |
3737
| id | VARCHAR(128) | Session UUID (PK) |
38-
| state | JSONB | Session state as JSON |
38+
| state | JSON | Session state as JSON |
3939
| create_time | TIMESTAMP | Creation timestamp |
4040
| update_time | TIMESTAMP | Last update timestamp |
4141

@@ -49,14 +49,14 @@ pip install google-adk asyncpg greenlet
4949
| session_id | VARCHAR(128) | Session reference (PK, FK) |
5050
| invocation_id | VARCHAR(256) | Invocation identifier |
5151
| timestamp | TIMESTAMP | Event timestamp |
52-
| event_data | JSONB | Event content as JSON |
52+
| event_data | JSON | Event content as JSON |
5353

5454
### app_states
5555

5656
| Column | Type | Description |
5757
| ----------- | ------------ | --------------------------- |
5858
| app_name | VARCHAR(128) | Application identifier (PK) |
59-
| state | JSONB | Application-level state |
59+
| state | JSON | Application-level state |
6060
| update_time | TIMESTAMP | Last update timestamp |
6161

6262
### user_states
@@ -65,7 +65,7 @@ pip install google-adk asyncpg greenlet
6565
| ----------- | ------------ | --------------------------- |
6666
| app_name | VARCHAR(128) | Application identifier (PK) |
6767
| user_id | VARCHAR(128) | User identifier (PK) |
68-
| state | JSONB | User-level state |
68+
| state | JSON | User-level state |
6969
| update_time | TIMESTAMP | Last update timestamp |
7070

7171
### adk_internal_metadata
@@ -182,12 +182,26 @@ session = await session_service.get_session(
182182

183183
## State Management
184184

185-
PostgreSQL's JSONB type provides efficient storage for state data:
185+
PostgreSQL's JSON type stores state data as written:
186186

187187
- **Session state**: Stored in `sessions.state`
188188
- **User state**: Stored in `user_states.state`
189189
- **App state**: Stored in `app_states.state`
190190

191+
Tables created by older ADK versions keep their `jsonb` columns, which still
192+
reject NUL (`\u0000`) in strings. To switch an existing database to `json`:
193+
194+
```sql
195+
ALTER TABLE sessions ALTER COLUMN state TYPE json USING state::json;
196+
ALTER TABLE events ALTER COLUMN event_data TYPE json USING event_data::json;
197+
ALTER TABLE app_states ALTER COLUMN state TYPE json USING state::json;
198+
ALTER TABLE user_states ALTER COLUMN state TYPE json USING state::json;
199+
```
200+
201+
Each statement rewrites the table under an `ACCESS EXCLUSIVE` lock, blocking
202+
reads and writes until it finishes, so run it during a maintenance window.
203+
Values already stored keep their `jsonb`-normalized form.
204+
191205
## Production Considerations
192206

193207
1. **Connection Pooling**: Use `pool_size` and `max_overflow` for high-traffic applications

‎src/google/adk/sessions/schemas/shared.py‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@ def utc_datetime_to_timestamp(value: datetime.datetime) -> float:
4949

5050

5151
class DynamicJSON(TypeDecorator[dict[str, Any]]): # type: ignore[misc]
52-
"""A JSON-like type that uses JSONB on PostgreSQL and TEXT with JSON serialization for other databases."""
52+
"""A JSON-like type: JSON on PostgreSQL, JSON-serialized TEXT elsewhere."""
5353

5454
impl = Text # Default implementation is TEXT
5555
# Behavior depends only on the dialect, which the compiled cache already
@@ -58,7 +58,9 @@ class DynamicJSON(TypeDecorator[dict[str, Any]]): # type: ignore[misc]
5858

5959
def load_dialect_impl(self, dialect: Dialect) -> TypeEngine[Any]:
6060
if dialect.name == "postgresql":
61-
return dialect.type_descriptor(postgresql.JSONB())
61+
# Not JSONB: it reorders keys, rewrites floats such as -0.0 and 1e23,
62+
# and rejects NUL, so values would not round-trip as on other dialects.
63+
return dialect.type_descriptor(postgresql.JSON())
6264
if dialect.name == "mysql":
6365
# Use LONGTEXT for MySQL to address the data too long issue
6466
return dialect.type_descriptor(mysql.LONGTEXT())
@@ -69,7 +71,7 @@ def process_bind_param(
6971
) -> dict[str, Any] | str | None:
7072
if value is not None:
7173
if dialect.name == "postgresql":
72-
return value # JSONB handles dict directly
74+
return value # JSON accepts dict directly
7375
if dialect.name == "sqlite":
7476
# Only SQLite TEXT is always Unicode; other backends' TEXT columns may
7577
# use a non-UTF-8 charset, so they keep the ASCII-only escaped form.

‎tests/unittests/sessions/test_schemas_shared.py‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -48,15 +48,15 @@ def precise_timestamp():
4848
@pytest.mark.parametrize(
4949
"dialect_name, expected_type",
5050
[
51-
("postgresql", postgresql.JSONB),
51+
("postgresql", postgresql.JSON),
5252
("mysql", mysql.LONGTEXT),
5353
("sqlite", Text),
5454
],
5555
)
5656
def test_dynamic_json_load_dialect_impl(
5757
dynamic_json, dialect_name, expected_type
5858
):
59-
"""Each dialect gets the widest JSON-capable column type it supports."""
59+
"""Each dialect gets the column type that round-trips JSON as written."""
6060
dialect = _dialect(dialect_name)
6161

6262
impl = dynamic_json.load_dialect_impl(dialect)
@@ -120,7 +120,7 @@ def test_dynamic_json_escapes_non_ascii_text_for_other_dialects(
120120

121121

122122
def test_dynamic_json_passes_values_through_for_postgresql(dynamic_json):
123-
"""JSONB accepts and returns Python objects, so no conversion happens."""
123+
"""JSON accepts and returns Python objects, so no conversion happens."""
124124
dialect = _dialect("postgresql")
125125
value = {"key": "value"}
126126

0 commit comments

Comments
 (0)