Foreign keys referencing partitioned table not copied nor dealt with in `swap`

[sdhull]

• Postgres 12.12
• Rails 5.2.8.1

Ran through the steps outlined in readme to partition by day, created 30 past and 5 future partitions. Ran db:structure:dump and then db:test:prepare to get schema loaded in test env, then ran specs and found a bunch of failures like this:

ActiveRecord::InvalidForeignKey:
  PG::ForeignKeyViolation: ERROR:  insert or update on table "user_events" violates foreign key constraint "fk_rails_377b6c2879"
  DETAIL:  Key (event_id)=(1) is not present in table "events_retired".
  : INSERT INTO "user_events" ("user_id", "event_id", "viewed_at", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"

"OK" I figured, "I just need to drop the retired table." So I did that, but then got one failing spec:

Failure/Error:
  expect{
    subject.event.destroy
  }.to raise_error(ActiveRecord::InvalidForeignKey)

  expected ActiveRecord::InvalidForeignKey but nothing was raised

So this leaves me with a few questions:

1. How to actually operationalize this? Seems like we can't guarantee zero downtime unless we drop foreign keys to this table first and re-create them later? (I guess I could do this)
   • should swap be updated to remove fks to the retired table?
2. How to deal with schema in specs? I guess I could shell out to pgslice in the spec helper suite setup, but that feels kinda gross
3. More generally: developers are often very hung up on structure.sql exactly matching production schema. It's a bit gross to have 30+ partitions in there, but also it will be out of date after one day. What do you recommend here?

[ankane]

Hey @sdhull,

For 1, that's my understanding. For Postgres < 12, it's not possible for foreign keys to reference partitioned tables. For Postgres 12+, I think swap and unswap could be updated to handle this if someone wants to submit a PR. For swap, I think the order could be:

1. For each foreign key, create as NOT VALID on the intermediate table and drop on the soon-to-be retired table
2. Do the swap
3. Validate the NOT VALID foreign keys

For 2 and 3, I think it's simplest to break dev-prod parity here.

[sdhull]

Thanks for the reply @ankane! If we get around to partitioning any tables in our live DB I may circle back around to this and work on a PR for ya, but for now I'm just working on partitioning a table that isn't used yet so I think I'm going to just do it destructively 😈 and use pg_partman for partition management.

But this issue may be useful for future travelers so thanks for weighing in 😄

[sasharevzin]

@ankane Regarding your scenario, where would I specify NOT VALID? Thank you