Postgres schemas and migrations

[ricksanchez]

Not possible to migrate if there is a table with the same name in other schema.
Example:
There is an existing "stat"."user" table
and the migration is for "public"."user" (default schema)
Extra: models

User
    username Text sqltype=varchar(17)
      ....

Result:
persistent is not creating the table "user" in "public" because of the one in "stat", after it I get:

*** Exception: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"user\" does not exist", sqlErrorDetail =
 "", sqlErrorHint = ""}

[snoyberg]

It would be helpful to have some script (ideally something like a Docker self contained script) to reproduce this issue. I'm unlikely to have a chance to jump into this bug in the near future, but hopefully it will help someone else debug this.

[ricksanchez]

I am not sure if this is really necessary.

I looked in the code for a possible explanation, here the result:

• see this line: https://github.com/yesodweb/persistent/blob/master/persistent-postgresql/Database/Persist/Postgresql.hs#L475
• It doesn't take in mind the current schema, it should be
  sql = "SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE schemaname = current_schema()" <> " AND tablename=?"
• Similar to other functions: https://github.com/yesodweb/persistent/blob/master/persistent-postgresql/Database/Persist/Postgresql.hs#L581
• Extra: The rest of the function can be cleaner, since this query always returns 1 or 0, so the error in the line 482 is not required. count(*) can be replaced with EXISTS for more readability (booleans).

What do you think about this solution?

[snoyberg]

Seems reasonable from your description.

[parsonsmatt]

The solution that @ricksanchez looks great to me and I'd be happy to accept a patch that implemented it.

[johannesgerer]

I was just about to file the following issue, when Github suggested it already exists. Posting it anyway in case someone finds it useful:

I understand there is no explicit schema support for the postgresql backend (#93 ), but there is the following schema-related family of bugs currently in the code.

The root cause of the problem is that the migration code finds necessary migrations using

select ... from information_schema / pg_catalog

without restricting itself to the current schema.

This leads to problems where persistent thinks the object is already present, but is in fact missing from the current schema. I encountered this in the form of ALTER TABLE xyz ADD COLUMN statements that failed because xyz did no exist (in the current schema).

Or problems where the migration is apparently successful, yet did not create everything it should because it was present in another schema.

[arrowd]

I wonder if this is fixed by #1466