[naviserver-devel] DB Connection Handles nsdbipg
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. 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. Is there a way to do this? Thanks! - Ian -- ___ naviserver-devel mailing list naviserver-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/naviserver-devel
Re: [naviserver-devel] DB Connection Handles nsdbipg
On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding 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
Re: [naviserver-devel] DB Connection Handles nsdbipg
On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey wrote:
> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding 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
naviserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
Re: [naviserver-devel] DB Connection Handles nsdbipg
On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey wrote:
> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding 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.
> 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.
OK, so my maxhandles is 0, per the default, so I should always get the
same handle in repeated calls. I'm apparently not because when I
issue
dbi_dml {set search_path to test,public}
dbi_rows {select * from tableintestbutnotpublic}
I get an error stating that the table does not exist.
Here's my postgres log
LOG: statement: set client_encoding to 'UTF8'
LOG: execute dbipg_0: set session client_encoding = 'UTF8'
LOG: execute dbipg_1: set session timezone = 'UTC'
LOG: execute dbipg_2: set session datestyle = 'ISO'
LOG: execute dbipg_3: set search_path to test,public
ERROR: relation "classification" does not exist
STATEMENT: SELECT classification
FROM classification
ORDER BY classification
but it does exist...
test=# \dt test.classification
List of relations
Schema | Name | Type | Owner
++---+---
test | classification | table | user
(1 row)
So I wrote a test that goes like this
if {[catch {
catch {dbi_dml {drop table test}}
catch {dbi_dml {drop schema test cascade}}
dbi_dml {
create schema test
}
dbi_dml {
create table test (
a integer not null,
b varchar not null,
c bytea,
test varchar
)
}
dbi_dml {insert into test (a, b, test) values (1, 'x', 'test1')}
dbi_dml {insert into test (a, b, test) values (2, 'y', 'test2')}
} err]} {
ns_log error $err
} else {
testConstraint table true
}
test schema-1 {bad search path} -constraints table -body {
dbi_dml {set search_path to test}
dbi_rows {select * from test}
} -returnCodes error -result {relation "test" does not exist}
test schema-2 {bad search path same thread} -constraints table -body {
dbi_rows {select * from test}
} -returnCodes error -result {relation "test" does not exist}
test schema-3 {good search path} -constraints table -body {
dbi_dml {set search_path to public}
dbi_rows {select * from test where a = 99}
} -result {}
Which passes when run with make test.
So, what could be my problem here? My config file is being used, and
it does say maxhandles=0...
ns_section "ns/server/${server}/module/dbpg"
ns_param default true
ns_param maxhandles0
ns_param timeout 10
- Ian
--
___
naviserver-devel mailing list
naviserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
Re: [naviserver-devel] DB Connection Handles nsdbipg
Please ignore, it is a permission issue. The user had not rights in
the schema, and since I used search_path, it didn't generate a
permission denied error, it just didn't look there and generated a
does not exist error.
Sorry about the noise. Things seem to work as expected.
- Ian
On 3/26/09, Ian Harding wrote:
> On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey wrote:
>> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding
>> 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.
>
>
>
>> 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.
>
> OK, so my maxhandles is 0, per the default, so I should always get the
> same handle in repeated calls. I'm apparently not because when I
> issue
>
> dbi_dml {set search_path to test,public}
> dbi_rows {select * from tableintestbutnotpublic}
>
> I get an error stating that the table does not exist.
>
> Here's my postgres log
>
> LOG: statement: set client_encoding to 'UTF8'
> LOG: execute dbipg_0: set session client_encoding = 'UTF8'
> LOG: execute dbipg_1: set session timezone = 'UTC'
> LOG: execute dbipg_2: set session datestyle = 'ISO'
> LOG: execute dbipg_3: set search_path to test,public
> ERROR: relation "classification" does not exist
> STATEMENT: SELECT classification
> FROM classification
> ORDER BY classification
>
> but it does exist...
>
> test=# \dt test.classification
> List of relations
> Schema | Name | Type | Owner
> ++---+---
> test | classification | table | user
> (1 row)
>
> So I wrote a test that goes like this
>
> if {[catch {
>
> catch {dbi_dml {drop table test}}
> catch {dbi_dml {drop schema test cascade}}
>
> dbi_dml {
> create schema test
> }
>
> dbi_dml {
> create table test (
> a integer not null,
> b varchar not null,
> c bytea,
> test varchar
> )
> }
> dbi_dml {insert into test (a, b, test) values (1, 'x', 'test1')}
> dbi_dml {insert into test (a, b, test) values (2, 'y', 'test2')}
>
> } err]} {
> ns_log error $err
> } else {
> testConstraint table true
> }
>
>
> test schema-1 {bad search path} -constraints table -body {
> dbi_dml {set search_path to test}
> dbi_rows {select * from test}
> } -returnCodes error -result {relation "test" does not exist}
>
> test schema-2 {bad search path same thread} -constraints table -body {
> dbi_rows {select * from test}
> } -returnCodes error -result {relation "test" does not exist}
>
> test schema-3 {good search path} -constraints table -body {
> dbi_dml {set search_path to public}
> dbi_rows {select * from test where a = 99}
> } -result {}
>
> Which passes when run with make test.
>
> So, what could be my problem here? My config file is being used, and
> it does say maxhandles=0...
>
> ns_section "ns/server/${server}/module/dbpg"
> ns_param default true
> ns_param maxhandles0
> ns_param timeout 10
>
> - Ian
>
--
___
naviserver-devel mailing list
naviserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
Re: [naviserver-devel] DB Connection Handles nsdbipg
On Thu, Mar 26, 2009 at 3:09 PM, Ian Harding wrote: > Please ignore, it is a permission issue. The user had not rights in > the schema, and since I used search_path, it didn't generate a > permission denied error, it just didn't look there and generated a > does not exist error. Great. FYI, there is some extra debug output available if you enable server-wide debug logging. Each handle has an id and much of the log output shows the id and the number of queries performed. In this case, you'd want to make sure that the same handle is being used for both the SET command and the following query. -- ___ naviserver-devel mailing list naviserver-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/naviserver-devel
