Opened 11 years ago

Last modified 6 years ago

#62 new patch

Qgis only supports postgres keys of type int

Reported by: g_j_m Owned by: jef
Priority: major: does not work as expected Milestone: Version 1.7.0
Component: Data Provider Version: Trunk
Keywords: Cc: mloskot, springmeyer, yjacolin, aren@…
Must Fix for Release: No Platform: All
Platform Version: Awaiting user input: no

Description

Transfer of item 1337919 from sourceforge.

Qgis should support postgres table keys of types other than int. There are workarounds for most situations but the better solution would be to directly support other key types.

Attachments (1)

featureid.diff (52.8 KB) - added by jef 9 years ago.
patch to 64bit keys

Download all attachments as: .zip

Change History (21)

comment:1 Changed 11 years ago by stevehalasz

A Debian was filed about this issue:

http://bugs.debian.org/355944

comment:2 Changed 10 years ago by anonymous

Awaiting user input: unset
Must Fix for Release: No

comment:3 Changed 9 years ago by timlinux

Type: enhancementdefect

comment:4 Changed 9 years ago by jef

Owner: changed from g_j_m to jef

comment:5 Changed 9 years ago by jef

Milestone: Version 1.0.0Version 2.0.0

comment:6 Changed 9 years ago by jef

Type: defectenhancement

comment:7 Changed 9 years ago by gjm

An email from the developers mailing list (Sun, 31 Aug 2008 02:42:42 +1200):

Currently QGIS requires a int4 column with unique IDs for accessing data in a PostGIS database. I routinely work with OpenStreetMap? data which uses int8 for its IDs. Currently the OSM IDs still fit into an int4, but this will not work forever. At the moment its a bit of a hassle that I always have to keep this limitation in mind.

Is the limitation on int4 columns something hardcoded deep in the code or is it something that could be changed reasonably easy? I understand the need for a unique ID column, but it would be nice if the data type was more flexible. I think at least int2, int4, and int8 should be supported.

Jochen Topf

Changed 9 years ago by jef

Attachment: featureid.diff added

patch to 64bit keys

comment:9 Changed 9 years ago by mloskot

Cc: mloskot added

comment:10 Changed 9 years ago by springmeyer

Cc: springmeyer added

+1 to this being made more flexible. uDig/geotools provides a much more noticeably more flexible approach to reading in postgis tablea (even without a unique id field), so perhaps this could might be a good reference:

http://svn.geotools.org/trunk/modules/plugin/postgis/src/main/java/org/geotools/data/postgis/ autogeneration of fids: http://svn.geotools.org/trunk/modules/plugin/postgis/src/main/java/org/geotools/data/postgis/fidmapper/

comment:11 Changed 8 years ago by yjacolin

Cc: yjacolin added

comment:12 Changed 8 years ago by jef

since r10474 the postgres providers resorts to using ctid as feature id, if no other usable primary key is found.

As a ctid currently consists of a 32bit block number and and 16bit offset, that option is limited to tables that have block numbers below 0x10000.

That problem would also disappear by applying the patch above to implement 64bit feature ids.

comment:13 Changed 8 years ago by lutra

Type: enhancementpatch

comment:14 Changed 8 years ago by pcav

Milestone: Version 2.0.0Version 1.3.0

comment:15 Changed 7 years ago by pcav

What prevents us from applying this patch?

comment:16 Changed 7 years ago by jef

Milestone: Version 1.5.0Version 1.6.0

comment:17 Changed 6 years ago by willfurnass

It would also be nice if PostGIS sequences could be used for layer keys.

I frequently wish to view the result of applying PostGIS aggregate functions using QGIS but this can't be done using relational views as the results of such queries lack anything that could be used as a primary key; I therefore end up littering my db with temporary tables created using 'SELECT...INTO...' so as to view the results of aggregate queries in QGIS. Allowing a self-incrementing sequence col within a view to be used as a layer key would be a (slightly ugly) way around this.

comment:18 in reply to:  17 ; Changed 6 years ago by jef

Replying to willfurnass:

It would also be nice if PostGIS sequences could be used for layer keys.

They already can. If the primary key is for instance SERIAL, the default value is a nextval() call, which QGIS evaluates on INSERT.

I frequently wish to view the result of applying PostGIS aggregate functions using QGIS but this can't be done using relational views as the results of such queries lack anything that could be used as a primary key; I therefore end up littering my db with temporary tables created using 'SELECT...INTO...' so as to view the results of aggregate queries in QGIS. Allowing a self-incrementing sequence col within a view to be used as a layer key would be a (slightly ugly) way around this.

The feature ids are necessary to identify which feature is to be deleted or updated. The table/view is not only queried once, but eg. for each render operation with the current extent in the where clause. And in every query each feature is supposed to have the same id as in the previous queries. I'm not sure how that should work with sequences in views.

comment:19 in reply to:  18 Changed 6 years ago by willfurnass

The feature ids are necessary to identify which feature is to be deleted or updated. The table/view is not only queried once, but eg. for each render operation with the current extent in the where clause. And in every query each feature is supposed to have the same id as in the previous queries. I'm not sure how that should work with sequences in views.

Interestingly both of the following views can be loaded by uDig.

CREATE VIEW myview AS ( SELECT zone_ref, ST_ConvexHull(ST_Collect(wkb_geometry)) FROM table_of_points GROUP BY zone_ref);

CREATE VIEW myview_with_id AS ( SELECT nextval('some_seq'), zone_ref, ST_ConvexHull(ST_Collect(wkb_geometry)) FROM table_of_points GROUP BY zone_ref);

Does anyone know how uDig internally handles the rendering and manipulation of tables/views lacking a UNIQUE constraint? Would it be possible to allow for the viewing of such relations in some sort of read-only mode? Apologies if I'm missing something obvious here.

comment:20 Changed 6 years ago by arencambre

Cc: aren@… added
Note: See TracTickets for help on using tickets.