Thread: Any form of connection-level "session variable" ?

This is revisiting a problem I posed to this group a month or so ago
regarding separating different users' data through schema views.  The
solution we're using is based on a suggestion we received here:

http://archives.postgresql.org/pgsql-general/2006-12/msg00037.php

Everything is working great with the exception of performance.  One of
our tables has close to a million records, and the overhead of calling
the get_client_id() function per row is eating us alive, I assume
because it is having to per-row call a select  on a table to retrieve
the proper ID within the function.

Is there any way I could establish this ID initially in some sort of
connection-level variable, and from this point on reference that
variable?  What I'm thinking is something like the following:

select initialize_client_id(); //This would actually hit the DB to
retrieve the proper ID for the logged in user

//Now, in the view get_client_id() retrieves the earlier established
"variable" instead of hitting the DB
select foo,bar FROM tbl_foo WHERE client_id = get_client_id();

Am I incorrect in assuming that the statement:

select foo from tbl_bar WHERE client_id = get_client_id();

will call get_client_id() for every row?

John

I think I got it:

CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
from secureview.tbl_employee where username = (SELECT current_user) $$
LANGUAGE SQL IMMUTABLE;

I made the function immutable so it only calls it once, therefore no
longer requiring a call per-row.



John McCawley wrote:

> This is revisiting a problem I posed to this group a month or so ago
> regarding separating different users' data through schema views.  The
> solution we're using is based on a suggestion we received here:
>
> http://archives.postgresql.org/pgsql-general/2006-12/msg00037.php
>
> Everything is working great with the exception of performance.  One of
> our tables has close to a million records, and the overhead of calling
> the get_client_id() function per row is eating us alive, I assume
> because it is having to per-row call a select  on a table to retrieve
> the proper ID within the function.
>
> Is there any way I could establish this ID initially in some sort of
> connection-level variable, and from this point on reference that
> variable?  What I'm thinking is something like the following:
>
> select initialize_client_id(); //This would actually hit the DB to
> retrieve the proper ID for the logged in user
>
> //Now, in the view get_client_id() retrieves the earlier established
> "variable" instead of hitting the DB
> select foo,bar FROM tbl_foo WHERE client_id = get_client_id();
> Am I incorrect in assuming that the statement:
>
> select foo from tbl_bar WHERE client_id = get_client_id();
>
> will call get_client_id() for every row?
>
> John
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

John McCawley <nospam@hardgeus.com> writes:
> I think I got it:
> CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
> from secureview.tbl_employee where username = (SELECT current_user) $$
> LANGUAGE SQL IMMUTABLE;
> I made the function immutable so it only calls it once, therefore no
> longer requiring a call per-row.

Since it's obviously *not* immutable, this will come back to bite you
sooner or later (probably sooner).  Labeling it STABLE would be
reasonable, although I'm not certain how much that helps you.  Do you
have indexes on the columns it's being compared to?

            regards, tom lane

Tom Lane wrote:
> John McCawley <nospam@hardgeus.com> writes:
>
>> I think I got it:
>> CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
>> from secureview.tbl_employee where username = (SELECT current_user) $$
>> LANGUAGE SQL IMMUTABLE;
>> I made the function immutable so it only calls it once, therefore no
>> longer requiring a call per-row.
>>
>
> Since it's obviously *not* immutable, this will come back to bite you
> sooner or later (probably sooner).  Labeling it STABLE would be
> reasonable, although I'm not certain how much that helps you.  Do you
> have indexes on the columns it's being compared to?
>
Besides, a temp table is pretty much a session variable.

--
erik jones <erik@myemma.com>
software development
emma(r)

>> John McCawley <nospam@hardgeus.com> writes:
>>> CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
>>> from secureview.tbl_employee where username = (SELECT current_user) $$
>>> LANGUAGE SQL IMMUTABLE;

One other thing: that no-op sub-SELECT isn't doing anything for you
except adding overhead ... you might've needed it to allow index use
back in 7.2 or before, but not in any remotely modern PG release.

            regards, tom lane

I tried stable, and that didn't help at all.  How long does PostgreSQL
maintain the "state" of the function when using immutable?  Until a
restart?  Until the end of a session?  Until the function is dropped and
re-added?

While this value isn't 100% absolutely positively guaranteed to never
change, it is pretty dang close.  I have a strict correlation between
the username that someone uses to login and the ID returned.  Changing
someone's username would break a whole lot more than this one app on the
project...



Erik Jones wrote:

