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
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 |