[Qgis-user] QGis 1.7.4 possible bug with PostgreSQL/Postgis manual integer primary key?

Tue May 15 23:41:31 PDT 2012

Hi Lee,

Am 15.05.2012 18:51, schrieb Lee Hachadoorian:
> On Tue, May 15, 2012 at 8:31 AM, Bernhard Ströbl
> <bernhard.stroebl at jena.de>  wrote:
>>
>>
>> Am 15.05.2012 14:16, schrieb F T:
>>
>>> I think the QGis code should not have this specific behaviour and should
>>> follow the standards.
>>
>>
>> Is there a standard for this? From the db's point of view it is always the
>> responsibility of the user to take care for proper primary keys. He can
>> achieve this by a) using a sequence or b) using a software taking care of it
>> (like QGIS) or c) error-prone manual entering.
>
> The use of an arbitrary key is commonplace, but not part of the SQL
> standard or required for normal form. Many table designs use
> meaningful primary keys, defined from a unique key within the data. To
> say that "A key is just a technical field used to identify a dataset.
> It should not contain any information." is contentious. There is
> specifically *not* a standard in SQL. I think the 'standard" Fabrice
> is referring to is this, that keys are not prohibited from being
> meaningful, and that the application should not just discard data that
> it allows you to enter and which is not in violation of  your table
> design.

I agree with the last sentence (see below). To explain my opinion: My 
users often try to pack a lot of information in the key field and I 
always advise them to not do so because if the information changes they 
have to change the key, too (normally they also want the information 
redundantly in a couple of fields anyways, so the table is not 
normalized then). From that experience _I_ would always advice to not 
put any information in the key. Thus the key can stay the same for the 
life cycle of a dataset.

>
>>> In my case, the values of the key come from an other application and I
>>> have to type these values to link the geometrical objects to the
>>> database informations.
>>
>>
>> So this is a foreign key. I would recommend to use a dedicated field for
>> this.
>
> It is not unreasonable to want a foreign key in a one-to-one
> relationship to match the primary key of the related table.

ok, but it does not harm to add another field, either, because as I 
understood, the data referred to are not stored in the same database but 
"come from an other application" hence my recommendation.

>
> QGIS is not alone among GIS applications in requiring the use of a
> unique integer key in source data, and of course, it can't tell the
> difference between a key which is meaningless or meaningful in terms
> of data design and how the data is used elsewhere. It is a little odd
> that QGIS allows you to edit the field at all, since applications
> which use surrogate keys (arbitrary, meaningless integer values)
> usually also don't allow those keys to be edited. It's even more odd
> that QGIS allows you to edit the field but then discards a value which
> does not violate database rules.

I agree, so if the user enters a value it should be passed to the 
database and not discarded. If it violates any database rules postgresql 
complains anyways. So this could be a bug then.
>
> BTW, I found I could *edit* the primary key field of a table with a
> key field typed as serial, but when I tried to add a new feature, any
> value entered manually generated a primary key violation. The column
> value in QGIS defaulted to nextval('sequence_name'::regclass),
> matching the column default in the table definition, but also
> generated a primary key violation. When I cleared the field
> completely, the row was added with the next available value from the
> sequence.

not confirmed here (PostgreSQL 8.4, QGIS 1.7.4)
primary key is integer with a nextval('sequence') default
adding features the key field defaults to nextval('sequence')
case 1) no change => nextval applied
case 2) change value => nextval applied (no error, though)

expected behaviour for case 2) would be to not apply the default
shall we file a ticket for this?

Bernhard

>
> --Lee
>
>>
>> Bernhard
>>>
>>>
>>> I found a workaround : if I change the field type of the key from
>>> integer to bigint then everything works as attended...
>>> QGis doesnt tries anymore to give a value when we don't need it.
>>> But this is not very logical isn't it?
>>>
>>> Fabrice
>>>
>>>
>>> 2012/5/15 Bernhard Ströbl<bernhard.stroebl at jena.de
>>> <mailto:bernhard.stroebl at jena.de>>
>>>
>>>
>>>     Hi Fabrice,
>>>
>>>     I think this is the intended behaviour. If you leave it to the user
>>>     to enter the primary key then the db might complain about non-unique
>>>     keys. On the other hand why would you _want_ to assign a certain
>>>     key? A key is just a technical field used to identify a dataset. It
>>>     should not contain any information.
>>>     On my db I have three tables (point, line, polygon) that insert into
>>>     the same table representing a n:m relation. I use a sequence on the
>>>     db and a trigger to assign the next sequence value on INSERT. Apart
>>>     from that I have no need to interfere with my pk values and I am
>>>     glad, that QGIS takes care of that.
>>>
>>>     Bernhard
>>>
>>>     Am 15.05.2012 13:07, schrieb F T:
>>>
>>>
>>>         Hi all,
>>>
>>>         We are not able to set explicitly the value of an integer
>>>         primary key field.
>>>
>>>         It is possible to set this value with pgAdmin.
>>>         But when the feature is created with QGis, it takes the next max
>>>         value
>>>         of the field and not the spécified value...
>>>
>>>         To reproduce this strange behaviour :
>>>
>>>         -- create a table in the schema public with an integer primary key
>>>         CREATE TABLE the_table
>>>         (
>>>            the_key integer NOT NULL,
>>>            the_obs character varying(150),
>>>            geom geometry,
>>>            CONSTRAINT the_table_pkey PRIMARY KEY (the_key),
>>>            CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
>>>            CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) =
>>>         'MULTILINESTRING'::text OR geom IS NULL),
>>>            CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
>>>         )
>>>         WITH (
>>>            OIDS=TRUE
>>>         );
>>>         -- spatialize the table
>>>         INSERT INTO geometry_columns(f_table___catalog, f_table_schema,
>>>
>>>         f_table_name, f_geometry_column, coord_dimension, srid, "type")
>>>         VALUES('', 'public', 'the_table', 'geom', 2, 2154,
>>>         'MULTILINESTRING');
>>>
>>>         Then in QGIS :
>>>         - add this table to a project
>>>         - create 1 line with the_key=100 and save the edition ->  open the
>>>         attribute table and you see the_key=0
>>>         - create 1 line with the_key=102 and save the edition ->  open the
>>>         attribute table and you see the_key=1
>>>
>>>         If I modify the value of the_key in pgAdmin, by example put the
>>>         value
>>>         1000 instead of 1, then the next line created with QGis will
>>>         have the
>>>         value 1001.
>>>
>>>         Any help is welcome !
>>>         Thanks
>>>
>>>         Fabrice
>>>
>>>
>>>
>>>
>>>

________ Information from NOD32 ________
This message was checked by NOD32 Antivirus System for Linux Mail Server.
http://www.nod32.com