[BUGS] 7.1beta1 JDBC Nested cursor problem
I think that I've found a little bug in the 7.1beta1 JDBC drivers. Attached is a
simple test case which produces the problem on my setup (LinuxPPC 2000, on Apple
PowerMac G3-400Mhz, 512MB). It would seem that the drivers and/or the DBMS has a
problem with nested queries, even simple ones.
Here's the stacktrace:
Bad Long
at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:284)
at Test.go(Test.java:35)
at Test.main(Test.java:15)
I'm using the 7.0.2 JDBC drivers against the 7.1beta1 backend and
everything seems to be working fine with those, just the new ones (7.1beta1) have this
problem.
Stu.
-- create table/data script
create table mytest (mykey int8, foo varchar(20));
insert into mytest(mykey,foo) values (0,'stuff');
insert into mytest(mykey,foo) values (1,'stuff');
insert into mytest(mykey,foo) values (2,'stuff');
insert into mytest(mykey,foo) values (3,'stuff');
insert into mytest(mykey,foo) values (4,'stuff');
insert into mytest(mykey,foo) values (5,'stuff');
insert into mytest(mykey,foo) values (6,'stuff');
insert into mytest(mykey,foo) values (7,'stuff');
insert into mytest(mykey,foo) values (8,'stuff');
insert into mytest(mykey,foo) values (9,'stuff');
-- Test Program
import java.sql.*;
import java.io.*;
public class Test
{
public static void main(String [] args)
{
if(args.length!=3) {
System.err.println("Usage: ");
System.exit(1);
}
Test app=new Test();
try {
app.go(args[0],args[1],args[2]);
}
catch(Exception e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
}
private void go(String url,String uid,String pwd) throws Exception
{
Class.forName("org.postgresql.Driver");
Connection connection=DriverManager.getConnection(url,uid,pwd);
connection.setAutoCommit(false);
PreparedStatement s=connection.prepareStatement("select mykey from mytest");
ResultSet rs=s.executeQuery();
while(rs.next()){
PreparedStatement s2=connection.prepareStatement("select * from mytest
where mykey=?");
s2.setLong(1,rs.getLong(1));
ResultSet rs2=s2.executeQuery();
while(rs2.next()) {
System.out.println(rs2.getLong(1) + " " + rs2.getString(2));
}
rs2.close();
s.close();
}
rs.close();
s.close();
connection.commit();
connection.close();
}
}
--
Stu Coates
Chelmsford, England U.K.
ICQ:146628
http://www.stucoates.com/
__
FREE Personalized Email at Mail.com
Sign up at http://www.mail.com/?sr=signup
[BUGS] 7.1beta1 JDBC bug?
I think that I've found a little bug in the 7.1beta1 JDBC drivers.
Attached is a simple test case which produces the problem on my setup
(LinuxPPC 2000, on Apple PowerMac G3-400Mhz, 512MB). It would seem that
the drivers and/or the DBMS has a problem with nested queries, even
simple ones.
Here's the stacktrace:
Bad Long
at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:284)
at Test.go(Test.java:35)
at Test.main(Test.java:15)
I'm using the 7.0.2 JDBC drivers against the 7.1beta1 backend and
everything seems to be working fine.
Stu.
--
Stu Coates
Chelmsford, England U.K.
http://www.StuCoates.com/
The day Microsoft makes something that doesn't suck is probably the day
they start making vacuum cleaners.
import java.sql.*;
import java.io.*;
public class Test
{
public static void main(String [] args)
{
if(args.length!=3) {
System.err.println("Usage: ");
System.exit(1);
}
Test app=new Test();
try {
app.go(args[0],args[1],args[2]);
}
catch(Exception e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
}
private void go(String url,String uid,String pwd) throws Exception
{
Class.forName("org.postgresql.Driver");
Connection connection=DriverManager.getConnection(url,uid,pwd);
connection.setAutoCommit(false);
PreparedStatement s=connection.prepareStatement("select mykey from mytest");
ResultSet rs=s.executeQuery();
while(rs.next()){
PreparedStatement s2=connection.prepareStatement("select * from mytest
where mykey=?");
s2.setLong(1,rs.getLong(1));
ResultSet rs2=s2.executeQuery();
while(rs2.next()) {
System.out.println(rs2.getLong(1) + " " + rs2.getString(2));
}
rs2.close();
s.close();
}
rs.close();
s.close();
connection.commit();
connection.close();
}
}
drop table mytest;
create table mytest (mykey int8, foo varchar(20));
insert into mytest(mykey,foo) values (0,'stuff');
insert into mytest(mykey,foo) values (1,'stuff');
insert into mytest(mykey,foo) values (2,'stuff');
insert into mytest(mykey,foo) values (3,'stuff');
insert into mytest(mykey,foo) values (4,'stuff');
insert into mytest(mykey,foo) values (5,'stuff');
insert into mytest(mykey,foo) values (6,'stuff');
insert into mytest(mykey,foo) values (7,'stuff');
insert into mytest(mykey,foo) values (8,'stuff');
insert into mytest(mykey,foo) values (9,'stuff');
[BUGS] JDBC buggy in 7.1beta3
I reported a JDBC bug in the 7.1 beta1 release back in December and notice that the same bug is still present in the current beta 3 sources. The problem relates to nested cursors failing and is quite simple to reproduce. A simple test case can be found at: http://www.twospuds.com/pg7-1b3-jdbc-bug.tar.gz The 7.0.2 JDBC drivers work fine against the 7.1b3 backend, with a little tweaking for "long" rows. Peter (Mount) did acknowledge the bug but I have not heard anything on the list since then. Is anyone looking at it? Come to think of it, I haven't seen anything on the lists [HACKERS mainly] from Peter for a while. Stu. -- Stu Coates Chelmsford, England U.K. ICQ:146628 http://www.stucoates.com/ __ FREE Personalized Email at Mail.com Sign up at http://www.mail.com/?sr=signup
Re: [BUGS] pg_dump failing on LinuxPPC
Tom Lane wrote: > > Looks like you're trying to run a 6.5-or-older pg_dump against a 7.1 backend. > Check your PATH. Boy am I red faced... I was forgetting that LinuxPPC2000 comes with PostgreSQL installed... oh well, it was late! ;-( Anyway, I've sorted my obsolete version problem, and have discovered another. Attached is a short shell script that causes pg_dump to core dump whilst trying to dump a single, quite simple, table. Stu. -- Stu Coates Chelmsford, England U.K. http://www.StuCoates.com/ The day Microsoft makes something that doesn't suck is probably the day they start making vacuum cleaners. dumpbug.sh
Lock Detection (was: [BUGS] pg_dump failing on LinuxPPC)
Tom Lane wrote: > > Stu Coates <[EMAIL PROTECTED]> writes: > > Anyway, I've sorted my obsolete version problem, and have discovered > > another. Attached is a short shell script that causes pg_dump to core > > dump whilst trying to dump a single, quite simple, table. > > Fix committed --- but it just missed the boat for 7.1beta5 :-(. Please > try current CVS or tomorrow's snapshot, instead. Thanks for that Tom, I'll give it a shot later. On a slightly different matter: I come from an Oracle background where I can lock an item of data by performing a "SELECT FOR UPDATE" on the row. This is also implemented in PostgreSQL. A quite useful feature Oracle does have is the ability to add a "NOWAIT" clause to the end of the command which will cause an exception if the item of data already has a lock taken out on it. AFAIK, this is not implemented in PostgreSQL. I did see a note that lock timeouts are not implemented, but if "NOWAIT" is added the application developer could implement/fudge timeouts him/herself. Would this be relatively easy to add? Stu. -- Stu Coates Chelmsford, England U.K. http://www.StuCoates.com/ The day Microsoft makes something that doesn't suck is probably the day they start making vacuum cleaners.
Re: Lock Detection (was: [BUGS] pg_dump failing on LinuxPPC)
Tom Lane wrote: > > Stu Coates <[EMAIL PROTECTED]> writes: > > I come from an Oracle background where I can lock an item of data by > > performing a "SELECT FOR UPDATE" on the row. This is also implemented > > in PostgreSQL. A quite useful feature Oracle does have is the ability > > to add a "NOWAIT" clause to the end of the command which will cause an > > exception if the item of data already has a lock taken out on it. > > AFAIK, this is not implemented in PostgreSQL. I did see a note that > > lock timeouts are not implemented, but if "NOWAIT" is added the > > application developer could implement/fudge timeouts him/herself. Would > > this be relatively easy to add? > > It'd be possible, but not particularly easy; anything that involves > changing parse/plan trees is tedious. Moreover, I doubt that it'd be > especially useful given the lack of nested transactions in Postgres. > You'll have to make a lot better case than the above if you want to > get anyone excited about the idea. I realize that PostgreSQL does not support nested transactions. For the project I'm currently working on I'd like my unit of work to be: begin transaction lock all of the entities (rows) that I'll require in this transaction if any lock fails then abort transaction (rollback) perform operations end transaction (commit) ...and then be able to perform this unit of work for a whole list of work units as fast as I can without waiting for locks to be released by other processes. If any of the units of work cannot be completed due to existing locks then they will be retried at a later time. At the moment I'd have to maintain my own lock lists and perform atomic operations on that to get the behavior that I require. Can you suggest another way using the PostgreSQL lock manager? Regards, Stu. -- Stu Coates Chelmsford, England U.K. http://www.StuCoates.com/ The day Microsoft makes something that doesn't suck is probably the day they start making vacuum cleaners.
