Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

To reopen a really old thread, I like the exclamation point/warning icon with the tool tip in 2.12. Figured I'd give some good feedback instead of complaining ever now and then :) Jim ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info:

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

I wonder if we could make use of the stats the query planner uses. http://www.postgresql.org/docs/9.1/static/monitoring-stats.html http://www.postgresql.org/docs/9.1/static/planner-stats.html I don't know how tightly this would couple us to any single version of postgresql though. Jim On

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Andrea, On Fri, 04. Sep 2015 at 11:20:39 +0200, Andrea Peri wrote: > Something like this (see image attached). Hm, the problem seems to be that it's to much to ask a user to select one unique key field of a view. Doesn't this make it even worse? Letting him select multiple columns in the

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Something like this (see image attached). 2015-09-04 10:29 GMT+02:00 Jürgen E. : > Hi Andrea, > > On Fri, 04. Sep 2015 at 10:18:31 +0200, Andrea Peri wrote: >> The first is to allow a standard access to a view only readonly . > > >> I complex step could be to link the field of

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Sorry for the double email. Also, I wonder how much we could extract from EXPLAIN SELECT * FROM view; (alternatively EXPLAIN SELECT * FROM view WHERE field1 = 1;) and if we could tease information out of that. This all seems more complicated than it needs to be though. Jim On 09/04/2015 02:42

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Explain also gives approx row counts and other info. On September 4, 2015 9:49:52 AM EDT, Sandro Santilli wrote: >On Fri, Sep 04, 2015 at 08:31:39AM -0400, James Keener wrote: >> Sorry for the double email. Also, I wonder how much we could extract >> from EXPLAIN SELECT * FROM

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

On Fri, Sep 04, 2015 at 08:31:39AM -0400, James Keener wrote: > Sorry for the double email. Also, I wonder how much we could extract > from EXPLAIN SELECT * FROM view; (alternatively EXPLAIN SELECT * FROM > view WHERE field1 = 1;) and if we could tease information out of that. Information can be

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Am 03.09.2015 um 23:41 schrieb Sandro Santilli: On Wed, Sep 02, 2015 at 08:48:34AM +0200, Bernhard Ströbl wrote: Am 01.09.2015 um 17:49 schrieb Matthias Kuhn: 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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Andrea, by reading out pg_constraint [1] if a UNIQUE constraint is defined the uniqueness is guaranteed by the db, no need for QGIS to also check it. Bernhard [1] http://www.postgresql.org/docs/9.4/interactive/catalog-pg-constraint.html Am 04.09.2015 um 08:42 schrieb Andrea Peri: Ho,

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Sandro, On Thu, 03. Sep 2015 at 23:44:51 +0200, Sandro Santilli wrote: > > I'd note that supporting complex primary keys would also allow > qgis to identify rows based on an expensively computed fingerprint > of the hash of the canonical form of each and every field. > As said we already

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Ho, How do qgis to analyze the table to retrieve the unique? A loop on every record or a SQL to count distinct ? A. Il 04/set/2015 08:01, "Bernhard Ströbl" ha scritto: > > > Am 03.09.2015 um 23:41 schrieb Sandro Santilli: > >> On Wed, Sep 02, 2015 at 08:48:34AM +0200,

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Andrea, On Fri, 04. Sep 2015 at 08:42:31 +0200, Andrea Peri wrote: > How do qgis to analyze the table to retrieve the unique? > A loop on every record or a SQL to count distinct ? For vies probably yes. And if that doesn't help, any combination of fields (up to all fields). And then hope

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi, I guess could be two kind of approaches alternative to the actual. The first is to allow a standard access to a view only readonly . Avoiding so the need for a pk field. For who need a view writable in qgis. The approach could be to allow in the properties of the layer in the field section

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Andrea, On Fri, 04. Sep 2015 at 10:18:31 +0200, Andrea Peri wrote: > The first is to allow a standard access to a view only readonly . That's already the case - but it doesn't solve that various other functions (eg. attribute table, selection) also require a unique feature id. > select

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

On Tue, Sep 01, 2015 at 01:15:59PM -0400, James Keener wrote: > 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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

On Wed, Sep 02, 2015 at 08:48:34AM +0200, Bernhard Ströbl wrote: > Am 01.09.2015 um 17:49 schrieb Matthias Kuhn: > >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. > > I hope you mean "relation" not

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi all, I run a database with many tables and views. All views that are to be used in QGIS have a column suitable as key (and I never had a problem in creating one). Most tables and views are loaded by scripts into my users' QGIS, but not all. My users do not know if a relation they try to

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi James, On Tue, 01. Sep 2015 at 08:50:27 -0400, James Keener wrote: > At the very least, don't tell people to use ROW_NUMBER() as a primary > key. This is just terrible advice! FTR I didn't suggest using row_number(). > Also, I'm running 2.10.1 and when I add a view on a table with no unique

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Leknín, On Tue, 01. Sep 2015 at 18:42:56 +0200, Leknín Řepánek wrote: > Situation with a view without unique (and indexed) key is bad database > model, or bad query. Not so sure about that - you might have queries (or views) that join two or more tables that have proper primary keys and

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi James, On Tue, 01. Sep 2015 at 13:15:59 -0400, James Keener wrote: > I'd alo 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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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. BTW: resolution of #11317 is set to "wontfix" --strk; On Mon, Aug 31, 2015 at 09:00:15PM +0200, Luca Lanteri wrote: >

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

2015-08-06 20:58 GMT+02:00 Jürgen E. : > Hi Randal, > > On Thu, 06. Aug 2015 at 13:53:08 -0400, Randal Hale wrote: > > In 2.8.x I can add the view and the Primary key is automatically > displayed. > > No, you're supposed to select the primary key. In 2.8.x you also have to >

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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. Why can I select non-unique columns as primary keys and after loading the

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

I realized I'm coming off very antagonistic and don't mean to be. I understand the importance of a primary key, but don't want users to be told to do anti patterns and non beneficial things like row_number. I also feel that the ui should be consistent and not have surprises. Tables and views

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

I'm not saying the old behaviour is correct. I'm saying the new behavior isn't friendly to non expert users. Loading from a database is no more difficult than loading a shapefile in most cases. I have clients and students do it quite often and "windowing function" means nothing and "primary

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi, I noticed that this new behavior has been backported to 2.8.3. I think this behavior should be considered a regression because now it's very uncomfortable to load views, specially when I have to load many views. I also noticed that lot of users are confused, because they thinks that views

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

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

[Qgis-user] QGIS 2.10.1 adding PostGIS Views

I noticed this on the Linux/Windows version of QGIS (2.10.1). When adding a view in Postgresql/Postgis (9.3/2.1.3) the primary key (all views have primary keys) has to be selected and with a single click it appears. In 2.8.x I can add the view and the Primary key is automatically displayed.

Re: [Qgis-user] QGIS 2.10.1 adding PostGIS Views

Hi Randal, On Thu, 06. Aug 2015 at 13:53:08 -0400, Randal Hale wrote: In 2.8.x I can add the view and the Primary key is automatically displayed. No, you're supposed to select the primary key. In 2.8.x you also have to choose from all columns, but first one is preselected. If that happens to