Обсуждение: 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 >