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.