problem: query result in jdbc is <> result in psql - Mailing list pgsql-jdbc

I'm working on some code that reads info from the pg lock table.


jks=# SELECT (select relname from pg_catalog.pg_class where
pg_catalog.pg_class.oid = relation) as relname, * FROM pg_locks ORDER BY
pid, relation;
           relname           |  locktype  | database | relation | page |
tuple | virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction |  pid  |        mode         | granted

----------------------------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------
  a                          | relation   |    16384 |    16406 |
|       |            |               |         |       |          |
2/19               |  7613 | AccessExclusiveLock | t
                             | virtualxid |          |          |
|       | 2/19       |               |         |       |          |
2/19               |  7613 | ExclusiveLock       | t
  a                          | relation   |    16384 |    16406 |
|       |            |               |         |       |          |
4/43               |  7796 | AccessExclusiveLock | f
                             | virtualxid |          |          |
|       | 4/43       |               |         |       |          |
4/43               |  7796 | ExclusiveLock       | t
  pg_class                   | relation   |    16384 |     1259 |
|       |            |               |         |       |          |
16/13              | 20847 | AccessShareLock     | t
  pg_class_oid_index         | relation   |    16384 |     2662 |
|       |            |               |         |       |          |
16/13              | 20847 | AccessShareLock     | t
  pg_class_relname_nsp_index | relation   |    16384 |     2663 |
|       |            |               |         |       |          |
16/13              | 20847 | AccessShareLock     | t
  pg_locks                   | relation   |    16384 |    11000 |
|       |            |               |         |       |          |
16/13              | 20847 | AccessShareLock     | t
                             | virtualxid |          |          |
|       | 16/13      |               |         |       |          |
16/13              | 20847 | ExclusiveLock       | t
(9 rows)

In this example I tried to lock the 'a' table in two different psql windows.
The works fine in psql. However when I run the query in jdbc I don't see
the 'a's.

I ran this script with

  scala -cp
~/.ivy2/cache/postgresql/postgresql/jars/postgresql-9.1-901.jdbc4.jar <
/tmp/pgjdbc.scala


import java.sql._
Class.forName("org.postgresql.Driver")
val url="jdbc:postgresql://localhost/template1"
val usr = "jks"
val conn = DriverManager.getConnection(url, usr,"")
val st = conn.createStatement
val sql = "SELECT (select relname from pg_catalog.pg_class where
pg_catalog.pg_class.oid = relation) as relname, * FROM pg_locks ORDER BY
pid, relation;"
val rs = st.executeQuery(sql)
val cols = rs.getMetaData().getColumnCount();

  for(colnum <- 1 to cols)
      print(rs.getMetaData().getColumnLabel(colnum) + "\t")
println("-------------------------")

while(rs.next){
    for(colnum <- 1 to cols)
       print( rs.getObject(colnum) + "\t")
    println
}

The output is:

null    relation        16384   16406   null    null    null    null
null    null    null    2/19    7613    AccessExclusiveLock     true
null    virtualxid      null    null    null    null    2/19    null
null    null    null    2/19    7613    ExclusiveLock   true
null    relation        16384   16406   null    null    null    null
null    null    null    4/43    7796    AccessExclusiveLock     false
null    virtualxid      null    null    null    null    4/43    null
null    null    null    4/43    7796    ExclusiveLock   true
pg_class        relation        1       1259    null    null    null
null    null    null    null    17/462  21265   AccessShareLock true
pg_class_oid_index      relation        1       2662    null    null
null    null    null    null    null    17/462  21265   AccessShareLock true
pg_class_relname_nsp_index      relation        1       2663    null
null    null    null    null    null    null    17/462  21265
AccessShareLock true
pg_locks        relation        1       11000   null    null    null
null    null    null    null    17/462  21265   AccessShareLock true
null    virtualxid      null    null    null    null    17/462  null
null    null    null    17/462  21265   ExclusiveLock   true

notice that there is only 'null' in the left column where 'a's should be.

Both psql and jdbc were connecting using the same user, 'jks'.  The pg
version is: PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by
gcc (GCC) 4.6.1 20110908 (Red Hat 4.6.1-9), 64-bit