Re: Totally weird behaviour in org.postgresql.Driver

My app used to work fine until I started getting tons of
NullPointerExceptions in org.postgresql.Driver:586

for (int tmp = 0;tmp < protocols.length;tmp++)
I traced into that line and 'protocols' variable is null. That variable is
private static, and is not being assigned to anywhere outside the
constructor (why isnt it 'final'?). I'm totally at loss why and how
protocols can become null. JVM bug perhaps? This is Tomcat 6 running on
Ubuntu 8.10, and my webapp uses BlazeDS.

Any ideas? Could this be due to improper synchronization somewhere in my
app?

Peter

Peter wrote:
> My app used to work fine until I started getting tons of
> NullPointerExceptions in org.postgresql.Driver:586
>
> for (int tmp = 0;tmp < protocols.length;tmp++)
> I traced into that line and 'protocols' variable is null. That variable is
> private static, and is not being assigned to anywhere outside the
> constructor (why isnt it 'final'?).

Yes, it should probably be private static final. (that code is pretty
ugly, anyway)

> I'm totally at loss why and how
> protocols can become null. JVM bug perhaps? This is Tomcat 6 running on
> Ubuntu 8.10, and my webapp uses BlazeDS.
>
> Any ideas? Could this be due to improper synchronization somewhere in my
> app?

It does sound like a JVM bug.

If you're using gcj (might be the default on an Ubuntu install, I don't
remember), throw it away; every time I've seen "impossible" NPEs like
this it's been because of gcj bugs.

-O

>> I'm totally at loss why and how
>> protocols can become null. JVM bug perhaps? This is Tomcat 6 running on
>> Ubuntu 8.10, and my webapp uses BlazeDS.
>>
>> Any ideas? Could this be due to improper synchronization somewhere in my
>> app?
>
> It does sound like a JVM bug.
>
> If you're using gcj (might be the default on an Ubuntu install, I don't
> remember), throw it away; every time I've seen "impossible" NPEs like
> this it's been because of gcj bugs.

I'm in fact using Sun JVM 1.6.0.10. I think you're right about gcj being the
default - that was one of the first things I scrapped.

I added a bunch more synchronized (mutex) blocks in my code to avoid
getConnection being called simultaneously from multiple threads (why isnt
that method synchronized anyway?) - so far so good... keeping fingers
crossed. Maybe should have just subclassed PGSimpleDataSource...

Peter

On Wed, 11 Mar 2009, Peter wrote:

>
> I added a bunch more synchronized (mutex) blocks in my code to avoid
> getConnection being called simultaneously from multiple threads (why isnt
> that method synchronized anyway?) - so far so good... keeping fingers
> crossed. Maybe should have just subclassed PGSimpleDataSource...

I'm not sure why it needs to be synchronized. The attached test case (20
threads each opening and closing connections as fast as possible) doesn't
show any problems for me. Does it fail for you? Is there a particular
change that fixes it for you?

Kris Jurka

>> I added a bunch more synchronized (mutex) blocks in my code to avoid
>> getConnection being called simultaneously from multiple threads (why
>> isnt that method synchronized anyway?) - so far so good... keeping
>> fingers crossed. Maybe should have just subclassed PGSimpleDataSource...

>I'm not sure why it needs to be synchronized. The attached test case (20
threads each opening and closing >connections as fast as possible) doesn't
show any problems for me. Does it fail for you? Is there a >particular
change that fixes it for you?

I'm not sure if it needs to be synchronized either. Most likely I'm running
into a JVM bug in my particular environment (Tomcat servlet). I adapted your
test case to run inside a servlet and it does not produce any errors.

In my app I'm using singleton class instance to handle all database
connections, sample code below. If I remove synchronized (dbManager.class)
blocks the bug is back with a vengeance...

final public class dbManager {


private static PGSimpleDataSource _ds;
// Our global instance
private static dbManager _dbManager = null;

/**

* Attempts to create a valid PG DataSource
*
* @return a boolean representing whether the DS was successfully
created
*/
private synchronized static boolean createDataSource() {
try {
synchronized (dbManager.class) {

_ds = new PGSimpleDataSource();
_ds.setServerName("myserver");
_ds.setDatabaseName("mydb");
_ds.setPortNumber(5432);

}

return true;
} catch (Exception ex) {
Logger.getLogger(dbManager.class.getName()).log(Level.SEVERE,
null, ex);
return false;
}
}

public synchronized static dbManager getInstance(String connString) {
if ( _dbManager == null ) {
if ( createDataSource() ) {
_dbManager = new dbManager();
}
}

return _dbManager;
}

/**
*
* @return returns a connection created w/ default initial credentials
and very little access rights
*/
public synchronized Connection getInitialConnection() throws
SQLException {
synchronized (dbManager.class) {
return _ds.getConnection("inituser", "initpass");
}
}

/**
*
* @param dbUser
* @param dbPass
* @return a connection if it can be created, null otherwise
*/
public synchronized Connection getNewUserConnection(String dbUser,
String dbPass){
try {

synchronized (dbManager.class) {

Connection userConn = _ds.getConnection(dbUser,
dbPass);
return userConn;

}

} catch (Exception ex) {

Logger.getLogger(dbManager.class.getName()).log(Level.SEVERE, null, ex);
return null;
}
}
}

