Skip site navigation (1) Skip section navigation (2)

Re: Totally weird behaviour in org.postgresql.Driver

From: Guy Rouillier <guyr-ml1(at)burntmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Totally weird behaviour in org.postgresql.Driver
Date: 2009-03-17 17:05:12
Message-ID: 49BFD848.5040207@burntmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Peter wrote:
>>>>> It's fairly unusual to have a tomcat application of any size login to
>>>>> the db as the user. Could you share the reason why ?
>>>>>
>>>>>
>>>> The app is actually middleware for Adobe Flex frontend and PG backend,
>>>> not a regular web app. The architecture requires PG to know which user
>>>> has connected (lots of heavy lifting takes place in PG), and we so far
>>>> havent found any other way how to let PG know which user has connected.
>>>> The only alternative was to supply user ID in every PG function call but
>>>> that is messy and introduces it's own limitations as well. If you have
>>>> any suggestions I'm all ears! ;)
>>>>
>>> Set a user variable after you've obtained a connection from the pool, and
>>> use that to log user-specific values.  That way, you maintain the 
>>> benefits
>>> of connection pools, but can still identify individual users.
>>>
>> It would seem to me that if you need to scale this app then you are going 
>> to
>> have to set the user in the application somewhere. Having all of the users
>> connect as themselves doesn't lend itself to being scalable.
> 
> I guess you're right, but even so I should be able to scale it to hundreds 
> of simultaneous users - the only limiting factor is number of connections on 
> PG server.
> 
> So is there a way to associate user variable with Postgres connection that 
> can be picked up by SQL code running in that connection? Right now I can 
> only think of PlPerl function that caches user id in a global variable, but 
> am not sure about potential pitfalls of such setup...

Yes, that is what I was suggesting in my previous post.  From an old 
thread titled "can I define own variables?":

=======
To do this, you need to set custom_variable_classes in postgresql.conf,
perhaps
     custom_variable_classes = uservars

and then you can do things like

     SET uservars.foo = whatever;
     SHOW uservars.bar;
======

So, you can use pooled connections for scalability.  After you retrieve 
a connection, set your user variable to contain the end user identifier. 
  Then you can retrieve it as necessary (e.g., in a trigger) for the 
duration of the connection.

-- 
Guy Rouillier

In response to

Responses

pgsql-jdbc by date

Next:From: Robin RosenbergDate: 2009-03-18 08:01:41
Subject: NoClassDefFoundErrors
Previous:From: Hannu KrosingDate: 2009-03-17 12:05:19
Subject: Re: Totally weird behaviour in org.postgresql.Driver

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group