On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey <[email protected]> wrote:
> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding <[email protected]> wrote:
>> I am in a situation where I'd like to be able to set the search_path
>> on each page request. In AOLServer I would have just made a db handle
>> getter function that would call ns_db gethandle, issue a quick SET
>> command, and pass the handle back to the caller.
>>
>> With nsdbipg I don't know how to do this. There doesn't seem to be an
>> explicit getting of a handle. It seems that they are gotten
>> automatically on each call and released on each call to a nsdbi
>> function.
>
>
> Handles are managed automatically by default, but you can extend the
> lifetime with the eval command:
>
> dbi_eval {
> dbi_dml {set search_path to foo, public}
> set users [dbi_rows {select * from users}]
> }
>
> When you don't pass the -transaction switch, you are simply reusing
> the same handle.
>
Ah. That works.
>
>> Maybe the datasource param can contain a schema setting, but even that
>> would not be what I want. I want to be able to set the path ideally
>> on each connection, or on each call to the nsdbi functions.
>
>
> Do you want to set the path for the lifetime of the *database*
> connection, or are you trying to create 'virtual' users with one
> database pool, switching the schema search path for duration of the
> *http* connection?
>
> If it's the first, you could set the default path in the postgresql.conf file:
>
> search_path = '$user, public'
>
> If it's the second, then maybe something like the above db_eval will
> work for you.
>
> You could wrap it up in a command like:
>
> with_schema foo {
> dbi_rows { ... }
> }
>
Even better..
> Alternatively, you could set the max number of handles to 0, in which
> case the driver switches to handle-per-thread mode (See docs). In
> this case, when each command 'gets' a handle it will always get the
> same one, as it is never actually returned to the pool but cached for
> the thread. The idea behind this was as a performance optimisation
> for the case where pretty much all your conn threads perform queries
> and getting and putting is just overhead. But it would allow you in
> this case to set the schema path at the beginning of the http request
> and have it persist.
>
I think that's what I want. All of my connections perform queries, so
if I set the search path first, it will stay in effect for the
lifetime of the connection. I'll give that a shot.
Thanks!
- Ian
------------------------------------------------------------------------------
_______________________________________________
naviserver-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
