3

I'm running a python script which drip-feeds data slowly into a postgres database with postgis extension. I'm using autocommit, and committing one row at a time. Horrendously slow, but I need to do it this way for a good reason :)

Once I add a postgres layer, QGIS seems to poll the database every so often and the number of features increases. This is great, and gives me visual feedback that my script is working.

If I stop my script, TRUNCATE the table using pgAdminIII and restart my script, QGIS correctly clears the display (it notices that there are no features). However, it doesn't seem to track subsequent changes to the database, and the feature count sticks at the number of rows there were, rather than 0. I need to add the postgres layer again, which can take a while.

Is this a bug, a feature, or am I doing something wrong?

(Environment: QGIS 2.12.1 Pisa, Postgres 9.3.10, PostGIS 2.1.2, Ubuntu Tahr 32 bit)

Update

It seems that once the number of features exceeds the number of features in the database before I truncated the database, QGIS starts tracking changes again.

  • 1
    Sounds like some kind of index or primary key thing. When the table is truncated, does it help to vaccum analyze and reindex it before it starts to be filled again? – SaultDon Dec 18 '15 at 1:06
  • thanks - will try that once my script's completed (any day now...!). I also tried truncate cascaded in case the metadata was not being updated, but that didn't seem to work either. – Steven Kay Dec 18 '15 at 14:16
  • tried vaccum analyse and reindex - no joy. However I did notice this table doesn't have a primary key, which might have an impact on this. – Steven Kay Dec 18 '15 at 17:52
0

I found out how to get around this - use a filter on the layer, and make it fetch all features.

In my case, the table contained a geometry only, no id field. I added a bigserial field called unid like so...

CREATE TABLE randomised3
(
  unid BIGSERIAL PRIMARY KEY,
  way geometry(Geometry,27571)
)
WITH (
  OIDS=FALSE
);

Next, I added a filter on the table - this will show all rows.

unid>0

Refreshing the feature count now resets to 0 when I restart with a truncated table. Clicking 'test' on the filter dialog seems to help too.

It seems that without a filter in place, QGIS is using a cached value of the estimated number of rows - adding the filter forces it to recalculate.

  • In this case, maybe truncating the table without reseting the primary key sequence migth work. – Alexandre Neto Dec 20 '15 at 0:16
  • I'm surprised you managed to add your Postgres table to QGIS without a serial primary key; I've always had problems much earlier than this without one. – Richard Law Dec 20 '15 at 0:28
  • @alpha-beta-soup It's certainly a problem adding via DBManager or QGIS browser - but it seems doable by simply adding a postgres layer using the toolbar button (as of 2.12.1) – Steven Kay Dec 20 '15 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.