<html><head></head><body>I don't disagree that it's a bad practice. However, is qgis a tool that you work with or around? Do we want to say "your data must have X as part of its schema or qgis can't use it" or will qgis make the best it can with poor data. Honestly, being loudly opinionated is a valid option if the developers feel it doesn't decrease the user experience drastically.<br>
<br>
I'd also like to point out that the primary key need not be a single field. For instance (state_fips, county_fips) could be a valid key for county-based metrics. Going back to the previous question I posed, do we want to tell users to conform to a single-field primary key or will qgis accept valid schema designs. Again, it comes down to dev time vs the cost to the user experience.<br>
<br>
Jim<br><br><div class="gmail_quote">On September 1, 2015 12:42:56 PM EDT, "Leknín Řepánek" <godzilalalala@gmail.com> wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="k9mail">Situation with a view without unique (and indexed) key is bad database<br />model, or bad query. Using window function is unefficient, but i can't<br />find another way to make unique column on query with duplicities in<br />records. In query without duplicities is possible to use oid (but this<br />needs tables with oids), or ctid (but ctid is slow).<br /><br />Sorting in row_number can be defined in (ORDER BY ...). But is<br />unefficient (as you say). But works well for display smaller parts of<br />data (thousands of features).<br /><br />Materialized view can be indexed. But this is about use case and it isnt<br />a "pretty solution". <br /><br />Je;<br /><br />On Tue, Sep 01, 2015 at 11:36:53AM -0400, James Keener wrote:<br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;"> Please stop saying this. It's fine for certain situations, but it is not a<br /> permanently unique identifier for
a row. It may change when the underlying<br /> table is altered. Sure, it's unique if you read the results and keep them in<br /> memory and never talk about it again, but qgis does that on its own already.<br /> <br /> Moreover querying on it is extremely inefficient. Querying against it forces a<br /> sequence scan on the table. Depending on the table and version, it could also<br /> try to materialize the view fully in memory before doing the scan.<br /> <br /> Jim<br /> <br /> On September 1, 2015 11:31:32 AM EDT, "Leknín Řepánek"<br /> <godzilalalala@gmail.com> wrote:<br /> <br /> Workaround about primary key in view, i had use sever times is using<br /> window function "row_number() over()". It works in views and in query<br /> from database manager.<br /> <br /> Je;<br /> <br /> On Tue, Sep 01, 2015 at 04:09:49PM +0200, Sandro Santilli wrote:<br /> On Tue, Sep 01, 2015 at 02:37:22PM +0200, Jürgen E. Fischer wrote:<br /> Hi
Sandro,<br /> <br /> On Tue, 01. Sep 2015 at 13:48:33 +0200, Sandro Santilli wrote:<br /> I agree with Luca this should have been better not backported to 2.8.3.<br /> Only proper bugs should be backported, and this was a (debatable)<br /> GUI enhancement, as far as I can<br /> tell.<br /> <br /> We intend to only backport fixes and not bugs. ;)<br /> <br /> You were always supposed to select the key column - preselecting the first<br /> column was the bug (also debatable). And #11317 is a ticket that demonstrates<br /> there were unaware users.<br /> <br /> Reading #11317 I looks to me that the reported bug was about<br /> "Add PostGIS Layer" not giving the user full detail of why a<br /> layer could not be loaded: "is an invalid layer - not loaded".<br /> In this I agree with Aren here: <a
href="http://hub.qgis.org/issues/11317#note-6">http://hub.qgis.org/issues/11317#note-6</a><br /> <br /> That the first column often happens to be the primary key and and the combobox<br /> is not lexically sorted is somewhat pure luck - and unless you avoid having the<br /> key verified (using "use estimated metadata"),<br /> keeping a wrongly select<br /> column will make the layer to insert invalid.<br /> <br /> I agree that the reported regression was based on the false expectation<br /> that QGIS would pick a primary key automatically, but in the<br /> (unlikely?) case a user was aware of that and properly coded the view<br /> to ensure primary key was first (or only) numeric the change was<br /> indeed a degradation of the experience.<br /> <br /> But I agree that the tooltip that you get on disabled lines (not only for the<br /> key selection,
but also geometry type and srid) might not be visible enough<br /> (but that IMHO would be just a GUI enhancement).<br /> <br /> There should maybe be another rule about LTS backports being:<br /> debatable fixes/enhancement need to be debated more on list ?<br /> <br /> --strk;<br /> ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━<br /> <br /> Qgis-user mailing<br /> list<br /> Qgis-user@lists.osgeo.org<br /> <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br />
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━<br /> <br /> Qgis-user mailing list<br /> Qgis-user@lists.osgeo.org<br /> <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br /> <br /> <br /> --<br /> Sent from my Android device with K-9 Mail. Please excuse my brevity.<br /></blockquote></pre></blockquote></div><br>
-- <br>
Sent from my Android device with K-9 Mail. Please excuse my brevity.</body></html>