GoogleCloudPlatform/spanner-migration-tool

Data-only migration fails on PostgreSQL-dialect target

joshbautista opened this issue · 0 comments

Expected Behavior

Migration succeeds.

Actual Behavior

Migration throws an error:

can't migrate database: error while validating existing database:
can't read spanner schema: error trying to read and convert spanner schema:
couldn't get indexes for table public.test_table: couldn't read row while fetching interleaved tables:
spanner: code = "InvalidArgument", desc = "Statements with DISTINCT clauses are not supported"
smt-web-ui-distinct-error

SELECT DISTINCT is not currently supported with PostgreSQL dialect databases:
https://cloud.google.com/spanner/docs/reference/postgresql/query-syntax#select

smt-select-distinct-query

Also, this part of the error message, couldn't read row while fetching interleaved tables, seems to be a copy-paste error.
Error line in GetIndexes() vs error line in GetInterleaveTables().

Steps to Reproduce the Problem

  1. Create a PostgreSQL database with one table and one index (indexes don't really matter, but for the purposes of this error we'll include one)
  2. Run a schema-only migration from PostgreSQL to a PostgreSQL-dialect Spanner database
  3. Restart SMT
  4. Run a data-only migration from PostgreSQL to the database created before

Specifications

  • Version: v3.1.1
  • Platform:
# Server running SMT
> uname -a
Linux <redacted> 6.1.0-11-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.38-4 (2023-08-08) x86_64 GNU/Linux

> go version
go version go1.21.0 linux/amd64

# Browser
Chrome Version 117.0.5938.92 (Official Build) (x86_64) on MacOS 13.5.2