Bug report #13060

Postgresql materialized view doesn't open

Added by zimirrr leonid almost 5 years ago. Updated over 4 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:2.8.1 Regression?:No
Operating System:windows 7 Easy fix?:No
Pull Request or Patch supplied:No Resolution:worksforme
Crashes QGIS or corrupts data:No Copied to github as #:21130

Description

I have postgresql postgresql-9.4.4-1-windows-x64 and postgis 2.1.7-1 installed
I created materialized view but qgis doesn't open them
The errormsg is:
dbname='test_qgis' host=localhost port=5432 user='t' password='1' sslmode=disable key='tableoid' srid=4326 type=MULTIPOLYGON table="dspec1_qgis"."build_a_50k" (geo) sql= is an invalid layer - not loaded

test.png (19.8 KB) zimirrr leonid, 2015-12-28 01:00 AM

tttt.png (42.5 KB) zimirrr leonid, 2015-12-28 04:22 AM

History

#1 Updated by zimirrr leonid almost 5 years ago

but it opens layer with DBManager!

#2 Updated by Jürgen Fischer almost 5 years ago

  • Status changed from Open to Feedback

check the message log - 'tableoid' is probably not unique.

#3 Updated by zimirrr leonid almost 5 years ago

i checked tableoid within my view, it is unique

#4 Updated by Jürgen Fischer almost 5 years ago

zimirrr leonid wrote:

i checked tableoid within my view, it is unique

And output in the log that has more information about the origin of the problem?

#5 Updated by zimirrr leonid almost 5 years ago

Jürgen Fischer wrote:

zimirrr leonid wrote:

i checked tableoid within my view, it is unique

And output in the log that has more information about the origin of the problem?

"dbname='test_qgis' host=localhost port=5432 user='t' password='1' sslmode=disable key='tableoid' srid=4326 type=MULTIPOLYGON table="dspec1_qgis"."build_a_50k" (geo) sql= is an invalid layer - not loaded"
this is the only message in log

#6 Updated by zimirrr leonid over 4 years ago

I tried new version qgis 2.10
"Add Postgis Table(s)" shows materialized views, but they are inactive
"DB Manager - shows and adds materialized views, no problem

#7 Updated by Jürgen Fischer over 4 years ago

zimirrr leonid wrote:

I tried new version qgis 2.10
"Add Postgis Table(s)" shows materialized views, but they are inactive

Selecting the unique column should enable it.

#8 Updated by zimirrr leonid over 4 years ago

I did some tests again.

"DB Manager" adds views and materialized views, but only one per click

in version 2.8 "Add Postgis Table(s)" adds simple views but not materialized views
in version 2.10
"Add Postgis Table(s)" - can't add same views and materialized views

i use 'SELECT row_number() over () AS qgisid ...' for unique rowid
What should i add to my views to open them in version 2.10?

#9 Updated by Jürgen Fischer over 4 years ago

zimirrr leonid wrote:

in version 2.8 "Add Postgis Table(s)" adds simple views but not materialized views

In 2.8 it just adds a list of columns for the selection of the primary key and preselects the first - regardless of whether it's the unique one or not.

in version 2.10

In 2.10 you need to select a primary key before the row is selectable.

Neither 2.8 or 2.10 verify that the selected key is unique before you insert the layer (and if you "use estimated metadata" then that test is even skipped).

#10 Updated by Nicolas Rochard over 4 years ago

  • Target version set to Version 2.10

Hello,

- Server Configuration : PostGreSQL 9.3.6 + PostGIS 2.1.5
- Desktop : QGIS 2.10 64bits openSUSE (tested also QGIS 2.8.2 64bit on Windows 7)

Got same problem.

If I execute a SQL query as
- a View : it works
- a Table : it works
- a matarialized view : not working (same message than zimirrr) by menu load postgresql layer (with selecting good primary key). But I can load by dbmanager where I could overview structure, table and geometry. Right click on it 'add to canevas' and layer appears ...

#11 Updated by Giovanni Manghi over 4 years ago

  • Target version deleted (Version 2.10)

#12 Updated by Giovanni Manghi over 4 years ago

Nicolas Rochard wrote:

Hello,

- Server Configuration : PostGreSQL 9.3.6 + PostGIS 2.1.5
- Desktop : QGIS 2.10 64bits openSUSE (tested also QGIS 2.8.2 64bit on Windows 7)

Got same problem.

If I execute a SQL query as
- a View : it works
- a Table : it works
- a matarialized view : not working (same message than zimirrr) by menu load postgresql layer (with selecting good primary key). But I can load by dbmanager where I could overview structure, table and geometry. Right click on it 'add to canevas' and layer appears ...

I made some tests here, and the only way I don't get a view/materialized views added via the "add postgis layer" dialog, is by selecting a not proper column for primary key.

A sample of your data, the SQL query to create the MV and the name of the column you are trying to use as PK would help here.

Please leave feedback.

#13 Updated by zimirrr leonid over 4 years ago

  • File test.png added
  • Assignee changed from Jürgen Fischer to Giovanni Manghi

i use 'SELECT row_number() over () AS qgisid ' to create unique rowid

i've just tested it in version 2.12.0 and that's how this MVs look like in "add postgis layer" dialog

#14 Updated by Giovanni Manghi over 4 years ago

  • Assignee deleted (Giovanni Manghi)

zimirrr leonid wrote:

i use 'SELECT row_number() over () AS qgisid ' to create unique rowid

i've just tested it in version 2.12.0 and that's how this MVs look like in "add postgis layer" dialog

you see that the dialog has a horizontal scrollbar? you must scroll, look for the "primary key" column and there choose the pk for your view or mview.

#15 Updated by zimirrr leonid over 4 years ago

Sorry, didn't notice that))
Here is the pic of full dialog
I can't expand Feature id, there is only "Select..."

#16 Updated by Giovanni Manghi over 4 years ago

zimirrr leonid wrote:

Sorry, didn't notice that))
Here is the pic of full dialog
I can't expand Feature id, there is only "Select..."

double click on "select" in the "feature id" column, and select a proper pk.

#17 Updated by zimirrr leonid over 4 years ago

thx a lot.
It worked!

But why MV do need to be selected manualy and simple view don't?

#18 Updated by Giovanni Manghi over 4 years ago

  • Status changed from Feedback to Closed
  • Resolution set to worksforme

zimirrr leonid wrote:

thx a lot.
It worked!

But why MV do need to be selected manualy and simple view don't?

I just tested and views behave exactly the same as the mv.

Also available in: Atom PDF