Cool! I'll experiment with your suggestions shortly and post what I find, for future googlers. Thanks for the pointers, this should be enough to get me in the right direction, if it isn't already exactly what I needed. I appreciate your time and effort on Sequel!
On Fri, Mar 11, 2016 at 3:41 PM, Jeremy Evans <[email protected]> wrote: > On Friday, March 11, 2016 at 2:22:00 PM UTC-8, Chris Riddoch wrote: >> >> Hi, folks. >> >> I've been experimenting with implementing the triggers, schemas, and >> functions mentioned in a talk by Marcus Hagander, presented at PGDay >> 15, called "A Tardis for your ORM"[1]. I finally got it working, >> after spending some quality time with the postgresql manual and adding >> the few bits he left out of the talk and slides. In the talk, he >> talks about how result-caching and connection pooling can cause >> "interesting" behavior with ORMs when making use of it the views. >> Here's why: >> >> databasename=# set search_path = 'tardis'; >> databasename=# select column from tablename where ....; >> (some result) >> databasename=# set history.timestamp = '2016-02... (sometime in the >> past)'; >> databasename=# select column from tablename where ....; >> (an older result!) >> databasename=# set history.timestamp = now; >> databasename=# select column from timestamp where ....; >> (the current result again) >> >> The search_path and history.timestamp are per-connection values, I >> believe. And even if they weren't, sending the same query after >> changing history.timestamp gives different values - which is the >> purpose of the whole setup, of course. From what I can tell, Sequel >> doesn't automatically cache results of queries, which is good. >> >> So, two specific questions: >> >> * Is there a Sequel wrapper for these 'set' queries? It looks like >> search_path is expected to only be set as a parameter on the connect >> method. > > > Whenever you change per connection settings, you need to make sure you check > the connection out from the pool, make the settings, and reset the settings > before returning the connection to the pool. Something like: > > def DB.with_tardis(schema, timestamp) > synchronize do |conn| > begin > log_connection_execute(conn, "SET search_path = #{literal(schema)}") > log_connection_execute(conn, "SET history.timestamp = > #{literal(timestamp)}'") > yield > ensure > log_connection_execute(conn, "SET search_path = ...") > log_connection_execute(conn, "SET history.timestamp = ...'") > end > end > > Then you can do: > > DB.with_tardis('tardis1', Time.now) do > # ... > end > >> >> * What would I need to set in order to have multiple different >> connections to the same database in an application, each with >> different values for search_path and history.timestamp? I'm >> envisioning having whatever normal behavior is used for the 'current' >> version of data, which would work entirely out of the public schema, >> and completely unaware of the history mechanism... and then also >> having an additional connection which queries only from the 'tardis' >> schema and changes history.timestamp as it needs. > > > You can use the sharding support with the server_block extension combined > with an after connect proc: > > DB = Sequel.connect(..., > :servers=>{:tardis1=>{}, :tardis2=>{}}, > :after_connect=>proc do |conn, server| > case server > when :tardis1 > DB.log_connection_execute(conn, "SET search_path = ...") > DB.log_connection_execute(conn, "SET history.timestamp = ...'") > when :tardis2 > # ... > end > end > ) > DB.extension :server_block > > Then you can do: > > DB.with_server(:tardis1) do > # ... > end > > DB.with_server(:tardis2) do > # ... > end > > All of the above code is untested, but hopefully it provides clues to how it > would work. > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sequel-talk. > For more options, visit https://groups.google.com/d/optout. -- Chris Riddoch http://www.syntacticsugar.org/ -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
