Обсуждение: A couple of questions
I'm using Postgres from a RedHat RPM postgresql-jdbc-7.2.4-5.73 on an
installed base of a few hundred RedHat 7.3 machines. (Upgrading to
Fedora Core 2 is in the not-near-enough future.) For historical
reasons, our Java code uses the /usr/share/pgsql/jdbc7.1-1.1.jar that
comes in that RPM, even though there are three other jars with higher
version numbers.
On these systems, we have several programs that access the database, and
some of them are multi-threaded. There is one table in particular
that's giving me trouble. It has a bunch of configuration parameters
stored as keys and values, with both the keys and values being character
strings. The initial key/value pairs are loaded in at creation time,
but they are frequently updated using "UPDATE sys_info SET VALUE = ?
WHERE KEY = ?".
cosdb=> \d sys_info
Table "sys_info"
Column | Type | Modifiers
--------+------------------------+-----------
key | character varying(50) | not null
value | character varying(128) |
Primary key: sys_info_pkey
Occassionally, one of the programs will suddenly act like one of the
keys disappeared. Either I'll get a null pointer exception in
org.postgresql.jdbc2.ResultSet.next(ResultSet.java:116) or I'll get a
NumberFormatException when parsing one of the values that should always
be an integer. As far as I can tell, at the time this happens there are
no other threads in that program updating the database in any way,
although there is a distinct possibility that another program is doing so.
So my questions to you:
1. Are there any known concurrency issues where one program is doing an
UPDATE on a table when another program is doing a query on it?
2. If there are known problems, would putting the UPDATE in a
transaction help?
3. What is the diffence between the various jdbc*.jar files in the RPM,
and should I switch to one of the other ones?
Thank you.
Paul, The problem is likely a bug in the driver, the server doesn't have any issues with this query. The difference between the jars is which jdk is used. The one you are using is for a java version 1.1, which version of java are you using? Dave Paul Tomblin wrote: > I'm using Postgres from a RedHat RPM postgresql-jdbc-7.2.4-5.73 on an > installed base of a few hundred RedHat 7.3 machines. (Upgrading to > Fedora Core 2 is in the not-near-enough future.) For historical > reasons, our Java code uses the /usr/share/pgsql/jdbc7.1-1.1.jar that > comes in that RPM, even though there are three other jars with higher > version numbers. > > On these systems, we have several programs that access the database, > and some of them are multi-threaded. There is one table in particular > that's giving me trouble. It has a bunch of configuration parameters > stored as keys and values, with both the keys and values being > character strings. The initial key/value pairs are loaded in at > creation time, but they are frequently updated using "UPDATE sys_info > SET VALUE = ? WHERE KEY = ?". > > cosdb=> \d sys_info > Table "sys_info" > Column | Type | Modifiers > --------+------------------------+----------- > key | character varying(50) | not null > value | character varying(128) | > Primary key: sys_info_pkey > > Occassionally, one of the programs will suddenly act like one of the > keys disappeared. Either I'll get a null pointer exception in > org.postgresql.jdbc2.ResultSet.next(ResultSet.java:116) or I'll get a > NumberFormatException when parsing one of the values that should > always be an integer. As far as I can tell, at the time this happens > there are no other threads in that program updating the database in > any way, although there is a distinct possibility that another program > is doing so. > > So my questions to you: > > 1. Are there any known concurrency issues where one program is doing > an UPDATE on a table when another program is doing a query on it? > > 2. If there are known problems, would putting the UPDATE in a > transaction help? > > 3. What is the diffence between the various jdbc*.jar files in the > RPM, and should I switch to one of the other ones? > > Thank you. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
Dave Cramer wrote: > Paul, > > The problem is likely a bug in the driver, the server doesn't have any > issues with this query. > > The difference between the jars is which jdk is used. The one you are > using is for a java version 1.1, which version of java are you using? > We're using Java 1.4.2. Is there any reason for or against using the /usr/share/pgsql/jdbc7.2dev-1.2.jar instead?
No, no reason not to use it, however it may have the same bug. I'm fairly certain there were some threading issues in the driver back then. Dave Paul Tomblin wrote: > Dave Cramer wrote: > >> Paul, >> >> The problem is likely a bug in the driver, the server doesn't have >> any issues with this query. >> >> The difference between the jars is which jdk is used. The one you are >> using is for a java version 1.1, which version of java are you using? >> > > We're using Java 1.4.2. Is there any reason for or against using the > /usr/share/pgsql/jdbc7.2dev-1.2.jar instead? > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561