cubicweb #17236690 Custom unique indexes doesn't work due to weird cubicweb behavior on deleting entities having inlined relations [done]

I have a custom unique index that cannot be expressed using yams (for instance allow only one NULL on a column).

The RQL DELETE behavior is to loop on all relations and issue a RQL DELETE on these relation including inlined ones to make hooks working on these too.

See https://hg.logilab.org/master/cubicweb/file/3.26.13/cubicweb/server/repository.py#l746

This trigger a sql "UPDATE cw_xxx SET cw_inlined_relation = NULL WHERE cw_eid = ..." which might trigger an index violation.

A simple test to reproduce the issue is:

--- a/cubicweb/server/test/unittest_repository.py
+++ b/cubicweb/server/test/unittest_repository.py
@@ -569,6 +569,16 @@ class PerformanceTest(CubicWebTC):
         logger = logging.getLogger('cubicweb.session')
         logger.setLevel(logging.CRITICAL)

+    def test_composite_foo(self):
+        with self.admin_access.cnx() as cnx:
+            # allow only one null
+            cnx.system_sql('CREATE UNIQUE INDEX test_composite_idx ON cw_personne(true) WHERE cw_personne_inlined is NULL')
+            cnx.commit()
+            p0 = cnx.create_entity('Personne', nom=u'p0')
+            p1 = cnx.create_entity('Personne', nom=u'p1', personne_inlined=p0)
+            cnx.commit()
+            cnx.entity_from_eid(p1.eid).cw_delete()
+

From a purely SQL point of view, I see no reason to update the column of an inlined relation to NULL.

I think this could to be fixed by not running the sql (update set null) in the particular case of deleting the whole entity, although this might have side effect.

prioritynormal
typebug
done in3.26.x
load left0.000
closed by#1a2c7d6397ec Avoid to update inlined relation column to NULL when deleting entities