QGIS » QGIS Application

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.

Thanks

Gavin

Hi,

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

--
CREATE OR REPLACE VIEW p_v_geol AS
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:

--
CREATE TABLE "GEOL"
(
"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)
)
WITHOUT OIDS;
ALTER TABLE "GEOL" OWNER TO llwradmin;
--

and

--
CREATE TABLE p_hole
(
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)
)
WITHOUT OIDS;
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.

--
CREATE OR REPLACE VIEW p_v_geol_test AS
SELECT * FROM p_v_geol;

ALTER TABLE p_v_geol_test OWNER TO llwradmin;
--

Does this help?

Thanks

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, cols=@0x6) at qgspostgresprovider.cpp:1158
/home/alexbond/SysAdmin/qgis_Release-0_8-preview-2/src/providers/postgres/qgspostgresprovider.cpp:1158:37251:beg:0xb5e5f9d2
(gdb) up
#3909 0xb5e62240 in QgsPostgresProvider::getPrimaryKey (this=0x81cffa0) at qgspostgresprovider.cpp:991
(gdb) up
#3910 0xb5e6e23a in QgsPostgresProvider (this=0x81cffa0, uri=@0x8257d50) 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, providerKey=@0xbfc74824, dataSource=@0x8257d50) at qgsproviderregistry.cpp:358
/home/alexbond/SysAdmin/qgis_Release-0_8-preview-2/src/core/qgsproviderregistry.cpp:358:10412:beg:0xb7bae223
(gdb) up
#3913 0xb7d9a105 in QgsVectorLayer::setDataProvider (this=0x8257d20, provider=@0xbfc74824) at qgsvectorlayer.cpp:2207
/home/alexbond/SysAdmin/qgis_Release-0_8-preview-2/src/gui/qgsvectorlayer.cpp:2207:60956:beg:0xb7d9a105
(gdb) up
#10 0xb7d9f3b9 in QgsVectorLayer (this=0x8257d20, vectorLayerPath=@0xbfc74818, baseName=@0xbfc74820, providerKey=@0xbfc74824) at qgsvectorlayer.cpp:124
(gdb) up
#3914 0xb7c6b1a2 in QgisApp::addDatabaseLayer (this=0x80931c0) at qgisapp.cpp:1907
/home/alexbond/SysAdmin/qgis_Release-0_8-preview-2/src/gui/qgisapp.cpp:1907:73536:beg:0xb7c6b1a2
(gdb) up
#3915 0xb7dd8c9f in QgisApp::qt_metacall (this=0x80931c0, _c=QMetaObject::InvokeMetaMethod, _id=7, _a=0xbfc74e1c) at qgisapp.moc.cpp:210
/home/alexbond/SysAdmin/qgis_Release-0_8-preview-2/src/gui/qgisapp.moc.cpp:210:8251:beg:0xb7dd8c9f
(gdb) up

the remainder of the messages relate to the QT4 gui only

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.

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.

Milestone Version 0.8 deleted