inplace_upgrade tries to truncate a table referenced in a foreign key constraint

• operator image: registry.opensource.zalan.do/acid/postgres-operator:v1.10.1
• Bare metal K8s
• Run in production: yes
• Type of issue: bug report

When attempting a major version upgrade (from PostgreSQL 12 to 15) of my (matrix synapse) database, I get the following error:

2024-10-23 15:43:34,494 inplace_upgrade INFO: Truncating unlogged table public.worker_read_write_locks
2024-10-23 15:43:34,496 inplace_upgrade ERROR: Failed: FeatureNotSupported('cannot truncate a table referenced in a foreign key constraint\nDETAIL:  Table "worker_read_write_locks_mode" references "worker_read_write_locks".\nHINT:  Truncate table "worker_read_write_locks_mode" at the same time, or use TRUNCATE ... CASCADE.\n')
2024-10-23 15:43:34,496 inplace_upgrade INFO: Truncating unlogged table public.worker_read_write_locks_mode
2024-10-23 15:43:34,496 inplace_upgrade ERROR: Failed: FeatureNotSupported('cannot truncate a table referenced in a foreign key constraint\nDETAIL:  Table "worker_read_write_locks" references "worker_read_write_locks_mode".\nHINT:  Truncate table "worker_read_write_locks" at the same time, or use TRUNCATE ... CASCADE.\n')

This doesn't seem to be a fatal error, since the major version upgrade proceeds anyway. Later the upgrade script fails during pg_restore with a different error that seems to be unrelated, so I created a separate issue for that: #2791

Start by locating the inplace_upgrade implementation that emits the truncation messages and inspect how related tables are selected and truncated. Reproduce the PostgreSQL foreign-key case described in the logs, then verify that the upgrade no longer reports these constraint errors while preserving the existing upgrade flow.