qgis freezes when opening attribute table of a postgis layer without key value

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
Hi,
I'm facing a severe bug in qgis 2.2 and master here.
I have layers defined in existing project that do not have a key in datasource definition.
It seems that when a table has a oid field, postgis connection dialog does not ask for a key field (it is a multi column primary key in that case).
When i had manually in definition string key=oid,  all seems fine.
The major problem is that most user existing project will be impacted.

Did I miss something? Why is key=oid no more explicitly written in datasource def?

Thanks for your lights
Régis
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
Hi again,
I'm probably totally wrong in the previous analysis.
It seems that this is related to table with multiple geometry columns. Maybe new attribute caching just can index additionnal geometry columns.
Anyway, this is still really severe.  Anyone confirms?
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Andrea Peri
In reply to this post by Régis Haubourg

AFAIK the last postgres versione dismiss by default the oid.

Il 21/mag/2014 11:54 "Régis Haubourg" <[hidden email]> ha scritto:
Hi,
I'm facing a severe bug in qgis 2.2 and master here.
I have layers defined in existing project that do not have a key in
datasource definition.
It seems that when a table has a oid field, postgis connection dialog does
not ask for a key field (it is a multi column primary key in that case).
When i had manually in definition string key=oid,  all seems fine.
The major problem is that most user existing project will be impacted.

Did I miss something? Why is key=oid no more explicitly written in
datasource def?

Thanks for your lights
Régis




--
View this message in context: http://osgeo-org.1560.x6.nabble.com/qgis-freezes-when-opening-attribute-table-of-a-postgis-layer-without-key-value-tp5141587.html
Sent from the Quantum GIS - Developer mailing list archive at Nabble.com.
_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/qgis-developer

_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/qgis-developer
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
Hi again,
my problem is unsolved. I eliminated either additionnal geometry column or oid/ pk / select at id causes.

for a very simple postgis table, no way to open the attribute table, Qgis freezes and transaction needs to be aborted on postgres side.

Any idea? Am I alone?

see table definition :
-- Table: dce.masse_eau_anomalie

-- DROP TABLE dce.masse_eau_anomalie;

