Обсуждение: problem: query result in jdbc is <> result in psql
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
I've verified this problem on a different machine with postgresql-8.4-702.jdbc4.jar and postgresql-9.1-901.jdbc4.jar
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
>
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
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 java version is 1.6.0_29 On 01/09/2012 07:08 AM, Dave Cramer wrote: > 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 ? >
Joseph, Same versions and I am unable to replicate this here. Can you send me your schema, and how you are locking it ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Jan 9, 2012 at 12:23 PM, Joseph Shraibman <jks@selectacast.net> wrote: > 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 > > > java version is 1.6.0_29 > > > On 01/09/2012 07:08 AM, Dave Cramer wrote: >> >> 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 ? >> >
I created table a with generate_series.
jks=# \d a
Table "public.a"
Column | Type | Modifiers
-----------------+---------+-----------
generate_series | integer |
I lock it by doing:
jks=# begin; lock table a;
BEGIN
LOCK TABLE
On 01/09/2012 12:49 PM, Dave Cramer wrote:
> Joseph,
>
> Same versions and I am unable to replicate this here. Can you send me
> your schema, and how you are locking it ?
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
> On Mon, Jan 9, 2012 at 12:23 PM, Joseph Shraibman<jks@selectacast.net> wrote:
>> 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
>>
>>
>> java version is 1.6.0_29
>>
>>
>> On 01/09/2012 07:08 AM, Dave Cramer wrote:
>>>
>>> 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 ?
>>>
>>
OK, tried with 9.1.2 still can't replicate this problem. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Jan 9, 2012 at 1:01 PM, Joseph Shraibman <jks@selectacast.net> wrote: > I created table a with generate_series. > > jks=# \d a > Table "public.a" > Column | Type | Modifiers > -----------------+---------+----------- > generate_series | integer | > > > I lock it by doing: > jks=# begin; lock table a; > BEGIN > LOCK TABLE > > > > On 01/09/2012 12:49 PM, Dave Cramer wrote: >> >> Joseph, >> >> Same versions and I am unable to replicate this here. Can you send me >> your schema, and how you are locking it ? >> >> >> Dave Cramer >> >> dave.cramer(at)credativ(dot)ca >> http://www.credativ.ca >> >> >> >> On Mon, Jan 9, 2012 at 12:23 PM, Joseph Shraibman<jks@selectacast.net> >> wrote: >>> >>> 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 >>> >>> >>> java version is 1.6.0_29 >>> >>> >>> On 01/09/2012 07:08 AM, Dave Cramer wrote: >>>> >>>> >>>> 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 ? >>>> >>> >
On 01/09/2012 01:44 PM, Dave Cramer wrote: > OK, tried with 9.1.2 still can't replicate this problem. > Found the problem. When connecting with jdbc I was connecting to template1, when connecting with psql I was connecting to a different database. When connecting to template1 I can't view the relname from the other database, even though its the same user.
On 01/09/2012 05:52 PM, Joseph Shraibman wrote: > On 01/09/2012 01:44 PM, Dave Cramer wrote: >> OK, tried with 9.1.2 still can't replicate this problem. >> > Found the problem. When connecting with jdbc I was connecting to > template1, when connecting with psql I was connecting to a different > database. When connecting to template1 I can't view the relname from the > other database, even though its the same user. > The problem being that when I'm connected to a different database selecting on pg_catalog in my database isn't going to give me the correct result.
Try connecting to the postgres database as the postgres user. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Jan 9, 2012 at 5:55 PM, Joseph Shraibman <jks@selectacast.net> wrote: > On 01/09/2012 05:52 PM, Joseph Shraibman wrote: >> >> On 01/09/2012 01:44 PM, Dave Cramer wrote: >>> >>> OK, tried with 9.1.2 still can't replicate this problem. >>> >> Found the problem. When connecting with jdbc I was connecting to >> template1, when connecting with psql I was connecting to a different >> database. When connecting to template1 I can't view the relname from the >> other database, even though its the same user. >> > The problem being that when I'm connected to a different database selecting > on pg_catalog in my database isn't going to give me the correct result. > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
That doesn't help. It isn't a permission problem, the problem is my sql has in it: (select relname from pg_catalog.pg_class where pg_catalog.pg_class.oid = relation) and pg_class is local to the db I'm connected to, so I can't get names of relations in other dbs. On 01/09/2012 07:41 PM, Dave Cramer wrote: > Try connecting to the postgres database as the postgres user. >