naviserver-devel

[naviserver-devel] DB Connection Handles nsdbipg

[Ian H. <har...@gm...>]

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

Re: [naviserver-devel] DB Connection Handles nsdbipg

[Stephen D. <sd...@gm...>]

On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding <har...@gm...> 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.

Re: [naviserver-devel] DB Connection Handles nsdbipg

[Ian H. <har...@gm...>]

On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey <sd...@gm...> wrote:
> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding <har...@gm...> 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

Re: [naviserver-devel] DB Connection Handles nsdbipg

[Ian H. <har...@gm...>]

On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey <sd...@gm...> wrote:
> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding <har...@gm...> 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.

<snip>

> 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   maxhandles    0
ns_param   timeout       10

- Ian

Re: [naviserver-devel] DB Connection Handles nsdbipg

[Ian H. <har...@gm...>]

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 <har...@gm...> wrote:
> On Wed, Mar 25, 2009 at 5:10 PM, Stephen Deasey <sd...@gm...> wrote:
>> On Wed, Mar 25, 2009 at 10:56 PM, Ian Harding <har...@gm...>
>> 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.
>
> <snip>
>
>> 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   maxhandles    0
> ns_param   timeout       10
>
> - Ian
>

Re: [naviserver-devel] DB Connection Handles nsdbipg

[Stephen D. <sd...@gm...>]

On Thu, Mar 26, 2009 at 3:09 PM, Ian Harding <har...@gm...> 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.