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.
