Redshift: separate quoting around schema and table name breaks reference parser

[mjpieters]

What happened

I'm testing tbls against a schema with a reserved name (time) as well as with a hyphenated table name:

CREATE SCHEMA time;

CREATE TABLE time.bar (
  id int IDENTITY(1, 1) PRIMARY KEY
);
CREATE TABLE time."hyphenated-table" (
  id int IDENTITY(1, 1) PRIMARY KEY
);
CREATE TABLE time.referencing (
  id int IDENTITY(1, 1) PRIMARY KEY,
  bar_id int NOT NULL,
  ht_id int NOT NULL,
  CONSTRAINT referencing_bar_id FOREIGN KEY(bar_id) REFERENCES time.bar(id),
  CONSTRAINT referencing_ht_id FOREIGN KEY(ht_id) REFERENCES time."hyphenated-table"(id)
);

While foreign keys are not enforced by Redshift, the query optimiser does use them so it makes sense to put these in.

The above schema breaks tbls' relations parsing, because it doesn't account for the quoting that AWS redshift puts on both the schema and on the second table, when querying constraints:

select pg_get_constraintdef(oid) as def from pg_constraint
where conrelid = 'time.referencing'::regclass and contype = 'f';
                def
-----------------------------------------------------
 FOREIGN KEY (bar_id) REFERENCES "time".bar(id)
 FOREIGN KEY (ht_id) REFERENCES "time"."hyphenated-table"(id)
(2 rows)

Note the "time" quoting in the response, and the separate quotes around hyphenated-table.

This causes tbls to be unhappy, because the postgres driver only strips quotes from the start and end of the string

With DEBUG=1 we thus see:

not found table 'time".bar'
github.com/k1LoW/tbls/schema.(*Schema).FindTableByName
	/Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:136
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze
	/Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:359
github.com/k1LoW/tbls/datasource.Analyze
	/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:93
github.com/k1LoW/tbls/cmd.glob..func6
	/Users/k1low/src/github.com/k1LoW/tbls/cmd/out.go:80
github.com/spf13/cobra.(*Command).execute
	/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
	/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
	/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/k1LoW/tbls/cmd.Execute
	/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:153
main.main
	/Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
	/Users/k1low/.anyenv/envs/goenv/versions/1.13.10/src/runtime/proc.go:203
runtime.goexit
	/Users/k1low/.anyenv/envs/goenv/versions/1.13.10/src/runtime/asm_amd64.s:1357

Perhaps a more sophisticated quote removal routine is needed?

Environment

• tbls version: 1.35.0
• Database version: redshift

[k1LoW]

@mjpieters Thank you for your report !!