Peter,

Couple things.... do you really need to synchronize twice in every method
???
Have you looked at dbcp or c3po instead of re-inventing the wheel ?

Can you supply us with a test case which does exhibit the bug ?

On Thu, Mar 12, 2009 at 6:04 AM, Peter <peter(at)greatnowhere(dot)com> wrote:

> >> I added a bunch more synchronized (mutex) blocks in my code to avoid
> >> getConnection being called simultaneously from multiple threads (why
> >> isnt that method synchronized anyway?) - so far so good... keeping
> >> fingers crossed. Maybe should have just subclassed PGSimpleDataSource...
>
> >I'm not sure why it needs to be synchronized. The attached test case (20
> threads each opening and closing >connections as fast as possible) doesn't
> show any problems for me. Does it fail for you? Is there a >particular
> change that fixes it for you?
>
> I'm not sure if it needs to be synchronized either. Most likely I'm running
> into a JVM bug in my particular environment (Tomcat servlet). I adapted
> your
> test case to run inside a servlet and it does not produce any errors.
>
> In my app I'm using singleton class instance to handle all database
> connections, sample code below. If I remove synchronized (dbManager.class)
> blocks the bug is back with a vengeance...
>
> final public class dbManager {
>
>
> private static PGSimpleDataSource _ds;
> // Our global instance
> private static dbManager _dbManager = null;
>
> /**
>
> * Attempts to create a valid PG DataSource
> *
> * @return a boolean representing whether the DS was successfully
> created
> */
> private synchronized static boolean createDataSource() {
> try {
> synchronized (dbManager.class) {
>
> _ds = new PGSimpleDataSource();
> _ds.setServerName("myserver");
> _ds.setDatabaseName("mydb");
> _ds.setPortNumber(5432);
>
> }
>
> return true;
> } catch (Exception ex) {
> Logger.getLogger(dbManager.class.getName()).log(Level.SEVERE,
> null, ex);
> return false;
> }
> }
>
> public synchronized static dbManager getInstance(String connString) {
> if ( _dbManager == null ) {
> if ( createDataSource() ) {
> _dbManager = new dbManager();
> }
> }
>
> return _dbManager;
> }
>
> /**
> *
> * @return returns a connection created w/ default initial credentials
> and very little access rights
> */
> public synchronized Connection getInitialConnection() throws
> SQLException {
> synchronized (dbManager.class) {
> return _ds.getConnection("inituser", "initpass");
> }
> }
>
> /**
> *
> * @param dbUser
> * @param dbPass
> * @return a connection if it can be created, null otherwise
> */
> public synchronized Connection getNewUserConnection(String dbUser,
> String dbPass){
> try {
>
> synchronized (dbManager.class) {
>
> Connection userConn = _ds.getConnection(dbUser,
> dbPass);
> return userConn;
>
> }
>
> } catch (Exception ex) {
>
> Logger.getLogger(dbManager.class.getName()).log(Level.SEVERE, null, ex);
> return null;
> }
> }
> }
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

Obiously I do not need to synchronize the method. the synchronized block
inside it should work just fine. Was just grasping at straws there.

I checked out dbcp and c3po (thanks for the pointers!) but none fits my app
as each user needs to connect with his own credentials.

I'll see if I can produce a test case and post it here.

From: davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] On Behalf Of Dave
Cramer
Sent: Thursday, March 12, 2009 12:43 PM
To: Peter
Cc: Kris Jurka; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Totally weird behaviour in org.postgresql.Driver

Peter,

Couple things.... do you really need to synchronize twice in every method
???
Have you looked at dbcp or c3po instead of re-inventing the wheel ?

Can you supply us with a test case which does exhibit the bug ?

On Thursday 12 March 2009 08:22:53 am Peter wrote:
> Obiously I do not need to synchronize the method. the synchronized block
> inside it should work just fine. Was just grasping at straws there.
>
>
>
> I checked out dbcp and c3po (thanks for the pointers!) but none fits my app
> as each user needs to connect with his own credentials.

This was a problem for me as well. So, I made a factory for returning
connections from dbcp and made some adjustments to the connection returned
from the pool, so that our auditing triggers knew the application user that
was using the connection.

That way we could get the auditing and use a pool that has hopefully had the
bugs beaten out of if. But, that would not work in every situation.

C.

--
When I'm on my own I'm in bad company.
-- David Bowie

> Couple things.... do you really need to synchronize twice in every method
> ???
> Have you looked at dbcp or c3po instead of re-inventing the wheel ?
>
> Can you supply us with a test case which does exhibit the bug ?

I'm fairly sure this is JVM/Tomcat bug I'm dealing with here. The test case
below was configured to run on every request received by servlet, but only
started exhibiting the weird behaviour after few hours of heavy load:

import java.sql.*;
import java.util.logging.Logger;

import org.postgresql.ds.PGSimpleDataSource;

