Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

spanner/spansql: ON DELETE NO ACTION gets added when no foreign key action is specified #8955

Open
toga4 opened this issue Nov 1, 2023 · 3 comments · May be fixed by #10121
Open

spanner/spansql: ON DELETE NO ACTION gets added when no foreign key action is specified #8955

toga4 opened this issue Nov 1, 2023 · 3 comments · May be fixed by #10121
Assignees
@rahul2393
Labels
priority: p3 Desirable enhancement or fix. May not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

Comments

@toga4
Copy link
Contributor

toga4 commented Nov 1, 2023

Client

Spanner spansql

Environment

Go Playground

Go Environment

Go 1.21

Code

e.g.

package main

import (
	"fmt"

	"cloud.google.com/go/spanner/spansql"
)

func main() {
	ddl, _ := spansql.ParseDDL("ddl", `CREATE TABLE Table2 (
	Table2ID STRING(MAX) NOT NULL,
	Table1ID STRING(MAX) NOT NULL,
	CONSTRAINT FK_Table1Table2 FOREIGN KEY(Table1ID) REFERENCES Table1(Table1ID)
) PRIMARY KEY(Table2ID);`)
	fmt.Println(ddl.List[0].SQL())
}

Expected behavior

Outputs without foreign key action

CREATE TABLE Table2 (
  Table2ID STRING(MAX) NOT NULL,
  Table1ID STRING(MAX) NOT NULL,
  CONSTRAINT FK_Table1Table2 FOREIGN KEY (Table1ID) REFERENCES Table1 (Table1ID),
) PRIMARY KEY(Table2ID)

Actual behavior

Outputs with ON DELETE NO ACTION added.

CREATE TABLE Table2 (
  Table2ID STRING(MAX) NOT NULL,
  Table1ID STRING(MAX) NOT NULL,
  CONSTRAINT FK_Table1Table2 FOREIGN KEY (Table1ID) REFERENCES Table1 (Table1ID) ON DELETE NO ACTION,
) PRIMARY KEY(Table2ID)

Additional context

The current behavior causes a problem when trying to apply DDL to the spanner-emulator using migration tools like wrench. Since the emulator does not support foreign key actions, this behavior results in the output of DDLs that are not accepted by the emulator.

Related to cloudspannerecosystem/wrench#95
@toga4 toga4 added the triage me I really want to be triaged. label Nov 1, 2023
@toga4 toga4 changed the title packagename: short description of bug spanner/spansql: ON DELETE NO ACTION gets added when no foreign key action is specified Nov 1, 2023
This was referenced Nov 1, 2023
Closed
Closed
@noahdietz noahdietz added type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns. priority: p3 Desirable enhancement or fix. May not be included in next release. and removed triage me I really want to be triaged. labels Nov 7, 2023
@noahdietz
Copy link
Contributor

Tagged as a bug for rotation triaging, seems kind of like a process/testing thing though. Feel free to change.

@manu2 manu2 mentioned this issue Nov 20, 2023
Open
@the-wendell
Copy link

Just a friendly bump 😄

It looks like there is already a PR (#8968 ) ready to go for this so fingers crossed that this can be resolved soon.

This issue blocks our CI pipeline because we are relying on spanner emulator which does not support ON DELETE actions. We would really like to upgrade to the latest Go spanner SDK to take advantage of the open telemetry support (https://github.com/googleapis/google-cloud-go/blob/main/spanner/CHANGES.md#1570-2024-02-13)

I am sure that we are not the only ones blocked by this.

@graeme-verticalscope
Copy link

@the-wendell FYI, this workaround might work for you:

I was able to upgrade my go spanner version by using spanner emulator version 1.5.10, note it has to be that exact emulator version. Newer or older emulator versions break. (See cloudspannerecosystem/wrench#95 (comment) and GoogleCloudPlatform/cloud-spanner-emulator#147 (comment))

@toga4 toga4 linked a pull request May 6, 2024 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rahul2393 rahul2393

Labels
priority: p3 Desirable enhancement or fix. May not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.
Projects
None yet
Milestone
No milestone
5 participants
@noahdietz @rahul2393 @the-wendell @graeme-verticalscope @toga4