Re: persistent connections and 'set variable'

Generally the way jboss apps work is a request is made to jboss it gets a
connection from the pool does work and returns it. The connection does not
actually close. Being logged in has nothing to do with the db. Most ppl
handle this by passing the user from the session to the db on each db
request. You cannot set something on login and expect it to persist from
one db request to the next within the same web session
On 2013-02-14 6:52 PM, "Marc Fournier" <mfournier(at)sd63(dot)bc(dot)ca> wrote:

>
> On 2013-02-14, at 15:29 , Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> If you open a connection for each user you will get 2 separate
> connections. The pool doesn't do anything fancy
>
>
> the way it was explained to me is that we aren't opening a connection, but
> are using jBoss to handle that … and from the database side of things, the
> 'user' is always the same …
>
> so, for instance, on two different browsers, I'm logged into the system,
> but there is one database connection:
>
> root(at)staging:/ # ps aux | grep postg
> pgsql 49509 0.3 0.3 163912 12944 ?? SsJ 11:36PM 0:00.13 postgres:
> openstudent openstudent 10.5.254.55(15338) (postgres)
> pgsql 53734 0.0 0.0 17912 1688 ?? IsJ 31Jan13 0:00.06 postgres:
> logger process (postgres)
> pgsql 53737 0.0 0.5 159816 19908 ?? IsJ 31Jan13 0:00.86 postgres:
> checkpointer process (postgres)
> pgsql 53738 0.0 0.1 159816 2716 ?? SsJ 31Jan13 0:08.59 postgres:
> writer process (postgres)
> pgsql 53739 0.0 0.1 159816 5940 ?? SsJ 31Jan13 0:08.26 postgres:
> wal writer process (postgres)
> pgsql 53740 0.0 0.1 159816 3076 ?? SsJ 31Jan13 1:05.22 postgres:
> autovacuum launcher process (postgres)
> pgsql 53741 0.0 0.0 17912 1832 ?? SsJ 31Jan13 1:16.90 postgres:
> stats collector process (postgres)
> pgsql 53722 0.0 0.3 159816 10492 3 SJ 31Jan13 0:33.30
> /usr/local/bin/postgres -D /usr/local/pgsql/data
> root 49540 0.0 0.0 9668 1400 1 R+J 11:37PM 0:00.00 grep postg
>
> As long as I don't do anything that 'overlap', it will stay at one
> connection to the backend indefinitely … I just got a colleague to login,
> so there are three of us connected, and still only the one backend
> connection from the application -> database …
>
> but his login id may be 1234, while mine is 4321 … I need to get that 1234
> -or- 4321 down to the trigger so that I know who modified a record …
>
> but because of the connection pooling that is happening, I don't think I
> can safely use 'set variable' at the beginning of the updates, as if he
> does a 'set variable' part way through my updates, his will override mine
> and he'll take ownership of subsequent changes … or, at least, that is what
> I *think* would happen …
>
> Sorry, I'm not wording this very well … and maybe what I'm thinking is not
> even possible … ?
>
>
>
>
> On 2013-02-14 6:20 PM, "Marc Fournier" <mfournier(at)sd63(dot)bc(dot)ca> wrote:
>
>>
>> We have an application that we're trying to get audit logging in place
>> for … right now, we're doing it as a stored procedure that is called by the
>> java app for each time it does an update / insert / delete, but I'd like to
>> move it to a trigger … the problem is (or so we think) is that we're using
>> a persistent connection vs re-establishing a connection …
>>
>> I don't know the internals of this … if two ppl log into the system (same
>> db role … our auth system is based on a users table), and both were to do a
>> transaction to do an update of a table:
>>
>>
>> user 1 user 2
>> *begin;*
>> * begin;*
>> *set variable = 1;*
>> * set variable = 2;*
>> *update table using variable;*
>> *end;*
>> * end;*
>>
>> now, since they are both in a transaction, does the connection pool start
>> up one connection per user, or does it somehow 'tag' the queries and re-use
>> the same connection?
>>
>> What if I did something foolish like avoid the begin/end around the
>> transaction? so user 1 sends his set, then user 2 sends his set, then user
>> 1 tries to update based on the variable? Do the queries get sent across
>> the same connection, and user 2's setting of variable overrides user 1?
>>
>> I've already tested using psql that if I do:
>>
>> *openstudent=> begin;*
>> *BEGIN*
>> *openstudent=> set var.modified_by = 1;*
>> *SET*
>> *openstudent=> end;*
>> *COMMIT*
>> *openstudent=> show var.modified_by;*
>> * var.modified_by *
>> *-----------------*
>> * 1*
>> *(1 row)*
>>
>> that variable retains the '1' value … so the end doesn't revert the
>> variable to an unset state …
>>
>> What I'm trying to do is to get the modified_by id passed down into a
>> trigger, instead of using a stored procedure … current_user doesn't apply,
>> since the login isn't at the 'db role' level … but I need to make sure how
>> it works with the connection pooling, whether what I'm thinking is even
>> possible / reliable …
>>
>> Thanks ...
>>
>>
>>
>
>