Re: problem: query result in jdbc is <> result in psql - Mailing list pgsql-jdbc
| From | Dave Cramer |
|---|---|
| Subject | Re: problem: query result in jdbc is <> result in psql |
| Date | |
| Msg-id | CADK3HHKe-yGSsfKAVEyL5rm+5x=iecVsuDWU5q7to7sCZUG8Lg@mail.gmail.com Whole thread Raw |
| In response to | Re: problem: query result in jdbc is <> result in psql (Joseph Shraibman <jks@selectacast.net>) |
| Responses |
Re: problem: query result in jdbc is <> result in psql
|
| List | pgsql-jdbc |
Joseph, I just tried your code using the latest driver and pg 8.4 and 9.1 it works fine. What version of the server are you using ? relname locktype database relation page tuple virtualxid transactionid classid objid objsubid virtualtransaction pid mode granted ------------------------- a relation 16385 16392 null null null null null null null 2/15 2392 RowShareLock true null virtualxid null null null null 2/15 null null null null 2/15 2392 ExclusiveLock true pg_class relation 16385 1259 null null null null null null null 3/11 2468 AccessShareLock true pg_class_oid_index relation 16385 2662 null null null null null null null 3/11 2468 AccessShareLock true pg_class_relname_nsp_index relation 16385 2663 null null null null null null null 3/11 2468 AccessShareLock true pg_locks relation 16385 11000 null null null null null null null 3/11 2468 AccessShareLock true null virtualxid null null null null 3/11 null null null null 3/11 2468 ExclusiveLock true Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Sun, Jan 8, 2012 at 7:43 PM, Joseph Shraibman <jks@selectacast.net> wrote: > 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 >> > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
pgsql-jdbc by date: