[Qgis-user] QGIS 2.10.1 adding PostGIS Views

[James Keener]

I don't want to reopen the ticket, as that's not my place, but I would
like to comment on it and your comment here:

> 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).

While I understand from the applications POV how a single primary key
makes sense, from the databases POV querying off of ROW_NUMBER() will
force a sequence scan, which may be _extremely_ slow. While this isn't
exactly your issue, advocating a "solution" to your problem that creates
a bad user experience isn't a good idea.

At least selecting based on geometry would be able to invoke the
(likely-to-exist) index on the underlying table(s), where there is no
possible way to index row_number() and it will _always_ sequence scan.

Additionally, ROW_NUMBER() _isn't_ a pk.  If the view has no ordering
being forced (and even if there is!), then additions to the underlying
table could reörder the order of the output rows, changing ROW_NUMBER().
If you were querying against the ROW_NUMBER() column, the updated view
the _wrong_ row returned -- this is a very very bad thing!

At the very least, don't tell people to use ROW_NUMBER() as a primary
key.  This is just terrible advice!

Also, I'm running 2.10.1 and when I add a view on a table with no unique
values, and even if I select a non-unique field as my primary key, QGIS
will happily load the view, not complain about the cardinality of the
"primary key" being less than the carnality of the rows, and proceed to
draw the geometries correctly and let me select features and will show
me the correct attributes. As a user, I felt very frustrated by this
(especially because the default window size hid the dropdown to select
the pk and there was no indication at all at what was wrong!) and like I
had to jump through a hoop for the programmer's convenience, not for any
matter that actually mattered.

As a programmer, I know what you're trying to do, but since it doesn't
appear to matter anyway, what's the point.  Try this.

> [local]:5432 jim at geodata=# create table testing_mult (a int, t text);CREATE TABLE
> Time: 6.248 ms
> [local]:5432 jim at geodata=# select AddGeometryColumn('public','testing_mult','g',4326,'POINT', 2);
>                  addgeometrycolumn                  
> ----------------------------------------------------
>  public.testing_mult.g SRID:4326 TYPE:POINT DIMS:2 
> (1 row)
> 
> Time: 5.349 ms
> [local]:5432 jim at geodata=# insert into testing_mult values (1, 'thingy', st_geomfromtext('point(1 1)',4326 ));
> INSERT 0 1
> Time: 3.062 ms
> [local]:5432 jim at geodata=# insert into testing_mult values (1, 'this', st_geomfromtext('point(1 0)',4326 ));
> INSERT 0 1
> Time: 2.865 ms
> [local]:5432 jim at geodata=# insert into testing_mult values (1, 'that', st_geomfromtext('point(0 0)',4326 ));
> INSERT 0 1
> Time: 2.873 ms
> [local]:5432 jim at geodata=# create view testing_mult_view as select * from testing_mult ;
> CREATE VIEW
> Time: 6.055 ms

Now, go to QGIS, select column 'a' as the "primary key".  You'll see 3
points, and you'll be able to get the correct attribute values for each

Moreover, you can load the test_multi without having to give it a pk, so
again, as a user, I'm extremely confused by inconsistent behavior,
doubly so if I didn't create the DB and don't understand what a view is.

If tables can be used without selecting a primary key, why must views?
and why must the UI be so tight-lipped?  "Select a primary key" as the
tool-tip means absolutely nothing to someone not used to databases. It
means less given that you can't see the "primary key" column in the
window with the window's default size. It means even less when someone
who understands what a primary key is seeing tables that are usable and
that don't have one.

What is the point of having the user select a "primary key" if QGIS is
accepting non-unique fields with no issue?  QGIS needs to either
complain about the lack of uniqueness, or drop forcing the user to
select one.

Jim

PS: I know this isn't the place, but the db manager gives absolutely,
positively terrible feedback about this.

On 09/01/2015 04:59 AM, Jürgen E. Fischer wrote:
> Hi James,
> 
> On Mon, 31. Aug 2015 at 15:05:05 -0400, James Keener wrote:
>> I don't mind the behavior as much as it's entirely unclear, especially
>> when the window is small and you have to scroll to see the drop down,
>> what is the problem.
> 
>> There should be an error message (or even an error/warning icon with the
>> tool tip saying there is on PK), along with possible remedies presented
>> to the user.
> 
> Well, it's not an error (same as having to select a geometry type or SRID,
> before being able to add a layer that doesn't have a 'proper' constraint).
> 
> And a tooltip is already there - although it could be more visible.
> 
> BTW the ticket for this is #11317.
> 
> 
> Jürgen
> 
> 
> 
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>