Support for Postgres schemas

[RiugaBachi]

As far as I understand, Beam does not support querying across multiple Postgres schemas within the same database, but supports queries involving multiple databases like most non-Postgres SQL databases do. I am not sure what such a query would do if executed on a Postgres backend - most likely an exception -, but I digress. Beam queries seem to be executed on the default public schema only. For my database setup, I need to be able to join some tables across different schemas; squashing them down into a single public schema would be infeasible for what I'm trying to achieve. I understand that I could perform a SET search_path via postgresql-simple to change the visible schema(s), however the tables in my schemas have identical names, requiring them to be fully qualified.

[o1lo01ol1o]

I have not yet tried to query across schemata in beam (though I was about to try). Can you not set the schema and write the query against the given database?

-- | Sets a schema for a `Database bd db`.
-- @
-- barDb :: DatabaseSettings be BarDb
-- barDb = withDbSchema defaultDbSettings "fooScheama" dbModification
-- @
withDbSchema :: forall db be
                    . (Database be db)
                   => db (DatabaseEntity be db)
                   -> Text
                   -> DatabaseModification (DatabaseEntity be db) be db
                   -> db (DatabaseEntity be db)
withDbSchema db schema mods =
  runIdentity $ zipTables (Proxy @be)
    (\tbl _ -> pure (appEndo (Endo (\(DatabaseEntity tbl') ->
        DatabaseEntity (tbl' & dbEntitySchema %~ const (Just schema)))) tbl)
        ) db mods

If not, what's required to support this?

[kmicklas]

@o1lo01ol1o The problem is the the schema set in DatabaseEntity is ignored in most places.

[tathougies]

Do we have a list of where it's ignored? It should be used?

[kmicklas]

@tathougies "Most" was definitely an overstatement. I tried to use schemas once and ran into multiple issues which I can't remember off the top of my head. I'll try to dig them up tomorrow.

[kmicklas]

One issue with schemas I've rediscovered is that getDbConstraintsForSchemas always returns Db.TableExistsPredicate (Db.QualifiedName Nothing tbl) (i.e. not qualified with a schema name).