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

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: problem: query result in jdbc is <> result in psql
Date: 2012-01-09 00:12:19
Message-ID: jedbd4$bo7$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2012-01-09 00:22:49 Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues
Previous Message Stefan Keller 2012-01-08 23:40:08 Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues