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
>