[BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Hi, I think I found two bugs (and I hope I am not wasting everyone's time). One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. So running this code: -- public class NewClass { public static void main(String[] args) { try { Class.forName("org.postgresql.Driver"); java.sql.Connection conn = java.sql.DriverManager.getConnection( "jdbc:postgresql://localhost:5432/dev", "dev", "devmm" ); java.util.Enumeration drivers = java.sql.DriverManager.getDrivers(); while( drivers.hasMoreElements() ) { java.sql.Driver d = drivers.nextElement(); System.out.println( d.toString() + ": " + d.getMajorVersion() + "." + d.getMinorVersion() ); } java.sql.PreparedStatement ps_ver = conn.prepareStatement( "SELECT version()" ); java.sql.ResultSet rs = ps_ver.executeQuery(); while(rs.next()) System.out.println(rs.getString(1)); java.sql.PreparedStatement ps_create = conn.prepareStatement( "CREATE TABLE public.aab (" + "ia integer[], " + "sa smallint[], " + "ba boolean[] )" ); ps_create.executeUpdate(); java.sql.PreparedStatement ps_insert = conn.prepareStatement( "INSERT INTO public.aab (ia,sa,ba) VALUES (?,?,?)" ); ps_insert.setArray( 1, conn.createArrayOf( "int4", new Integer[] { 1, 2 } ) ); ps_insert.setArray( 2, conn.createArrayOf( "int2", new Short[] { 100, 200 } ) ); ps_insert.setArray( 3, conn.createArrayOf( "bool", new Boolean[] {false,true} ) ); ps_insert.executeUpdate(); java.sql.PreparedStatement ps_select = conn.prepareStatement( "SELECT ia,sa,ba FROM public.aab" ); rs = ps_select.executeQuery(); java.sql.Array jdbcArr; while(rs.next()) { System.out.println("Integer[]"); jdbcArr = rs.getArray("ia"); Integer[] javaIntArr = (Integer[]) jdbcArr.getArray(); System.out.println(javaIntArr[0] + " " + javaIntArr[1]); System.out.println(String.format( "%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.INTEGER, jdbcArr.getBaseType())); System.out.println("END Integer[]"); System.out.println("Boolean[]"); jdbcArr = rs.getArray("ba"); Boolean[] javaBooArr = (Boolean[]) jdbcArr.getArray(); System.out.println(javaBooArr[0] + " " + javaBooArr[1]); System.out.println(String.format( "%s: %d %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.BOOLEAN, jdbcArr.getBaseType(), java.sql.Types.BIT)); System.out.println("END Boolean[]"); System.out.println("Short[]"); jdbcArr = rs.getArray("sa"); Short[] javaShoArr = (Short[]) jdbcArr.getArray(); System.out.println(javaShoArr[0] + " " + javaShoArr[1]); System.out.println(String.format( "%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.SMALLINT, jdbcArr.getBaseType())); System.out.println("END Short[]"); } catch( Exception e ) { ; } } // main } // class -- Prints the following for me: -- sun.jdbc.odbc.jdbcodbcdri...@de6f34: 2.1 org.postgresql.dri...@47b480: 8.4 PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit Integer[] 1 2 int4: 4 4 END Integer[] Boolean[] false true bool: 16 -7 -7 END Boolean[] Short[] java.lang.ClassCastException: [Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short; at NewClass.main(NewClass.java:90) [Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short; -- Thanks for any help, -- Saneesh Apte 510-642-5478 http://www.calccit.org California Center for Innovative Transportation University of California, Berkeley, MC3830 210
[BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Hi I've started testing our applications against PostgreSQL 9.0 beta2 and found that this function now does not work anymore(rewritten to be as small and anonymous as possible) CREATE TYPE test_type AS( product text, amount numeric(30,4) ); CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$ BEGIN RETURN QUERY SELECT 'test'::text, 30.2::numeric; END; $$ LANGUAGE plpgsql STABLE; Output in postgresql 8.4: postgres=# SELECT version(); version PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit (1 row) postgres=# SELECT test_func(); test_func - (test,30.2) (1 row) postgres=# And in postgresql 9.0 beta2 version - PostgreSQL 9.0beta2 on x86_64-apple-darwin10.4.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5659), 64-bit (1 row) postgres=# \set VERBOSITY verbose postgres=# SELECT test_func(); ERROR: 42804: structure of query does not match function result type DETAIL: Returned type numeric does not match expected type numeric(30,4) in column 2. CONTEXT: PL/pgSQL function "test_func" line 2 at RETURN QUERY LOCATION: convert_tuples_by_position, tupconvert.c:112 postgres=# Currently unning Mac OS X 10.6 Marcel Asio Network & System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Re: [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Saneesh Apte wrote: > the base type of a boolean[] is java.sql.Types.BIT instead or > java.sql.Types.BOOLEAN. At the very least shouldn't these be > aliases for the same type? > > And secondly the returned type from a smallint[] is an Integer[] > instead of a Short[]. Should the objects in the array returned by getArray be typed according to the rules of an individual value returned by getObject? (I couldn't find anything explicit on that, but it seems reasonable.) If that *is* true, the controlling part of the spec is: http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html#table3 That maps SQL types TINYINT, SMALLINT, AND INTEGER to Java Integer. It also maps SQL BIT to Java Boolean. The SQL type of BOOLEAN was a latecomer to the SQL spec, and it appears that JDBC hasn't yet added it to the mappings. Do you have a reference to something which indicates that getArray should use a different mapping? Maybe someone will see it differently, but I don't think I see a bug here. Compliance with the spec is not a bug, even if the spec is a bit odd ;-) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Marcel Asio writes: > I've started testing our applications against PostgreSQL 9.0 beta2 and found > that this function now does not work anymore(rewritten to be as small and > anonymous as possible) > CREATE TYPE test_type AS( > product text, > amount numeric(30,4) > ); > CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$ > BEGIN > RETURN QUERY SELECT 'test'::text, 30.2::numeric; > END; > $$ LANGUAGE plpgsql STABLE; You need to actually coerce the 30.2 to numeric(30,4), not just numeric. The former behavior wasn't self-consistent. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Hi Tom, Yes I managed to figured that out, but when was this changed? It was working in 8.4 but not 9.0 and I could not find anything about this in the release notes. On Jun 29, 2010, at 17:32 , Tom Lane wrote: > Marcel Asio writes: >> I've started testing our applications against PostgreSQL 9.0 beta2 and found >> that this function now does not work anymore(rewritten to be as small and >> anonymous as possible) >> CREATE TYPE test_type AS( >>product text, >>amount numeric(30,4) >> ); >> CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$ >> BEGIN >>RETURN QUERY SELECT 'test'::text, 30.2::numeric; >> END; >> $$ LANGUAGE plpgsql STABLE; > > You need to actually coerce the 30.2 to numeric(30,4), not just numeric. > The former behavior wasn't self-consistent. > > regards, tom lane Regards Marcel Asio Network & System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Re: [BUGS] BUG #5520: PG unable to find java stored procs without input parameters
On Tue, Jun 22, 2010 at 8:30 AM, Peter Mengaziol wrote: > > The following bug has been logged online: > > Bug reference: 5520 > Logged by: Peter Mengaziol > Email address: pmengaz...@electrainfo.com > PostgreSQL version: 8.4.4 > Operating system: OS X 10.4.13 > Description: PG unable to find java stored procs without input > parameters > Details: > > Under PG 8.4.1 and pl/java 1.4.0 have been unable to get a jsproc to be > registered and found when there is not a single input parameter. The same > code with a dummy input parameter is found and executed sucessfully. Since there's been no response here you might want to try the JDBC mailing list: http://archives.postgresql.org/pgsql-jdbc/ You might also want to provide some more details, like a complete test case that you think should work but doesn't. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [JDBC] [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
On Mon, 28 Jun 2010, Saneesh Apte wrote: One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? These are aliases for the same type. I believe we accept either BOOLEAN or BIT as equivalent in all cases. We default to BIT for historical reasons because it was defined first in the JDBC2 spec while BOOLEAN came around in the JDBC3 version. And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. The JDBC spec says that the result of getObject on a Types.SMALLINT value should return Integer, so we have followed that for array types as well. The spec contains this historical note: The JDBC 1.0 specification defined the Java object mapping for the SMALLINT and TINYINT JDBC types to be Integer. The Java language did not include the Byte and Short data types when the JDBC 1.0 specification was finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to preserve backwards compatibility For more information see table B-3 in the JDBC4.0 spec. Kris Jurka -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Marcel Asio writes: > Yes I managed to figured that out, but when was this changed? > It was working in 8.4 but not 9.0 and I could not find anything about this in > the release notes. The 9.0 release notes are not really up to snuff yet :-(. The only thing in the notes about it is "Allow PL/pgSQL to handle row types with dropped columns" which is a rather inadequate description of the consequences of that patch. We're probably going to need to call it out as an incompatibility. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
I was suspecting that it was incompatibility that hadn't been documented correctly, just wanted to make sure that this was the case. It probably should go into the release notes too since it is bound to affect others Thankfully Marcel Asio Network & System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. On 29 June 2010 19:38, Tom Lane wrote: > Marcel Asio writes: > > Yes I managed to figured that out, but when was this changed? > > > It was working in 8.4 but not 9.0 and I could not find anything about > this in the release notes. > > The 9.0 release notes are not really up to snuff yet :-(. The only > thing in the notes about it is "Allow PL/pgSQL to handle row types with > dropped columns" which is a rather inadequate description of the > consequences of that patch. We're probably going to need to call it out > as an incompatibility. > >regards, tom lane >
Re: [JDBC] [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Hi, I think I found two bugs (and I hope I am not wasting everyone's time). One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. So running this code: The running of the supplied code also gives the same results with PostgreSQL 8.3.3 results: sun.jdbc.odbc.jdbcodbcdri...@fc9944: 2.1 org.postgresql.dri...@8b819f: 8.4 PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 Integer[] 1 2 int4: 4 4 END Integer[] Boolean[] false true bool: 16 -7 -7 END Boolean[] Short[] java.lang.ClassCastException: [Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short; The 8.4 JDBC Driver though does gives a consistent result as past versions via the tableMetaData.getColumnClassName() & tableMetaData.getColumnTypeName() for the base types, integer, smallint, and boolean. System.out.println(i + " " + colNameString + " " + columnClass + " " + columnType + " " + columnSize); 3 int_type java.lang.Integer int4 11 2 smallint_type java.lang.Integer int2 6 21 boolean_type java.lang.Boolean bool 1 1 ia java.sql.Array _int4 11 2 sa java.sql.Array _int2 6 3 ba java.sql.Array _bool 1 Attached slight modification to NewClass to correctly compile and drop table if run more than once. danap. public class NewClass2 { public static void main(String[] args) { try { Class.forName("org.postgresql.Driver"); java.sql.Connection conn = java.sql.DriverManager.getConnection( "jdbc:postgresql://192.168.157.32:5432/dev", "dev", "devmm"); java.util.Enumeration drivers = java.sql.DriverManager.getDrivers(); while (drivers.hasMoreElements()) { java.sql.Driver d = drivers.nextElement(); System.out.println(d.toString() + ": " + d.getMajorVersion() + "." + d.getMinorVersion()); } java.sql.PreparedStatement ps_ver = conn.prepareStatement("SELECT version()"); java.sql.ResultSet rs = ps_ver.executeQuery(); while (rs.next()) System.out.println(rs.getString(1)); java.sql.Statement dbStatement = conn.createStatement(); dbStatement.executeUpdate("DROP TABLE IF EXISTS public.aab"); java.sql.PreparedStatement ps_create = conn.prepareStatement("CREATE TABLE public.aab (" + "ia integer[], " + "sa smallint[], " + "ba boolean[] )"); ps_create.executeUpdate(); java.sql.PreparedStatement ps_insert = conn .prepareStatement("INSERT INTO public.aab (ia,sa,ba) VALUES (?,?,?)"); ps_insert.setArray(1, conn.createArrayOf("int4", new Integer[] {1, 2})); ps_insert.setArray(2, conn.createArrayOf("int2", new Short[] {100, 200})); ps_insert.setArray(3, conn.createArrayOf("bool", new Boolean[] {false, true})); ps_insert.executeUpdate(); java.sql.PreparedStatement ps_select = conn.prepareStatement("SELECT ia,sa,ba FROM public.aab"); rs = ps_select.executeQuery(); java.sql.Array jdbcArr; while (rs.next()) { System.out.println("Integer[]"); jdbcArr = rs.getArray("ia"); Integer[] javaIntArr = (Integer[]) jdbcArr.getArray(); System.out.println(javaIntArr[0] + " " + javaIntArr[1]); System.out.println(String.format("%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.INTEGER, jdbcArr.getBaseType())); System.out.println("END Integer[]"); System.out.println("Boolean[]"); jdbcArr = rs.getArray("ba"); Boolean[] javaBooArr = (Boolean[]) jdbcArr.getArray(); System.out.println(javaBooArr[0] + " " + javaBooArr[1]); System.out.println(String.format("%s: %d %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.BOOLEAN, jdbcArr.getBaseType(), java.sql.Types.BIT)); System.out.println("END Boolean[]"); System.out.println("Short[]"); jdbcArr = rs.getArray("sa"); Short[] javaShoArr = (Short[]) jdbcArr.getArray(); System.out.println(javaShoArr[0] + " " + javaShoArr[1]); System.out.println(String.format("%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.SMALLINT, jdbcArr.getBaseType())); System.out.println("END Short[]"); } rs.close(); dbStatement.close(); conn.close(); } catch (Exception e) { System.out.println(e.toString()); } } // main } // class -- Sent via pgsql-bugs mailing list (p
[BUGS] BUG #5530: Default cost of crypt causes poor decisions
The following bug has been logged online: Bug reference: 5530 Logged by: Baegle Email address: a...@wifiny.org PostgreSQL version: 8.4.4 Operating system: Linux - Ubuntu - Lucid Lynx - 2.6.31-19-generic Description:Default cost of crypt causes poor decisions Details: The default cost of crypt() is 1, but crypt is intentionally a high cost function. This mis-match between assigned cost and actual cost causes the query planner to use crypt too soon and slows down execution of queries significantly. The cost of the function should be more congruent with it's intended cost. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Marcel Asio writes: > I was suspecting that it was incompatibility that hadn't been documented > correctly, just wanted to make sure that this was the case. > It probably should go into the release notes too since it is bound to affect > others Yeah, done. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5530: Default cost of crypt causes poor decisions
"Baegle" writes: > The default cost of crypt() is 1, but crypt is intentionally a high cost > function. This mis-match between assigned cost and actual cost causes the > query planner to use crypt too soon and slows down execution of queries > significantly. The cost of the function should be more congruent with it's > intended cost. Perhaps, but the same could be said of most of the contrib/pgcrypto functions, no? Which ones do you think should have a nondefault cost, and what should we set it to? Also, it'd be easier to persuade people this is worth worrying about if you showed a real-world case where it's a problem. crypt() doesn't seem like a function that would be very likely to be used in contexts where this'd matter. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] local_preload_libraries filenames converted to lowercase
Tom Lane wrote: > Josh Berkus writes: > > Yes, I'd say that documentation is the answer, given. Hmmm are > > double-quotes respected in postgresql.conf, though? Need testing. > > Not sure. Something like > > local_preload_libraries = '"Foo", "Bar"' > > will definitely work. Don't know if you can dispense with the outer > single quotes. I have documented the requirement for double quotes; attached patch applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: doc/src/sgml/config.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.285 diff -c -c -r1.285 config.sgml *** doc/src/sgml/config.sgml 28 Jun 2010 22:46:11 - 1.285 --- doc/src/sgml/config.sgml 29 Jun 2010 22:17:58 - *** *** 1015,1020 --- 1015,1021 mylib.so (or on some platforms, mylib.sl) to be preloaded from the installation's standard library directory. + All library names are converted to lowercase unless double-quoted. If more than one library is to be loaded, separate their names with commas. This parameter can only be set at server start. *** *** 4838,4844 This variable specifies one or more shared libraries that are to be preloaded at connection start. If more than one library ! is to be loaded, separate their names with commas. This parameter cannot be changed after the start of a particular session. --- 4839,4846 This variable specifies one or more shared libraries that are to be preloaded at connection start. If more than one library ! is to be loaded, separate their names with commas. All library ! names are converted to lowercase unless double-quoted. This parameter cannot be changed after the start of a particular session. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [JDBC] [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Saneesh Apte wrote: I think I found two bugs (and I hope I am not wasting everyone's time). One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? Types.BOOLEAN does not exist before JDBC3. Client code can use either, but we have to pick one or the other when returning a value, so we return BIT. And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. smallint is a Types.SMALLINT which is mapped to java.lang.Integer by the JDBC spec. See appendix B of the spec: Note – The JDBC 1.0 specification defined the Java object mapping for the SMALLINT and TINYINT JDBC types to be Integer. The Java language did not include the Byte and Short data types when the JDBC 1.0 specification was finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to preserve backwards compatibility. -O -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 "ERROR: structure of query does not match function result type"
Awesome Thanks for the help Marcel Asio Network & System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. On 29 June 2010 23:21, Tom Lane wrote: > Marcel Asio writes: > > I was suspecting that it was incompatibility that hadn't been documented > > correctly, just wanted to make sure that this was the case. > > > It probably should go into the release notes too since it is bound to > affect > > others > > Yeah, done. > >regards, tom lane >