On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding <harding....@gmail.com> 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.
> 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 { ... }
}
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.
------------------------------------------------------------------------------
_______________________________________________
naviserver-devel mailing list
naviserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
