Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception
On Fri, 11 Jan 2013, Stefan Reiser wrote: What about returning Statement.SUCCESS_NO_INFO as it says in http://docs.oracle.com/javase/6/docs/api/java/sql/BatchUpdateException.html#getUpdateCounts%28%29 and http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29 It seems better to report no number at all rather than a number (INT_MAX) that is known to be wrong. What about Statement.executeUpdate? It has provision for returing a batch execution response code. 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: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception
On Fri, 11 Jan 2013, Dave Cramer wrote: Ok, I've pushed this fix into master You've made any failure to parse the affected row count return SUCCESS_NO_INFO. Shouldn't you change the integer parsing to a long parsing and only modify the response if the value is INT_MAX while still throwing an exception if we get something that is truly undecipherable? 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] JDBC Driver can't read UUID from database
On Mon, 11 Jun 2012, Dean Schulze wrote: Out of curiosity why didn't the driver map the Postgresql UUID to a Java UUID? The example I gave of calling getString must return a String and not another type. With a recent JDBC Driver and Java version, calling getObject will return a UUID object. 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] JDBC Driver can't read UUID from database
On Tue, 5 Jun 2012, Dean Schulze wrote: I'm using Hibernate (3.6 and 4.0) with Postgresql 9.1. Our tables have UUIDs in them and your driver is trying to convert UUIDs to longs: This is a hibernate mapping problem, not a JDBC Driver problem. If you have a plain ResultSet and call getString() on a UUID field, it will certainly work. Mapping the UUID to BLOB or CLOB is incorrect. I'm not sure why your attempt to map it to a String didn't work, but you are not convincing Hibernate to use a plain getString call. 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] BUG #6445: PreparedStatement.setObject(1,java.util.String)
On Wed, 8 Feb 2012, rajesh...@tcs.com wrote: The following bug has been logged on the website: Bug reference: 6445 PostgreSQL version: 8.4.3 I have a table in PostgreSQL database CREATE TABLE newtest ( id numeric(6), name character varying(120), email character varying(120) ) I am using PreparedStatement so that i can insert data into newtest table INSERT INTO NewTest (ID,NAME,EMAIL ) values (?,?,? ) Actual query fired -INSERT INTO NewTest (ID,NAME,EMAIL ) values ('1','Rajesh ','rajesh...@abs.com' ) Please note I am reading data from an excel sheet and datacell[eachCell].getContents() is of type String..prepareStatement setObject is complaining that I am sending String datatype which cannot be casted to bigint or numeric,but that is the job of setObject right to decide the target datatype and cast accordingly..please reply any solution..Parameter to setObject(can be anything) so I cannot cast the data to appropriate format before sending the data It is not the job of setObject to determine the correct conversion to the column type. The setObject javadoc says, The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database. So it is solely looking at the Java type of the object passed to it and converting that to a SQL type. So you pass it a String and it converts it to a varchar which is appropriate. If you want setObject to do a conversion to a different type, that is the reason for the additional setObject variant which takes a target sql type to convert to, but that doesn't help your situation where you don't know what the target type is. The Postgresql JDBC driver offers three possible workarounds. 1) Use getParameterMetaData to try and determine the target types. 2) Use setObject(int, Object, Types.OTHER) to indicate that you don't know that the Java type you are passing is correct. 3) Use the stringtype=unspecified URL parameter to indicate that all String bindings may not really be strings. 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] BUG #6293: JDBC driver performance
On Tue, 15 Nov 2011, Teun Hoogendoorn wrote: The following bug has been logged online: Bug reference: 6293 PostgreSQL version: 9.1 Description:JDBC driver performance Details: Using the postgresql-9.1-901.jdbc3.jar driver instead of postgresql-9.0-801.jdbc3.jar drops performance dramatically. I think it has something to do with using ResultSetMetaData in Java. The postgres log shows me hundreds of identical query's when retrieving the ResultSetMetaData for a single query. I'm not using an ORM framework, just simple JDBC calls. The 9.1 JDBC driver was changed to try and fetch all metadata for the entire resultset in one query instead of potentially issuing multiple queries for each column. So this change was supposed to improve things. Looking at the code, the caching pattern has changed slightly, so now it's important to hold onto the same ResultSetMetaData instance. That is you need to do: ResultSet rs = ... ResultSetMetaData rsmd = rs.getMetaData(); for (int i=1; irsmd.getColumnCount(); i++) { // good System.out.println(rsmd.getAutoIncrement()); // bad System.out.println(rs.getMetaData().getAutoIncrement()); } The driver should probably be changed to hand back the same ResultSetMetaData instance each time instead of a new one for each MetaData call. Does this explain your problem? If not, can you provide more details on how you access and use ResultSetMetaData? 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] BUG #6292: java.sql.PreparedStatement.setNull() throws PSQLException
On Mon, 14 Nov 2011, David Pinheiro wrote: Bug reference: 6292 PostgreSQL version: 8.4 Description:java.sql.PreparedStatement.setNull() throws PSQLException Details: I'm trying to make: java.sql.PreparedStatement.setNull(1,java.sql.Types.NULL); org.postgresql.util.PSQLException: ERROR: column number_column is of type integer but expression is of type character varying Hint: You will need to rewrite or cast the expression. My code is something like: if (String.valueOf(input_field) == null){ statement.setNull(1, java.sql.Types.NULL); } else { statement.setObject(1, valor); } statement.executeUpdate(); I don't think your problem is with setNull, I think your problem is actually with the setObject branch of your if statement. If the valor variable is a String, the JDBC driver is assigning it a string type. You may want to do setObject(1, valor, Types.INTEGER) or convert it prior to doing the setObject call to an appropriate numeric type. 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] BUG #5961: JDBC Driver acceptURL does not check 'jdbc:postgresql:'
On 4/1/2011 1:34 AM, DI Martin Handsteiner wrote: Here a working test case: Properties properties = new Properties(); properties.setProperty(user, sa); properties.setProperty(password, ); properties.put(shutdown, Boolean.TRUE); Connection hsqlConnection = DriverManager.getConnection(jdbc:hsqldb:hsql://localhost/testdb, properties); The method acceptsUrl should not only check, if it finds some suitable properties, It should also check, if the url is ment for postgresql. In my opinion it should look like: It turns out that DriverManager.getConnection does not call acceptsUrl at all. It just tries to connect with each registered driver in turn. Apparently acceptsUrl is only used for DriverManager.getDriver. So changing acceptsUrl (which I maintain works just fine) won't help anything. What's happening here is that the DriverManager tries to use the postgresql Driver to establish a connection to the given URL, but it ends up choking on the provided Properties before it can bail out because it is not the correct URL. You should not use the Hashtable inherited Properties.put method to insert non-string data into a properties object because all keys and values should be Strings. If you get rid of properties.put(shutdown, Boolean.TRUE), it works just fine. 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] BUG #5961: JDBC Driver acceptURL does not check 'jdbc:postgresql:'
On Thu, 31 Mar 2011, Martin Handsteiner wrote: The following bug has been logged online: Bug reference: 5961 PostgreSQL version: 9.0 Build 801 Description:JDBC Driver acceptURL does not check 'jdbc:postgresql:' Details: JDBC Driver acceptURL does not check 'jdbc:postgresql:' I'm not sure what check you want it to make. It is a valid JDBC connection string. Testing here shows that it falls back from a v3 to a v2 protocol connection when a database name is not provided, but it does still seem to work. We have to connect to several Databases on the server. Due to the wrong implementation of acceptURL in the Postgres jdbc driver, connecting to any other database is impossible. I'm not sure what you mean. If you don't provide a database name, how do you expect it to connect to different databases? 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] BUG #5961: JDBC Driver acceptURL does not check 'jdbc:postgresql:'
On 3/31/2011 8:50 AM, DI Martin Handsteiner wrote: thank you very much for your fast response. The problem with the current implementation of acceptsURL in the the postgres driver is, that it also returns true if the connection url is like: jdbc:oraclethin:.. jdbc:hsqldb:.. That is the reason, why connections to other databases than postgres are not possible, if you use also a postgres driver. I understand what your problem description is now, but I'm not seeing that in a simple test case here. Can you provide the actual URLs that make the attached code accept a non-postgresql URL? Kris Jurka import java.sql.Driver; public class Conn { public static void main(String args[]) throws Exception { String urls[] = { jdbc:xx:postgresql, jdbc:xx:postgresql:xx, jdbc:xx:xx:postgresql, jdbc:hsqldb:., jdbc:oraclethin:... }; Driver driver = new org.postgresql.Driver(); for (String url : urls) { System.out.println(url + = + driver.acceptsURL(url)); } } } -- 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 #5948: JDBC wrond insert of timestamp data
On Fri, 25 Mar 2011, yuriy.tereschuk wrote: The following bug has been logged online: Bug reference: 5948 PostgreSQL version: postgresql90 Operating system: Linux Fedora 14 Description:JDBC wrond insert of timestamp data Details: org.postgresql.util.PSQLException: ERROR: column datetime is of type timestamp without time zone but expression is of type character varying Hint: You will need to rewrite or cast the expression. JDBC version postgresql90-jdbc-9.0.801-1PGDG.f14.i686 You are most likely setting the timestamp value by using setString or setObject with a string value on a PreparedStatement. This is indicating to the driver that you want the parameter to have a string (varchar) type. This is not correct for a timestamp. You should use setTimestamp or setObject with a third parameter indicating that the value is a timestamp. If you are unable to change your application for some reason, you can adjust the driver's binding behavior by using the stringtype=unspecified connection parameter described here: http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters 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] BUG #5943: jdbc client doesn't omit pg_toast_temp tables from schema views
On Wed, 23 Mar 2011, Daniel Ceregatti wrote: The following bug has been logged online: Bug reference: 5943 Logged by: Daniel Ceregatti Email address: dan...@ceregatti.org Description:jdbc client doesn't omit pg_toast_temp tables from schema views Details: In the file org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java is the query: sql = SELECT nspname AS TABLE_SCHEM FROM pg_catalog.pg_namespace WHERE nspname 'pg_toast' AND nspname !~ '^pg_temp_' ORDER BY TABLE_SCHEM; Using Data Tools Platform in eclipse I was able to see all the pg_toast_temp_XXX schemas in the schema view. This has already been fixed in CVS and will be in the next set of releases. 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] BUG #5753: Existing Functions No Longer Work
On Tue, 16 Nov 2010, Korry Douglas wrote: Vince - is there any chance that you were using a non-standard PostgreSQL distribution or driver before? Like something from EnterpriseDB? It may be that a third-party JDBC driver was doing some behind-the-curtains work on your behalf. The stock JDBC driver does have support for refcursors, so that it will do FETCH ALL FROM it and return a ResultSet object instead of just a string if you call getObject on a refcursor value. This thread is too confusing with random snippets, assertions, and differences between pgadmin, psql, and other interfaces to tell what's really going on. The behavior of the JDBC driver shouldn't have changed between releases either, but this may explain the differences seen between pgadmin and the user's application. http://jdbc.postgresql.org/documentation/84/callproc.html#callproc-resultset-refcursor 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] BUG #5637: JDBC driver method setClob always uses getAsciiStream()
On Thu, 2 Sep 2010, Jochen Terstiege wrote: The following bug has been logged online: Bug reference: 5637 Description:JDBC driver method setClob always uses getAsciiStream() Details: Using driver: postgresql-8.4-701.jdbc3.jar The method setClob() in the AbstractJdbc2Statement calls the method getAsciiStream() on the provided Clob. This leads to problems if the given Clob contains an UTF-8 encoded string. Should the driver call getCharacterStream() instead? Can this problem be solved in a different way? The issue here is that postgresql doesn't have a real Clob type on the server, only a Blob type (and even that has some quirks). So the JDBC driver allows you to retrieve a large object as either a Blob or a Clob. This means that the data the driver gets is a simple binary stream with no encoding information. The JDBC driver uses getAsciiStream because it returns an InputStream which does not have to deal with encoding conversion which could fail because it doesn't know the source data's encoding. This is important so that it can faithfully reproduce an arbitrary PG Clob. Your complaint is that this doesn't work when passed a non-PG Clob. I suppose we could try to inspect the Clob to determine if it was a PG Clob or not and choose different methods based on that determination. 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: [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] BUG #5501: PostgreSQL JDBC Driver has inappropriate import
On Sat, 12 Jun 2010, Dave Jarvis wrote: The following bug has been logged online: Bug reference: 5501 PostgreSQL version: 8.4.4 Description:PostgreSQL JDBC Driver has inappropriate import Details: PROBLEM postgresql-jdbc-8.4-701.src/org/postgresql/gss/MakeGSS.java:21: package com.sun.security.auth.callback does not exist [javac] import com.sun.security.auth.callback.TextCallbackHandler; [javac] ^ SOLUTION Remove the import line (the class is not used). You should not rely on Sun's internal packages. This has already been fixed in CVS. 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] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
On Fri, 4 Jun 2010, Farid Zidan wrote: Here is actual statements I am running and like I said they work for all 9+ DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax for the current user ID): '2010-04-30 00:00:00', '2010-04-30 00:00:00', {fn user() } If you're into using standard ODBC escapes for portability, shouldn't you be using {ts '2010-04-30 00:00:00'}? http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx 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] Possible bug with BYTEA and JDBC
On Sat, 13 Feb 2010, Gregory Kotsaftis wrote: postgresql-8.4-701.jdbc4.jar postgresql-8.5alpha3 I followed the steps in the blob tutorial and tried to test the BYTEA example: The 8.5/9.0 release has changed the default bytea output format and the 8.4 JDBC driver does not support it. You've got a couple of options: 1) Change the bytea format back to the 8.4 supported format. Set bytea_output = escape in your postgresql.conf. 2) Build the JDBC driver from CVS which does support the new format. 3) Use an 8.4 server. 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] BUG #5269: postgres backend terminates with SIGSEGV
On Tue, 12 Jan 2010, Tom Lane wrote: preparing something as trivial as a ROLLBACK is pretty silly so nobody does it. Actually the JDBC driver has been preparing BEGIN, COMMIT, and ROLLBACK since the 8.0 release. http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00149.php 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] BUG #5099: When MetaData is acquired, it becomes an SQL error.
On Wed, 7 Oct 2009, konishi wrote: Bug reference: 5099 PostgreSQL version: 8.4.1 Description:When MetaData is acquired, it becomes an SQL error. Details: In sample source and sample db when used postgresql-8.4-701.jdbc3.jar is error when used postgresql-8.3-603.jdbc3.jar is no error The error disappears when prepared.getParameterMetaData() row delete. String url = jdbc:postgresql://XXX.XXX.XXX.XXX:5432/test; Connection con = DriverManager.getConnection(url, postgres, test); try{ String sql = insert into test(filename,upddate) values(?,?); PreparedStatement prepared = con.prepareStatement(sql); System.out.println(ParameterMetaData[ + prepared.getParameterMetaData() + ]); prepared.setString(1, 0); prepared.setTimestamp(2, new Timestamp(Calendar.getInstance().getTimeInMillis())); prepared.executeUpdate(); }catch(Exception e){ System.out.println(e.getMessage()); } error message: java.lang.IllegalArgumentException: Can't change resolved type for param: 1 from 1043 to 25 at org.postgresql.core.v3.SimpleParameterList.setResolvedType(SimpleParameterLi st.java:230) at I have applied a fix to CVS for this problem and it will be included in the next release. For the 8.4 release an optimization was added to avoid re-describing a statement if we already had the type information available by copying the resolved type information from the query to the provided parameters. Its goal was just to overwrite parameters without a type (unknown), but it was actually overwriting all types which could change the query's desired behavior. In this case a safeguard to prevent that change was throwing the exception noted. 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] BUG #5197: JDBC: selecting oid results in Exception
On Thu, 19 Nov 2009, Joseph Shraibman wrote: The following bug has been logged online: Bug reference: 5197 Description:JDBC: selecting oid results in Exception Details: query: select oid FROM pg_catalog.pg_class c; --- org.postgresql.util.PSQLException: Bad value for type int : 2148618421 at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.jav a:2630) Java doesn't have an equivalent for unsigned types, so the JDBC driver internally tries to fit it into a signed int by using the negative values. Unfortunately that's failing and it's not what the user wants for this case. I imagine the user really wants to get a Long back from this. I'm going on vacation next week, but when I return, I'll make that happen. 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] BUG #5177: Concurrency problem in AbstractJdbc23PooledConnection
On Tue, 10 Nov 2009, Mauro Molinari wrote: Bug reference: 5177 PostgreSQL version: 8.3.8 Description:Concurrency problem in AbstractJdbc23PooledConnection Details: Hello, we're using PostgreSQL JDBC3 driver 8.3-605 to connect to a 8.3.8 Postgres instance. Our application has a JTA-XA transaction infrastructure managed by Spring and using JBoss Transactions as a JTA implementation. We're using a connection pool of our own to pool XA connections on top of which there's JBoss Transactions transactional driver that is managing connections. Our connection pool infrastructure creates a PGXADataSource from which it gets new XA connections. In this scenario, sometimes happens that the PostgreSQL driver fails with the following exception: java.lang.NullPointerException at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$ConnectionHandler.in voke(AbstractJdbc23PooledConnection.java:319) at $Proxy5.close(Unknown Source) at com.arjuna.ats.internal.jdbc.ConnectionImple.close(ConnectionImple.java:369) Looking at the JDBC driver source code, it seems a concurrency problem, because at row 304-305 the variable con is checked against null. What is actually happening here is that our code is closing a connection used to read some data from the DB. This read operation is executed outside any transaction, so JBoss Transactions is honouring the connection close request by first calling calling close on the XA connection (and this is causing the XA connection to be given back to our pool); after that, because of the actual JDBC connection reports false on isClosed(), JBoss Transactions is also calling close() on it too... and this generates the NullPointerException given above. Looking at the AbstractJdbc23PooledConnection it seems there's no synchronization at all between isClosed() and close(). My suspect is that in the previous scenario something like this happens: 1. thread 1: close() is invoked 2. thread 2: isClosed() returns false 3. thread 2: close() is invoked At operation 2., isClose returns false, while it should return true because a close request has already been made on thread 1. Anyway, there should be no problem at all to call close repeatedly on the same connection (as the JDBC contract states), so the NullPointerException should not happen in any case. Attached is a test case which reproduces this problem easily. Thanks in advance and please let me know how I can monitor this bug report (is there any bug tracking system for PostgreSQL?). There is no bug tracker for postgresql in general. The JDBC driver does have a bug tracker, but the mailing list is where most of the action happens. http://pgfoundry.org/tracker/?group_id=1000224 Kris Jurkaimport java.sql.*; import javax.sql.XAConnection; import org.postgresql.xa.PGXADataSource; public class XaCloseTest { public static void main(String args[]) throws Exception { PGXADataSource ds = new PGXADataSource(); ds.setServerName(localhost); ds.setUser(kjurka); ds.setPassword(); ds.setDatabaseName(kjurka); ds.setPortNumber(5850); XAConnection xaconn = ds.getXAConnection(); Handle h = new Handle(); h.conn = xaconn.getConnection(); for (int i=0; i5; i++) { Closer c = new Closer(h); c.start(); } Opener o = new Opener(xaconn, h); o.start(); } private static class Handle { public Connection conn; } private static class Closer extends Thread { private final Handle _h; public Closer(Handle h) { _h = h; } public void run() { while (true) { try { _h.conn.close(); } catch (SQLException sqle) { sqle.printStackTrace(); } } } } private static class Opener extends Thread { private final XAConnection _xaconn; private final Handle _h; public Opener(XAConnection xaconn, Handle h) { _xaconn = xaconn; _h = h; } public void run() { while (true) { try { _h.conn = _xaconn.getConnection(); } catch (SQLException sqle) { sqle.printStackTrace(); } } } } } -- Sent via pgsql-bugs mailing list
Re: [BUGS] pgsql-jdbc/pgsql-odbc
On Wed, 18 Nov 2009, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I see that there's been no response to bugs #5194, #5187, #5051. Should we consider adding a message to the bug reporting page that suggests emailing the relevant list directly for JDBC/ODBC bugs? +1. Either that or somebody takes responsibility for forwarding bug reports, which might be nicer but I don't know who would want to do it. I do read -bugs and try to follow up on all JDBC bug reports. Notably none of the above referenced bug numbers are JDBC problems. I think the problem is trying to determine what projects in the entire postgresql ecosystem deserve special mention on the bug reporting page. Surely there must be at least one ODBC person subscribed to -bugs. What about just asking them to be more responsive even if they don't have a solution? 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: [JDBC] [BUGS] BUG #5058: [jdbc] Silent failure with executeUpdate()
On Thu, 17 Sep 2009, Joseph Shraibman wrote: [when passing a multi-statement sql string to executeUpdate, if a statement after the first is a select, it is silently not fully executed.] Running queries in executeUpdate is not allowed. If you pass a plain select to executeUpdate it complains: stmt.executeUpdate(SELECT 1); org.postgresql.util.PSQLException: A result was returned when none was expected. The problem here is that we don't complain if it's not the first part of a multi-part statement. stmt.executeUpdate(/* */; SELECT 1) does not produce an error, but should, which I believe is the bug in this case. Since the JDBC driver knows it's going to complain if it sees a query result during executeUpdate, it wants to avoid the possibility of the user issuing a query which returns a giant dataset and having to process that just to error out anyway. So it passes the statements to the server with an additional instruction to say, I just want the first row back at this time. That way if it is a big query result we'll only get one row instead of the whole thing. The server can implement this by either holding the whole resultset on the server or, as in this case, partially executing the query and returning control to the driver to see if it wants to continue executing it. So your select is only partially executed, getting run for only one row of the joa table rather than the whole thing as you are expecting. I intend to change the driver to error out in this case. 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] BUG #4638: Bug with Geometry in Array
On Mon, 2 Mar 2009, Dr. Björn Weitzig wrote: I can't find any modified JDBC driver, only the JDBC extension postgis.jar (your link), which I use und which doesn't help. postgis.jar seems to be an add-on to the default JDBC driver, not an replacement. I use postgis_1.3.5.jar with postgresql-8.3-603.jdbc3.jar Right, postgis.jar is just an addon, and it's really a bug in the main JDBC driver. I have put in a fix for this bug into the JDBC driver for the next release. I've put up a copy of it here, and it should fix things for you. http://ejurka.com/pgsql/jars/arrdim/ 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] BUG #4617: JDBC Drivers 8.2/8.3 return no ResultSet
On Thu, 15 Jan 2009, Raymond L. Naseef wrote: The following bug has been logged online: Bug reference: 4617 PostgreSQL version: 8.3 7.4 Operating system: FreeBSD Description:JDBC Drivers 8.2/8.3 return no ResultSet Details: Below is query using temporary table instead of IN to greatly improve performance. This worked in older JDBC driver, but no longer does. The following were checked, making no difference: RESULT: org.postgresql.util.PSQLException: No results were returned by the query. QUERY: -- create temporary table lookup_username (username varchar) on commit drop; insert into lookup_username values ('naseef'); CREATE UNIQUE INDEX lookup_username_idx ON lookup_username USING btree (username); SELECT enp.username, trim(enp.full_name) FROM egr_nis_person as enp JOIN lookup_username as lu ON lu.username = enp.username WHERE enp.start_date now() + interval '30 seconds' AND enp.end_date now() + interval '30 seconds'; What's happening here is that you are actually issuing four commands, not a single query. The newer drivers will return a status for each command executed, and won't just give you the final select result. For multi-command statements you should use Statement.execute() and then getResultSet or getUpdateCount until getMoreResults is false. 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] BUG #4598: flaw in hashCode() method implementation of Connection class in postgresql-8.3-604.jdbc3.jar
On Tue, 30 Dec 2008, Radu Buzila wrote: The following bug has been logged online: Bug reference: 4598 driver: postgresql-8.3-604.jdbc3.jar) Description:flaw in hashCode() method implementation of Connection class in postgresql-8.3-604.jdbc3.jar Details: [Pooled connections' hashCode method doesn't work after the connection is closed failing with a NullPointerException] Fixed in CVS. 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] BUG #4488: jdbc : DatabaseMetaData.getPrimaryKeys no result
On Mon, 29 Dec 2008, davidsarmstrong wrote: I get the same error. I've verified the table name and the primary key. The interesting part is, it seems to work in cases where the primary key is a serial. All I can say is the same as before, it works for me. If you can post a complete test case I'd be happy to look at it, but there's not much else I can do. 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] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
On Wed, 17 Dec 2008, Miroslav Nachev wrote: Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with annotations? Serializable or BigInteger or byte[] or java.util.UUID? The server uuid type is already mapped to java.util.UUID in recent JDBC driver versions: http://archives.postgresql.org/pgsql-jdbc/2008-09/msg00102.php 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] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
Miroslav Nachev wrote: I try to use it but I have the following exception: java.lang.IllegalArgumentException: Unknown entity: java.util.UUID at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:223) at psqluuidtest.Main.persist(Main.java:33) at psqluuidtest.Main.main(Main..java:25) Surely you want to persist a TestTable1 instance, not the uuid itself. 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] BUG #4488: jdbc : DatabaseMetaData.getPrimaryKeys no result
On Tue, 21 Oct 2008, Marcel Petavy wrote: Bug reference: 4488 PostgreSQL version: 8.2.4 Operating system: Windows XP Description:jdbc : DatabaseMetaData.getPrimaryKeys no result Details: If I try to query the primary key via JDBC driver postgresql-8.3-603.jdbc4.jar DatabaseMetaData.getPrimaryKeys I get not result. create table test (id integer not null primary key(id)) ResultSet rs = connection.getMetaData(null,null,test); rs gives no rows. It definitely does work, perhaps your table is really TEST instead of test? If you still believe it's a driver problem, please post a complete test case that shows the exact create table command and java code calling getPrimaryKeys. 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] Deadlock condition in driver
On Wed, 21 May 2008, Kris Jurka wrote: On Wed, 21 May 2008, Daniel Migowski wrote: I came across a deadlock condition in the JDBC driver that rises when very large queries, containing thousends of statements are send to the server with statement.execute(). We already consider this case for batch execution and break the batch into an internal size that we expect is safe from deadlock. It looks like we should be doing the same for these one query batches. I'm not sure how tough that will be, but I'll take a look. I've committed a fix to CVS for this problem, and it should fix your case, but will not fix all deadlocks as a move to NIO or threading would. For more details, see: http://archives.postgresql.org/pgsql-jdbc/2008-10/msg00034.php 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] BUG #4483: setAutoCommit false shouldn't be necessary for fetchSize to work
On Thu, 16 Oct 2008, David Smiley wrote: Bug reference: 4483 Description:setAutoCommit false shouldn't be necessary for fetchSize to work Details: No matter what is going on internally within the JDBC driver, I don't believe disabling autoCommit should be necessary to benefit from the fetchSize hint (i.e. cursors). As long as the user code hasn't iterated to the end of the resultSet yet, the semantics of autoCommit are irrelevant. Unless the cursor has been opened WITH HOLD, the cursor will be closed at transaction end. So to be able to fetch data from the cursor you must not have autocommit on. Since the JDBC driver uses protocol level cursors (portals), it doesn't even have the option of specifying WITH HOLD. 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] Deadlock condition in driver
Previously we did not want to move to nio to be able to support older JDK versions, but with the next major release we're ready to drop that support. If you have this code, I'd certainly be interested in looking at it. One of the big drawbacks of breaking up a single execute call into multiple batches is that in autocommit mode you would get one transaction per batch instead of the previous behavior of one transaction for the whole execute call. Kris Jurka On Thu, 22 May 2008, Richard Evans wrote: We hit this problem and modified the driver to use non-blocking IO to fix it. You lose some of the more unusual features of the driver (such as switching the socket after open), but it does fix the blocking. Richard Evans On Wed, 21 May 2008, Daniel Migowski wrote: I came across a deadlock condition in the JDBC driver that rises when very large queries, containing thousends of statements are send to the server with statement.execute(). We already consider this case for batch execution and break the batch into an internal size that we expect is safe from deadlock. It looks like we should be doing the same for these one query batches. I'm not sure how tough that will be, but I'll take a look. 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 -- 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] Deadlock condition in driver
On Wed, 21 May 2008, Daniel Migowski wrote: I came across a deadlock condition in the JDBC driver that rises when very large queries, containing thousends of statements are send to the server with statement.execute(). We already consider this case for batch execution and break the batch into an internal size that we expect is safe from deadlock. It looks like we should be doing the same for these one query batches. I'm not sure how tough that will be, but I'll take a look. 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] Re: BUG #4123: Statement.setQueryTimeout does not work with Postgres Java Driver
On Wed, 23 Apr 2008, valgog wrote: Is it possible to implement the setStatementTimeout() as somethig like: s = c.prepareStatement(SELECT set_config('statement_timeout', neededTimeoutInMilliseconds, false); ); s.executeQuery(); c.commit(); Not really. This sets a global timeout for all queries while the JDBC API specifies that it is per-Statement. Also this only protects against long running queries. Recently there was some discussion on the JDBC list about soft vs hard timeouts and it seemed the conclusion was that people wanted setQueryTimeout to protect against things like the network connection dropping that statement_timeout can't do. In many cases statement_timeout is an adequate substitute for setQueryTimeout, but not in the general case that the JDBC driver must implement. 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] BUG #4123: Statement.setQueryTimeout does not work with Postgres Java Driver
On Tue, 22 Apr 2008, Amit Mujawar wrote: The following bug has been logged online: Bug reference: 4123 PostgreSQL version: 8.1 Description:Statement.setQueryTimeout does not work with Postgres Java Driver Details: I am using PostgreSQL through JDBC PostgreSQL ??? 8.1, Driver - org.postgresql.Driver 8.1-408.jdbc3 I suspect there is a problem with JDBC driver implementation for setQueryTimeout API. setQueryTimeout is not implemented at all. Newer drivers (8.3+) will throw an exception telling you that if you try to call setQueryTimeout while older drivers silently accept the value and do nothing. 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] setseed accepts bad seeds
On Mon, 10 Mar 2008, Tom Lane wrote: I'd be inclined to leave the mapping alone and just insert a warning (or hard error) for inputs outside the range -1 to 1. Here's a patch that errors out for out of range values. Kris JurkaIndex: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.423 diff -c -r1.423 func.sgml *** doc/src/sgml/func.sgml 6 Mar 2008 18:49:32 - 1.423 --- doc/src/sgml/func.sgml 10 Mar 2008 06:11:55 - *** *** 828,834 row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypevoid/type/entry !entryset seed for subsequent literalrandom()/literal calls (value between 0 and 1.0)/entry entryliteralsetseed(0.54823)/literal/entry entry/entry /row --- 828,834 row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypevoid/type/entry !entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry entryliteralsetseed(0.54823)/literal/entry entry/entry /row Index: src/backend/utils/adt/float.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/float.c,v retrieving revision 1.153 diff -c -r1.153 float.c *** src/backend/utils/adt/float.c 1 Jan 2008 19:45:52 - 1.153 --- src/backend/utils/adt/float.c 10 Mar 2008 06:11:55 - *** *** 1684,1691 setseed(PG_FUNCTION_ARGS) { float8 seed = PG_GETARG_FLOAT8(0); ! int iseed = (int) (seed * MAX_RANDOM_VALUE); srandom((unsigned int) iseed); PG_RETURN_VOID(); --- 1684,1695 setseed(PG_FUNCTION_ARGS) { float8 seed = PG_GETARG_FLOAT8(0); ! int iseed; + if (seed -1 || seed 1) + elog(ERROR, setseed parameter %f out of range [-1,1], seed); + + iseed = (int) (seed * MAX_RANDOM_VALUE); srandom((unsigned int) iseed); PG_RETURN_VOID(); Index: src/backend/utils/misc/guc.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.435 diff -c -r1.435 guc.c *** src/backend/utils/misc/guc.c10 Mar 2008 03:22:29 - 1.435 --- src/backend/utils/misc/guc.c10 Mar 2008 06:11:55 - *** *** 1849,1855 GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE }, phony_random_seed, ! 0.5, 0.0, 1.0, assign_random_seed, show_random_seed }, { --- 1849,1855 GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE }, phony_random_seed, ! 0.5, -1.0, 1.0, assign_random_seed, show_random_seed }, { -- 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 #3965: UNIQUE constraint fails on long column values
On Mon, 18 Feb 2008, Bruce Momjian wrote: Juho Saarikko wrote: While I didn't test, I'd imagine that this would also mean that any attempt to insert such values to an already unique column would fail. Works here in 8.3: test= insert into test values (repeat('a', 5) || 'b'); This only works because it gets toasted before being put in the index. Since you've selected something real compressible, you can fit 50k chars into it. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
On Tue, 12 Feb 2008, Adam Hardy wrote: Because this rules out certain important features of the JPA framework such as native SQL queries, you may want to prioritize this issue. I will have to use Oracle or mySQL until PostgreSQL can rectify things. I wouldn't expect postgresql to change anytime soon. Could you explain in more detail what you can't use and why the JPA side couldn't be fixed to support postgresql? You could also consider always quoting all identifiers in both database creation scripts and queries. This guarantees that the database won't mess with the case of the object although it can be a pain to type if you writing a lot of queries by hand. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
On Fri, 1 Feb 2008, Adam Hardy wrote: it seems to me from what you just said that PostgreSQL server and JDBC driver require the schema name to be lower case deliberately, and that any given name that is not all lower case is converted to lower case by the server or the driver. Am I correct? Anything that is not quoted is converted to lowercase. This is contrary to the SQL spec (it says to convert it to uppercase), but that's unlikely to change anytime soon. A JDBC app can portably detect this case by checking DatabaseMetaData.storesLowerCaseIdentifiers() and adjusting the other metadata calls appropriately. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3897: plJava dll still doesn't load
On Wed, 23 Jan 2008, David Gradwell wrote: Bug reference: 3897 PostgreSQL version: 8.3rc2 Operating system: Windows Server 2003 Description:plJava dll still doesn't load Details: I've successfully installed plJava on postgres 8.2.6 using Kris Jurka's version at http://www.ejurka.com/pgsql/pljava/826/. Note that I failed to get the standard distribution download to work. 8.2.6 will not be repackaged, so the official fix will be in 8.2.7 which has not yet been scheduled. I've now tried to get plJava running on 8.3rc2 (having failed on rc1 - already reported) using the pljava.dll and pljava.jar in the distribution. I checked with the windows installer guys and they say they've got the updated pljava in RC2 and have tested it to ensure that it works. What they put in RC2 is the repackaged pljava for 8.3rc1 I put up here: http://ejurka.com/pgsql/pljava/83rc1/ So it wouldn't hurt to try the above, but I don't have great hopes that it will change things for you unless something got lost along the way. Kris Jurka ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
On Tue, 22 Jan 2008, Adam Hardy wrote: The following bug has been logged online: Bug reference: 3894 Description:JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name Details: create schema DEV; but then DatabaseMetaData.getTables(null, DEV, %, new String[] {TABLE}) returned an empty resultset. However giving it the schema name dev returns the full resultset. This is by design. While SQL provides case folding and quoting rules those are not the same as the JDBC driver's rules. In SQL an unquoted identifier is case folded while quoted identifiers are not. To provide the most straightforward mapping for the JDBC driver, we require an exact match because quoting in Java String objects isn't the same as SQL. It would be odd to say getTable(..., \Dev\,...) to imply that you wanted a case sensitive match. Even odder would it be to do a search for a schema with a quote in it's name using SQL identifier rules in Java code. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3801: max_fsm_pages postgresql.conf default != guc.c default
On Thu, 6 Dec 2007, Reece Hart wrote: This seems to directly say that the commented out settings are the default values, and furthermore that one must restart to get the indicated default back. Based on your evidence, it seems that the postgresql.conf comment for max_fsm_pages needs revising to indicate that the m_f_p default is determined at initdb-time. Yes, the commented out values are the defaults, but after initdb max_fsm_pages is not commented out, which is why I'm suggesting you or some other admin modified your file. Try this test: $ ./tmp/82/bin/initdb -D fsmtest fsmlog 21 $ grep max_fsm fsmlog selecting default shared_buffers/max_fsm_pages ... 24MB/153600 $ grep max_fsm_pages fsmtest/postgresql.conf max_fsm_pages = 153600 # min max_fsm_relations*16, 6 bytes each So you can see max_fsm_pages is not commented out, so it is not the true default (2). Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3800: Java/Postgres PreparedStatement returns stale data
On Wed, 5 Dec 2007, Michael Han wrote: The following bug has been logged online: Bug reference: 3800 PostgreSQL version: 8.2 Description:Java/Postgres PreparedStatement returns stale data Details: PROBLEM : Java/Postgres Returns Stale data when underlying Table View is Replaced Prior to the 8.3 release, prepared plans don't notice when underlying objects change. Aside from waiting for 8.3, the only workaround is to prevent the JDBC driver from reusing the same server plan. By default the JDBC driver reuses the server plan after the 5th execution, and you can adjust this by the prepareThreshold URL parameter. You can disable plan reuse by adding prepareThreshold=0. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3801: max_fsm_pages postgresql.conf default != guc.c default
On Thu, 6 Dec 2007, Reece Hart wrote: Bug reference: 3801 PostgreSQL version: 8.2.5 Description:max_fsm_pages postgresql.conf default != guc.c default Details: The guc.c hardwired default for max_fsm_pages is 2. In postgresql.conf (and .sample), the default is claimed to be 1638400. I don't know which is correct. You need to consider the units. guc.c is in number of pages, while postgresql.conf is in kB. Since the page size is 8192, these are equivalent. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3801: max_fsm_pages postgresql.conf default != guc.c default
On Wed, 5 Dec 2007, Reece Hart wrote: On Wed, 2007-12-05 at 23:20 -0500, Kris Jurka wrote: You need to consider the units. guc.c is in number of pages, while postgresql.conf is in kB. Since the page size is 8192, these are equivalent. I did consider that, but I'm not certain that it's that simple. Here's why: Actually what I said earlier was completely bogus, please ignore. I just looked at the two numbers and essentially made up an answer (even though that answer was off by a factor of 10). Here's something hopefully more useful... max_fsm_pages for the initial postgresql.conf is determined at initdb time as it depends on the shared_buffers settings it picks for the machine: src/bin/initdb/initdb.c says: #define FSM_FOR_BUFS(nbuffers) ((nbuffers) 1000 ? 50 * (nbuffers) : 2) so the actual default at initdb time can be set as high as nbuffers * 50, where the max shared_buffers is 4096. So the default max_fsm_pages for a beefier machine will be 204800 which is what you will find in postgresql.conf.sample. The fact that you have a commented out value in your postgresql.conf does not mean it is the default. I'd guess someone set that themself. Try initdbing a fresh data dir and seeing what the actual default is. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
On Fri, 16 Nov 2007, Lance J. Andersen wrote: The table in this case that needs to be looked at is B-5, not B-4 which i believe you are referring to. The expectation of setObject(int/String, Object, int) is that you are sending the value to the backend as the SQL Type specified for the 3rd parameter, in this case a BIT. I would expect that the driver can do the conversion in this case because i have the table created with a BIT(1) column and the backend supports both BIT and BOOLEAN. 13.2.2.2 of the JDBC 3.0 and 13.2.2.3 of the JDBC 4.0 spec tries to make this clear that an explicit conversion is required. For the JDBC 4.1 spec, i plan to add additional clarifications, but i would expect the setObject() call in the test to succeed based on the current spec. No part of the spec says anything about the server or SQL Type that a Java type or JDBC type maps to, so your whole argument is predicated on the fact that java.sql.Types.BIT must match up with the server side bit type, but my contention is that it doesn't. The current 2003 sql spec does not have bit types at all, but the previous versions did. The 1999 spec has a bit type, but it is a bit string which is what the postgresql server side type implements, not a single bit. The best mapping for a bit string to a Java type is java.util.BitSet, not java.lang.Boolean. Just because bit can be made to work doesn't make it the best option. Particularly our concern arises from what to do when returning meta data. Can you describe this in more detail as I have not noticed a problem via ResultSetMetaData or DatabaseMetaData.getColumns() as the columns indicate they are a BIT with the correct precision. SELECT '1'::bit || '1'::bit; SELECT '1'::bit(1) UNION ALL SELECT '11'::bit(2); CREATE TABLE bittable (a bit(1), b bit(2)); SELECT COALESCE(a, b) FROM bittable; CREATE TABLE bittable2 (a bit); SELECT a FROM bittable2; None of these ResultSets will contain length information sufficient to let you know if you have bit(1) or bit(N) data. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
Lance J. Andersen wrote: Thank you for your time, but i think there is still a driver issue here: If i use the same types as i sent in the email and execute select * from bit_in_min(1::bit) I have no problems and the table is correctly updated. This would lead me to believe that the driver has a problem with correctly mapping the setObect() of the String to a BIT which is required conversion by the JDBC spec. According to our reading of the JDBC spec java.sql.Types.BIT and BOOLEAN are equivalent. So it doesn't make sense to map BIT to one server type and BOOLEAN to another. When thinking about Types.BIT it's easy to see a server type named bit and assume it's a match, but for the semantics of Types.BIT, we think boolean is a better match. http://archives.postgresql.org/pgsql-jdbc/2004-04/msg00107.php Just because bit can be made to work doesn't make it the best option. Particularly our concern arises from what to do when returning meta data. Consider a table that has columns a bit(1), b bit(2). While a does have boolean semantics because it's limited to a length of 1, b is not. Returning b as Types.BIT would be a mistake. Driving this decision solely on the length attribute is not good because the server will not give you the length information in certain circumstances and all you'll have is the raw bit type name. This is why we've chose to use boolean as the server type for Types.BIT + BOOLEAN. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
On Thu, 15 Nov 2007, Lance Andersen wrote: The following bug has been logged online: Bug reference: 3751 PostgreSQL version: 8.2.x Description:Conversion error using PreparedStatement.setObject() Details: A PreparedStatement.setObject(1, 1, Types.Bit) will fail with the following Exception: This is not a great test case. 1) It doesn't contain the definition of the Drivers class so it doesn't compile. The whole drivers class and dumping metadata is needless complication for a simple test case. 2) It doesn't contain the definition of the bit_tab table, so it doesn't run. 3) The error is actually coming from stmt.executeUpdate(Min_Insert), not a PreparedStatement. So where does that leave us? 1) The raw insert fails. INSERT INTO bit_tab (1,0,null) fails because 1 gets typed as an integer and there are no implicit or assignment casts from integer to bit. You would need to say, '1' so it comes in untyped and converted to bit, or be explicit about the type with a cast, saying 1::bit or CAST(1 AS bit). 2) There might be a problem with bit conversion in prepared statements, but we didn't get that far. Other notes: In PG the bit type is really for multiple bits, not a single bit. Consider SELECT 77::bit(8) results in 01001101. It's more likely that you want to use boolean as the type instead although it doesn't have any casts that will help you out in this situation either. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3751: Conversion error using PreparedStatement.setObject()
The test runs for me when I change all of the underlying types from bit to boolean: create table Bit_Tab (MAX_VAL boolean, MIN_VAL boolean, NULL_VAL boolean NULL) ; CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM boolean) returns void as 'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ; Kris Jurka Lance J. Andersen wrote: Sorry Bad, Cut and paste. This test is a strip down of much larger test. The reason the metadata is there as this gets run from a framework which exercises JDBC drivers from all of the major vendors which is also the reason for the Drivers class. As far as the INSERT, i did not look at the postgresql docs in enough detail probably given that it works against all of the other vendors who support BIT data types, so my mistake. Here is the the entire scenario: The table is created as create table Bit_Tab (MAX_VAL bit(1), MIN_VAL bit(1), NULL_VAL bit(1) NULL) ; and the stored procedure via CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM bit(1)) returns void as 'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ; even if i change the insert as you suggest, to insert into Bit_Tab values('1', '0', null ) it still fails org.postgresql.util.PSQLException: ERROR: column min_val is of type bit but expression is of type boolean at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302) at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:93) at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:41) at com.sun.jdbc.samples.BitTest.main(BitTest.java:16) BUILD SUCCESSFUL (total time: 2 seconds) The failure now is on cstmt.executeUpdate() which i would infer either the driver is not doing the proper conversion or the function is having issues. The test is validating that a String can be sent as a BIT and returned as a Boolean per the JDBC specifcation. -lance Kris Jurka wrote: On Thu, 15 Nov 2007, Lance Andersen wrote: The following bug has been logged online: Bug reference: 3751 PostgreSQL version: 8.2.x Description:Conversion error using PreparedStatement.setObject() Details: A PreparedStatement.setObject(1, 1, Types.Bit) will fail with the following Exception: This is not a great test case. 1) It doesn't contain the definition of the Drivers class so it doesn't compile. The whole drivers class and dumping metadata is needless complication for a simple test case. 2) It doesn't contain the definition of the bit_tab table, so it doesn't run. 3) The error is actually coming from stmt.executeUpdate(Min_Insert), not a PreparedStatement. So where does that leave us? 1) The raw insert fails. INSERT INTO bit_tab (1,0,null) fails because 1 gets typed as an integer and there are no implicit or assignment casts from integer to bit. You would need to say, '1' so it comes in untyped and converted to bit, or be explicit about the type with a cast, saying 1::bit or CAST(1 AS bit). 2) There might be a problem with bit conversion in prepared statements, but we didn't get that far. Other notes: In PG the bit type is really for multiple bits, not a single bit. Consider SELECT 77::bit(8) results in 01001101. It's more likely that you want to use boolean as the type instead although it doesn't have any casts that will help you out in this situation either. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3722: PSQLWarning missing call to super in CTOR
On Tue, 6 Nov 2007, Henning Nitschke wrote: Bug reference: 3722 PostgreSQL version: 8.2 Operating system: Java Description:PSQLWarning missing call to super in CTOR Details: public PSQLWarning(ServerErrorMessage err) { super(err.toString()); // == missing this.serverError = err; } Why is this necessary? Since PSQLWarning overrides both getMessage and toString, why is the super call needed? Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] creating a table with a serial column sets currval
On Thu, 18 Oct 2007, Kris Jurka wrote: jurka=# create table t (c serial); NOTICE: CREATE TABLE will create implicit sequence t_c_seq for serial column t.c CREATE TABLE jurka=# select currval('t_c_seq'); currval - 1 (1 row) I would expect it to say that currval wasn't set like so: Looks like any alter sequence command will do this. The serial case uses alter sequence owned by under the hood which exposes this. The problem is that altering the sequence puts it into the SeqTable cache list when it really shouldn't be. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] creating a table with a serial column sets currval
jurka=# create table t (c serial); NOTICE: CREATE TABLE will create implicit sequence t_c_seq for serial column t.c CREATE TABLE jurka=# select currval('t_c_seq'); currval - 1 (1 row) I would expect it to say that currval wasn't set like so: jurka=# create sequence myseq; CREATE SEQUENCE jurka=# select currval('myseq'); ERROR: currval of sequence myseq is not yet defined in this session Kris Jurka ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] creating a table with a serial column sets currval
On Thu, 18 Oct 2007, Tom Lane wrote: Looks like any alter sequence command will do this. The serial case uses alter sequence owned by under the hood which exposes this. The problem is that altering the sequence puts it into the SeqTable cache list when it really shouldn't be. It's not that it gets put in the cache, it's that read_info gets called (setting elm-increment). I think we probably should clean this up by creating a separate flag in that struct that explicitly says currval is valid, which would be set by nextval(), setval() (because historically it's acted that way), and I guess ALTER SEQUENCE RESTART WITH (for consistency with setval()). Personally I think setval should only set validCurrval and the last_value if iscalled = true. If is_called = false I think it should retain the previous last_value if any until the next nextval call. jurka=# create sequence s; CREATE SEQUENCE jurka=# select nextval('s'); nextval - 1 (1 row) jurka=# select setval('s',5, false); setval 5 (1 row) jurka=# select currval('s'); currval - 5 (1 row) Should return 1 instead of 5. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3675: Crash on xpath function with 2 parameters
On Sat, 13 Oct 2007, Jeremy Palmer wrote: The following bug has been logged online: Bug reference: 3675 PostgreSQL version: 8.3b1 Operating system: WinXP SP2 Description:Crash on xpath function with 2 parameters Details: The following query crashes the backend: SELECT xpath('/my:a/text()', 'my:a xmlns:my=http://example.com;test/my:a'); This patch avoids the double free of xpathcomp and fixes things for me. Kris JurkaIndex: src/backend/utils/adt/xml.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v retrieving revision 1.47 diff -c -r1.47 xml.c *** src/backend/utils/adt/xml.c 23 Sep 2007 21:36:42 - 1.47 --- src/backend/utils/adt/xml.c 13 Oct 2007 17:27:17 - *** *** 3184,3189 --- 3184,3191 xpathobj = xmlXPathCompiledEval(xpathcomp, xpathctx); xmlXPathFreeCompExpr(xpathcomp); + xpathcomp = NULL; + if (xpathobj == NULL) ereport(ERROR, (errmsg(could not create XPath object))); /* TODO: reason? */ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3589: /etc/init.d/postgresql reload doesn't reflect /etc/postgresql/postgresql.conf log_statement
On Thu, 30 Aug 2007, Magnus Hagander wrote: There's your problem right there: it does *not* mean that the default setting would be effective. It means that the setting won't be changed. Remove the comment and set it to false, and it'll work. (if you restart the server instead of reload it *will* fall back to the default, which isn't very consistent) This is a known problem (though arguably not actually a bug) but it's far from trivial to fix which is why it hasn't been fixed yet. Actually this will be fixed in 8.3: http://archives.postgresql.org/pgsql-committers/2007-03/msg00097.php Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] JDBC-Interface - Behaviour on Update, Insert or Delete returning ResultSets - Inconsistency to Console ODBC
On Sat, 18 Aug 2007, Otto Weichselbaum wrote: While using views calling functions on INSERT, UPDATE and DELETE via according rules, I noticed an inconsistent behaviour of the JDBC-interface of postgres; an INSERT-, UPDATE- or DELETE-statement producing tuples as return-value (in our case through rules calling functions but although via the 'RETURNING'-clause of a single INSERT- or UPDATE-statement) is returning the expected number of tuples when called via the console (even through pgAdmin) or via ODBC BUT when called via JDBC only an 'UpdateCount' of 0 is returned; debugging to protocol-level showed, that the postgreSQL-server does not even differ between a 'simple' UPDATE or one returning tuples; I believe this differing results you are seeing is based upon whether the query is executed using the simple or extended query protocol. The JDBC driver always uses extended query protocol when speaking to a V3 protocol capable server. Another workaround you can use is adding ?protocolVersion=2 to your URL to make it use the V2 protocol and simple query execution. It works with the simple query protocol because you send the server a query and then loop retrieving any number of results that are returned. The extended query protocol is designed around single query gives a single result, so it isn't really prepared to handle the update count and results at the same time. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3544: SQLException from JDBC driver
On Thu, 16 Aug 2007, Scott Harper wrote: The following bug has been logged online: Bug reference: 3544 PostgreSQL version: 8.1.9 Operating system: Debian Linux (2.6.18-4-486) Description:SQLException from JDBC driver Details: I have installed PostgreSQL 8.1 via the Debian package manager (APT) -- installed the package postgresql-8.1. We are running Sun Java 5, Apache 2, and Tomcat 5.5. From the discussion at http://jdbc.postgresql.org/download.html, I have determined that I should be using the 8.1-410 JDBC 3 driver. I downloaded the driver, and it is bundled into my servlet's war file in WEB-INF/lib. When the servlet makes the DriverManager.getConnection() call, the following exception is thrown: org.postgresql.util.PSQLException: Something unusual has occured to cause the driver to fail. Please report this exception. This exception is most likely caused by a permission denied error because you are running the tomcat server with a security policy. The driver should be placed in $CATALINA_HOME/common/lib instead of a war file. Kris Jurka ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3480: JDBC driver: getIndexInfo() returns quotes around quoted column names
On Mon, 23 Jul 2007, Andrei Badea wrote: The following bug has been logged online: Bug reference: 3480 Description:JDBC driver: getIndexInfo() returns quotes around quoted column names Details: The DatabaseMetaData.getIndexInfo() method returns quotes around quoted column names, unlike the getColumns() method. To reproduce run the following statement in a database named test and the public schema: Fixed in CVS for 8.1, 8.2, and HEAD. Thanks for the test case. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3278: PSQLException when using setBinaryStream via JDBC
On Mon, 14 May 2007, jeffrey zhao wrote: The following bug has been logged online: Bug reference: 3278 When I run the following program, I got an exception as following: ps.setBinaryStream(2, fis, file.length()); org.postgresql.util.PSQLException: Method org.postgresql.jdbc4.Jdbc4PreparedStat ement.setBinaryStream(int, InputStream, long) is not yet implemented. Is the method setBinaryStream really not yet implemented? JDBC4 added a setBinaryStream(int, InputStream, long) method which has not been implemented yet. JDBC2 offers setBinaryStream(int, InputStream, int) which is implmented. So you'll need to cast the last parameter to an integer to make it work with the current driver. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] setseed accepts bad seeds
Why doesn't setseed complain when given a seed value outside of its expected range? The documentation claims that 0 - 1 should be used, but shouldn't it actually be -1 - 1 to get the full range of the seed? If passed a value outside this range you always get the same value which isn't going to produce a good random number stream. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] setseed accepts bad seeds
On Wed, 11 Apr 2007, Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: Why doesn't setseed complain when given a seed value outside of its expected range? Why should it complain? The use of the value is totally unspecified anyway. Because the user is likely using it incorrectly. I'm not sure what you mean by totally unspecified. The documentation[1] states: set seed for subsequent random() calls (value between 0 and 1.0) When a user calls setseed(5), setseed(500), or setseed(-500) they get the same seed value each time which is surely not what they intended. At minimum I think it should raise a warning. Also I think that documentation should be corrected to indicate that vaules -1 to 1 are the correct seed value range or it should it should map 0-1 to the entire seed space, not just half of it as is currently done. The decision of which change to make is unclear because it's a change to either the call signature or to the generated values for a given user supplied seed. Kris Jurka [1] http://www.postgresql.org/docs/8.2/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] setseed accepts bad seeds
On Wed, 11 Apr 2007, Tom Lane wrote: It's not really possible to use it incorrectly, AFAICS. Any value you might pass to it will result in a specific new seed value. Nowhere is there any guarantee of what the mapping is, and it's obviously impossible to guarantee that the mapping is one-to-one, so any user assumptions about what a specific seed value might mean seem broken regardless. Then please consider this patch which checks the range and maps the provided value to the entire seed space. Kris JurkaIndex: src/backend/utils/adt/float.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/float.c,v retrieving revision 1.149 diff -c -r1.149 float.c *** src/backend/utils/adt/float.c 27 Feb 2007 23:48:08 - 1.149 --- src/backend/utils/adt/float.c 11 Apr 2007 18:48:42 - *** *** 1783,1790 setseed(PG_FUNCTION_ARGS) { float8 seed = PG_GETARG_FLOAT8(0); ! int iseed = (int) (seed * MAX_RANDOM_VALUE); srandom((unsigned int) iseed); PG_RETURN_VOID(); --- 1783,1800 setseed(PG_FUNCTION_ARGS) { float8 seed = PG_GETARG_FLOAT8(0); ! int iseed; ! ! if (seed 0 || seed 1) ! elog(WARNING, setseed parameter %f out of expected range [0,1], seed); ! ! /* !* map seed range from [0, 1] to [-1, 1] to get the !* full range of possible seed values. !*/ ! seed = 2 * (seed - 0.5); + iseed = (int) (seed * MAX_RANDOM_VALUE); srandom((unsigned int) iseed); PG_RETURN_VOID(); ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3106: A problem with escaping table name pattern for DatabaseMetaData.getColumns()
On Sun, 4 Mar 2007, Valery Meshkov wrote: The following bug has been logged online: Bug reference: 3106 PostgreSQL version: 8.2.3 Description:A problem with escaping table name pattern for DatabaseMetaData.getColumns() Details: I am seeing a problem in the JDBC driver postgresql-8.2-504.jdbc3.jar with getting columns of the table 'A_B'. I am escaping '_' with the escape value returned by DatabaseMetaData.getSearchStringEscape(), which in my case is (standard_conforming_strings is off). When I pass the resulting table name 'A\\_B' to DatabaseMetaData.getColumns() the number of backslashes doubles again, resulting in 4 backslashes in the select statement: The problem is that there is a different search string escape depending on whether you plan to interpolate it into a query or pass it as a parameter to a PreparedStatement. The getSearchStringEscape method is assuming you're going to interpolate it into a query and returns the doubled version. getColumns is assuming you're passing a parameter that it then interpolates and must escape itself. The fact that the javadoc for getColumns has a see also for getSearchStringEscape implies to me that our implementation is wrong and it shouldn't return the doubled version and anyone interpolating text into a query must escape it appropriately including the search string escape. I'll put a fix for this into the next release. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2856: Jdbc 4 connector running on JDK 1.6 should
On Fri, 5 Jan 2007, Steve Langridge wrote: At the present time, I am not really interested in actually using the client info data at all - I really just need the getClientInfo() and setClientInfo() to rather not raise a not-implemented exception. With the JDBC 3 driver under Java 1.5, there were no problems, so I presume these methods were implemented - it is now a real hassle since when running under Java 1.6, one has to use the JDBC 4 driver. These methods are new in JDBC 4 which is why it wasn't a problem with JDBC 3. I understand that all you want is not to error, but I don't think that's appropriate behavior for all users. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2856: Jdbc 4 connector running on JDK 1.6 should
On Fri, 22 Dec 2006, Steve Langridge wrote: Bug reference: 2856 PostgreSQL version: 8.2 Description:Jdbc 4 connector running on JDK 1.6 should not raise exception for getClientInfo/setClientInfo Details: When using the Jdbc 4 driver running on Java 1.6, it would be preferable if the methods getClientInfo() and setClientInfo() returned silently, instead of raising an exception about not being implemented. When running on Glassfish with Toplink Essentials, the app server log gets filled with these exceptions for every access from a JDBC connection pool (when using the PGConnectionPoolDataSource). I'm not sure it's legal to simply ignore the fact that the method is not implemented. I think at minimum we'd need to keep the client info data in the Connection object even if we don't send it to the database. This would make it look like it was working to the JVM there just wouldn't be a way to get at it on the database side. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] multiple SRFs in SELECT clause.
I'm getting some odd results when running two generate_series calls in a SELECT. When the two calls return the same number of rows you get that many rows out: # SELECT generate_series(1,3), generate_series(1,3); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 (3 rows) When the row counts differ you get the least common multiple number of rows. # SELECT generate_series(1,4), generate_series(1,2); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 1 4 | 2 (4 rows) I was personally expecting a cross join between them that would be equivalent to # SELECT * FROM generate_series(1,4) a, generate_series(1,2) b; a | b ---+--- 1 | 1 1 | 2 2 | 1 2 | 2 3 | 1 3 | 2 4 | 1 4 | 2 (8 rows) Tested on 8.1.3 and CVS HEAD. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2628: jdbc2
On Thu, 14 Sep 2006, viniciusasousa wrote: The following bug has been logged online: Bug reference: 2628 PostgreSQL version: 8.0 Description:jdbc2 Details: I has problem: org.postgresql.jdbc2.Jdbc2DatabaseMetaData.locatorsUpdateCopy()Z locatorsUpdateCopy is not part of the JDBC2 spec, but was added in JDBC3. So you'll need to download the JDBC3 version. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2636: JDBC error behaviour incorrect
On Mon, 18 Sep 2006, Kiren Pillay wrote: The following bug has been logged online: Bug reference: 2636 PostgreSQL version: 8.1.4 Operating system: Windows Description:JDBC error behaviour incorrect Details: try { DBActions db=DBActions.getInstance(); rs = db.getConnection().prepareCall(loadApplicationParameters ).executeQuery(); while (rs.next()) { // Testing values.put(rs.getString(1), rs.getString(2)); System.out.println(rs.getString(1)+,+ rs.getString(2)); } } catch (SQLException sqle) { logger.error(Could not load application parameters , sqle); } catch (Exception e){ e.printStackTrace(); } catch (Throwable e){ e.printStackTrace(); } /*finally { DBConnection.close(rs, ps); }*/ In this code, the program gets to the executeQuery part with no exception. When it gets to the rs.next() part, the code jumps to the finally block. This is non-deterministic behaviour as the code seems to have thrown an exception, but even the catch Throwable part does not catch the error. Isn't it just likely that rs.next() returned false? The database being connected to is a restore from another database. The fact is this database restore could be incorrect, but how can I find the actual error cause of the problem. Is there a way to increase the logging so that I can figure out the problem here. The driver can be started given a URL connection parameter ?loglevel=2 that will print out a fair amount of protocol information. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2593: Improper implimentation of SQLException
On Sun, 27 Aug 2006, Igor Urisman wrote: The following bug has been logged online: Bug reference: 2593 PostgreSQL version: 8.1.3 Description:Improper implimentation of SQLException Details: The vendor error code is returned in getSQLState(), instead of getError(). At least this is the case for 23505 (ERROR: duplicate key violates unique constraint ...) Postgresql does not have specific vendor error codes and always returns 0 for SQLException.getErrorCode(). Postgresql uses standard SQL State values only. These often have characters in them so they cannot also be returned as the vendor error code. Perhaps the problem is that this page is referring to error codes when a more careful reading shows that it is really talking about sql state values? http://www.postgresql.org/docs/current/static/errcodes-appendix.html Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2559: why jdbc-driver AbstractJdbc2ResultSetMetaData.
On Tue, 1 Aug 2006, flash.xu wrote: Bug reference: 2559 PostgreSQL version: 8.1.4 Description:why jdbc-driver AbstractJdbc2ResultSetMetaData. getTableName(int column) return Details: I'm using jdbc3 8.1 to build my java project.But when I use ResultSetMetaData class to get table'name,the result is ! I check the jdbc8 8.1's source code,and found the AbstractJdbc2ResultSetMetaData. getTableName(int column) return . Instead, the method of getBaseTableName() return the real table'name. Similar,the method ofgetColumnName()return the column'lable,but the method ofgetColumnName() return the real column'name. please show why?thanks. My project has been used Oracle and Mysql.It's my first to use PostgreSQL. Please see the thread Wrong column names in ResultSetMetaData http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#8 Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] The column index is out of range in PostgreSQL + resolve
On Mon, 31 Jul 2006, Murali Doss wrote: SQL Query is running perfect from PG Admin but it gives error The column index is out of range when running through java application.Please advise me how to solve this issue. This error message has nothing to do with the query execution, but rather the fetching of results from the ResultSet. I imagine you are using one of the ResultSet.getXXX() methods using an improper column index. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2514: (jdbc driver) Multiple inlined statements with
On Wed, 5 Jul 2006, Gilles wrote: Bug reference: 2514 Description:(jdbc driver) Multiple inlined statements with mixed updates and queries return wrong results Details: When issuing this: statement.execute(SELECT 2;UPDATE foo SET d='babar' where i=1; SELECT 3); The 2 result returned is wrong. This seems to be a ResultSet containing the same values as the first RS (it should be an update count) Fixed in CVS for 8.0, 8.1 and 8.2dev. It was mistakenly retaining some state from the first SELECT when issuing the update. I've put up a temporary jar file here if you need a fix before the next official releases. http://www.ejurka.com/pgsql/jars/gr/ Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] Fwd: [JDBC] Diffrence between 8.0.3 and 8.1.3
On Wed, 5 Jul 2006, Yann PICHOT wrote: When i use this SQL request : SELECT col1, cast(' ' as varchar(3)) as mycol FROM mytable, getColumnDisplaySize return -5. When i use this SQL request : SELECT col1, cast(' ' as varchar(3)) as mycol, getColumnDisplaySize return 3 OK, I misunderstood your test case and was only looking at the second version which works. As you noted earlier the server is responding with a different typmod value, so this is not a JDBC problem, but a server regression and I've forwarded this on to the -bugs list. To summarize for them: In 8.0 SELECT ' '::varchar(3) FROM tab retained the typmod value specified in the query, but in 8.1 it is lost and -1 is used instead. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2084: Add WIN1253 to CLIENT_ENCODING encodings
On Wed, 14 Jun 2006, Bruce Momjian wrote: Added to TODO list. Already done. http://archives.postgresql.org/pgsql-committers/2006-02/msg00299.php Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2420: NetBSD doesn't need float8-small-is-zero regression
Bruce Momjian wrote: Thanks for the feedback. Patch attached, so NetBSD = 3.0 will not use float8-small-is-zero. The buildfarm seems to think this is a bad idea. http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=gazelledt=2006-05-05%2016:30:00 Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2297: plpgsql function causes disconnect sometimes
On Thu, 2 Mar 2006, bernd wrote: Bug reference: 2297 PostgreSQL version: 8.1.3 Description:plpgsql function causes disconnect sometimes I'm trying to make some admin functions that I can use to revoke privileges from users. The functions are all stored in a schema called admin. Here are the functions: I reported this here: http://archives.postgresql.org/pgsql-bugs/2006-03/msg6.php and it was fixed here: http://archives.postgresql.org/pgsql-committers/2006-03/msg00021.php This will be in 8.1.4 when released. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] plpgsql functions crash cvs
eKol in #postgresql reported a problem with a plpgsql function crashing the server. I tested the attached against 8.2cvs as of this morning and got this stacktrace: #0 plpgsql_xact_cb (event=XACT_EVENT_COMMIT, arg=0x0) at pl_exec.c:4521 #1 0x0046f43d in CallXactCallbacks (event=XACT_EVENT_COMMIT) at xact.c:2618 #2 0x0047138b in CommitTransaction () at xact.c:1534 #3 0x00472be7 in CommitTransactionCommand () at xact.c:2184 #4 0x0058ecde in finish_xact_command () at postgres.c:2017 #5 0x00590475 in exec_simple_query ( query_string=0x8f8f28 select admin.fn_revoke_all('public');) at postgres.c:1041 #6 0x005917ab in PostgresMain (argc=4, argv=0x8bb360, username=0x8bb320 jurka) at postgres.c:3231 #7 0x00566a18 in ServerLoop () at postmaster.c:2917 #8 0x00567861 in PostmasterMain (argc=3, argv=0x89b830) at postmaster.c:980 #9 0x005250de in main (argc=3, argv=0x3e8) at main.c:254 Kris JurkaDROP SCHEMA admin CASCADE; CREATE SCHEMA admin; create or replace function admin.fn_show_functions(text) returns setof text as $$ declare v_schema alias for $1; v_schema_oid oid; v_function pg_catalog.pg_proc%rowtype; v_function_arg text; v_function_name_and_args text; begin select into v_schema_oid oid from pg_catalog.pg_namespace where nspname = v_schema; if found then for v_function in select * from pg_catalog.pg_proc where pronamespace = v_schema_oid loop v_function_name_and_args := v_function.proname || '('; for i in 0..(v_function.pronargs - 1) loop select into v_function_arg typname from pg_catalog.pg_type where oid = v_function.proargtypes[i]; if v_function_arg is not null then v_function_name_and_args := v_function_name_and_args || v_function_arg || ', '; end if; end loop; v_function_name_and_args := trim(trailing ', ' from v_function_name_and_args); v_function_name_and_args := v_function_name_and_args || ')'; return next v_function_name_and_args; end loop; end if; return; end; $$ language plpgsql; create or replace function admin.fn_revoke_all(text) returns void as $$ declare v_user alias for $1; v_schema record; v_obj record; v_current_db text; begin -- Second, revoke on functions, tables, and views from -- user schemas. for v_schema in select nspname AS name FROM pg_namespace WHERE nspname NOT LIKE 'pg%' AND nspname NOT LIKE 'info%' loop perform admin.fn_revoke_all_functions_from(v_user, v_schema.name); end loop; end; $$ language plpgsql; create or replace function admin.fn_revoke_all_functions_from(text, text) returns void as $$ declare v_user alias for $1; v_schema alias for $2; v_obj record; begin for v_obj in select * from admin.fn_show_functions(v_schema) as name loop --raise notice 'revoking function %', v_obj.name; execute 'revoke all on function ' || quote_ident(v_schema) || '.' || replace(v_obj.name, '(', '(') || ' from ' || quote_ident(v_user); end loop; end; $$ language plpgsql; SELECT admin.fn_revoke_all('public'); -- Sometimes doesn't crash on the first attempt SELECT admin.fn_revoke_all('public'); SELECT admin.fn_revoke_all('public'); SELECT admin.fn_revoke_all('public'); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2250: JSTL parameterized queries inserting numeric
On Thu, 9 Feb 2006, Ian Moore wrote: The following bug has been logged online: Bug reference: 2250 PostgreSQL version: 8.0.3 Description:JSTL parameterized queries inserting numeric values Details: When using the JSTL via JDBC, there is the option to write INSERT/UPDATE statements with parameters in JSP's. There is only two types the data parameters can be, a date or a character varying string. In most databases, if a character varying string is provided that contains a numeric value, and this is used to insert/update a numeric field, the driver will attempt a type conversion to the numeric value of the string and report errors only if the string is not a valid . However trying this in JSTL, which only provides support for text or date parameters results in the following error: ERROR: column is of type integer but expression is of type character varying Your options are to rewrite your query like: INSERT INTO mytable (intcol) VALUES (CAST(? AS int)) or you may use the 8.2dev driver and add ?stringtype=unspecified to your URL. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2249: unsupported frontend protocol
On Thu, 9 Feb 2006, Satheesh wrote: The following bug has been logged online: Bug reference: 2249 PostgreSQL version: 7.3.2 Description:unsupported frontend protocol Details: iam having database server postgres version 7.3.2 and jboss 4.x as webserver, while executing the application iam getting an error message of FATAL:Unsupported Front end Protocol. This makes my application to execute slowly. plz help me. Tahnks in Advance When using a new JDBC driver with an old server version it first tries to connect with the v3 protocol and then falls back to the v2 protocol if that fails. To tell the driver you want a v2 protocol from the start add ?protocolVersion=2 to your URL. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] database level client_encoding setting check
When setting the default client_encoding for another database the validity of the conversion is checked using the current database's server encoding, not the targets. jurka=# create database utf8 with encoding='utf8'; CREATE DATABASE jurka=# create database win1251 with encoding='win1251'; CREATE DATABASE jurka=# \c utf8 You are now connected to database utf8. utf8=# alter database win1251 set client_encoding to 'mule_internal'; ERROR: conversion between mule_internal and UTF8 is not supported utf8=# alter database win1251 set client_encoding to 'latin1'; ALTER DATABASE utf8=# \c win1251 FATAL: conversion between LATIN1 and WIN1251 is not supported Previous connection kept Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2220: PostgreSQL-JDBC 8.1-404 fails to compile with
On Sun, 29 Jan 2006, Bernhard Rosenkraenzer wrote: Bug reference: 2220 Description:PostgreSQL-JDBC 8.1-404 fails to compile with ecj Details: PostgreSQL-JDBC 8.1-404 fails to compile (ecj bytecode compiler w/ gij 4.1 JDK): This is because the driver dynamically decides what version of the driver to build (JDBC2, JDBC3, or JDBC3 w/ generics) at runtime based on the current JVM. You are running ant with a 1.5 JVM, but ecj's default -source argument is 1.4, so it's trying to build 1.5 code, but interpreting it as 1.4 code which cleary fails. Adding source=1.5 in the javac tag in build.xml fixes this, but that's not a general solution because it clearly won't be able to build other versions. Do you have a better solution for runtime determination or specification of the version we should try to build. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] date overflows
On Sat, 3 Dec 2005, Michael Fuhr wrote: On Sat, Dec 03, 2005 at 07:53:23PM -0500, Kris Jurka wrote: I'm seeing some date input overflows here. Yep, I noticed this a few days ago while looking at another problem. I probably should have started a new thread. This seems to fix it. Kris JurkaIndex: doc/src/sgml/datatype.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/datatype.sgml,v retrieving revision 1.163 diff -c -r1.163 datatype.sgml *** doc/src/sgml/datatype.sgml 22 Oct 2005 19:33:57 - 1.163 --- doc/src/sgml/datatype.sgml 5 Dec 2005 08:30:22 - *** *** 1360,1366 entry4 bytes/entry entrydates only/entry entry4713 BC/entry ! entry32767 AD/entry entry1 day/entry /row row --- 1360,1366 entry4 bytes/entry entrydates only/entry entry4713 BC/entry ! entry5874897 AD/entry entry1 day/entry /row row Index: src/backend/utils/adt/date.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/date.c,v retrieving revision 1.122 diff -c -r1.122 date.c *** src/backend/utils/adt/date.c15 Oct 2005 02:49:28 - 1.122 --- src/backend/utils/adt/date.c5 Dec 2005 08:30:22 - *** *** 97,102 --- 97,107 break; } + if (!IS_VALID_JULIAN(tm-tm_year, tm-tm_mon, tm-tm_mday)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), +errmsg(date out of range: \%s\, str))); + date = date2j(tm-tm_year, tm-tm_mon, tm-tm_mday) - POSTGRES_EPOCH_JDATE; PG_RETURN_DATEADT(date); ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] date overflows
I'm seeing some date input overflows here. I tested on CVS HEAD without --enable-integer-datetimes and 7.4.9 and 8.0.4 with --enable-integer-datetimes, so it appears to have been around for a while: jurka=# select '23456-01-01'::date; date --- 5290466-07-05 (1 row) jurka=# select '14824-01-01 BC'::date; date 11744398-01-21 (1 row) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2060: Issue with Data base connection
kalisetty manideep wrote: [Backend reports too many open connections] But I am 100% sure that its not the issue with the code. Do you know any Postgresql - JDBC driver, which is not from Postgresql development group. I think JDBC driver is not closing the connection even though I am closing the connection. I have no reason to believe that and you certainly haven't shown that. Please refer to the DBACCESS file attached. All this shows is the Connection being opened, not closed. I've implemented an idea I got from rupa in #postgresql a while ago about a way of logging leaked connections. I've added some code to the Connection's finalize() method that prints some logging information if the Connection was not closed. This is enabled by using the new logUnclosedConnections URL parameter. See the attached code for an example. I've put up a new jar that contains this here: http://www.ejurka.com/pgsql/jars/km import java.sql.*; public class OpenConn { public static void main(String args[]) throws Exception { Class.forName(org.postgresql.Driver); leakConnection(); System.gc(); } private static void leakConnection() throws Exception { Connection conn = DriverManager.getConnection(jdbc:postgresql://localhost:5810/jurka?logUnclosedConnections=true,jurka,); } } ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2060: Issue with Data base connection
On Mon, 21 Nov 2005, Manideep Kalisetty wrote: Bug reference: 2060 Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2.2 Description:Issue with Data base connection Details: I am using postgresql-8.1dev-402.jdbc3.jar JDBC driver and websphere application server. Even though I am successfully closing connections, the error message I am receivng is Backend start-up failed: FATAL 1: Sorry, too many clients already. Please refer to the Exception report below: Well, something is leaving connections open. Checking the results of SELECT * FROM pg_stat_activity; will tell you what database and username they are connected to which may help. While this code doesn't appear to be using a connection pool, perhaps you have another application that is using a pool that is configured to open too many connections? Still, the most likely cause is a coding error failing to close connections. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2036: 8.1 JDBC busted date with INTERVAL update
On Fri, 11 Nov 2005, Reuben Pasquini wrote: Bug reference: 2036 Description:8.1 JDBC busted date with INTERVAL update Details: Something like this would work with the postgres-7.4 jdbc3.jar driver, but fails with the shown error with postgres-8.1 jdbc3.jar driver. Running with a non-prepared statement works with 8.1. I'm running 8.1 server on linux, jdk1.5. Not sure if this is a bug, or intentional. s_query = UPDATE cue.proc SET dt_started = now() - INTERVAL ? WHERE pk=5; x_stmt = x_conn.prepareStatement ( s_query ); x_stmt.setString ( 1, 0 seconds ); This is a known limitation of server side prepared statements (which were added in the 8.0 JDBC driver). You may not use the syntax INTERVAL ?, but must instead use CAST(? AS INTERVAL) or ?::interval. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] [JDBC] BUG #1780: JDBC driver setNull throws for BLOB and CLOB
On Mon, 1 Aug 2005, Andrus Adamchik wrote: Just tested it with build 312 and the original problem went away. Now I am seeing a new problem with null BLOBs only: java.sql.SQLException: ERROR: column blob_col is of type bytea but expression is of type oid bytea is not the correct type to use when storing Blobs. You need to use oid as the underlying type, or if you want to use bytea you need to use setBytes or setNull(x, Types.BINARY). Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1780: JDBC driver setNull throws for BLOB and CLOB
On Fri, 22 Jul 2005, Andrus Adamchik wrote: Bug reference: 1780 PostgreSQL version: 8.0.1 Description:JDBC driver setNull throws for BLOB and CLOB columns Details: Whenver I call PreparedStatement.setNull(int, int) on BLOB or CLOB columns, an exception below occurs. Driver version: postgresql-8.0-310.jdbc3.jar. But looks like latest CVS version has the same problem. This has been fixed in CVS for the 8.0 and development drivers, we just haven't done a release in nearly three months. I'll put out a release this weekend unless there are any other outstanding fixes I'm unaware of. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [JDBC] [BUGS] BUG #1780: JDBC driver setNull throws for BLOB
On Fri, 22 Jul 2005, Kris Jurka wrote: On Fri, 22 Jul 2005, Andrus Adamchik wrote: Whenver I call PreparedStatement.setNull(int, int) on BLOB or CLOB columns, an exception below occurs. Driver version: postgresql-8.0-310.jdbc3.jar. But looks like latest CVS version has the same problem. This has been fixed in CVS for the 8.0 and development drivers, we just haven't done a release in nearly three months. I'll put out a release this weekend unless there are any other outstanding fixes I'm unaware of. Actually looking more closely, this was fixed in build 311 which is available, but we should still put out a new release for other fixes. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1721: mutiple bytes character string comaprison
On Sun, 19 Jun 2005, Tom Lane wrote: Chii-Tung Liu [EMAIL PROTECTED] writes: PostgreSQL version: 8.0.3 Operating system: Windows XP SP2 When compare two UTF-8 encoded string that contains Chinese words, the result is always TRUE Sorry, but UTF-8 encoding doesn't work properly on Windows (yet). Use some other database encoding. Shouldn't we forbid its creation then? At least a strongly worded warning? We see these complaints too often. Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[BUGS] plpython regression test leaves /tmp/plpython file
The plpython regression test leaves a file /tmp/plpython around after the tests have completed running. This causes a subsequent failure if the tests are run with another user running the database because a permission denied error will be returned when trying to write the /tmp/plpython file. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1661: JDBC DatabaseMetaData.getExportedKeys() returns
On Wed, 11 May 2005, Pavel Krupets wrote: Bug reference: 1661 PostgreSQL version: 8.0.2 Description:JDBC DatabaseMetaData.getExportedKeys() returns invalid keys. If I ask getExportedKeys(null, bc4jgen_test_04, table_03) to return exported foreign keys it will return 'fk_table_05_2_table_03' key. But this key isn't exported. table_05 does not references table_03's primary key, just a unique one! Please check javadocs excerpts below. I guess we consider this a feature, not a bug. Otherwise there is no way to retrieve foreign key information for foreign keys to unique constraints. If you've got a concrete reason why this is a bad thing please let us know. The javadoc does indeed say primary key, but I don't see why including unique ones as well is a problem. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1656: ResultSetMetaData.getColumnDisplaySize()
On Tue, 10 May 2005, Sergio Lob wrote: Bug reference: 1656 PostgreSQL version: 8.0.1 Description:ResultSetMetaData.getColumnDisplaySize() returns value -1 Details: ResultSetMetaData.getColumnDisplaySize() returns value -1 for several columns of answer set generated by DatabaseMetaData.getTables() method. ResultSetMetaData.getColumnDisplaySize() returns -1 for any column that has an unknown size. Consider the REMARKS column of getTables(). This data comes from the system's pg_description.description column which is of type text. There is no defined length for this, so we have decided to return -1. Additionally, I don't understand why the data type of column 1 is described as OTHER, as opposed to all the other columns, which are of type VARCHAR (attained through ResultSetMetaData.getColumnType() method). TABLE_CAT is generated by an untyped SELECT NULL, we should probably cast this to name, or report the currently connected database name here. Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] set returning function
On Mon, 9 May 2005, Dennis Bjorklund wrote: [ I can call sql or C SRFs without FROM, but not plpgsql.] Trying this in pltcl (while knowing nothing about tcl and the docs not mentioning any srf support) shows: CREATE FUNCTION tclset() RETURNS SETOF int AS 'return 0' LANGUAGE pltcl; SELECT * FROM tclset(); -- works SELECT tclset(); -- goes into an infinite loop Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1640: Using JDBC, multiple statements do not return
On Fri, 29 Apr 2005, Kevin Self wrote: Bug reference: 1640 PostgreSQL version: 8.0.2 Description:Using JDBC, multiple statements do not return results if select follows insert,delete or update When attempting to perform a multi-statement command through the JDBC Statement.execute() or the Statement.executeQuery() function, the database does not return any result set if the select statement follows an insert, update or delete. This ability is required for atomic record inserts for auto-generated keys that must be returned to the caller, since postgreSQL does not support RETURN_GENERATED_KEYS as an option, and calling the sequence ahead of time to obtain the value is not possible. There is no atomicity from issuing them in the same query. This is guaranteed by the sequence for the entire session. If you do what to do this it should be written as: Statement.execute(INSERT ...; SELECT currval('myseq')); Statement.getMoreResults(); ResultSet rs = Statement.getResultSet(); The javadoc for execute() says it should return true if the first result is a ResultSet object the first result is an update count, not a ResultSet. You need to move to the next result, which is actually a ResultSet. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] V2 protocol - 8.1 server
With the promised files this time. On Sat, 23 Apr 2005, Kris Jurka wrote: The 8.1 server doesn't work when issuing queries using the v2 protocol, notably the isnull bitmask in the DataRow message is incorrect. I suspect the problem is with this commit: http://archives.postgresql.org/pgsql-committers/2005-03/msg00224.php I've attached the tcpdump output from establishing a connection with the JDBC driver using the V2 protocol to a 7.4 and an 8.1 server. In the 7.4 server after the 'D' message is c0 while 8.1 has 00 as the next byte. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend 22:20:05.430250 IP localhost.51539 localhost.postgresql: S 2034505391:2034505391(0) win 32767 mss 16396,sackOK,timestamp 3743595316 0,nop,wscale 2 0x: 4500 003c ad9b 4000 4006 8f1e 7f00 0001 E..[EMAIL PROTECTED]@... 0x0010: 7f00 0001 c953 1538 7944 16af .S.8yD.. 0x0020: a002 7fff 86d4 0204 400c 0402 080a [EMAIL PROTECTED] 0x0030: df22 bb34 0103 0302..4 22:20:05.430304 IP localhost.postgresql localhost.51539: S 2038983743:2038983743(0) ack 2034505392 win 32767 mss 16396,sackOK,timestamp 3743595316 3743595316,nop,wscale 2 0x: 4500 003c 4000 4006 3cba 7f00 0001 E..[EMAIL PROTECTED]@.. 0x0010: 7f00 0001 1538 c953 7988 6c3f 7944 16b0 .8.Sy.l?yD.. 0x0020: a012 7fff 06a4 0204 400c 0402 080a [EMAIL PROTECTED] 0x0030: df22 bb34 df22 bb34 0103 0302..4..4 22:20:05.430345 IP localhost.51539 localhost.postgresql: . ack 1 win 8192 nop,nop,timestamp 3743595316 3743595316 0x: 4500 0034 ad9d 4000 4006 8f24 7f00 0001 [EMAIL PROTECTED]@..$ 0x0010: 7f00 0001 c953 1538 7944 16b0 7988 6c40 .S.8yD..y.l@ 0x0020: 8010 2000 cfc2 0101 080a df22 bb34 ..4 0x0030: df22 bb34..4 22:20:05.457987 IP localhost.51539 localhost.postgresql: P 1:297(296) ack 1 win 8192 nop,nop,timestamp 3743595344 3743595316 0x: 4500 015c ad9f 4000 4006 8dfa 7f00 0001 [EMAIL PROTECTED]@... 0x0010: 7f00 0001 c953 1538 7944 16b0 7988 6c40 .S.8yD..y.l@ 0x0020: 8018 2000 ff50 0101 080a df22 bb50 .PP 0x0030: df22 bb34 0128 0002 6a75 726b ..4...(jurk 0x0040: 6100 a... 0x0050: 0x0060: 0x0070: 6a75 726b jurk 0x0080: 6100 a... 0x0090: 0x00a0: 0x00b0: 0x00c0: 0x00d0: 0x00e0: 0x00f0: 0x0100: 0x0110: 0x0120: 0x0130: 0x0140: 0x0150: 22:20:05.458485 IP localhost.postgresql localhost.51539: . ack 297 win 8192 nop,nop,timestamp 3743595344 3743595344 0x: 4500 0034 d774 4000 4006 654d 7f00 0001 [EMAIL PROTECTED]@.eM 0x0010: 7f00 0001 1538 c953 7988 6c40 7944 17d8 [EMAIL PROTECTED] 0x0020: 8010 2000 ce62 0101 080a df22 bb50 .bP 0x0030: df22 bb50..P 22:20:05.462500 IP localhost.postgresql localhost.51539: P 1:16(15) ack 297 win 8192 nop,nop,timestamp 3743595348 3743595344 0x: 4500 0043 d776 4000 4006 653c 7f00 0001 [EMAIL PROTECTED]@.e 0x0010: 7f00 0001 1538 c953 7988 6c40 7944 17d8 [EMAIL PROTECTED] 0x0020: 8018 2000 fe37 0101 080a df22 bb54 .7T 0x0030: df22 bb50 5200 004b 732f 371a ..PRK..s/7. 0x0040: 0f42 5a .BZ 22:20:05.462556 IP localhost.51539 localhost.postgresql: . ack 16 win 8192 nop,nop,timestamp 3743595348 3743595348 0x: 4500 0034 ada1 4000 4006 8f20 7f00 0001 [EMAIL PROTECTED]@... 0x0010: 7f00 0001 c953 1538 7944 17d8 7988 6c4f
Re: [BUGS] jdbc driver return wrong medata values
On Thu, 14 Apr 2005, Xavier Maysonnave wrote: postgresql-8.0-311.jdbc3.jar While analysing the index structures of a metadata table with the following statement : ResultSet parts = dbMeta.getIndexInfo(aCatalog, aMetaSchema, aTable, false, false); ... if (parts != null) { try { while (parts.next()) { String indexName = parts.getString(6); String columnName = parts.getString(9); short position= parts.getShort(8); String direction = parts.getString(10); boolean unique = parts.getBoolean(4); } } finally { parts.close(); } } While controling the result with pgadmin III it appears that : - Unique Index have a returned boolean unique set to false : - this value should apply to Non Unique Index - Non Unique Index have a returned boolean unique set to true : - this value should apply to Unique Index If you check the javadoc for getIndexInfo you will see column four is actually NON_UNIQUE not unique. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1525: wrong time when getting timestamp from date
On Fri, 4 Mar 2005, François LECOMTE wrote: Bug reference: 1525 PostgreSQL version: 8.0.1 Description:wrong time when getting timestamp from date field Problem when getting a Java Timestamp object on a DATE database field. With driver pg74jdbc3.jar : hours, minutes, seconds and millis have zero values (ex : 2003-09-10 00:00:00.0) in the Timestamp object, no problem. With driver postgresql-8.0-310.jdbc3.jar (same database, same table, same record) : we get 1 hour and zero minutes, seconds and millis ! (2003-09-10 01:00:00.0) Maybe UTC time ? but suerly a bug... There is a known bug in the 8.0 jdbc driver when the client and server are in different timezones. Is that the case here? Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org