Accessing different schemas with Persistent on PostgreSQL database

[Vincent Laulagnet]

Hi,

I am using Persistent with PostgreSQL database. 

PostgreSQL supports the concept of Schemas to efficiently isolate different data set (http://www.postgresql.org/docs/9.1/static/ddl-schemas.html)

I am looking at an easy way to specify for each persistent access which schema is used.

I am using this kind of command for defining the database with connStr specifying the database and user.

What would be the best way to specify the schema to operate on as an option for this kind of command?

runStdoutLoggingT $ withPostgresqlPool connStr 10 $ \pool ->

       liftIO $ flip runSqlPersistMPool pool $ do

        runMigration migrateAll

Best Regards,

Vincent

[Greg Weber]

Does running a raw sql command to set the schema work?

--
You received this message because you are subscribed to the Google Groups "Yesod Web Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to yesodweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vincent Laulagnet]

Hi Greg,

Thank you.

It seems that the SET function could do the job http://www.postgresql.org/docs/9.4/static/sql-set.html

What would be the best way to implement it?

I would think that I could then add it after each connection in the code

runStdoutLoggingT $ withPostgresqlPool connStr 10 $ \pool ->

       liftIO $ flip runSqlPersistMPool pool $ do

        Here add rawsql set schema command

        runMigration migrateAll

It does look error prone and repetitive. 

Would there be a way to add it as an optional parameter for the withPostgresqlPool function ?

Best Regards,

Vincent

[Michael Snoyman]

This is a common enough request that we should probably add a function that takes some raw SQL to be executed (or perhaps a list of SQL commands) each time a connection is created. I'm thinking of something like:

createPostgresqlPoolExecute :: ConnectionString -> Int -> [Text] -> m ConnectionPool

Anyone interested in taking a crack at that? If not, I can look into it some time next week.

[Vincent Laulagnet]

Hi Michael,

Great. Thank you.

I am not confident in implementing this yet. I would love to help in a couple of weeks.

Best Regards,

Vincent

[Greg Weber]

On Fri, Mar 6, 2015 at 12:34 AM, Michael Snoyman <mic...@snoyman.com> wrote:

This is a common enough request that we should probably add a function that takes some raw SQL to be executed (or perhaps a list of SQL commands) each time a connection is created. I'm thinking of something like:

createPostgresqlPoolExecute :: ConnectionString -> Int -> [Text] -> m ConnectionPool

Better would be to have something a little more extensible that this can be created on top of.

Perhaps something like `(Connection -> m ())` instead of `[Text]`.

That starts to look like a general way to construct a pool and not anything specific to Postgres.

[Michael Snoyman]

Definitely a better generalization, thank you.

[Michael Snoyman]

I've pushed a commit[1] to add the function `createPostgresqlPoolModified` (bikeshedding on a better name welcome).

[1] https://github.com/yesodweb/persistent/commit/bbb0ce0027b4cdf53a5b1fee831520c0a7565ad3

On Sat, Mar 7, 2015 at 6:43 PM Michael Snoyman <mic...@snoyman.com> wrote:

Definitely a better generalization, thank you.

On Fri, Mar 6, 2015, 6:16 PM Greg Weber <gr...@gregweber.info> wrote:

On Fri, Mar 6, 2015 at 12:34 AM, Michael Snoyman <mic...@snoyman.com> wrote:

This is a common enough request that we should probably add a function that takes some raw SQL to be executed (or perhaps a list of SQL commands) each time a connection is created. I'm thinking of something like:

createPostgresqlPoolExecute :: ConnectionString -> Int -> [Text] -> m ConnectionPool

Better would be to have something a little more extensible that this can be created on top of.

Perhaps something like `(Connection -> m ())` instead of `[Text]`.

That starts to look like a general way to construct a pool and not anything specific to Postgres.

Anyone interested in taking a crack at that? If not, I can look into it some time next week.

To unsubscribe from this group and stop receiving emails from it, send an email to yesodweb+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Yesod Web Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to yesodweb+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Yesod Web Framework" group.

To unsubscribe from this group and stop receiving emails from it, send an email to yesodweb+unsubscribe@googlegroups.com.

[Kristoffer Josefsson]

Could someone please supply a simple example to this solution above? I'd like to inject the string "EXPLAIN ANALYZE;" using the above technique, something along the lines of "execute" at the bottom of https://github.com/yesodweb/yesod/wiki/RawSQL , but that page is outdated.

The `const` example in the implementation of createPostgresqlPool doesn't help me unfortunately.

[Manuel Gómez]

On Wed, Mar 4, 2015 at 11:02 AM, Greg Weber <gr...@gregweber.info> wrote:
> Does running a raw sql command to set the schema work?

That will indeed work as long as all queries reference only one schema
at a time. As far as I recall, Persistent would require support for
Postgres qualified names to handle queries involving objects in
multiple schemas, and AFAIR, no such support is present.