[Qgis-user] QGIS 2.10.1 adding PostGIS Views
Matthias Kuhn
matthias at opengis.ch
Tue Sep 1 10:51:16 PDT 2015
Hi Jim,
Feature ids are used for communication with the data source, basic CRUD
operations.
* Want to change attribute X of feature F: update table set x = newvalue
where id = featureid
* Want to query a set of features? select* from table where id in (id1,
id2, id3)... (e.g. often used by plugins with selected features)
etc... you get the point.
Feature ids are used as temporary references inside qgis when using
forms, selections and tons of other things.
Ctids are definitely a bad choice (I see you read the docs :) ). But
they tend to work and are certainly more stable than anything you can
generate on a view at query time and faster as well.
QGIS does not enforce a unique column. But it expects it.
IMHO it's a question of communicating this expectation of QGIS to the
user at an appropriate time and *optionally* offering heuristics, tools
and checks to identify suitable columns (--> it all comes down to the
requirement of development time being contributed or contracted).
Unfortunately there is no simple "one-suits-all" solution to this.
Regards Matthias
On 09/01/2015 06:58 PM, James Keener wrote:
> I guess what I'm getting at is what is it used for outside of, and not
> within qgis. Are they used for queries? (Also, ctid can change on
> vacuum, as such they can't be relied upon outside the query (with any
> guarantees.) How would the situation be different if qgis used an
> incremental, internal integer? It's a guaranteed unique value (can't
> be said for oid or any column without an actual unique constraint), it
> just can't be persisted (can't be said for the ctid or a windowed
> function).
>
> As I mentioned, qgis will accept non-unique fields as the primary key
> for a view (after being loaded, I don't expect that to be checked
> beforehand), so qgis isn't attempting to store the primary key in a
> constrained data store? This is what I'm most confused about. Why am i
> , as a user, being (poorly) to give qgis the name of the pk, when a
> non-unique field is just as acceptable?
>
> Jim
>
> On September 1, 2015 12:09:28 PM EDT, Matthias Kuhn
> <matthias at opengis.ch> wrote:
>
> On 09/01/2015 05:54 PM, James Keener wrote:
>
> We also need to ask what qgis is doing with this and why we
> need to bug the user about it at all. Why do we not big the
> user about tables without a primary key? If tables can be
> loaded without a pk, why can't views.
>
> QGIS relies on feature ids for a lot of different things to
> unambiguously identify features.
>
> For tables there is a (also not optimal) ctid which can be used as
> feature id:
> http://www.postgresql.org/docs/8.2/static/ddl-system-columns.html
>
> For views there is none (see
> http://www.postgresql.org/docs/9.1/static/rules-views.html, section 37.2.4)
>
> Why can I select non-unique columns as primary keys and after
> loading the layer get no error?
>
> Because a lot of things in QGIS rely on feature ids and see above why
> the fallback which exists for tables does not work for views.
> Especially - but not only - editing. See my proposal #4.
>
> Are these also bugs? Should I make (or find?) tickets for
> them? Jim On September 1, 2015 11:49:23 AM EDT, Matthias Kuhn
> <matthias at opengis.ch> wrote: Hi, I would like to second
> Andreas, what we need is an improvement in the UI to make the
> user aware of the problem. My proposal (please review and
> improve!!!!) 1. Let the user choose whatever table/view he
> likes. Don't disable any items. 2. If there are tables without
> a PK open a second modal dialog with an explanation of the
> problem and offer to select a pk from a combobox.
> ------------- 3. Optional: Add a button "search suitable pk"
> which looks for a suitable unique column. 4. Optional: Add a
> selection "read-only" to the combobox and do some row_number()
> or other black magic and warn the user with a big red dialog
> that he's about to do something very dangerous, unreliable and
> that his warranty is now very void. Best, Matthias On
> 09/01/2015 05:36 PM, Andreas Neumann wrote: Hi, I would regard
> the loading of layers from a database something "relatively
> advanced". Normally I prepare ready to use QGIS project to my
> users who edit and query our GIS data where they don't have to
> bother with loading layers. But you are correct that it can be
> different persons - the one who creates the view and the ones
> who are loading them. You are welcome to improve the
> situation/GUI, but please don't go back to the old behavior
> where it is an assumption that the first column in the list is
> always the primary key. Andreas On 01.09.2015 14:51, James
> Keener wrote: Why are you assuming the user who created the
> view is the one using QGIS? Jim On 09/01/2015 08:50 AM,
> Andreas Neumann wrote: Hi, I agree with Jürgen - better let
> the user choose the pkey column. If the user knows how to
> create a Postgis View he also knows how to select a primary
> key column. Andreas On 01.09.2015 14:37, 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. 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. 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). Jürgen
> ------------------------------------------------------------------------
> 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
> ------------------------------------------------------------------------
> 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/027796a4/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/027796a4/attachment.sig>
More information about the Qgis-user
mailing list