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

[haslersn]

• 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

[FxKu]

Same here, more an issue for Spilo because the operator only executes the script. So should we rather use TRUNCATE CASCADE then so all your data gets removed? What's your point on using UNLOGGED tables?

[haslersn]

This is a matrix synapse database and from the table names, I assume these two tables hold locks created by worker processes. I assume it's no problem if they get truncated and not replicated, but I'll ask upstream to be sure.

Anyway, since these are two unlogged tables that reference each other, they should probably be truncated together instead of sequentially. That would solve the problem without using TRUNCATE CASCADE, right?

[FxKu]

You mean you truncate manually before the upgrade? Yeah, try it maybe...

[haslersn]

My comment was meant as follows: it might be a good idea if spilo's upgrade script truncates all UNLOGGED tables at the same time instead of sequentially.