[Qgis-user] QGIS 2.10.1 adding PostGIS Views

[Matthias Kuhn]

PS: I'm sure a pull request for compound primary keys will gladly be
merged :-)

On 09/01/2015 07:15 PM, James Keener wrote:
> 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.
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150901/af6088ed/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: OpenPGP digital signature
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150901/af6088ed/attachment.sig>