Re: problem: query result in jdbc is <> result in psql

The equivalent java code:

import java.sql.*;

class PgTest{

     public static void main(String[] args)throws Exception{
         Class.forName("org.postgresql.Driver");
         String url="jdbc:postgresql://localhost/template1";
         String usr = "jks";
         Statement st = DriverManager.getConnection(url,
usr,"").createStatement();
         String 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;";
         ResultSet rs = st.executeQuery(sql);
int cols = rs.getMetaData().getColumnCount();

         for(int colnum = 1; colnum <= cols ; colnum++)
             System.out.print(rs.getMetaData().getColumnLabel(colnum) +
"\t");

         System.out.println();
         System.out.println("-------------------------");

         while(rs.next()){
             for(int colnum = 1; colnum <= cols ; colnum++)
                 System.out.print( rs.getObject(colnum) + "\t");
             System.out.println();
         }

     }
}

produces:

[jks@jks-desktop /tmp]{f15}$ javac PgTest.java && java -cp
~/.ivy2/cache/postgresql/postgresql/jars/postgresql-9.1-901.jdbc4.jar:.
PgTest
relname locktype        database        relation        page    tuple
virtualxid      transactionid   classid objid   objsubid
virtualtransaction      pid     mode    granted
-------------------------
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    3/2656  22125   AccessShareLock true
pg_class_oid_index      relation        1       2662    null    null
null    null    null    null    null    3/2656  22125   AccessShareLock true
pg_class_relname_nsp_index      relation        1       2663    null
null    null    null    null    null    null    3/2656  22125
AccessShareLock true
pg_locks        relation        1       11000   null    null    null
null    null    null    null    3/2656  22125   AccessShareLock true
null    virtualxid      null    null    null    null    3/2656  null
null    null    null    3/2656  22125   ExclusiveLock   true


On 01/08/2012 07:12 PM, Joseph Shraibman wrote:
> 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
>