Rubberband/digitizing not working correctly with snapping after database trigger has normalized polygon with inner ring during saving

[rnousia]

What is the bug or the crash?

If a polygon with inner ring is changed in the database with trigger after saving edits (e.g. due to normalization of polygon), rubberband snaps to old vertices and not to the updated ones if the inner ring is used as snapping target. This looks very confusing to the user although in some cases the actual snapping seems to work (maybe if the digitized ring was symmetrical?) and resulting feature is created as digitized.

The problem occurs only with inner rings and only if digitizing + saving is done in a specific order. So it is quite difficult to reproduce but at least the steps in the following section describe one case where this issue occurs.

Here the digitized ring also seems to be incorrect (not the using the vertices snapped).

Steps to reproduce the issue

1. Create tables with trigger that normalizes geometry before insert or update (tested with postgis database):

-- create trigger function to normalize polygons
CREATE OR REPLACE FUNCTION public.normalize_geom()
RETURNS trigger AS $$
BEGIN
  NEW.geom = ST_Normalize(NEW.geom);
  RETURN NEW;
END; $$ LANGUAGE 'plpgsql';

-- create test tables
CREATE TABLE normalized_polygons_1
(
    id bigserial NOT NULL,
    geom geometry(POLYGON, 3067),
    CONSTRAINT "PK_normalized_polygons_1" PRIMARY KEY (id)
);

CREATE TABLE normalized_polygons_2
(
    id bigserial NOT NULL,
    geom geometry(POLYGON, 3067),
    CONSTRAINT "PK_normalized_polygons_2" PRIMARY KEY (id)
);

-- create triggers
CREATE TRIGGER normalize_geom
BEFORE INSERT OR UPDATE ON normalized_polygons_1
FOR EACH ROW EXECUTE PROCEDURE public.normalize_geom();

CREATE TRIGGER normalize_geom
BEFORE INSERT OR UPDATE ON normalized_polygons_2
FOR EACH ROW EXECUTE PROCEDURE public.normalize_geom();

2. Add layers to QGIS and digitize a polygon to both layers that are overlapping each other, like so:

3. Save edits on all layers (this is important step as the issue only happens if layer is saved before adding the inner ring)
4. Add a ring to one of the layers and save edits (again, saving must be done at this step, otherwise everything works correctly in next step)
5. Start adding ring to the other layer with vertex snapping on -> digitized rubberband is drawn to wrong vertices

I couldn't figure any way to reset this behaviour once it starts happening. Also, as mentioned in the steps, this only occurs if edits are saved after each step and layer is not changed before saving the first ring creation.

Versions

<style type="text/css"> p, li { white-space: pre-wrap; } </style>

QGIS version	3.36.1-Maidenhead	QGIS code revision	3e58945
Qt version	5.15.13
Python version	3.12.3
GDAL/OGR version	3.8.5
PROJ version	9.4.0
EPSG Registry database version	v11.004 (2024-02-24)
GEOS version	3.12.1-CAPI-1.18.1
SQLite version	3.45.1
PDAL version	2.6.3
PostgreSQL client version	16.2
SpatiaLite version	5.1.0
QWT version	6.2.0
QScintilla2 version	2.14.1
OS version	Windows 10 Version 2009
Active Python plugins
db_manager	0.1.20
grassprovider	2.12.99
MetaSearch	0.3.6
processing	2.12.99

QGIS version 3.36.1-Maidenhead QGIS code revision [3e58945](https://github.com/qgis/QGIS/commit/3e589453) Qt version 5.15.13 Python version 3.12.3 GDAL/OGR version 3.8.5 PROJ version 9.4.0 EPSG Registry database version v11.004 (2024-02-24) GEOS version 3.12.1-CAPI-1.18.1 SQLite version 3.45.1 PDAL version 2.6.3 PostgreSQL client version 16.2 SpatiaLite version 5.1.0 QWT version 6.2.0 QScintilla2 version 2.14.1 OS version Windows 10 Version 2009

Active Python plugins
db_manager
0.1.20
grassprovider
2.12.99
MetaSearch
0.3.6
processing
2.12.99

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

No response

[Joonalai]

The bug exists on 3.34.3-Prizren as well.

[uclaros]

Is this also a #31251 duplicate?

[Djedouas]

Reproduced on master 623ceab11d8

With a more simple test case, on one polygon only:

qgis_issue_57181-2024-07-29_17.08.32_srt.mp4

It seems to be related to how the vertices order is not updated in the cache, when saving after a modification action, but it is updated when saving after digitizing a new feature.

This vertex confusion stays even after digitizing other features.

A refresh via the toolbar reorders everything.

I will investigate further into the code.

[Djedouas]

After some investigations, we are on a case where QGIS can't be aware of underlying data change.

💾 When the "save layer" button is pushed, the trigger normalize_geom() changes the feature geometry, and QGIS has no clue about this, but the snapping geometry index is invalidated (as well as the independent vertex tool geometry cache).

🧠 This explains why everything works fine when not saving the layer and doing all the digitizing steps: the geometry is always in a state that QGIS knows about.

🤔 "But... the label with the geometry WKT is up-to-date right after saving, so... surly somewhere QGIS does know that the geometry has changed?"

Well... the label expression directly queries the data provider (PostgreSQL) after the save action (because of refreshing the canvas after saving a layer).
PostgreSQL will always give the expression the up-to-date WKT. But no way to know if the WKT has changed recently, in fact.

📡 Right. What now? We have a solution! Let's use PostgreSQL notification signal! With

NOTIFY qgis;

SQL statement, that we will add in our trigger that becomes

-- create trigger function to normalize polygons
CREATE OR REPLACE FUNCTION public.normalize_geom()
RETURNS trigger AS $$
BEGIN
  NEW.geom = ST_Normalize(NEW.geom);
  NOTIFY qgis;  -- tell QGIS layers listening to the notification to refresh!
  RETURN NEW;
END; $$ LANGUAGE 'plpgsql';

And configure our layers to listen to the provider notifications in the Rendering properties:

✅ This configuration fixes the vertex tool geometry cache and snapping index being wrong.

⚠️ The notification triggers the emission of QgsMapLayer::dataChanged() signal which completely clears the cache and index, which will be rebuilt. There is no way currently to say "only refresh the feature I was just modifying". Changing geometry under the hood by a database trigger could impact any feature.

NB: the notification can include a message: NOTIFY, 'my great message'; which can be specifically listened by the layer (for instance, to have one different signal for each layer/trigger).

[Djedouas]

Another solution is to check the layer itself in Dependencies section of the layer properties.

In this way, every change on the layer will trigger a full refresh of the data, caches, and rubber bands.

[rnousia]

Thank you for the workaround suggestions. We tried these and using "refresh layer on notification" option did solve the issue but unfortunately this created a new one: each layer with the option enabled seems to create a new Postgresql connection and therefore the maximum connection limit is reached at some point. We have about 100 layers and 100 users so this would result in 10 000+ extra connections? I tested this with QGIS-3.34.3-1 though and QGIS crashed if number of layers exceeded database's max_connection-setting (the crashing may have been fixed in #54260 but not sure if the number of connections used is fixed).

However, we came up with a 3rd workaround: reloading the layer after the layer is saved. This seems to fix vertex ordering.

# pseudo code, not actually tested with this
layer.afterCommitChanges.connect(lambda: layer.reload())

[Djedouas]

each layer with the option enabled seems to create a new Postgresql connection and therefore the maximum connection limit is reached at some point. We have about 100 layers and 100 users so this would result in 10 000+ extra connections?

@troopa81 What are your thoughts about this?