Bug report #277

Problem viewing PostGIS views of views

Added by alexbond-quintessa-org - over 16 years ago. Updated over 13 years ago.

Assignee:Gavin Macaulay -
Affected QGIS version: Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied: Resolution:fixed
Crashes QGIS or corrupts data: Copied to github as #:10336


I seem to have hit an ongoing bug with PostGIS vector layers.

When attempting to load a PostGIS vector layer (having hit 'add') that is constructed as a PostgreSQL table view using other table views in the same database, QGIS 0.8 preview 1 and 2 under Windows XP SP2 crashes out with no visible error message.

Table views in the same database that are dependent on tables only load without a problem.

Under version 0.7.4 of QGIS under Windows XP SP2 I also get a crash under the same circumstances, but with the same set of error messages as reported in ticket 261.

I've tried various combinations of the problem, but the issue appears to be attempting to load a table view dependent on other table views (rather than specific use of aggregate functions for example)


#1 Updated by Gavin Macaulay - over 16 years ago

What the definitions of the tables and view in question? I have tried various views of views but have had no problems.

Can you get a backtrace when qgis crashes when trying to load your view of a view. The would help pin down where it's going wrong.



#2 Updated by alexbond-quintessa-org - over 16 years ago


The SQL for onr of the views that fails is:

CREATE OR REPLACE VIEW p_v_geol_xyz_lff_top AS
SELECT p_v_geol.hole_id, p_v_geol.hole_nate, p_v_geol.hole_natn, max(p_v_geol.hole_gl - p_v_geol.geol_top) AS geol_z, p_v_geol.geol_geol, p_v_geol.f_geometry_column, min(p_v_geol.id_geol) AS id_geol
FROM p_v_geol
WHERE p_v_geol.geol_geol = 'LFF'::text
GROUP BY p_v_geol.hole_id, p_v_geol.hole_nate, p_v_geol.hole_natn, p_v_geol.f_geometry_column, p_v_geol.geol_geol;

ALTER TABLE p_v_geol_xyz_lff_top OWNER TO llwradmin;

which is dependent on the view p_v_geol, which is defined using the following SQL (and can be loaded successfully in QGIS):

SELECT p_hole.hole_id, p_hole.hole_nate, p_hole.hole_natn, p_hole.hole_gl, p_hole.hole_fdep, "GEOL"."GEOL_TOP" AS geol_top, "GEOL"."GEOL_BASE" AS geol_base, "GEOL"."GEOL_DESC" AS geol_desc, "GEOL"."GEOL_LEG" AS geol_leg, "GEOL"."GEOL_GEOL" AS geol_geol, p_hole.f_geometry_column, "GEOL".id_geol
FROM p_hole p_hole, "GEOL" "GEOL"
WHERE p_hole.hole_id = "GEOL"."HOLE_ID";

ALTER TABLE p_v_geol OWNER TO llwradmin;

The f_geometry_column holds the PostGIS geometry data. "id_geol" is the int4 primary key. "p_hole" and "GEOL" are both tables. The definitions are:

"HOLE_ID" text,
"GEOL_TOP" numeric(15,4),
"GEOL_BASE" numeric(15,4),
"GEOL_DESC" text,
"GEOL_LEG" text,
"GEOL_GEOL" text,
"GEOL_GEO2" text,
"GEOL_STAT" text,
"FILE_FSET" text,
id_geol int4 NOT NULL DEFAULT nextval('"GEOL_id_geol_seq"'::regclass),
CONSTRAINT primary_geol PRIMARY KEY (id_geol)


hole_id text NOT NULL,
hole_type text,
hole_nate numeric(15,4),
hole_natn numeric(15,4),
hole_gl numeric(15,4),
hole_fdep numeric(15,4),
hole_star date,
hole_log text,
hole_lgdt text,
hole_rem text,
hole_etrv numeric(15,4),
hole_ntrv numeric(15,4),
hole_ltrv numeric(15,4),
hole_lett text,
hole_locx numeric(15,4),
hole_locy numeric(15,4),
hole_locz numeric(15,4),
hole_endd date,
hole_bacd text,
hole_crew text,
hole_ornt numeric(15,4),
hole_incl numeric(15,4),
hole_flmd text,
hole_cbit text,
hole_ckby text,
hole_ckdt text,
hole_exc text,
hole_shor text,
hole_stab text,
hole_diml numeric(15,4),
hole_dimw numeric(15,4),
hole_locm text,
hole_loca text,
hole_clst text,
file_fset text,
cust_project text,
cust_contr_bore_name text,
cust_contr text,
cust_contr_rep_no text,
cust_sjo_no text,
cust_sitx text,
cust_sity text,
cust_sitz text,
id serial NOT NULL,
f_geometry_column geometry,
CONSTRAINT p_hole_primary PRIMARY KEY (id),
CONSTRAINT enforce_dims_f_geometry_column CHECK (ndims(f_geometry_column) = 2),
CONSTRAINT enforce_geotype_f_geometry_column CHECK (geometrytype(f_geometry_column) = 'POINT'::text OR f_geometry_column IS NULL),
CONSTRAINT enforce_srid_f_geometry_column CHECK (srid(f_geometry_column) = 1)
ALTER TABLE p_hole OWNER TO llwradmin;

