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(at)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
>