Bug report #11317

PostGIS views not handled correctly

Added by Harry Clarke over 5 years ago. Updated almost 2 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:2.4.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:19613

Description

QGIS is not handling views correctly demonstrated by some examples.

1) Shape file containing multiple polygons imported into postgreSQL. View created to extract one polygon.
DBManager in QGIS correctly shows with geometry MULTIPOLYGON, and icon next to the view as multipolygon

2) Table created in PostgreSQL from x.y data, using the command
UPDATE table SET the_geom = ST_SetSRID(ST_Point(x,y),27700);
DBManager in QGIS correctly shows this as POINT data

3) simple View created in PostgreSQL from the table 2) above, say of just the first 100 records
DBManager in QGIS displays a question mark symbol next to the view, and states in the info tab under PostGIS
"There isn't entry in geometry_columns"
The Geometry is shown as GEOMETRY, even though the source table the Geometry is shown as POINT.
This View can be added as a layer into QGIS via the DBManager using the command Add to Convas.
However, if the command Add PostGIS layer is used, an error message is returned (see example 4 below)
Changing the primary key to the primary key of the base table enables the layer to be added using the Add PostGIS layer command

4) complex aggregated view created in PostgreSQL from the table in 2) and another table, with the view containing the original geometry column. This is possible as the original table contained multiple records at the same point.
DBManager in QGIS shows the same information as in 3) above.
However, when attempting to add this as a layer using DBManager, nothing happens.
If the command Add PostGIS layer is used, the following error message is given

dbname='mydb' host=localhost port=5432 user='postgres' password='XXXX{2G.N' sslmode=disable key='months_visited' srid=27700 type=POINT table="myschema"."vc17_tetrad_months" (the_geom) sql= is an invalid layer - not loaded

Changing the Primary key from the arbitrary value chosen by QGIS to something that is unique enables the layer to added via the Add PostGIS layer command

There are no obvious errors in examining the view in PostgreSQL

In the table used in the Add PostGIS layer, examples 2,3 and 4 are all shown as two lines, the first greyed out and the second line showing the information in all cases as
Column = the_geom
Data Type = Geometry
Spatial Type = Point
SRID = 27700
For example 1 above, Spatial Type = Multi Polygon and SRID = 0
The Primary key column seems to pick an arbitrary column as the primary Key

PostgreSQL version 9.3.5
PostGIS version 2.1
Windows 8.1 64 bit

Associated revisions

Revision a54c2d02
Added by Jürgen Fischer almost 5 years ago

add postgres layer: enforce selection of primary key for views (refs #11317)

Revision 7d009473
Added by Jürgen Fischer almost 5 years ago

add postgres layer: enforce selection of primary key for views (refs #11317)

(cherry picked from commit a54c2d02cf580cbc90df2dbfe21bd160442528fc)

History

#1 Updated by Jürgen Fischer over 5 years ago

you need to choose a unique column. QGIS doesn't choose it just offers numeric columns.

#2 Updated by Harry Clarke over 5 years ago

So what are you supposed to do in a view that has no unique column?
The original table had a primary key, but when aggregated views are created, two or more columns might make a unique key. But PostgreSQL does not allow a primary key to be defined, and QGIS only allows one column to be defined as a "primary key". Why does QGIS need a primary key?

#3 Updated by Antonio Locandro over 5 years ago

could you add an artificial unique key (gid) just for QGIS in the view? That should solve your issue I think

#4 Updated by Jürgen Fischer over 5 years ago

  • Category set to Data Provider/PostGIS

#5 Updated by zimirrr leonid over 5 years ago

  • Target version set to Version 2.6

if add unique id column to a view (I used "row_number() over () as qgisid") everything works as it should

#6 Updated by Aren Cambre over 5 years ago

  • Category changed from Data Provider/PostGIS to GUI

This is a user interface bug. The problem is that we are not informed of the problem before we hit the Add button in the Add PostGIS Table(s) dialog. In other words, the UI is not telling us about a preventable problem until we are after the point where we can fix the problem.

If there is a problem preventing me from adding a selected PostGIS layer, view or not, I should not be able to hit the Add button and should be informed of the problem.

#7 Updated by Jürgen Fischer over 5 years ago

  • Resolution set to wontfix
  • Target version deleted (Version 2.6)
  • Status changed from Open to Closed
  • Category changed from GUI to Data Provider/PostGIS

Harry Clarke wrote:

So what are you supposed to do in a view that has no unique column?

Change the view.

Why does QGIS need a primary key?

To create (persistent) feature ids that are used for selection (and deletion and changing of attribute or geometries - but that doesn't apply to normal views).

Deducing which numeric column might have unique values might be expensive and therefore is not done.

#8 Updated by Sandro Santilli almost 5 years ago

  • Target version set to Version 2.10
  • Status changed from Closed to Reopened
  • % Done changed from 0 to 100
  • Resolution changed from wontfix to fixed/implemented

I think it was an error to backport this to 2.8.3 as it changes the behavior, as noted by a user who was negatively affected by the change: https://lists.osgeo.org/pipermail/qgis-user/2015-August/033357.html

#9 Updated by Giovanni Manghi over 4 years ago

  • Target version deleted (Version 2.10)

#10 Updated by Giovanni Manghi about 3 years ago

  • Regression? set to No
  • Easy fix? set to No

#11 Updated by Jürgen Fischer over 2 years ago

  • Description updated (diff)

#12 Updated by Harry Clarke almost 2 years ago

  • Status changed from Reopened to Closed

The way layers are added in QGIS v3 has changed, so the original issue seems to have been resolved

Also available in: Atom PDF