Accessing different schemas with Persistent on PostgreSQL database

173 views
Skip to first unread message

Vincent Laulagnet

unread,
Mar 4, 2015, 3:52:45 AM3/4/15
to yeso...@googlegroups.com
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

unread,
Mar 4, 2015, 10:32:36 AM3/4/15
to Yesod Web Framework
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

unread,
Mar 6, 2015, 3:10:17 AM3/6/15
to yeso...@googlegroups.com
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

unread,
Mar 6, 2015, 3:34:46 AM3/6/15
to yeso...@googlegroups.com
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

unread,
Mar 6, 2015, 4:53:37 AM3/6/15
to yeso...@googlegroups.com, mic...@snoyman.com
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

unread,
Mar 6, 2015, 11:16:33 AM3/6/15
to Yesod Web Framework
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

unread,
Mar 7, 2015, 11:44:02 AM3/7/15
to Yesod Web Framework

Definitely a better generalization, thank you.

Michael Snoyman

unread,
Mar 18, 2015, 12:54:20 PM3/18/15
to Yesod Web Framework
I've pushed a commit[1] to add the function `createPostgresqlPoolModified` (bikeshedding on a better name welcome).


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

unread,
May 18, 2015, 1:43:48 PM5/18/15
to yeso...@googlegroups.com, mic...@snoyman.com
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

unread,
May 18, 2015, 2:24:14 PM5/18/15
to yeso...@googlegroups.com
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.
Reply all
Reply to author
Forward
0 new messages