GoogleCloudPlatform / GoogleCloudPlatform/spanner-migration-tool

Data-only migration fails on PostgreSQL-dialect target

Open
#644 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
PLpgSQL
Stars
155
Forks
83
Avg merge
3d 7h
Merged PRs (30d)
6

Description

## 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](https://github.com/GoogleCloudPlatform/spanner-migration-tool/blob/v3.1.1/sources/spanner/infoschema.go#L350) in `GetIndexes()` vs [error line](https://github.com/GoogleCloudPlatform/spanner-migration-tool/blob/v3.1.1/sources/spanner/infoschema.go#L396) 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)
1. Run a schema-only migration from PostgreSQL to a PostgreSQL-dialect Spanner database
1. Restart SMT
1. Run a data-only migration from PostgreSQL to the database created before

## Specifications

- Version: v3.1.1
- Platform:
```
# Server running SMT
> uname -a
Linux 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
```

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.