If I try to create a simple view of p_v_geol called p_v_geol_test using the following SQL, then p_v_geol_test also causes QGIS to crash for version 0.7.4 and 0.8 Preview 2, i.e. it doesn't look like it is any of the aggregate functions I'm using.

SELECT * FROM p_v_geol;

ALTER TABLE p_v_geol_test OWNER TO llwradmin;

Does this help?


#3 Updated by alexbond-quintessa-org - over 16 years ago

For completeness I've compiled QGIS 0.8 P2 under Linux (Mandriva 2006 Official) with the same effect - this time I get something useful out of it re: the crash.

ERROR: Failed to find the column that .public.p_hole refers to.
Search for the underlying table.column for view column .public.p_hole failed: exceeded maximum interation limit (100).
Relation . doesn't exist in the pg_class table. This shouldn't happen and is odd.
qgis: qgspostgresprovider.cpp:1158: QString QgsPostgresProvider::chooseViewColumn(conststd::map<QString, QgsPostgresProvider::SRC, std::less<QString>, std::allocator<std::pair<const QString, QgsPostgresProvider::SRC> > >&): Assertion @0' failed.
Aborted (core dumped)

When looking at the core dump in gdb I get the following as I go up the trace:

Core was generated by @qgis'.
Program terminated with signal 6, Aborted.
warning: svr4_current_sos: Can't read pathname for load map: Input/output error

#0 0xffffe410 in +kernel_vsyscall ()
(gdb) up
#3905 0xb652aef1 in raise () from /lib/tls/libc.so.6
(gdb) up
#3906 0xb652c83b in abort () from /lib/tls/libc.so.6
(gdb) up
#3907 0xb6524045 in +assert_fail () from /lib/tls/libc.so.6
(gdb) up
#3908 0xb5e5f9d2 in QgsPostgresProvider::chooseViewColumn (this=0x81cffa0, [email protected]) at qgspostgresprovider.cpp:1158
(gdb) up
#3909 0xb5e62240 in QgsPostgresProvider::getPrimaryKey (this=0x81cffa0) at qgspostgresprovider.cpp:991
(gdb) up
#3910 0xb5e6e23a in QgsPostgresProvider (this=0x81cffa0, [email protected]) at qgspostgresprovider.cpp:288
(gdb) up
#3911 0xb5e6f3cd in classFactory (uri=0x8257d50) at qgspostgresprovider.cpp:2835
(gdb) up
#3912 0xb7bae223 in QgsProviderRegistry::getProvider (this=0x81080f0, [email protected], [email protected]) at qgsproviderregistry.cpp:358
(gdb) up
#3913 0xb7d9a105 in QgsVectorLayer::setDataProvider (this=0x8257d20, [email protected]) at qgsvectorlayer.cpp:2207
(gdb) up
#10 0xb7d9f3b9 in QgsVectorLayer (this=0x8257d20, [email protected], [email protected], [email protected]) at qgsvectorlayer.cpp:124
(gdb) up
#3914 0xb7c6b1a2 in QgisApp::addDatabaseLayer (this=0x80931c0) at qgisapp.cpp:1907
(gdb) up
#3915 0xb7dd8c9f in QgisApp::qt_metacall (this=0x80931c0, _c=QMetaObject::InvokeMetaMethod, _id=7, _a=0xbfc74e1c) at qgisapp.moc.cpp:210
(gdb) up

the remainder of the messages relate to the QT4 gui only

#4 Updated by Gavin Macaulay - over 16 years ago

Partial fix in SVN e642476c (SVN r5878). Qgis should now load and display data in the above series of tables/views.

However, there is another problem, in that qgis currently doesn't cope with renamed view columns (ie, using the AS statement). I'll try to fix that in the coming days.

#5 Updated by Gavin Macaulay - over 16 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

Fixed in svn c12cae23 (SVN r5958).

Two other bugs are also fixed:

1. Tables that contain captial letters in their name or column names now load into qgis.
2. Views that rename their column (using AS) now also load into qgis.

#6 Updated by Gavin Macaulay - over 16 years ago

  • Resolution deleted (fixed)
  • Status changed from Closed to Feedback

Not quite fixed - I've reverted the sql changes in svn 75b68177 (SVN r5960) as the change made things worse when the database had more than one view in it. Needs some more thinking...

#7 Updated by Gavin Macaulay - about 16 years ago

Partial fix in e0249e3c (SVN r6267)

It needs some more testing and enhancement, but should work in most cases. I am committing this so that I can check it out on other computers and test, and also so that other users can test it. I am especially interested in cases where it doesn't work as expected, along with the definition of the views and tables in question.

#8 Updated by anonymous - about 16 years ago

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

Fixed even better in 0f834076 (SVN r6268).

#9 Updated by Anonymous over 13 years ago

Milestone Version 0.8 deleted

Also available in: Atom PDF