QGIS » QGIS Application

Wouter Boasson wrote:

- Captured the debug output on Windows, of which I think it will not be of much help, unfortunately; the log starts when clicking 'Connect' (I stripped the log a bit, it was just displaying a thousand more layers without errors)

You're right. Doesn't help much. I'm seeing no "Retrieving geometry types: " at all. Is that with "look in geometry columns only" checked?

What about a public database?

Indeed, with look in geometry columns only checked.
Unfortunately, I can't provide you with a public database for security and license reasons. My guess is, as things are working properly for POINT types, that you can easily replicate the issue by putting type 'GEOMETRY' in the geometry_columns table for a non-POINT type. The type itself is correctly stored in the data records, see the following output:

sde_gdbrivm=# SELECT objectid, pc4, GeometryType(shape) FROM nl.adm_pc4_2010_basis LIMIT 2;
 objectid | pc4  | geometrytype 
----------+------+--------------
        1 | 1011 | POLYGON
        2 | 1012 | POLYGON
(2 rows)

sde_gdbrivm=# SELECT * FROM public.geometry_columns WHERE f_table_name = 'adm_pc4_2010_basis';
 f_table_catalog | f_table_schema |    f_table_name    | f_geometry_column | coord_dimension | srid  |   type   
-----------------+----------------+--------------------+-------------------+-----------------+-------+----------
                 | nl             | adm_pc4_2010_basis | shape             |               2 | 28992 | GEOMETRY
(1 row)

sde_gdbrivm=# 

Without the 'only look in the geometry_columns table' option (UNchecked) the tables are correctly scanned in the 1.8 version. However, this takes more than 5 minutes in our case, as we have thousands of layers, many of them with millions of records. This not only takes too much time, but also imposes a pretty heavy database load.

I experimented a bit more, and will try to outline the differences more precisely.

• with 'only look in the geometry_columns table' (OLIG) checked, the layer selection dialog immediately shows the layers, and does not try to detect the geometry type, any layer can be selected and loaded (regardless of the type was already detected)
• with OLIG UNchecked it starts to detect the geometry type, and does not lock the layers that were not detected yet

• with OLIG checked, the layer selection dialog immediately shows the layers, and does not allow one to select a layer for which the geometry type is registered as GEOMETRY, it says 'Detecting...' but nothing happens (also checked on the database server side)
• with OLIG UNchecked it starts to detect the geometry type, but locks the layers for which the geometry type is not yet detected; it looks as if the entire dialog is locked (wait cursor is shown), but you can indeed open a schema and select layers for which the geometry type is already detected, but the ones for which the type is not yet detected are greyed out (this is different from 1.7 and older)

Hope this helps to nail down the problem!

In practice the 1.7 and older behaviour was very acceptable to us. Both modes could be used, as in both modes the dialog opens quickly and displays the available layers, and they could be immediately selected (no locked/greyed out layers) and loaded. The OLIG option was very usefull to prevent 5 minutes of unneccessary heavy load on the database, and prevents clutter from working tables that aren't registered (a good thing in a multi-user enterprise database).

IMHO the most gentle behaviour would be the 1.7 way (do not grey out anything in both modes), with a slight modification: initially pick only the first record (LIMIT 1) and perform a better check at load (or select?) time. Alternatively, make it only detect the geometry type for unknown layers as soon as you select one and even skip the initial check (selectable with a check box?).

Wouter Boasson wrote:

Without the 'only look in the geometry_columns table' option (UNchecked) the tables are correctly scanned in the 1.8 version. However, this takes more than 5 minutes in our case, as we have thousands of layers, many of them with millions of records. This not only takes too much time, but also imposes a pretty heavy database load.

You can check 'use estimated metadata' to speed that up. d12c4a79 fixes the 'only look in the geometry_columns table' issue - geometry_columns columns that are GEOMETRY are also scanned now.

Thank you, so if I install the latest nightly build (tomorrow), this should work again? The use estimated metadata is also very helpful.

Wouter Boasson wrote:

Thank you, so if I install the latest nightly build (tomorrow), this should work again? The use estimated metadata is also very helpful.

The patch was applied before 1am CEST - so the nightly builds already have it.

Excellent! It is working again, the combination of 'estimated metadata' and 'geometry_columns only' is just what we need. Thanks! Will this patch be backported to the 1.8 branch?