Bug report #2137

Wrong ID provided to the PostGIS on save

Added by fsat - almost 10 years ago. Updated almost 10 years ago.

Status:Closed
Priority:Low
Assignee:nobody -
Category:Data Provider
Affected QGIS version: Regression?:No
Operating System:Gentoo Easy fix?:No
Pull Request or Patch supplied: Resolution:fixed
Crashes QGIS or corrupts data: Copied to github as #:12197

Description

I'm trying to use qgis with my PostGIS database. I have only 3 tables for different kinds of components. So I've created a couple of updatable views to make a work with QGIS more comfortable and to keep my internal structure. But when I'm setting a unique ID to the element (in QGIS) I receive a complaints about duplicate ID.
For example: I have an empty view (but not empty table with elements), but I know - the ID=15 is unique, I create a new element in the layer associated with this view and set the ID to 15, but on save I receive error. After the empty record created manualy with ID=15 and I'm saving to this view - I see ID's started from 16. Inspite of provided data to ID field it is counted automaticaly.

gis.sql - Database schema (8.2 KB) fsat -, 2009-11-22 08:57 PM

Associated revisions

Revision 6024c32b
Added by Jürgen Fischer almost 10 years ago

fix #2137

git-svn-id: http://svn.osgeo.org/qgis/trunk/[email protected] c8812cc2-4d05-0410-92ff-de0c093fc19c

Revision 8a66aee7
Added by Jürgen Fischer almost 10 years ago

fix #2137

git-svn-id: http://svn.osgeo.org/qgis/[email protected] c8812cc2-4d05-0410-92ff-de0c093fc19c

History

#1 Updated by Jürgen Fischer almost 10 years ago

can you provide sample data?

#2 Updated by fsat - almost 10 years ago

Schema is attached to the ticket, to populate it with sample data just execute the following statements.

insert into maps (ID, NAME) values(1, "map1");
insert into layers (ID, NAME, MAP_ID) values(1, "layer1", 1);

-------------------------------------------------------------
After that you can add LINESTRING layer in QGIS using view with name "map1_l1_l" and draw a line.
And after that add POINT layer in QGIS using view with name "map1_l1_s" and reproduce my error.

As you can see from section:

query := 'create or replace rule '|| view_name || 'i ' || '
as on insert to '|| view_name || '
do instead (
insert into layer_elements (layer_id, id, name) values('|| lid ||', NEW.id, NEW.name);
insert into ' || le ||' (id, the_geom) values(NEW.id, NEW.the_geom);
)';

The ID is not calculated automaticaly, I'm using the provided one, without defaults.

PostGIS version - 1.4.0, Postgresql version - 8.2.14, QGIS version - 1.3.0

#3 Updated by fsat - almost 10 years ago

Sorry, little mistake in queries. Following are correct ones.

insert into maps (ID, NAME) values(1, 'map1');

insert into layers (ID, NAME, MAP_ID) values(1, 'layer1', 1);

#4 Updated by Jürgen Fischer almost 10 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

should be fixed in 8a66aee7 (SVN r12236) - hopefully without breaking something else.

#5 Updated by Jürgen Fischer almost 10 years ago

Replying to [comment:5 jef]:

should be fixed in 8a66aee7 (SVN r12236) - hopefully without breaking something else.

actually it was. 58c68efc (SVN r12419) roll 8a66aee7 (SVN r12236) back and deduces the default value the column the id column of the view is derived from and uses that to retrieve new ids.

Also available in: Atom PDF