Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#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 strk, 11 years ago

Will be fixed by https://github.com/OSGeo/gdal/pull/16

Lacks an automated testcase

comment:2 by warmerdam, 11 years ago

Status: newassigned

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 warmerdam, 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 Even Rouault, 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 strk, 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 warmerdam, 11 years ago

Resolution: fixed
Status: assignedclosed

Back ported patch to 1.10 branch (r26100).

comment:7 by Even Rouault, 11 years ago

Milestone: 1.10.1
Note: See TracTickets for help on using tickets.