Postgresql materialized view doesn't open

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)
Original Redmine Issue: 13060
Affected QGIS version: 2.8.1
Redmine category:data_provider/postgis

---

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 (zimirrr leonid)
• tttt.png (zimirrr leonid)

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

but it opens layer with DBManager!

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

i checked tableoid within my view, it is unique

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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?

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

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

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

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

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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.

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

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?

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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

[qgib]

Author Name: Nicolas Rochard (Nicolas Rochard)

---

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

---

• fixed_version_id was configured as Version 2.10

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• fixed_version_id removed Version 2.10

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

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

---

• 9431 was configured as test.png
• assigned_to_id was changed from Jürgen Fischer to Giovanni Manghi

---

• test.png (zimirrr leonid)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

---

• assigned_to_id removed Giovanni Manghi

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

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

---

• 9432 was configured as tttt.png

---

• tttt.png (zimirrr leonid)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

[qgib]

Author Name: zimirrr leonid (zimirrr leonid)

---

thx a lot.
It worked!

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

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

---

• status_id was changed from Feedback to Closed
• resolution was changed from to worksforme