public class PGTester {

public static void main() {

org.postgresql.ds.PGSimpleDataSource _ds=new
org.postgresql.ds.PGSimpleDataSource();
_ds.setDatabaseName("mydb");
_ds.setServerName("myhost");
_ds.setPortNumber(5432);

for (int i=0; i<20; i++) {
Connector con = new Connector(i+1,_ds);
new Thread(con, "Conn" ).start();
}
}

private static class Connector implements Runnable {
@SuppressWarnings("unused")
private int _num;
private PGSimpleDataSource _ds;

Connector(int n, PGSimpleDataSource ds) {
_num = n;
_ds = ds;
}

public void run() {
for (int i=0; i<100; i++) {
try {
Connection conn = _ds.getConnection("user","pass");
// System.out.println("Connector " + _num + " made connection #" +
(i+1));
conn.close();
} catch (Exception e) {
Logger.getLogger(this.getClass().getName()).severe(e.getMessage());
}
}
}
}

}

>> I checked out dbcp and c3po (thanks for the pointers!) but none fits my
>> app
>> as each user needs to connect with his own credentials.
>
> This was a problem for me as well. So, I made a factory for returning
> connections from dbcp and made some adjustments to the connection returned
> from the pool, so that our auditing triggers knew the application user
> that
> was using the connection.
>
> That way we could get the auditing and use a pool that has hopefully had
> the
> bugs beaten out of if. But, that would not work in every situation.

Thanks for sharing the tip! What adjustments exactly did you have to make? I
just make every user to log on with different PG username. Are there any
other options?

Peter

>
> Thanks for sharing the tip! What adjustments exactly did you have to make?
> I
> just make every user to log on with different PG username. Are there any
> other options?
>

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 ?

Dave

>
> Peter
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

>
> Thanks for sharing the tip! What adjustments exactly did you
> have to make? I
> just make every user to log on with different PG username. Are
> there any
> other options?
>
> 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! ;)

Peter

peter wrote:
>>
>> Thanks for sharing the tip! What adjustments exactly did you
>> have to make? I
>> just make every user to log on with different PG username. Are
>> there any
>> other options?
>>
>> 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! ;)

JCA's' pooling API in theory has the ability to distinguish different
connections on the basis of credentials (and you can map that to JDBC),
but I'm not sure if there are any pool implementations out there that
make use of it. You'd need some decent pool logic to avoid thrashing
connections depending on the number of unique users you have.

-O

peter wrote:
>>
>> Thanks for sharing the tip! What adjustments exactly did you
>> have to make? I
>> just make every user to log on with different PG username. Are
>> there any
>> other options?
>>
>> 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.

--
Guy Rouillier

On Sun, Mar 15, 2009 at 9:30 PM, Guy Rouillier <guyr-ml1(at)burntmail(dot)com>wrote:

> peter wrote:
>
>> Thanks for sharing the tip! What adjustments exactly did
>>> you
>>> have to make? I
>>> just make every user to log on with different PG username. Are
>>> there any
>>> other options?
>>>
>>> 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.

Dave

>
>

> 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.

Do you mean setting a variable (or connection property) in Java? That wont
work - I need it for much more than just auditing. There are tons of PGSQL
code that relies on knowing the logged-on user, and performs complex data
analysis based on that. Now, if I had a way to set user-defined variable in
Postgres connection context that would work - but does not seem like PG
supports it...

Peter

>>>> 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...

Peter

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...

Perhaps you could have your pool connect as a fixed superuser, and issue
a SET SESSION AUTHORIZATION each time you get a connection before you do
anything else.

Or you could do something similar with a non-superuser that had many
roles, and use SET ROLE.

-O

2009/3/17 Peter <peter(at)greatnowhere(dot)com>

>
> 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...
>

You could use temporary table.
E.g. create temporary table localdata(name, value) as select values('user',
'john');
This will be connection-scope.

On Tue, 2009-03-17 at 13:41 +0200, Віталій Тимчишин wrote:
>
>
> 2009/3/17 Peter <peter(at)greatnowhere(dot)com>
>
> 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...
>
> You could use temporary table.
> E.g. create temporary table localdata(name, value) as select
> values('user', 'john');
> This will be connection-scope.

Or use pl/python and its global dictionaries, write 2 functions

hannu=# create function set_current_web_user(username text) returns void
as $$
GD['current_web_user'] = username;
$$ language plpythonu security definer;
CREATE FUNCTION
hannu=# create function get_current_web_user() returns text as $$
hannu$# return GD['current_web_user']
hannu$# $$ language plpythonu security definer;
CREATE FUNCTION
hannu=# select get_current_web_user();
ERROR: plpython: function "get_current_web_user" failed
DETAIL: <type 'exceptions.KeyError'>: 'current_web_user'
hannu=# select set_current_web_user('adalbert');
set_current_web_user
----------------------

(1 row)

hannu=# select get_current_web_user();
get_current_web_user
----------------------
adalbert
(1 row)

GD has session scope.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

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

> 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.

Excellent suggestion! Thanks a lot!

Peter