CREATE TABLE dce.masse_eau_anomalie
(
  id_anomalie integer NOT NULL DEFAULT nextval('masse_eau_anomalie_id_anomalie_seq'::regclass),
  eu_cd character varying(24) NOT NULL,
  version_dce character varying(10) NOT NULL,
  description_anomalie text,
  date_signalement date NOT NULL,
  operateur character varying(15) NOT NULL,
  date_correctif date,
  statut character varying(20),
  geom_point geometry, -- localisation de l'anomalie
  CONSTRAINT pk_masse_eau_anomalie PRIMARY KEY (id_anomalie),
  CONSTRAINT enforce_dims_geom_point CHECK (st_ndims(geom_point) = 2),
  CONSTRAINT enforce_geotype_geom_point CHECK (geometrytype(geom_point) = 'POINT'::text OR geom_point IS NULL),
  CONSTRAINT enforce_srid_geom_point CHECK (st_srid(geom_point) = 2154)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE dce.masse_eau_anomalie
  OWNER TO postgres;
GRANT ALL ON TABLE dce.masse_eau_anomalie TO postgres;
COMMENT ON TABLE dce.masse_eau_anomalie
  IS 'stocke les anomalies constatées pour les futures mises à jour';
COMMENT ON COLUMN dce.masse_eau_anomalie.geom_point IS 'localisation de l''anomalie
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
Making a view from that table goes well.
I really don't have a clue.

see definition of the view
-- DROP VIEW dce.masse_eau_anomalie_ag;

CREATE OR REPLACE VIEW dce.masse_eau_anomalie_ag AS
SELECT id_anomalie, eu_cd, version_dce, description_anomalie, date_signalement,
       operateur, date_correctif, statut, geom_point
  FROM dce.masse_eau_anomalie;


ALTER TABLE dce.masse_eau_anomalie_ag
  OWNER TO postgres;
GRANT ALL ON TABLE dce.masse_eau_anomalie_ag TO postgres;


and some data to populate the table (postgis 1.5):

INSERT INTO masse_eau_anomalie VALUES (7, 'FRFR523B', 'EDL2013', 'inversion de confluence serre-mayne . impact segments et tracé et FRFR523A', '2014-05-20', 'RH', NULL, 'acceptée', '01010000206A080000000000000C83214100000000AAA75841');
INSERT INTO masse_eau_anomalie VALUES (8, 'FRFR928', 'EDL_2013', 'rigole de la plaine pas codifiée en RM&C. demander une codif à AERMC', '2014-05-23', 'RH', NULL, 'en attente', '01010000206A08000099F41342C94E2341F29539CFEEDB5741');
INSERT INTO masse_eau_anomalie VALUES (9, 'FRFR926', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A0800001343046C3F1E184199900EE1B3F75841');
INSERT INTO masse_eau_anomalie VALUES (10, 'FRFRR332_6', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A080000D2457342D4201C4111D3BDDA82CF5841');
INSERT INTO masse_eau_anomalie VALUES (11, 'FRFT01', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A0800004A0CDD8CC7161841EAA584BBE2F85841');
INSERT INTO masse_eau_anomalie VALUES (12, 'FRFRR49_7', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A080000625BE5C313AE2041B3B0B9AF21C75841');
INSERT INTO masse_eau_anomalie VALUES (13, 'FRFR926', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A080000F7C622F65A6E18419CE9344156F45841');
INSERT INTO masse_eau_anomalie VALUES (14, 'FRFR928', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A0800009C42C21F58562341B27B97EE05DC5741');
INSERT INTO masse_eau_anomalie VALUES (15, 'FRFR928', 'EDL_2013', 'segment recodifié dans carthage 2013', '2014-05-23', 'RH', NULL, 'acceptée', '01010000206A0800009C42C21F58562341B27B97EE05DC5741');
INSERT INTO masse_eau_anomalie VALUES (16, 'FRFR431', 'EDL_2013', 'supprimer segments latéral ', '2014-05-23', 'RH', NULL, NULL, '01010000206A080000E58221BC24B11841FABC684B63E35741');


Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
In reply to this post by Régis Haubourg
Qgis 1.8 is concerned too. This is probably something on postgres side then.
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg

We seem to have trouble with cursors.
here is postgres log when trying to open attribute table in qgis:


2014-05-23 17:56:51.885 CEST Quantum GIS 172.16.9.169(65371) 6153 idle in transaction postgres refgeo2 ERROR:  syntax error at or near "FROM" at character 42
2014-05-23 17:56:51.885 CEST Quantum GIS 172.16.9.169(65371) 6153 idle in transaction postgres refgeo2 STATEMENT:  DECLARE qgisf3 BINARY CURSOR FOR SELECT  FROM "dce"."masse_eau_anomalie"

and that is all, the binary cursor creation stays idle.


Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
other symptoms are that identify or select actions on map consider all objects as one multi point.

 
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
QGIS 1.8 raises those error message :
2014-05-23T17:56:51: 1 cursor states lost.
SQL: DECLARE qgisf3 BINARY CURSOR FOR SELECT  FROM "dce"."masse_eau_anomalie"
Résultat: 7 (ERROR:  syntax error at or near "FROM"
LINE 1: DECLARE qgisf3 BINARY CURSOR FOR SELECT  FROM "dce"."masse_e...

2014-05-23T17:56:52 nextFeature() sans select()
                                                 ^
)

Now way to get QGIS 2.x messages, interface is frozen
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
QGIS 2.2 tries to create cursors infinitly.. when qgis 1.8 let first cursor transaction idle.

Creating the table raw without any constraint in another schema works.

if anybody finds one day what happens, I'll be happy to learn..

Régis
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Yves Jacolin
Régis,

I created the dce schema and the masse_eau_anomalie table inside. Added the
view and import some data from SQL request in your last email.

I have no problem when opening attribute table windows or using the identify
tools.

I am using QGIS master, with POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2
r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.12dev, released
2014/04/16" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER
and PostgreSQL 9.3.4.

Hope it can help in any way.

Y.

Le vendredi 23 mai 2014, 09:22:31 Régis Haubourg a écrit :

> QGIS 2.2 tries to create cursors infinitly.. when qgis 1.8 let first cursor
> transaction idle.
>
> Creating the table raw without any constraint in another schema works.
>
> if anybody finds one day what happens, I'll be happy to learn..
>
> Régis
>
>
>
> --
> View this message in context:
> http://osgeo-org.1560.x6.nabble.com/qgis-freezes-when-opening-attribute-tab
> le-of-a-postgis-layer-without-key-value-tp5141587p5142199.html Sent from the
> Quantum GIS - Developer mailing list archive at Nabble.com.
> _______________________________________________
> Qgis-developer mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/qgis-developer

--
Yves Jacolin
_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/qgis-developer
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
Thanks Yves!

We definitly have weird things, the query send by QGIS to define the cursor is malformed, (no * in select, no commas between fields..)
In addition, we see several errors that should not exist. Qgis always queries the style definition tbale even if it does not exists. QGis should test for that table on connection.

We keep lmooking what happens. My problem is that it happens on other tables too.

Régis
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg

little update. We now reproduce the problem. It disappear when dropping the primary key.
Yves, the DDL I gave you didn't have a PK, could you add one on id_anomlie field and test?
Régis
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Yves Jacolin
Régis,

Le lundi 26 mai 2014, 02:15:25 Régis Haubourg a écrit :
> little update. We now reproduce the problem. It disappear when dropping the
> primary key.
> Yves, the DDL I gave you didn't have a PK, could you add one on id_anomlie
> field and test?
> Régis

"pk_masse_eau_anomalie" PRIMARY KEY, btree (id_anomalie)

no change on my side!

I can't add new feature (form shows only input filed disabled).

Y.
--
Yves Jacolin
_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/qgis-developer
Reply | Threaded
Open this post in threaded view
|

Re: qgis freezes when opening attribute table of a postgis layer without key value

Régis Haubourg
OK, we have the problem each time we have a int primary key on a table, but only in our production database. (pgpool2 + 2 pg replicating). dropping the constraint restore normal behaviour....

We are dumping the prod and restoring it in a new instance to check what happens.

In QGIS provider code, it seems that primary key is not recognized (and it doesn't appear in connection string on qgis side). In that case, the delimiter between field is not set , and query set in cursor for attribute table or identify is like "Select myfield1::text myfield2::integer from mytable". The query is malformed so the cursor fails and qgis tries another cursor infinitly. We couldn't find what is different between test database and production database that could explain why QGIS can't interpret that the same way.


Régis