refactor: set cascade_backrefs=False for SavedQuery

Enable the pytest error filter for the "'SavedQuery' object is being
merged into a Session along the backref cascade path" RemovedIn20Warning
and adopt the SQLAlchemy 2.0 behavior for both SavedQuery backrefs
(User.saved_queries and Database.saved_queries).

Constructing SavedQuery(database=...) or SavedQuery(user=...) no longer
implicitly merges the new object into the parent's session. All
persisting code paths already add saved queries explicitly.

See discussion #40273.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

pytest.ini

@@ -30,8 +30,8 @@ filterwarnings =
     always::sqlalchemy.exc.RemovedIn20Warning
     error:Passing a string to Connection.execute\(\) is deprecated:sqlalchemy.exc.RemovedIn20Warning
 #    error:"Query" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
-#    error:"SavedQuery" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
-    error:"SqlaTable" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
+    error:"SavedQuery" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
+#    error:"SqlaTable" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
 #    error:"SqlMetric" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
 #    error:"TableColumn" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning
 #    error:"TaggedObject" object is being merged into a Session:sqlalchemy.exc.RemovedIn20Warning

superset/connectors/sqla/models.py

@@ -1364,14 +1364,7 @@ class SqlaTable(
 
     database: Database = relationship(
         "Database",
-        backref=backref(
-            "tables",
-            cascade="all, delete-orphan",
-            # SQLAlchemy 2.0 behavior: assigning `table.database` no longer
-            # cascades the SqlaTable into the Database's session; callers must
-            # add objects to a session explicitly.
-            cascade_backrefs=False,
-        ),
+        backref=backref("tables", cascade="all, delete-orphan"),
         foreign_keys=[database_id],
     )
     schema = Column(String(255))

superset/examples/generic_loader.py

@@ -173,13 +173,6 @@ def load_parquet_table(  # noqa: C901
 
     if not tbl:
         tbl = SqlaTable(table_name=table_name, database_id=database.id)
-        # Explicitly add the new table to the session. Assigning `tbl.database`
-        # below no longer implicitly adds `tbl` to the session (SQLAlchemy 2.0
-        # behavior, cascade_backrefs=False), so without this, the two
-        # `db.session.merge()` calls below (one inside `fetch_metadata()`, one
-        # at the end of this function) would each create a separate transient
-        # copy of `tbl`, resulting in two pending inserts for the same uuid.
-        db.session.add(tbl)
         # Set the database reference
         tbl.database = database
 
@@ -194,7 +187,7 @@ def load_parquet_table(  # noqa: C901
         tbl.fetch_metadata()
 
     db.session.merge(tbl)
-    db.session.commit()  # pylint: disable=consider-using-transaction
+    db.session.commit()
 
     return tbl
 
@@ -249,7 +242,7 @@ def create_generic_loader(
         if description and tbl:
             tbl.description = description
             db.session.merge(tbl)
-            db.session.commit()  # pylint: disable=consider-using-transaction
+            db.session.commit()
 
     # Set function name and docstring
     loader.__name__ = f"load_{parquet_file}"

superset/models/sql_lab.py

@@ -493,13 +493,25 @@ class SavedQuery(
     template_parameters = Column(Text)
     user = relationship(
         security_manager.user_model,
-        backref=backref("saved_queries", cascade="all, delete-orphan"),
+        backref=backref(
+            "saved_queries",
+            cascade="all, delete-orphan",
+            # SQLAlchemy 2.0 behavior: assigning `saved_query.user` no longer
+            # cascades the SavedQuery into the User's session; callers must
+            # add objects to a session explicitly.
+            cascade_backrefs=False,
+        ),
         foreign_keys=[user_id],
     )
     database = relationship(
         "Database",
         foreign_keys=[db_id],
-        backref=backref("saved_queries", cascade="all, delete-orphan"),
+        backref=backref(
+            "saved_queries",
+            cascade="all, delete-orphan",
+            # SQLAlchemy 2.0 behavior: see `user` above.
+            cascade_backrefs=False,
+        ),
     )
     rows = Column(Integer, nullable=True)
     last_run = Column(DateTime, nullable=True)

tests/integration_tests/sqla_models_tests.py

@@ -173,7 +173,6 @@ class TestDatabaseModel(SupersetTestCase):
             "'{{ 'xyz_' + time_grain }}' as time_grain",
             database=get_example_database(),
         )
-        db.session.add(table)
         TableColumn(
             column_name="expr",
             expression="case when '{{ current_username() }}' = 'abc' "
@@ -276,7 +275,6 @@ class TestDatabaseModel(SupersetTestCase):
         table = SqlaTable(
             table_name="test_validate_adhoc_sql", database=get_example_database()
         )
-        db.session.add(table)
         db.session.commit()
 
         with pytest.raises(QueryObjectValidationError):