Bug report #13643
After adding Postgis view I can't see records in attribute table
Status: | Closed | ||
---|---|---|---|
Priority: | Normal | ||
Assignee: | - | ||
Category: | Data Provider/PostGIS | ||
Affected QGIS version: | master | Regression?: | No |
Operating System: | Easy fix?: | No | |
Pull Request or Patch supplied: | No | Resolution: | invalid |
Crashes QGIS or corrupts data: | No | Copied to github as #: | 21679 |
Description
Hi!
win7sp1x64, qgis-dev rev. 4cdf05f, in postgis I have multipolygon layer 'o_podst' (public, gid, geom), I create sequence '_vert_opodst_ogc_fid_seq' and view '_vertices_o_podst_' (all vertices of polygon layer 'o_podst':
CREATE SEQUENCE vert_opodst_ogc_fid_seq;
CREATE VIEW "public"."_vertices_o_podst" AS
SELECT nextval('_vert_opodst_ogc_fid_seq')AS "ogc_fid",
(ST_DumpPoints(geom)).geom ::geometry(Geometry, 2180)
FROM public.o_podst;
In that way I have in my view field that I show as feature id's source- here that's "ogc_fid".
In pgadmin everything is ok, I can add that view in openjump etc.
But in Qgis after adding that view to project attribute table has no records and log messages panel for postgis shows such info:
2015-10-21T04:11:46 1 Fetching from cursor qgis_1 failed
Database error: ERROR: can not perform nextval() inside read-only transaction
2015-10-21T04:11:46 1 Query: CLOSE qgis_1 returned 7 [ERROR: current transaction was canceled, requests ignored to the end of transaction's block
]
2015-10-21T04:11:46 1 1 cursor states lost.
SQL: CLOSE qgis_1
Result: 7 (BŁĄD: bieżąca transakcja została przerwana, polecenia ignorowane do końca bloku transakcji
)
]
2015-10-21T04:07:06 1 1 cursor states lost.
SQL: CLOSE qgis_34
Result: 7 (ERROR: current transaction was canceled, requests ignored to the end of transaction's block
(In my language - polish, it looks like:
]
2015-10-21T04:11:46 1 Fetching from cursor qgis_1 failed
Database error: BŁĄD: nie można wykonać nextval() wewnątrz transakcji tylko do odczytu
2015-10-21T04:11:46 1 Query: CLOSE qgis_1 returned 7 [BŁĄD: bieżąca transakcja została przerwana, polecenia ignorowane do końca bloku transakcji
]
2015-10-21T04:11:46 1 1 cursor states lost.
SQL: CLOSE qgis_1
Result: 7 (BŁĄD: bieżąca transakcja została przerwana, polecenia ignorowane do końca bloku transakcji
)
I can add another views, but in that case I didn't need to create sequences.
History
#1 Updated by Sebastian Dietrich about 9 years ago
I am not sure this is really a bug. QGIS uses a read only connection to read data and as nextval() modifies data, it can not be used.
Please note: QGIS now allows views (and queries) to have composite keys. Maybe using this feature you can find a primary key without using nextval().
And as always there is the row_number() alternative. But it slows your layer down because it prevents effecient filtering and should only be used as a last resort.
#2 Updated by Giovanni Manghi about 9 years ago
- Category set to Data Provider/PostGIS
#3 Updated by Jürgen Fischer about 9 years ago
- Status changed from Open to Feedback
I'd say this is the intended behaviour. The queries are run readonly where nextval
doesn't work. Why not use row_number()
?
#4 Updated by Giovanni Manghi about 9 years ago
- Resolution set to invalid
- Status changed from Feedback to Closed
closing for lack of feedback, reopen if necessary.