Gorm Custom schema and too long table name generate invalid constraint

GORM Playground Link

https://github.com/go-gorm/playground/pull/428

Description

It appears that a table name that includes a custom schema name, and exceeds a length of 52 characters, can cause an invalid foreign key constraint name to be generated when referenced by another table. Here are two models that demonstrate the issue:

type TableOne struct {
  ID uint `gorm:"primaryKey;autoIncrement"`

  TableTwoID uint
  TableTwo   TableTwo
}

func (to TableOne) TableName() string {
  return "my_schema.table_oneeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"
}

type TableTwo struct {
  ID uint `gorm:"primaryKey;autoIncrement"`
}

func (tt TableTwo) TableName() string {
  return "my_schema.table_two"
}

Attempting to run migrations from the linked playground example for the above tables generates the following error:

2022/01/26 17:14:17 /home/mnussbaum/go/pkg/mod/gorm.io/driver/postgres@v1.2.3/migrator.go:172 ERROR: syntax error at or near "." (SQLSTATE 42601)
[0.720ms] [rows:0] CREATE TABLE "my_schema"."table_oneeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee" ("id" bigserial,"table_two_id" bigint,PRIMARY KEY ("id"),CONSTRAINT "fkmy_schema"."table_oneeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeta520923f2" FOREIGN KEY ("table_two_id") REFERENCES "my_schema"."table_two"("id"))
2022/01/26 17:14:17 Failed to auto migrate, but got error ERROR: syntax error at or near "." (SQLSTATE 42601)

I believe the constraint should be named "fkmy_schema_table_oneeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeta520923f2" instead of "fkmy_schema"."table_oneeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeta520923f2", though the length of the expected constraint name should probably be truncated.

It seems the bug only occurs if the schema is included as a part of the table name and if the total table name length exceeds 52 characters.

Comment From: mnussbaum

This bug can be worked around using a custom naming strategy like so:

type NamingStrategy struct {
  gormNamingStrategy gorm_schema.NamingStrategy
}

func (ns *NamingStrategy) TableName(table string) string {
  return ns.gormNamingStrategy.TableName(table)
}

func (ns *NamingStrategy) SchemaName(table string) string {
  return ns.gormNamingStrategy.SchemaName(table)
}

func (ns *NamingStrategy) ColumnName(table, column string) string {
  return ns.gormNamingStrategy.ColumnName(table, column)
}

func (ns *NamingStrategy) JoinTableName(table string) string {
  return ns.gormNamingStrategy.JoinTableName(table)
}

func (ns *NamingStrategy) RelationshipFKName(rel gorm_schema.Relationship) string {
  return strings.ReplaceAll(
    ns.gormNamingStrategy.RelationshipFKName(rel),
    ".",
    "_",
  )
}

func (ns *NamingStrategy) CheckerName(table, column string) string {
  return ns.gormNamingStrategy.CheckerName(table, column)
}

func (ns *NamingStrategy) IndexName(table, column string) string {
  return ns.gormNamingStrategy.IndexName(table, column)
}

That said, this work-around shouldn't be required and the default behavior is still incorrect, and should be fixed

Comment From: jinzhu

Seems this have been fixed for a while, please provide a playground if it still doesn't works for you.

Comment From: mnussbaum

I can confirm this is still an active issue. I have a playground that fails DB migrations due to the bug at https://github.com/go-gorm/playground/pull/428. I also have a PR for a fix with test cases that demonstrate the issue at https://github.com/go-gorm/gorm/pull/5045.

In addition to the code currently generating invalid foreign key constraint names, I've recently found it also can generate invalid index names due to the same issue.

Thank you for your time!

Comment From: mnussbaum

This was fixed by #5045

Comment From: zzh8829

hello i believe this issue is still active, all the previous fixed results in 64 character string but in postgres standard it actually says 63 is the maximum allowed length https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. If this limit is problematic, it can be raised by changing the NAMEDATALEN constant in src/include/pg_config_manual.h.

we ran into this issue in production and had to override with a custom naming strategy