Skip to content

Commit 0f9e080

Browse files
author
Philippe Beaudoin
committed
When a constraint name is greater than 63 characters, do not use it in the generated scripts and let PostgreSQL rebuild the name at constraint creation time.
1 parent 27014fa commit 0f9e080

2 files changed

Lines changed: 22 additions & 13 deletions

File tree

‎regression/reg_tests.sql‎

98 Bytes
Binary file not shown.

‎sqlserver2pgsql.pl‎

Lines changed: 22 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -567,6 +567,18 @@ sub format_identifier_cols_index
567567
return $formatted . ' ' . $order;
568568
}
569569

570+
# This sub returns FALSE and prints a warning message when the length of a provided constraint name is greater than 63.
571+
sub is_constraint_name_valid
572+
{
573+
my ($name) = @_;
574+
if (length($name) > 63)
575+
{
576+
print STDERR "Warning: because of its length, the constraint name $name is ignored and will be set by Postgres at execution time.\n";
577+
return 0;
578+
}
579+
return 1;
580+
}
581+
570582
# This one will try to convert what can obviously be converted from transact to PG
571583
# Things such as getdate() which can become CURRENT_TIMESTAMP
572584
sub convert_transact_function
@@ -2753,7 +2765,7 @@ sub generate_schema
27532765
next;
27542766
}
27552767
my $pkdef = "ALTER TABLE " . format_identifier($schema) . '.' . format_identifier($table) . " ADD";
2756-
if (defined $refpk->{NAME})
2768+
if (defined $refpk->{NAME} and is_constraint_name_valid($refpk->{NAME}))
27572769
{
27582770
$pkdef .= " CONSTRAINT " . format_identifier($refpk->{NAME});
27592771
}
@@ -2775,7 +2787,7 @@ sub generate_schema
27752787
{
27762788
next unless ($constraint->{TYPE} eq 'UNIQUE');
27772789
my $consdef = "ALTER TABLE " . format_identifier($schema) . '.' . format_identifier($table) . " ADD";
2778-
if (defined $constraint->{NAME})
2790+
if (defined $constraint->{NAME} and is_constraint_name_valid($constraint->{NAME}))
27792791
{
27802792
$consdef .= " CONSTRAINT " . format_identifier($constraint->{NAME});
27812793
}
@@ -2872,12 +2884,11 @@ sub generate_schema
28722884
{
28732885
next if ($constraint->{TYPE} =~ /^UNIQUE|PK$/);
28742886
my $consdef = "ALTER TABLE " . format_identifier($schema) . '.' . format_identifier($table) . " ADD";
2875-
if (defined $constraint->{NAME})
2887+
if (defined $constraint->{NAME} and is_constraint_name_valid($constraint->{NAME}))
28762888
{
28772889
$consdef .= " CONSTRAINT " . format_identifier($constraint->{NAME});
28782890
}
2879-
if ($constraint->{TYPE} eq
2880-
'FK') # COLS are already a comma separated list
2891+
if ($constraint->{TYPE} eq 'FK') # COLS are already a comma separated list
28812892
{
28822893
# We need to convert the column list to protected names
28832894
my @localcollist=map{format_identifier($_)} @{$constraint->{LOCAL_COLS}};
@@ -2911,23 +2922,22 @@ sub generate_schema
29112922
}
29122923
# We need a name on the constraint to be able to validate it later. Maybe it would be better to generate one
29132924
# FIXME: we'll see later if a generator is needed (probably)
2914-
if ($constraint->{TYPE} eq 'FK' and ($validate_constraints =~ /^after|no$/) and defined($constraint->{NAME}))
2925+
if (($validate_constraints =~ /^after|no$/) and defined($constraint->{NAME}) and is_constraint_name_valid($constraint->{NAME}))
29152926
{
29162927
$consdef .= " NOT VALID";
29172928
}
29182929
$consdef .= ";\n";
29192930
print AFTER $consdef;
2920-
if ($constraint->{TYPE} eq 'FK' and $validate_constraints eq 'after' and defined $constraint->{NAME})
2931+
if ($validate_constraints eq 'after' and defined $constraint->{NAME} and is_constraint_name_valid($constraint->{NAME}))
29212932
{
29222933
print UNSURE "ALTER TABLE " . format_identifier($schema) . '.' . format_identifier($table) . " VALIDATE CONSTRAINT " . format_identifier($constraint->{NAME}) . ";\n";
29232934
}
29242935
}
29252936
elsif ($constraint->{TYPE} eq 'CHECK')
2926-
{
2927-
$consdef .= " CHECK (" . convert_transactsql_code($constraint->{TEXT}) . ");\n";
2928-
print UNSURE $consdef
2929-
; # Check constraints are SQL, so cannot be sure
2930-
}
2937+
{
2938+
$consdef .= " CHECK (" . convert_transactsql_code($constraint->{TEXT}) . ");\n";
2939+
print UNSURE $consdef; # Check constraints are SQL, so cannot be sure
2940+
}
29312941
elsif ($constraint->{TYPE} eq 'CHECK_CITEXT')
29322942
{
29332943
# These have been generated here, for citext mostly. So we know their syntax is ok
@@ -3182,7 +3192,6 @@ sub resolve_name_conflicts
31823192
}
31833193
$known_names{format_identifier($domain."2pgd")}=1;
31843194
}
3185-
31863195
}
31873196

31883197
# Then we scan all indexes

0 commit comments

Comments
 (0)