[Qgis-user] QGIS 2.10.1 adding PostGIS Views

[James Keener]

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.

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.

Jim

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

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150901/37e7357a/attachment.html>