GeoPackage is my favorite geo-format but I also have some strange behaviors. One of them: I delete all records/features within a table using QGIS -> Delete Features or SQL statement but there are still features counted as you can see in the screenshot below:
I also deleted all records and reset the ID sequence with the DB-Browser for SQLite using:
DELETE FROM "infrastruktur";
UPDATE sqlite_sequence SET seq = 0 WHERE name = 'infrastruktur';
VACUUM;
Table is empty but features still counted. What is the reason for that?
Best Answer
GDAL and QGIS want to know how many features there are on a layer but
SELECT COUNT(*)
is slow for big tables. Therefore GDAL creates an additional metadata tablegpkg_ogr_contents
from where the feature count can be fetched directly. The structure of the table can be checked from sqlite_master:The gpkg_ogr_metadata table does not have triggers for guarding the integrity and adding such trigger would probably be a bad idea because the table is not used by other programs than GDAL.
The GDAL GPKG driver understands the meaning of the extra metadata table and feature_count field. I made a test by deleting one feature with ogrinfo and the feature_count was correctly updated.
QGIS also updated the feature count for me when I deleted some features from the attribute table by selecting and pressing Del or by using the Delete selected feature button. The count got updated immediately in the table of contents during the editing session and the change was permanent when I stopped editing and saved the edits.
I did not test the QGIS Execute SQL method because I found a few slightly different Execute SQL tools from the processing toolbox but none of them looked alike your screen capture. I guess anyway that the SQL tool that you used edits the SQLite table directly in the same way than DB Browser for SQLite without going through the GDAL driver and therefore does not update the feature_count. A workaround would be to update also the gpkg_ogr_contents table manually
I used QGIS 3.24 for testing.