GoogleCloudPlatform / GoogleCloudPlatform/pgadapter

Issue with `jsonb_build_object` using PGAdapter and Google Spanner Emulator

Open
#3,101 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
91
Forks
33
Avg merge
3d 57m
Merged PRs (30d)
58

Description

## Summary
I'm running PGAdapter alongside the Google Spanner Emulator in Docker and have connected using the PostgreSQL driver in JetBrains DataGrip. When I execute a query using the`jsonb_build_object` function, I receive an error. Notably, the query works in Google Spanner Studio on GCP but fails in DataGrip (and similarly in DBeaver with the Google Spanner driver).

## Environment

- PGAdapter: Running in Docker.
- Google Spanner Emulator: Running in Docker.
- Client Tools:
- JetBrains DataGrip (using PostgreSQL driver or Google Spanner Driver)
- DBeaver (using Google Spanner driver)
- Database Dialect: PostgreSQL (as set during database creation).

## Steps to Reproduce
1. Set Up the Google Spanner Emulator Container:

```yaml
spanner-dev:
image: gcr.io/cloud-spanner-emulator/emulator:latest
container_name: spanner-dev
ports:
- "9010:9010"
- "9020:9020"
```
2. Initialize the Emulator:
```sh
gcloud config configurations create emulator --no-activate || true
gcloud config configurations activate emulator
gcloud config set auth/disable_credentials true
gcloud config set project test-project
gcloud config set api_endpoint_overrides/spanner http://spanner-dev:9020/

gcloud spanner instances create local-instance \
--config=emulator-config \
--description="Local Instance" \
--nodes=1

gcloud spanner databases create local-database \
--instance=local-instance \
--database-dialect=POSTGRESQL
```

3. Start PGAdapter:

Configure and run PGAdapter with the following Docker settings:

```yaml
image: gcr.io/cloud-spanner-pg-adapter/pgadapter:latest
container_name: pga-adapter
ports:
- "5432:5432"
environment:
- SPANNER_EMULATOR_HOST=spanner-dev:9010
command: ["-p", "test-project", "-i", "local-instance", "-d", "local-database", "-x", "-r", "autoConfigEmulator=true", "-c", ""]
```
> Note: Replace the empty string in the -c parameter with your credentials.

4. Connect and Execute the Query:
Connect to the PostgreSQL data source in DataGrip or Connect to the Google Spanner data source in DataGrip or Connect to the Google Spanner connection in DBeaver

Run the following SQL query:

```pgsql
SELECT jsonb_build_object('title','Academy Dinosaur','length',86);
```

## Error Details
The query returns the following error:
```pgsql
[P0001] ERROR: Unknown statement: SQL Error [3]: INVALID_ARGUMENT: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Unable to resolve argument type. Please consider adding an explicit cast - Statement: 'SELECT jsonb_build_object( 'title', 'Academy Dinosaur', 'length', 86 )'
```

### Additional Notes
The error is observed when connecting with DataGrip (using the PostgreSQL driver) and DBeaver (using the Google Spanner driver).

The same query executes successfully in Google Spanner Studio in GCP.

## Request for Assistance
Any insights or workarounds to resolve the issue with `jsonb_build_object` when using PGAdapter with the Google Spanner Emulator would be greatly appreciated.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.