> Tom Lane wrote:
>
>> John McCawley <nospam@hardgeus.com> writes:
>>
>>
>>> I think I got it:
>>> CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
>>> from secureview.tbl_employee where username = (SELECT current_user)
>>> $$ LANGUAGE SQL IMMUTABLE;
>>> I made the function immutable so it only calls it once, therefore no
>>> longer requiring a call per-row.
>>>
>>
>>
>> Since it's obviously *not* immutable, this will come back to bite you
>> sooner or later (probably sooner).  Labeling it STABLE would be
>> reasonable, although I'm not certain how much that helps you.  Do you
>> have indexes on the columns it's being compared to?
>>
>
> Besides, a temp table is pretty much a session variable.
>

John McCawley <nospam@hardgeus.com> writes:
> While this value isn't 100% absolutely positively guaranteed to never
> change, it is pretty dang close.

Counterexample: SET ROLE or SET SESSION AUTHORIZATION.

            regards, tom lane

I'm not trying to be argumentative, but I honestly don't know what you
mean here...and the only reason I ask for clarification is that you are
probably trying to tell me something important :)

Tom Lane wrote:

>John McCawley <nospam@hardgeus.com> writes:
>
>
>>While this value isn't 100% absolutely positively guaranteed to never
>>change, it is pretty dang close.
>>
>>
>
>Counterexample: SET ROLE or SET SESSION AUTHORIZATION.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>

What about:

 create function set_emp_id() returns void as $$
 begin
    drop table if exists emp_1_id;
    select emp_id into temp emp_1_id from secureview.tbl_employee where
username = current_user;
 end;
 $$ language plpgsql;

 create function get_emp_id() returns int as $$
    return select emp_id from emp_1_id;
 $$ language plpgsql stable;

Call set_emp_id once on connection, then use get_emp_id thereafter. Would
that be any faster? (This is what Erik meant by " a temp table is pretty
much a session variable" in his earlier message.)

 --
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe wrote:
> What about:
>
>  create function set_emp_id() returns void as $$
>  begin
>     drop table if exists emp_1_id;
>     select emp_id into temp emp_1_id from secureview.tbl_employee where
> username = current_user;
>  end;
>  $$ language plpgsql;
>
>  create function get_emp_id() returns int as $$
>     return select emp_id from emp_1_id;
>  $$ language plpgsql stable;
>
> Call set_emp_id once on connection, then use get_emp_id thereafter. Would
> that be any faster? (This is what Erik meant by " a temp table is pretty
> much a session variable" in his earlier message.)

Or, in similar fashion, use this:
http://www.joeconway.com/sessfunc.tar.gz
http://www.onlamp.com/pub/a/onlamp/2004/06/28/postgresql_extensions.html

HTH,

Joe

On Thu, Jan 04, 2007 at 06:53:09PM -0700, Scott Ribe wrote:
> What about:
>
>  create function set_emp_id() returns void as $$
>  begin
>     drop table if exists emp_1_id;
>     select emp_id into temp emp_1_id from secureview.tbl_employee where
> username = current_user;
>  end;
>  $$ language plpgsql;
>
>  create function get_emp_id() returns int as $$
>     return select emp_id from emp_1_id;
>  $$ language plpgsql stable;
>
> Call set_emp_id once on connection, then use get_emp_id thereafter.
> Would that be any faster? (This is what Erik meant by "a temp table
> is pretty much a session variable" in his earlier message.)

You can use PL/Perl's %_SHARED hash
<http://www.postgresql.org/docs/current/static/plperl-global.html> to
store session-long variables, and you don't need to worry about
cleanup :)  Similar things exist in PL/Python and possibly others.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

> ...and you don't need to worry about cleanup...

What cleanup? Temp tables are dropped on connection close.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

In article <459D4FA0.4020202@hardgeus.com>,
John McCawley <nospam@hardgeus.com> wrote:

% Is there any way I could establish this ID initially in some sort of
% connection-level variable, and from this point on reference that
% variable?

We do this sort of thing using a custom C function, but I've been
thinking lately you could use a custom variable class to do the same
thing.

Put
 custom_variable_classes = 'session'

in your postgresql.conf, and you can have
 set session.myid = 23;

then retrieve the value either by joining to pg_settings or using
 show session.myid;

You can perform per-user initialisation with
 alter user set session.myid = 23;

Which allows the value to persist between sessions.

I haven't done anything with this idea so I can't say how well it
works or whether there are downsides to it.
--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com

In article <459D4FA0.4020202@hardgeus.com>,
John McCawley <nospam@hardgeus.com> wrote:

% Is there any way I could establish this ID initially in some sort of
% connection-level variable, and from this point on reference that
% variable?

We do this sort of thing using a custom C function, but I've been
thinking lately you could use a custom variable class to do the same
thing.

Put
 custom_variable_classes = 'session'

in your postgresql.conf, and you can have
 set session.myid = 23;

then retrieve the value either by joining to pg_settings or using
 show session.myid;

You can perform per-user initialisation with
 alter user set session.myid = 23;

Which allows the value to persist between sessions.

I haven't done anything with this idea so I can't say how well it
works or whether there are downsides to it.
--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com