Re: persistent connections and 'set variable' - Mailing list pgsql-jdbc

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@sd63.bc.ca> wrote:

On 2013-02-14, at 15:29 , Dave Cramer <pg@fastcrypt.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@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@sd63.bc.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 ...