#5125 closed defect (fixed)
PostgreSQL primary key name exceeds name limit
Reported by: | strk | Owned by: | warmerdam |
---|---|---|---|
Priority: | normal | Milestone: | 1.10.1 |
Component: | OGR_SF | Version: | unspecified |
Severity: | normal | Keywords: | postgres postgis |
Cc: |
Description
When asked to create a table with a name of 63 characters (postgresql name limit) ogr2ogr adds a PRIMARY KEY constraint with a name that is derived from the table name with 3 characters appended ("_pk"). PostgreSQL truncates those 3 characters from the constraint name, to get it within limits, and the result is an error due to the fact that a relation with the given name already exists (a primary key creates an index which is a relation):
See session for detail:
"ERROR 1: CREATE TABLE \"public\".\"sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo\" ( \"cartodb_id\" SERIAL, CONSTRAINT \"sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo_pk\" PRIMARY KEY (\"cartodb_id\") ) ERROR: relation \"sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo\" already exists ERROR 1: Terminating translation prematurely after failed\ntranslation of layer sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo20130620-29277-1vedg2r (use -skipfailures to skip errors)
Omitting the primary key name and letting PostgreSQL decide over it succeeds as pgsql truncates the name to take into account the suffix (it adds _pkey as a suffix).
See this pgsql session:
strk=# CREATE TABLE "a123456789b12345678c123456789d123456789e123456789f123456789g123" ( a int, constraint "a123456789b12345678c123456789d123456789e123456789f123456789g123_pl" primary key (a) ); NOTICE: identifier "a123456789b12345678c123456789d123456789e123456789f123456789g123_pl" will be truncated to "a123456789b12345678c123456789d123456789e123456789f123456789g123" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a123456789b12345678c123456789d123456789e123456789f123456789g123" for table "a123456789b12345678c123456789d123456789e123456789f123456789g123" ERROR: relation "a123456789b12345678c123456789d123456789e123456789f123456789g123" already exists strk=# CREATE TABLE "a123456789b12345678c123456789d123456789e123456789f123456789g123" ( a int, primary key (a) );NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a123456789b12345678c123456789d123456789e123456789f12345678_pkey" for table "a123456789b12345678c123456789d123456789e123456789f123456789g123" DEBUG: building index "a123456789b12345678c123456789d123456789e123456789f12345678_pkey" on table "a123456789b12345678c123456789d123456789e123456789f123456789g123" LOG: duration: 212.168 ms statement: CREATE TABLE "a123456789b12345678c123456789d123456789e123456789f123456789g123" ( a int, primary key (a) ); CREATE TABLE
Change History (7)
comment:1 by , 11 years ago
comment:2 by , 11 years ago
Status: | new → assigned |
---|
Strk - I'm struggling to figure out how to turn this into a patch I can apply.
Are you confident that this autonaming of constraints will on older Postgresql versions?
comment:3 by , 11 years ago
Keywords: | postgres postgis added |
---|
Patch resolved via https://github.com/OSGeo/gdal/pull/16.patch and applied in trunk (r26099).
If this causes no problems, and assuming strk responds positively about backward compatability I think we could migrate this into 1.10 branch. I'll leave open a bit towards that end.
comment:4 by , 11 years ago
This works even on archaic PostgreSQL 7.4 (and on my PostgreSQL 8.4 too), so should be safe backporting :
$ /opt/PostgreSQL/7.4/bin/psql -p 5436 -d autotest -U postgres Welcome to psql 7.4.29, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit autotest=# create table stupid_table(my_id serial, primary key(my_id)); NOTICE: CREATE TABLE will create implicit sequence "stupid_table_my_id_seq" for "serial" column "stupid_table.my_id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stupid_table_pkey" for table "stupid_table" CREATE TABLE autotest=# create table stupid_table_with_a_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_longname(my_id serial, primary key(my_id)); NOTICE: identifier "stupid_table_with_a_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_longname" will be truncated to "stupid_table_with_a_very_very_very_very_very_very_very_very_ver" NOTICE: CREATE TABLE will create implicit sequence "stupid_table_with_a_very_very_very_very_very_very_ver_my_id_seq" for "serial" column "stupid_table_with_a_very_very_very_very_very_very_very_very_ver.my_id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stupid_table_with_a_very_very_very_very_very_very_very_ver_pkey" for table "stupid_table_with_a_very_very_very_very_very_very_very_very_ver" CREATE TABLE
comment:5 by , 11 years ago
Thanks roualt, I don't have access to a 7.x anymore... Thanks Frank, I usually merge commits to a local git-svn branch and then svn-commit it after testing
comment:6 by , 11 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Back ported patch to 1.10 branch (r26100).
comment:7 by , 11 years ago
Milestone: | → 1.10.1 |
---|
Will be fixed by https://github.com/OSGeo/gdal/pull/16
Lacks an automated testcase