[Qgis-user] SOLVED: split features tool on postgres layer causes pkey violation

[Janneke van Dijk]

Hi Bernd,

Indeed, that was the problem.

Running

SELECT setval('a_streets_streetsid_seq', COALESCE((SELECT 
MAX(streetsid)+1 FROM a_streets), 1), false);

solved the issue. Still not sure what caused this in the first place, 
but have added your trigger to the table for the future.

greetings,
Janneke


On 26/11/2015 18:32, Bernhard Ströbl wrote:
> Hi,
>
> I cannot confirm this behaviour neither with 2.8 on Win7 nor with 2.10 
> on Linux. When asking the new features with the "i" tool after 
> splitting, one keeps its pk value the other displays 
> nextval("my_sequence"). Maybe the problem is that features have 
> received pk values by other means then nextval("sequence"), thus the 
> sequence's current start value is lower than the maximum pk value. To 
> avoid such confusion I create a BEFORE INSERT or UPDATE trigger for my 
> tables. This is the trigger function:
>
> CREATE OR REPLACE FUNCTION my_schema.my_table_ensure_sequence()
>   RETURNS trigger AS
> $BODY$
> BEGIN
>     IF (TG_OP = 'INSERT') THEN
>         NEW.id:= nextval('my_id_seq');
>     ELSIF (TG_OP = 'UPDATE') THEN
>         NEW.id := OLD.id;
>     END IF;
>     RETURN NEW;
> END; $BODY$
>   LANGUAGE plpgsql VOLATILE
>
>
> Users cannot manipulate the value of the id field anymore neither when 
> INSERTing nor when UPDATEing.
>
> Bernhard
>
>
> Am 25.11.2015 um 20:14 schrieb Janneke van Dijk:
>> Dear all,
>>
>> When using the split features tool in 2.10 and 2.12 on a postgres layer
>> (both on windows 7 and windows xp) I receive the following error after
>> trying to save the edits:
>>
>> "
>> Could not commit changes to layer a_streets
>>
>>
>> Errors: ERROR: 1 feature(s) not added.
>>
>> Provider errors:
>>
>> PostGIS error while adding features: ERROR: duplicate key value violates
>> unique constraint "streets_pkey"
>>
>> DETAIL: Key (streetsid)=(5) already exists.
>>
>> "
>> The a_streets table has a serial primary key.
>>
>> It seems similar to the bug described here:
>> https://hub.qgis.org/issues/5475
>> in 1.9 that was apparently fixed 3 years ago.
>>
>> Is this indeed a bug? Or can the tool not be used on postgres layers?
>>
>> Thanks for any insights,
>>
>> Janneke
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
> __________ Information from ESET Mail Security, version of virus 
> signature database 12628 (20151126) __________
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user