Bug report #13643

After adding Postgis view I can't see records in attribute table

Added by Piotr Kania over 8 years ago. Updated over 8 years ago.

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 over 8 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 over 8 years ago

  • Category set to Data Provider/PostGIS

#3 Updated by Jürgen Fischer over 8 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 over 8 years ago

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

closing for lack of feedback, reopen if necessary.

Also available in: Atom PDF