GeoPackage, SQLite: features count but don't exist (QGIS)

Question

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?

Answer 1

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 table gpkg_ogr_contents from where the feature count can be fetched directly. The structure of the table can be checked from sqlite_master:

SELECT sql FROM sqlite_master
WHERE type = 'table' AND name = 'gpkg_ogr_contents';

CREATE TABLE gpkg_ogr_contents(table_name TEXT NOT NULL PRIMARY KEY,feature_count INTEGER DEFAULT NULL);

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.

ogrinfo test.gpkg -sql "delete from test where id=100"

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

UPDATE gpkg_ogr_contents set feature_count=(SELECT count(*) from table) where table_name='table';

I used QGIS 3.24 for testing.