[GitHub] [superset] eschutho commented on a diff in pull request #25164: DRAFT: change constraint alters to non blocking for postgres

["eschutho (via GitHub)" <gi...@apache.org>]

eschutho commented on code in PR #25164:
URL: https://github.com/apache/superset/pull/25164#discussion_r1313659916


##########
superset/migrations/versions/2023-08-09_15-39_4448fa6deeb1__dd_on_delete_cascade_for_embedded_dashboards.py.py:
##########
@@ -40,9 +44,50 @@
 
 def upgrade():
     for foreign_key in foreign_keys:
-        redefine(foreign_key, on_delete="CASCADE")
+        bind = op.get_bind()
+        insp = Inspector.from_engine(bind)
+        db_dialect = bind.engine.url.drivername
+
+        if db_dialect == "postgresql":
+            if constraint := generic_find_fk_constraint_name(
+                table=foreign_key.table,
+                columns=set(foreign_key.remote_cols),
+                referenced=foreign_key.referent_table,
+                insp=insp,
+            ):
+                # rename the constraint so that we can drop it later- non-blocking
+                # this only works on postgres
+                op.execute(
+                    f"ALTER TABLE {foreign_key.table} RENAME CONSTRAINT {constraint} TO {foreign_key.constraint_name}_old"
+                )
+
+            # create the new constraint- uses a non-blocking ALTER TABLE Not Valid property
+            op.create_foreign_key(
+                constraint_name=foreign_key.constraint_name,
+                source_table=foreign_key.table,
+                referent_table=foreign_key.referent_table,
+                local_cols=foreign_key.local_cols,
+                remote_cols=foreign_key.remote_cols,
+                ondelete="CASCADE",
+                postgresql_not_valid=True,
+            )
+
+            # validate the constraint- non-blocking
+            op.execute(
+                f"ALTER TABLE {foreign_key.table} VALIDATE CONSTRAINT {foreign_key.constraint_name}"
+            )
+
+            # drop the old constraint which isn't needed anymore- non-blocking
+            if constraint:
+                op.drop_constraint(
+                    f"{foreign_key.constraint_name}_old",
+                    foreign_key.table,
+                    type_="foreignkey",
+                )
+        else:
+            redefine(foreign_key, on_delete="CASCADE")
 
 
 def downgrade():
     for foreign_key in foreign_keys:
-        redefine(foreign_key)
+        pass

Review Comment:
   todo



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org