Migration with foreign key failing with two separate calls to runMigration

[bergmark]

Description copied from the google group

I have a pretty simple test case with two models (in separate modules) with a foreign key constraint, https://gist.github.com/bergmark/6539373

I make a call to runMigration for each module and then get the error "Table not found: A" on the second migration. If i put both models in the same persist block this problem doesn't occur. This is with the latest hackage versions of persistent, persistent-template, persistent-sqlite (and persistent-postgresql).

[bergmark]

I checked this again using the same gist as in #162, https://gist.github.com/bergmark/6935922

One step in the right direction. Running the initial migration works, re-running it does not:

$ test
Migrating: CREATe TABLE "foo"("id" SERIAL PRIMARY KEY UNIQUE)
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
Migrating: CREATe TABLE "bar"("id" SERIAL PRIMARY KEY UNIQUE,"foo_id" INT8 NOT NULL)
NOTICE:  CREATE TABLE will create implicit sequence "bar_id_seq" for serial column "bar.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"

$ test
Migrating: CREATe TABLE "foo"("id" SERIAL PRIMARY KEY UNIQUE)
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq1" for serial column "foo.id"
test: SqlError {sqlState = "42P07", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"foo\" already exists", sqlErrorDetail = "", sqlErrorHint = ""}

[snoyberg]

This is caused by a totally different issue: PostgreSQL migrations for tables with no columns. We test for table existence right now by looking for columns in the table.

[snoyberg]

@bergmark Should be fixed with f4d4bf3, can you confirm?

[bergmark]

It works, thanks!

The only thing that remains now is, as we discussed, that the foreign keys aren't added when the tables are defined in different blocks.

[fluffynukeit]

I came across this thread when looking for a solution to a similar foreign key problem as @bergmark. Has the issue of foreign keys not being added when tables are defined in different modules been fixed, or is there a work around? I, too, would like to have foreign key constraints active even when the linked entities are in separate modules. Even if I have to enter a command manually in the block to get the foreign keys to work, that would be much better than not having any FK constraints or needing to define all of the tables in the same block/module.

[snoyberg]

I don't believe we've made any changes on this front. Is that right @gregwebs?

[gregwebs]

I don't touch migrations :)

[gregwebs]

I did cleanup some migrations (so try the latest version), but I have not tested cross-module behavior.

[snoyberg]

Closing out old issues, please reopen if still relevant

[parsonsmatt]

There's a work-around for this, documented in my blog post.

I'd think the problem might be solved by running:

runMigration $ do
  A.migrateAll
  B.migrateAll

instead of

runMigration A.migrateAll
runMigration B.migrateAll

but I'll need to experiment with it.

I think this is a larger problem in that persistent actually has no way of knowing whether or not a type is a foreign key or not. If we parse a set of tables [Foo, Bar, Baz], then we know that a FooId is almost certainly referring to the Key Foo and then we can make a foreign key reference. But if I parse another set of tables [King, Queen, Dog], then we don't have any certainty that BarId is an alias for Key Bar, or even necessarily that there exists a table Bar at all! It could just be some thing I defined in non database land like data BarId = A | B | C.

It should not cause problems to say:

import A

share [...] [persistLowerCase|

B 
  aId AId
  Foreign A aId 
  
|]

It's annoying, for sure, but it should work, and it's certainly a lot less fragile.