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
