Issue with `jsonb_build_object` using PGAdapter and Google Spanner Emulator

[taua-almeida]

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 thejsonb_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:

spanner-dev:
    image: gcr.io/cloud-spanner-emulator/emulator:latest
    container_name: spanner-dev
    ports:
      - "9010:9010"
      - "9020:9020"

2. Initialize the Emulator:

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:

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:

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

Error Details

The query returns the following error:

[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.

[olavloite]

@taua-almeida Thanks for reaching out. I think that this is not directly an issue in PGAdapter, but an issue in the Spanner Emulator. It seems that the function is not available in the Emulator. Let me create an issue there and link to this one.

[taua-almeida]

Interesting, I guess it makes sense to be part of the emulator instead of the PGAdapter since it is just transporting right?

[olavloite]

Interesting, I guess it makes sense to be part of the emulator instead of the PGAdapter since it is just transporting right?

Yeah, the separation of responsibilities here are:

1. PGAdapter translates the PostgreSQL network protocol to the Spanner gRPC protocol.
2. All (or at least most) SQL statements are sent unmodified to either Spanner or the Emulator.
3. Spanner or the Emulator executes the actual SQL statement, including functions.