search

hdus / pg_raster_upload

QGIS plugin for uploading raster data to a PostGIS database.
GNU General Public License v2.0
5 stars 3 forks source link

Bug: index creation fails with long names #15

Closed effjot closed 1 year ago

effjot commented 1 year ago

If the name of the layer is too long (PostgreSQL default NAMEDATALEN is 64, i.e. identifiers can be max. 63 characters long), upload of the raster itself is possible, but then fails creating the index here: https://github.com/hdus/pg_raster_upload/blob/10d322d208a4e7869c55c29ff7dab219e45c647a/raster/raster_upload.py#L113

Postgres automatically truncates longer identifiers, so both relations (table and index) end up with the same name, because the _gist_idx suffix is lost. (The primary key and sequence are properly truncated, i.e. the base name is shortened to make space for the suffixes.)

This might also affect overview creation later on, because the overviews add a prefix, which increases the risk of name collisions.

I could think of different solutions:

  1. Reject too long names before uploading
  2. Truncate table name before uploading, so there is enough space for suffixes
  3. Generate index name in make_sql_create_gist() in such a way that the suffix is still there, so no collisions arise.

@hdus What do you think is the best way forward?

hdus commented 1 year ago

I think that you have to inform the user that the table name is too long. Then the user has to make the change of the table name himself. It would be great if during the input of the new name the validity is checked right away.