Thanks, Jeremy. That works like a charm. For posterity's sake, the method
to call on the database instance is *Database#extend_datasets* as opposed
to *Database#dataset_extend*.
On Monday, September 14, 2020 at 7:34:20 PM UTC-4 Jeremy Evans wrote:
> On Monday, September 14, 2020 at 3:33:14 PM UTC-7, Jason Rogers wrote:
>>
>> Is there a way to set connection or session parameters before executing a
>> query? My use case is that I have a specific query that ends up using a
>> disk merge when run, but if I set *work_mem* to some higher value (e.g.
>> 50M) for this query it can do all of its work in-memory. I don't want to
>> set *work_mem* that high for all queries though, which is why it's at
>> its default in the server config file (4MB).
>>
>> I've tried using *Database::run*, *Database::execute*, and *Database::[]*
>> but none of those approaches worked. E.g.
>>
>> ds = DB[...].where(...)
>> DB[%{SET work_mem TO '50MB';#{ds.sql}}].all
>>
>
> Putting multiple queries in the same dataset is undefined behavior in
> Sequel, whether it works depends on the driver.
>
> Something like this should work:
>
> DB.synchronize do
> begin
> DB.run "SET work_mem TO '50MB'"
> ds.all
> ensure
> DB.run "SET work_mem TO '4MB'"
> end
> end
>
> I would probably wrap it:
>
> DB.dataset_extend do
> def all_with_work_mem(mem)
> db.synchronize do
> begin
> db.run "SET work_mem TO '#{mem.to_i}MB'"
> all
> ensure
> db.run "SET work_mem TO '4MB'"
> end
> end
> end
> end
>
> ds.all_with_work_mem(50)
>
> 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 view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/eb8536e5-86e3-4221-996f-7943935386b8n%40googlegroups.com.
