Thread: BUG #13659: Constraint names truncated without error

The following bug has been logged on the website:

Bug reference:      13659
Logged by:          James Coleman
Email address:      jtc331@gmail.com
PostgreSQL version: 9.4.4
Operating system:   OS X (and Linux etc.)
Description:

If I create the following schema:

create table t(n integer);
alter table t add constraint
  test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit
  check (n != 1);

the constraint name appears to be automatically truncated without error, as
confirmed with:

SELECT tc.constraint_name, tc.table_name
FROM information_schema.table_constraints AS tc
WHERE tc.table_name = 't'

Since PG raises errors when index names, for example, are too long, I
believe it should do the same for constraints. I discussed this with Magnus
at PostgresOpen and he agreed that the behavior should be the same and that
if it weren't it was likely a bug.

jtc331@gmail.com writes:
> If I create the following schema:

> create table t(n integer);
> alter table t add constraint
>   test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit
>   check (n != 1);

> the constraint name appears to be automatically truncated without error, as
> confirmed with:

> SELECT tc.constraint_name, tc.table_name
> FROM information_schema.table_constraints AS tc
> WHERE tc.table_name = 't'

> Since PG raises errors when index names, for example, are too long, I
> believe it should do the same for constraints.

Really?  I see the same type of behavior for both cases:

regression=# create table t(n integer);
CREATE TABLE
regression=# alter table t add constraint
  test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit
  check (n != 1);
NOTICE:  identifier "test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit" will be truncated to
"test_contrainst_that_has_a_very_long_name_to_trigger_the_charac"
ALTER TABLE
regression=# create index test_index_test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit on t(n);
NOTICE:  identifier "test_index_test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit" will be
truncatedto "test_index_test_contrainst_that_has_a_very_long_name_to_trigger" 
CREATE INDEX
regression=# \d+ t
                          Table "public.t"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 n      | integer |           | plain   |              |
Indexes:
    "test_index_test_contrainst_that_has_a_very_long_name_to_trigger" btree (n)
Check constraints:
    "test_contrainst_that_has_a_very_long_name_to_trigger_the_charac" CHECK (n <> 1)


Given where the identifier truncation behavior occurs, in the lexer, it
would be mildly astonishing if it didn't work the same for both cases.

            regards, tom lane

I did some more testing, and at least one of the cases I found out that the
tool I was using to execute the DDL was intercepting some of the length
issues and not others.

I was pretty confident that I had encountered an issue with raw SQL as
well, but I just put together an test case with indexes, functions, foreign
keys, etc. and couldn't find an issue. My apologies for submitting
incorrect information in that regard.

Perhaps what I'd encountered was the fact that a truncated function name
means you can accidentally run into a "function already exists with name"
error unintentionally.

Has there ever been any discussion about making truncation an error level
message rather than a notice?

On Thu, Oct 1, 2015 at 11:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> jtc331@gmail.com writes:
> > If I create the following schema:
>
> > create table t(n integer);
> > alter table t add constraint
> >
>  test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit
> >   check (n != 1);
>
> > the constraint name appears to be automatically truncated without error,
> as
> > confirmed with:
>
> > SELECT tc.constraint_name, tc.table_name
> > FROM information_schema.table_constraints AS tc
> > WHERE tc.table_name = 't'
>
> > Since PG raises errors when index names, for example, are too long, I
> > believe it should do the same for constraints.
>
> Really?  I see the same type of behavior for both cases:
>
> regression=# create table t(n integer);
> CREATE TABLE
> regression=# alter table t add constraint
>   test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit
>   check (n != 1);
> NOTICE:  identifier
> "test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit"
> will be truncated to
> "test_contrainst_that_has_a_very_long_name_to_trigger_the_charac"
> ALTER TABLE
> regression=# create index
> test_index_test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit
> on t(n);
> NOTICE:  identifier
> "test_index_test_contrainst_that_has_a_very_long_name_to_trigger_the_character_limit"
> will be truncated to
> "test_index_test_contrainst_that_has_a_very_long_name_to_trigger"
> CREATE INDEX
> regression=# \d+ t
>                           Table "public.t"
>  Column |  Type   | Modifiers | Storage | Stats target | Description
> --------+---------+-----------+---------+--------------+-------------
>  n      | integer |           | plain   |              |
> Indexes:
>     "test_index_test_contrainst_that_has_a_very_long_name_to_trigger"
> btree (n)
> Check constraints:
>     "test_contrainst_that_has_a_very_long_name_to_trigger_the_charac"
> CHECK (n <> 1)
>
>
> Given where the identifier truncation behavior occurs, in the lexer, it
> would be mildly astonishing if it didn't work the same for both cases.
>
>                         regards, tom lane
>