Re: [GENERAL] Run Vacuum Through JDBC
On Mon, 10 May 2010, Yan Cheng CHEOK wrote: I was wondering, how can I check whether Vacuum operation had been executed without problem? final Statement st2 = connection.createStatement(); st2.executeUpdate(VACUUM FULL ANALYZE VERBOSE); st2.close(); Nothing print out at console. The results of the vacuum are available in Statement.getWarnings(). Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] helo
On Mon, 22 Feb 2010, beulah prasanthi wrote: Helo I am working on spring project with postgres 8.4 i wrote a function in postgrees which i am passing the argument email email[] array From front end we need to insesrt data into that emailarray .so i used java.arraylist.util while i am running i got the following error Please help me error: org.postgresql.util.PSQLException: Cannot cast an instance of java.util.ArrayList to type Types.ARRAY You need to pass an instance of java.sql.Array, not a Collection or something else that is array like. Also please only report an issue once rather than a half dozen times. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] libpq port number handling
On Thu, 24 Sep 2009, Tom Lane wrote: Sam Mason s...@samason.me.uk writes: + if (portnum 1 || portnum 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. Restricting the target port seems like a bad idea. What about a firewall (or ssh tunnel) that did port forwarding. What PG binds to and what a client connects to may not be the same thing. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Compiling Pl/Java
On Mon, 22 Jun 2009, Jorge Vidal - Disytel wrote: I've been trying to compile pl/java from cvs with no success. Is it going to be avaiable for pg 8.4 ? pl/java CVS builds against 8.4. A common gotcha is that pljava will only build with JDK 1.4 or 1.5 and fails with 1.6. It can be run with a 1.6 JVM, just not built with it. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed
David Fetter wrote: If you have access to a compiler but not CVS or git, you can get one of the daily tarballs. Are you *sure* you can't use CVS or git, though? The problem is pljava, not postgresql. pljava doesn't have a daily tarball or a git repo, so CVS is the only option at the moment. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed
Grzegorz Jaśkiewicz wrote: On Fri, May 29, 2009 at 1:19 AM, Kris Jurka bo...@ejurka.com wrote: To build against 8.4 you need pljava from CVS. Also pljava can only be built with the 1.4 or 1.5 JDK, not with the 1.6 version you are using. is it a lot of work to make it 1.6 friendly ? can I help? It depends how it's done. It would be easy to make pljava require 1.6 and just add stubs that throw unimplemented exceptions for the new methods. Building against both 1.4/1.5 (JDBC 3) and 1.6 (JDBC 4) would require a more complicated conditional compilation system like that provided with the standard JDBC driver. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed
Grzegorz Jaśkiewicz wrote: another question, what about tmdb ? it requires java6, so I assumed that jdbc is 1.6 friendly odd. I have no idea what tmdb is. JDK 1.6 includes the JDBC 4 API while 1.4 and 1.5 include the JDBC 3 API. So building pljava doesn't implement all of the JDBC 4 API and can't be built with JDK 1.6. It will run under a 1.6 JVM as long as you don't use methods that are new in JDBC 4. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed
Craig Ringer wrote: Perhaps a stupid question, but isn't the `-source' parameter to javac intended to mask new features and such for just the purpose of compiling older sources on a new JDK? The -source argument only controls language features, not interface/class definitions. java.sql.* provides interfaces that drivers must implement. When they add new functions to those interfaces you have to implement those, otherwise the compile fails saying that the class doesn't implement the specified interface. Sometimes you can implement new interface functions so that the code can compile in either version, but not always. Consider the following two cases: 1) JDBC4 has added a new method to java.sql.Array named void free(). This can be implemented for JDBC3 and there's no problem that the JDBC3 class implements an additional method that's only required by JDBC4. The code can be compiled against either JDK version. 2) JDBC4 has added a new interface, java.sql.SQLXML, and added methods to java.sql.ResultSet to retrieve SQLXML objects. You can't add a method SQLXML getSQLXML(int) to your JDBC3 class because it doesn't know anything about SQLXML at all because it isn't defined by JDBC3. For this we must add code that is only compiled if we're building with a JDK that has JDBC4. The JDBC driver does this by defining a hierarchy: AbstractJdbc2ResultSet |__Jdbc2ResultSet |__AbstractJdbc3ResultSet |__Jdbc3ResultSet |__AbstractJdbc4ResultSet |__Jdbc4ResultSet AbstractJdbc4ResultSet will have the getSQLXML method and the JdbcXResultSet classes will be just stubs that officially implement the java.sql.ResultSet interface. If we're building a JDBC3 driver we'll compile AbstractJdbc2ResultSet, AbstractJdbc3ResultSet, and Jdbc3ResultSet and when asked for a ResultSet instance we'll return a Jdbc3ResultSet. When building a JDBC4 driver we'll then exclude Jdbc3ResultSet and compile AbstractJdbc4ResultSet and Jdbc4ResultSet and when asked for ResultSet instance we'll return a Jdbc4ResultSet. By using this inheritance we can conditionally compile entire classes which is easy to do with ant rather than trying to implement something like #ifdef which is ugly to do with ant's copy with filtering task. Unfortunately pljava currently has no such infrastructure. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed
On Wed, 27 May 2009, Emanuel Calvo Franco wrote: Hi community, I'm trying to compile pl/java sources for 8.4 beta1 (for a test) but it gives me 20 errors at the end: To build against 8.4 you need pljava from CVS. Also pljava can only be built with the 1.4 or 1.5 JDK, not with the 1.6 version you are using. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A question about RAISE NOTICE
On Thu, 7 May 2009, Craig Ringer wrote: Most programs will not be aware of notice messages. Unlike error messages there's no provision for them in standard JDBC/ODBC/etc APIs, and you have to specifically ask the client driver for them. Unless your app is aware of PostgreSQL specifically, it won't be doing that. The JDBC API has a SQLWarning that we map notice messages to and are understood by client tools. http://java.sun.com/javase/6/docs/api/java/sql/SQLWarning.html Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC and setting statement_timeout
On Mon, 3 Nov 2008, Jason Long wrote: *Would someone please comment on the status of setQueryTimeout in the JDBC driver? Is there any workaround if this is still not implemented?* setQueryTimeout is not implemented, the workaround is to manually issue SET statement_timeout = xxx calls via Statement.execute. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] need help of getting PK after insertRow in JDBC
On Fri, 26 Sep 2008, Chen, Dongdong (GE Healthcare, consultant) wrote: I am a software engineer from GE. I am using JDBC to operate PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My problem is: There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD, EE, AA is primary key and auto-generated type, BB, CC, DD and EE is string type. I want to get the value of AA immediately after insert a row into the table. the code is like this: Statement st = db.creatStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); The JDBC driver does not implement true SCROLL_SENSITIVE cursors. The driver copies the values you provide into its ResultSet and sends the query to the backend. On the server the auto-generated values are filled in, but the JDBC driver doesn't get any notification of that. Perhaps with newer server versions we can make it issue a INSERT ... RETURNING ... so that we get that data. I am not sure it is proper to send this mail to this mail list. Sorry if bring you any inconvenience. Generally cross posting should be avoided and JDBC/Java questions should be directed to the -jdbc list, but this is a very good question and one that does prompt further investigation about what can be done in the JDBC driver to make this better. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
On Wed, 2 Jul 2008, Tom Lane wrote:
Matt Magoffin [EMAIL PROTECTED] writes:
Below is a test case that simulates the use of xpath() within a plpgsql
function in my application.
I'm able to duplicate the memory leak in this function with the current
Fedora 8 libxml2 (2.6.32). The leak is definitely inside libxml2
itself, because the bloat shows up here:
I think this should fix it.
Kris JurkaIndex: src/backend/utils/adt/xml.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.74
diff -c -r1.74 xml.c
*** src/backend/utils/adt/xml.c 12 May 2008 00:00:51 - 1.74
--- src/backend/utils/adt/xml.c 2 Jul 2008 22:22:57 -
***
*** 3160,3165
--- 3160,3166
{
str = xmlXPathCastNodeToString(cur);
result = (xmltype *) cstring_to_text((char *) str);
+ xmlFree(str);
}
return result;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Closed ResultSet error
On Wed, 30 Apr 2008, Bayless Kirtley wrote: I am migrating from another database to PostgreSQL and have run into a serious problem. The Java program runs fine on the other DB but under PostgreSQL it gives the error, ResultSet is closed. I have removed all occurrences of closing any result sets and there are no instances of closing underlying statements within those methods. Finally, I have stepped through the entire operation with a debugger and found no unexpected events. Each time a new result set was created, the debugger indicated it to be a new entity. When finally returning to the original, it appeared to be the correct instance. When the same statement is used to execute a second query, the first ResultSet is closed automatically. I'm guessing that this is what's happening to you and you need to be sure to create a new Statement for each concurrently open ResultSet you have. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC addBatch more efficient?
On Sun, 20 Apr 2008, David Wall wrote: Just checking if the JDBC library's batch processing code is more efficient with respect to the postgresql back end or not. Does it really batch the requests and submit them once over the link, or does it just send them to the database to be processed one at a time? The JDBC driver's batch processing is more efficient than regular execution because it requires fewer network roundtrips so there's less waiting. The JDBC batch is broken into an internal batch size of 256 statement and all of these are sent over to the server at once. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which JDBC version to use with PostgreSQL 8.1.11?
On Thu, 20 Mar 2008, Bj?rn T Johansen wrote: Ok, I just thought since the version says 8.1, 8.2 and 8.3 that this meant that jdbc version 8.1 should be used for db version 8.1, jdbc version 8.2 was meant for db 8.2, and so forth The version numbering is meant to indicate that for a 8.1 database you need at least a 8.1 JDBC driver because the driver usually needs some changes for each new server version, but we maintain compatibility with previous server versions. We maintain the older driver versions because the driver's behavior changes slightly from version to version, so people can upgrade to get bug fixes without new features/behavior changes. So if you're starting a new application with a 8.1 server, there's no reason not to use the 8.3 driver, but if you're already using the 8.1 driver, upgrading to the 8.3 driver without testing is not a good idea. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding ps -ef command column
On Fri, 22 Feb 2008, David Jaquay wrote: In my case, I've got a connection that's hanging around after my code should have closed it, which means almost certainly that I've got problems in my code, but I'd love to be able to get that 57413 number from my jdbc object and write it to my logs to troubleshoot this. Any ideas? The JDBC driver has an option logUnclosedConnections[1] that can be used to find where you've neglected to close things. Any connection that gets cleaned up by the garbage collector logs the stacktrace of its creation, so you can see where it got built from. Kris Jurka [1] http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters ---(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: [GENERAL] [Pljava-dev] pljava.dll - bogus error
On Fri, 1 Feb 2008, James Calfee wrote: Please improve error reporting for this: org.postgresql.util.PSQLException: ERROR: could not load library C:/Program Files/PostgreSQL/8.2/lib/pljava.dll: The specified module could not be found. Unfortunately this error message comes from the server which gets it from windows. So pljava is at least two steps removed from it, so we're in no position to improve it. Perhaps the server guys can coax some more details out of windows. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Pljava-dev] pljava.dll - bogus error
On Fri, 1 Feb 2008, Guy Rouillier wrote: Kris Jurka wrote: Unfortunately this error message comes from the server which gets it from windows. So pljava is at least two steps removed from it, so we're in no position to improve it. Perhaps the server guys can coax some more details out of windows. I looked on archives for both mailing lists and could not find this email thread. This error happened to me years ago and I don't remember what the real issue was. Could you point to the actual problem and I'll see if Windows can identify it? The problem is that pljava.dll depends on jvm.dll, when jvm.dll is not available pljava.dll can't be loaded. That problem is true of any shared library, but it often hits pljava because jvm.dll is in different places for different people. That's not the only potential error though. Consider the latest security releases that adjusted an API that pljava uses. If you try to run a pljava that was built for 8.2.5 against a 8.2.6 server it won't load because of missing symbols. Here's the windows code to open a dll. If there's any way to get more information out of the error message, we'd love to hear it: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/port/dynloader/win32.c?rev=1.9;content-type=text%2Fx-cvsweb-markup 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: [GENERAL] [JDBC] Bug (?) in JDB
On Mon, 21 Jan 2008, Calum wrote: I've noticed that when using Java and postgres-jdbc, setting ssl=false in Properties doesn't get read, and ssl=false and ssl=true both cause SSL to be tried. The current implementation sees the mere presence of ssl as a request for ssl, as the original implementation and docs suggested using ?ssl alone. At various times we've discussed changing this to be more like libpq's try/require options, but that's how it is now. So it's not ideal, but it is working as designed. If you don't want ssl, don't set the property at all. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Prepared Statements
On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote: ah! So it doesn't help if it's the same statement, it has to be the same object! So DBCP has a statement pool like a map, say MapString, PreparedStatement so it can fetch the reference to already existing prepared Statement by looking at the statement itself, right? Exactly. But JDBC itself uses the PREPARE sql command, right? So the statement is not really unnamed its name is unnamed like prepare unnamed as select * from table; execute unnamed; But i can't see any DEALLOCATE statements in my log file. The JDBC driver does not use SQL level PREPARE / DEALLOCATE calls, but instead uses protocol level commands that are pretty much equivalent. The logging process tries to log sql and protocol level commands the same way which is why it shows up that way in the log. Perhaps the server should log the protocol level deallocate as well? What do you mean with longer lifespan? Doesn't the JDBC driver uses the PREPARE Sql Statement and therefore the prepared Statement has the same lifespan as the connection? If so, as connections are pooled and never closed, the prepared Statement will last forever. What if the table analyzes changes and a better execution plan could be found? Data and stats changes do not trigger a replan. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Prepared Statements
On Wed, 9 Jan 2008, [EMAIL PROTECTED] wrote: - I know there is a PREPARE Statement in Postgresql and read the docs. - in PostgresqlJDBC i have a prepareThreshold parameter which i left to default of 5. - in DBCP i have a property poolPreparedStatements, set to true. Does ist just configure prepareThreshold of JDBC or does it maintain a statementPool of it's own? The postgresql JDBC driver does not have a statement pool of its own, so the two options prepareThreshold and poolPreparedStatements are complementary. By itself the JDBC driver will switch to a long term prepared plan once you've used the exact same PreparedStatement object prepareThreshold number of times. Since it doesn't pool things behind the scenes, you've got to retain a reference to the same PreparedStatement object which is difficult or impossible in many applications. This is where the DBCP statement pool comes in. It proxies PreparedStatement wrappers to one underlying PG PreparedStatement object so that you can hit prepareThreshold. In my Log files of postgresql each query is called like this: EXECUTE unnamed [PREPARE: select ...] I have not found anything about preparing unnamed statements. What does it mean? Unnamed statements are what the driver uses before it hits the prepareThreshold limit. Once it has determined the statement will be reused many times it changes to a named statement that has a longer lifespan. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] jdbc lob and postgresql
On Thu, 13 Dec 2007, Thomas Kellerer wrote: Can you point me to the manual for these types of BLOBs are described? The Data Types chapter does not list them as far as I can tell. Apparently the only documentation appears to be in the client interfaces section... http://www.postgresql.org/docs/8.2/static/largeobjects.html The JDBC documentation describes some of the tradeoffs between oid and bytea data types, but doesn't describe the getBlob/setBlob API, only a PG specific version. http://jdbc.postgresql.org/documentation/82/binary-data.html 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: [GENERAL] RETURNING clause: how to specifiy column indexes?
On Thu, 13 Dec 2007, Ken Johanson wrote: Here is the query I will call to the get the name of columns by ordinal position. Do you see any compatibility drivers will older server versions, or other issues? SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position Using pg_catalog tables is better than using information_schema because of the way permissions work. For information_schema you must be the table owner, while people who only have permissions to access a table will most likely be able to read pg_catalog. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
On Wed, 12 Dec 2007, Ken Johanson wrote: Kris, do you have pointers to a spec that says the named-columns should specify the index, or that it should instead be the order in the table? My interpretation from the JDBC spec was that the latter is true, I may be wrong... No, I was actually misremembering what the JDBC spec said, although I think it's an interesting case to consider regardless of any specs. In the case where it is table-order, then I presume in PG that the natural order of the columns (even if reordering is allowed at a alter date) is specified by data in one of the pg_* tables (pg_class, pg_index, etc). Does anyone know if this is true/false? pg_attribute.attnum stores column order at the moment. If/when reordering is allowed, there will be another column indicating the logical order of the colums. If true, my next idea would be to derive the column name using a subquery in the returning clause. But it sounds like this may have potential security contraints (will any INSERT query always have read access to the PG tables?). And no guarantee of the order matching in the long term. There is no requirement that insert permission on a user table implies read access to pg_catalog. Still many clients will break if they can't read pg_catalog. For example, all of the JDBC driver's MetaData results need to query pg tables, updatable ResultSets need to query pg tables to know what the primary key is and so on. So if this functionality required access to pg_catalog that would neither be unprecedented nor unreasonable. Is there a more elegant approach, like?: INSERT... RETURNING (PG_LIST_KEYS(tblname)) You can't dynamically derive the returning clause for the same reason you can't say INSERT INTO (SELECT myfunc()) VALUES (...), using myfunc to determine the table name at runtime. The planner needs to know all the tables/columns/other database parts up front before executing anything. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] jdbc lob and postgresql
On Thu, 13 Dec 2007, Thomas Kellerer wrote: I am contemplating to use postgresql for a product that requires to manipulate many blobs. From the documentation I am not clear if one has to use postgres api to insert/update/select blobs or one can use standard jdbc api? Can I just execute a statement insert into (..) that has a blob and do a getBlob to retrieve it? Using a PreparedStatement with setBinaryStream() to insert the blob and getBinaryStream() to read the BLOB works fine for me. It depends how you want to handle binary data on the server side. get/setBinaryStream only work with the bytea data type. getBlob/setBlob only work with real large objects (oid data type). Both methods work, it's just a tradeoff on how you want handle it on the server. The API downside of Blob is that only JDBC4 offers a portable way to create a Blob (Connection.createBlob) and the pg driver does not implement that yet. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] RETURNING clause: how to specifiy column indexes?
On Wed, 12 Dec 2007, Tom Lane wrote: Every few weeks we get a complaint from someone who thinks that it should be easy to rearrange the logical order of table columns. If that comes to pass, it would be a seriously bad idea to have encouraged applications to rely on table column numbers. I think the expectation is that: CREATE TABLE t(a int, b int); INSERT INTO t(b,a) VALUES (1,2) RETURNING *; will return 1,2 instead of 2,1 as it does now. In this case the op is not expecting that the (potentially reorganized) table order is driving the results, but the order that they've actually specified the columns in creates the result. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Error while compiling PostgreSQL with Java
On Sun, 25 Nov 2007, Никоноров Григорий wrote: I have a problem. I try to install PostgreSQL from source with java. I Installed JDK, ANT properly but when i try to configure i have an error - Warning.Ignored options - --with-java. Please help me ! Starting with the 8.0 release the JDBC driver was made an external project instead of bundled with the server source code. The JDBC driver is available from http://jdbc.postgresql.org/download.html Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On Mon, 15 Oct 2007, Laurent Duperval wrote: I have a large amount of tests I run in Eclipse to test my application. Many of them create and delete a lot of information in PG and at some point, PG will crash and restart. I get en error in the logs that state: Server process exited with exit code -1073741502 This is likely a server bug. If you can isolate the failing test and extract a self contained example someone can probably fix it. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [JDBC] creating temporary table PostgreSql 8.1 and iBatis 2.3
On Fri, 22 Jun 2007, tomasz brymora wrote: I've build a script in pgAdmin 3 ( os x 10.4 on ppc) that does a select, some left outer joins and puts the results into a temporary table. Works great when I run in pgAdmin. Then I try to use in an iBatis sqlmap as a 'select' and get this when trying to execute it: org.postgresql.util.PSQLException: ERROR: must be member of role postgres Other than that queries run fine. I'm thinking the problem is on the db admin side of things, but I'm at a loss as to how to start fixing it. Knowing the SQL that it actually executed would be valuable. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Hex numbers in psql
On Sun, 20 May 2007, madhtr wrote: How do use hex numbers in psql? I.E. instead of: select 16 I want to do select 0x10 See 4.1.2.3: SELECT x'1FF'::int http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS 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: [GENERAL] Protocol error. Session setup failed (PostgreSQL 8.3devel/postgresql-8.3dev-600.jdbc3)
On Sat, 28 Apr 2007, Marcelo de Moraes Serpa wrote: I've built PostgreSQL 8.3 devel with VC++ 2005, done all the db When I tried to run my web java application, I received the following PSQLException: Protocol error. Session setup failed This is a known bug in the server in CVS. A recent patch broke the ability for clients to set the encoding they wish to converse in. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] large table problem
On Fri, 20 Apr 2007, Jason Nerothin wrote: I'm trying to work my way around a large query problem. Not too unexpectedly, the app server (EJB3/JPA) is choking on the queries which are unnamed native queries in Java parliance. Work-around attempt 1 was to call directly to the JDBC driver, but the cursor doesn't dispose of the memory in the ResultSet once I've passed it by (OutOfMemoryError) and the documentation suggests that cursor behavior is a little buggy for the current postgres driver. (The docs suggest implementing a custom stored procedure to provide iteration.) I'm not sure what documentation you're reading: http://jdbc.postgresql.org/documentation/82/query.html#query-with-cursor and it works as adverstised. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.2.3, Jboss 4.0.3 and postgresql-8.2-504.jdbc4
On Wed, 28 Feb 2007, Andrew Madu wrote: i've just upgraded from posgreSQL 8.1 to 8.2.3. I placed postgresql-8.2-504.jdbc4 in /deploy/ejb3.deployer, restarted jboss and the proces breaks when it gets to detecting what postgresql driver is being used! I remove the jdbc4 driver and replace it with, my original driver, postgresql-8.1-405.jdbc3 and all works fine again! I am using Java 1.5.0_06 on Window XP SP2 JDBC4 is only available with the 1.6 JDK. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Re: [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
On Tue, 27 Feb 2007, Henry B. Hotz wrote: Question: are there any corresponding deadlines for the Java client code that I need to worry about? The JDBC driver will release a new version at the same time as the server, but we don't have nearly as strict rules about feature freeze/beta. We don't need multiple months of beta so as long as the patch isn't terribly complicated we'll accept it close to the release. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.2.x and JDBC driver
On Mon, 26 Feb 2007, DANTE Alexandra wrote: I am not sure that this is the appropriated list but I try... I try to used BenchmarkSQL (release 2.3.2, built on February 12, 2006) with PostgreSQL 8.2.2 and then 8.2.3. By default, the JDBC driver included with this release of BenchmarkSQL is postgresql-8.0.309.jdbc3.jar. So I downloaded at http://jdbc.postgresql.org/download.html postgresql-8.2-504.jdbc3.jar as I used JDK 1.4 and PostgreSQL 8.2.x. I have a question about the release of this JDBC driver : the version 8.2-504 has been built on December 1st, 2006, that is to say for PostgreSQL 8.2.0, so can I use it with 8.2.2 and 8.2.3 ? Does a release exist for these releases of PostgreSQL or is it correct to use the version 8.2-504 ? 8.2-504 is the release to use. The JDBC driver has a different release schedule than the server so it will be ahead or behind of the server release date, but the latest version is always OK to use. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] JDBC issue
On Tue, 23 May 2006, Joel Alejandro Espinosa Carra wrote: I searched at google and did not find any answer. I have an older JDCB driver in my java web application, if I update the driver to the 7.4 build 216 version some of the querys like the next show no results in the ResultSet object: I have no idea why the JDBC driver would cause any sort of difference in the results of a query. Are you sure you're connecting to the same server and running the exact same query when you get results in psql? If you can send a self contained reproducible example I'll take a look at it, but I've got to guess it's something wrong with your code or your connection. You could also try updating to a newer JDBC driver, the 7.4 series is not maintained any longer. ps. please tell me if it is the wrong mailing list for this issue. You should ask JDBC questions on the pgsql-jdbc list. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to get recordset with CallableStatemente
On Sat, 20 May 2006, dfx wrote: I am trying to get recordset from PostgreSQL database (8.1.3) with java but I am some problem. Can somebody send me a short fragment of code that call a function that returns a set of records? http://jdbc.postgresql.org/documentation/81/callproc.html#callproc-resultset 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: [GENERAL] parameter passing from java program
On Mon, 15 May 2006, dfx wrote:
I am spending about 10 hours on little problem: to pass a parameter (text)
to a PostgreSQL (v. 8.1 on Win2000). The
relevant code is:
java side:
private CallableStatement cs;
cs = conn.prepareCall(INSERT_CONGRESSO);
cs.setString(1, itemCongresso.getCongresso()); //return a String
cs.execute();
...
public static final String INSERT_CONGRESSO = {SELECT ins_congressoa(?)};
You're mixing your calling syntax. You need to write either:
conn.prepareCall({ ? = call ins_congressoa(?)});
or
conn.prepareStatement(SELECT ins_congressoa(?));
Kris Jurka
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Mac Problem with Tunneling...
On Sat, 13 May 2006, Jerry LeVan wrote: channel 3: open failed: administratively prohibited: open failed This is the standard error you'll see when /etc/ssh/sshd_config (or local equivalent) has AllowTcpForwarding no. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Mailing list setup issue
On Thu, 27 Apr 2006, Chris Velevitch wrote: In addition, it's not the norm. I subscribe to lots of mailing lists and postgresql.org lists are the only ones that I've seem do that. So what's the reasoning behind this choice? http://www.unicom.com/pw/reply-to-harmful.html Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres 8.1 sequences and 'CALL'-syntax
On Thu, 27 Apr 2006, Schnabl, Sebastian wrote:
Hello,
I use postgres 8.1 and trie to run jboss over sequoia-ha
(http://sequoia.continuent.org/HomePage). But i have an problem with
sequences. Sequoia claims to support for good reasons and db-independece
only sql-standard(s). Therefore they DON'T support the
postgres-specific select nextval('seq_name'). Instead they gave me the
hint to use the sql-conform call nexval('seq_name').
I'm not sure where they layer their stuff on, but the pg jdbc driver will
support something like the following:
Connection conn = ...
CallableStatement cs = conn.prepareCall({? = call nextval('seq_nm')});
cs.registerOutParameter(1, Types.BIGINT);
cs.execute();
long nextval = cs.getLong(1);
cs.close();
Kris Jurka
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] catch SQLException, error code for Foeign key violation,
On Wed, 19 Apr 2006, surabhi.ahuja wrote: what is the way to capture such exception in Cpp, are there any examples available for this? right now in cpp, i do this rStatus = PQresultStatus(result); but what is the specific error code, how to get that, See PQresultErrorField http://www.postgresql.org/docs/8.1/static/libpq-exec.html Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql and Creator2 commitChanges()
On Tue, 18 Apr 2006, lash wrote: Sun Java Studio Creator 2 and PostgreSQL 8.1 database using 8.1-405 jdbc3. But someDataProvider.commitChanges() works only first time. Then the only message i get after someDataProvider.commitChanges() is can't change isolation level in the middle of transaction. If you are going to post to multiple lists, please cross-post instead of sending two separate messages. This is a JDBC question, so I'll follow up there. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] catch SQLException, error code for Foeign key violation,
On Mon, 17 Apr 2006, surabhi.ahuja wrote: The question is that is from this SQLException can i detect if it is a foreign key violation, You should check the value of SQLException.getSQLState() against this table: http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PSQL Data Type: text vs. varchar(n)
On Thu, 30 Mar 2006, kurt _ wrote: I am having a problem with Sun Java Studio Creator because the latest version of the JDBC driver returns a field length of -1 for text fields. You should try the latest development driver, 8.2dev-501. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] parameter substitution problem in jdbc driver? (8.1)
On Fri, 27 Jan 2006, Dave E Martin wrote: When a query of this form: /* from xxx where yyy = ? */ select a,b,c,d from xxx where yyy = ? is sent to the jdbc driver (as a prepared statement), it complains that parameter 2 is not set. This is a query from hibernate3.1, with hibernate's generate comments in sql option turned on. Is the problem in the JDBC driver, in Hibernate, or in the JDBC spec? This is the JDBC driver's fault. There are a number of places it does not correctly detect placeholders vs. comments/text, for example dollar quoting is another one. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres 8.1.2, Java, JDO, and case sensitivity woes
On Fri, 27 Jan 2006, Matthew Hixson wrote: The problem is that the JDO layer is looking for the table name in a system table like so: When using DatabaseMetaData calls to determine what tables and columns are available you must be aware of what case the search terms need to be passed in with. You must know if your tables were created with or without quotes, but then you can use the DatabaseMetaData methods stores[XXX]Case[Quoted]Idetifiers() to determine what case to pass parameters to methods like getTables(). It's unclear what control (if any) you have over this, but that's how it's supposed to work. 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: [GENERAL] [JDBC] SQLData user-defined-types and getObject()
On Sat, 7 Jan 2006, Assad Jarrahian wrote: I am quite confused (PLEASE PLEASE Help), I cannot find anything on the web). I read that you can declare a class that implements SQLData (in this case I set up a class called Complex from the /src/tutorial datatype that mimics the user-defined datatype in the db) and then set the mapping appropriately (see below). This is possible according to the JDBC spec, but the postgresql driver does not implement it. At the moment your only option is to have your class implement org.postgresql.util.PGobject and register it using a pg specific method. This is fine for a true user defined type, but it certainly isn't ideal for a composite type because it requires the type creator to handle all of the fields himself. There isn't much documentation on PGobject implementations either, but the javadoc and source code will point you in the right direction. http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGobject.html Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Specify schema in jdbc connection string?
On Thu, 8 Dec 2005, Dan Armbrust wrote: Is there any way to specify which schema I want to use (or have at the front of the search path) within the jdbc connection string? No. It is still an open todo open. http://jdbc.postgresql.org/todo.html Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] information_schema._pg_keypositions() in 8.1???
On Thu, 1 Dec 2005, Jason Long wrote: I cannot get automatic schema update to work in 8.1 with hibernate 3.0.5. I get the following error every time. java.sql.SQLException: ERROR: function information_schema._pg_keypositions() does not exist This is actually a sign that you are using an 8.0 jdbc driver against an 8.1 server. You need the 8.1 driver. 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: [GENERAL] PREPARE TRANSACTION and webapps
On Tue, 15 Nov 2005, Lincoln Yeoh wrote: Can we have a reconnect and reopen prepared/saved transactions feature? Please? :) Note that this (transaction suspend/resume) is also required for a full implementation of XA. Our current 2PC only supports the basics. There's a bunch of other complicated features, like transaction interleaving[1] and multiple threads of control participating in the same backend transaction[2] that we currently don't support either. Now some of these may be worked around and faked on the driver side, but it won't be able to do these well. For example you could implement suspend/resume by simply holding the backend connection open or you could implement interleaved transactions by opening multiple connections, but both have a serious cost in the number of open connections. It would be better to implement this functionality in the backend, but I'm not sure how important these situations are in the real world. Some on the jdbc list have shown ways to configure transaction managers to avoid using these exotic features. Also I think that trying to use 2PC without a real transaction manager is just asking for trouble. Normal XA usage is two serverside resources held open the time it takes to service a single request, not wait for user input. A random webapp leaving suspended or prepared transactions around is going to lock things up in a hurry. Kris Jurka [1] http://archives.postgresql.org/pgsql-jdbc/2005-06/msg00165.php [2] http://archives.postgresql.org/pgsql-jdbc/2005-06/msg00171.php ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] lo_import()
On Mon, 14 Nov 2005, Aaron Steele wrote:
i would like to use lo_import() on the client side to insert images into
postgresql version 8.0.1 on a remote server using psql as follows:
psql -h my.db.host.com http://my.db.host.com -U user -c insert into
binblob (binary_blob) values(lo_import('/path/on/client/machine/ks.jpg'))
-d mydb
ERROR: must be superuser to use server-side lo_import()
HINT: Anyone can use the client-side lo_import() provided by libpq.
It is one of psql's backslash commands. See \? and \lo_import.
Kris Jurka
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Possible move away from PG
On Thu, 30 Jun 2005, Jason Tesser wrote: 1. Our dev plan involves alot of stored procedures to be used and we have found the way this is done in PG to be painful. (ie. To return multiple record from different tables you have to define a type. This is a pain to maintain because if you ever have to change what it returns it cannot be dropped because of dependencies etc.. In some other databases you can simpley write a stored proc to return whatever the query inside returns and this is handled dynamically) Have you considered returing refcursors instead of setof some type. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is postgresql 8.xx jdbc support SCROLL_SENSITIVE?
On Wed, 18 May 2005, Hengki Suhartoyo wrote: I have problem with my jdbc program, I need Sensitive scrollable result. Postgresql only supports insensitive cursors and ResultSets. Your only option is to reissue the query to pick up any changes. 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: [GENERAL] PostgreSQL XA ?
On Tue, 17 May 2005, FERREIRA, William (COFRAMI) wrote: hi at present my j2ee application use an Oracle database with XA transactions. i'm migrating Oracle to PostgreSQL, and i'm looking for a XA driver for PostgreSQL. does it exist ? No, postgresql does not support distributed transactions, so it does not have XA JDBC support. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] jdbc3 and ResultSet.getFetchSize()
On Tue, 26 Apr 2005, Michal Hlavac wrote: I have this code: ResultSet rs = stmt.executeQuery(SELECT attr FROM table WHERE id=1); System.out.println(rs.getFetchSize()); rs.getFetchSize() everytime returns 0... why??? The fetch size is a hint for the driver to know how many rows at a time to retrieve from the database. If you haven't given the driver any hints it is set to zero meaning If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. Older drivers used to report the number of rows fetched in this method, but that was just an implementation artifact at best and a bug at worst. 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: [GENERAL] lots of puzzling log messages
On Wed, 20 Apr 2005, Tom Lane wrote: On Tue, Apr 19, 2005 at 05:39:42PM -0700, Dennis Sacks wrote: I am Seeing twelve of these messages every five to ten seconds in the Postgresql serverlog when my java application is running: 2005-04-19 16:43:03 LOG: 0: statement: rollback; begin; LOCATION: pg_parse_query, postgres.c:464 I think older versions of the JDBC driver will do that behind your back ... try the latest driver, and if you still see it then inquire on pgsql-jdbc. The latest driver will do that as well, but in two statements now that transaction end doesn't automatically start a new one immediately. The difference is that the server doesn't log it for the 8.0 driver because it prepares the begin/commit/rollback statements just once and executes them from then on. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] current transaction is aborted, commands ignored until
On Tue, 19 Apr 2005, Thomas Kellerer wrote: Joshua D. Drake wrote on 19.04.2005 00:21: I'm having a bit of trouble with the error message from the subject. Is there a way to simply go on with my commands if I hit this error? Only if you are using savepoints (nested transactions) otherwise you must rollback. Is this a problem with the JDBC interface that I'm using, or is this a general Postgres problem? I do not know if the JDBC interface supports savepoints. The problem is, that I need to support several different DBMS and only for handling Postgres stuff I don't really want to introduce this. This is an open todo item for the JDBC driver. It could be done by automatically wrapping all statements in savepoints behind the scenes. Then any error would rollback to the savepoint and you could continue on with your transaction. This option would only be used for compatibility which makes it a low priority for people who are mostly PostgreSQL users. Also, it'll certainly decrease performance. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Loosing connection with the database
On Tue, 12 Apr 2005, [UTF-8] Poul Møller Hansen wrote:
I have rewritten the application so every client thread is opening a new
database connection, and yesterday it happened again.
---
2005-04-11 12:27:54 ERROR: invalid string enlargement request size
1358954492
2005-04-11 12:27:54 WARNING: AbortTransaction and not in in-progress
state
2005-04-11 12:27:54 FATAL: invalid frontend message type
78
---
The application is opening a socket listener, and every client
connection opens a new connection to the database.
The clients sends a status message every 2nd minute that are written to
the database.
I'm using Postgresql version 7.4.7 and jdbc driver version
pg74.215.jdbc3.jar.
Do you have a clue on what's going on ?
No, I don't. Do you have any more information? What is your code doing
when it fails? Just issuing a regular query? Are you using any of the
less common driver features: Large objects, fastpath api, a COPY patch?
If the driver had a protocol problem I would expect it to be rather
repeatable. If the driver had a synchronization problem it should have
disappeared when you moved to a single thread model. I've attached the
test script I've used to try and beat on the driver.
Kris Jurkaimport java.sql.*;
import org.postgresql.*;
import org.postgresql.largeobject.*;
public class Threads {
public static void main(String args[]) throws Exception {
Class.forName(org.postgresql.Driver);
Connection conn =
DriverManager.getConnection(jdbc:postgresql://localhost:5432/jurka,jurka,);
conn.setAutoCommit(false);
Runner runners[] = new Runner[10];
setupBlob(conn, runners.length);
for (int i=0; irunners.length; i++) {
runners[i] = new Runner(conn, i);
runners[i].start();
}
}
private static void setupBlob(Connection conn, int len) throws
Exception {
Statement stmt = conn.createStatement();
stmt.execute(CREATE TEMP TABLE tblob (a int, b oid));
PreparedStatement pstmt = conn.prepareStatement(INSERT INTO
tblob VALUES (?, lo_creat(-1)));
for (int i=0; ilen; i++) {
pstmt.setInt(1,i);
pstmt.executeUpdate();
}
pstmt.close();
byte buf[] = new byte[2048];
for (int i=0; ibuf.length; i++) {
buf[i] = (byte)i;
}
LargeObjectManager lom =
((PGConnection)conn).getLargeObjectAPI();
ResultSet rs = stmt.executeQuery(SELECT b FROM tblob);
while (rs.next()) {
LargeObject obj = lom.open(rs.getInt(1),
LargeObjectManager.WRITE);
obj.write(buf, 0, buf.length);
obj.close();
}
}
}
class Runner extends Thread {
private Connection conn;
private int version;
public Runner(Connection conn, int version) {
this.conn = conn;
this.version = version;
}
public void run() {
while(true) {
try {
if (version % 4 == 0) {
// Normal query execution.
Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery(SELECT * FROM pg_class);
while (rs.next()) { }
rs.close();
rs = stmt.executeQuery(SELECT * FROM
pg_class);
while (rs.next()) { }
rs.close();
stmt.close();
} else if (version % 4 == 1) {
// Use a cursor
Statement stmt = conn.createStatement();
stmt.setFetchSize(20);
ResultSet rs =
stmt.executeQuery(SELECT * FROM pg_class);
while (rs.next()) { }
rs.close();
rs = stmt.executeQuery(SELECT * FROM
pg_class);
while (rs.next()) { }
rs.close();
stmt.close();
} else if (version % 4 == 2) {
// Use a prepared statement
PreparedStatement pstmt =
conn.prepareStatement(SELECT
Re: [GENERAL] Loosing connection with the database
On Sat, 2 Apr 2005, [ISO-8859-1] Poul Møller Hansen wrote: This sort of thing has been seen to occur when multiple client-side threads try to use the same database connection without proper locking to ensure only one thread uses it at a time. See for example http://archives.postgresql.org/pgsql-hackers/2004-09/msg00104.php This is exactly what I am doing. Must admit I haven't considered that as an issue. For performance reasons I suppose one database connection per client are preferred rather than using synchronized on the db class ? The JDBC driver should be doing any synchronization necessary for multiple threads. Could you be more clear what you are doing? What driver version? Any chance you've got a reproducible example? Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor AS. How
On Thu, 24 Mar 2005, David Gagnon wrote: I'm already able to get Refcursor from a stored procedure. But now I need to get a SETOF refcursor and I can't make it work... Is that possible to do this via JDBC? He is the code I did. The rsTmp.next() throws a Connection is closed. Operation is not permitted. Exception. rs = new ResultSet[j]; System.arraycopy(tempArray, 0, rs, 0, j); rsTmp.close(); System.arraycopy does not make a deep copy, so the rsTmp.close() closes the ResultSet. You really can't copy resources around like that. Consider how you would copy a Connection object. Does that establish a new connection? The underlying tcp/ip connection can't be copied. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor AS. How
On Thu, 24 Mar 2005, David Gagnon wrote:
Hi Kris,
I don't get error with the rsTmp.close() statement but with
(rsTmp.next()) . The arraycopy is because I want to shrink the
original array (size 50) to it real size. It's not intended to be a
deep copy.
Right, my bad. I see nothing wrong with your code, but you haven't
included a complete example. There aren't any thread safety problems in
your code where the executePreparedStatementQueryMultipleCursor function
is called simultaneously? I've attached the test code I've used to verify
that this is not a driver problem.
Kris Jurka
import java.sql.*;
public class MultRefCursor {
public static void main(String args[]) throws Exception {
Class.forName(org.postgresql.Driver);
Connection conn =
DriverManager.getConnection(jdbc:postgresql://localhost:5432/jurka,jurka,);
Statement stmt = conn.createStatement();
stmt.execute(CREATE OR REPLACE FUNCTION multcurfunc() RETURNS
SETOF refcursor AS 'DECLARE ref1 refcursor; ref2 refcursor; BEGIN OPEN ref1 FOR
SELECT 1; RETURN NEXT ref1; OPEN ref2 FOR SELECT 2; RETURN next ref2; RETURN;
END;' LANGUAGE plpgsql);
stmt.close();
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(SELECT * FROM
multcurfunc());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
ResultSet rs2 = (ResultSet)rs.getObject(1);
while (rs2.next()) {
System.out.println(rs2.getInt(1));
}
rs2.close();
}
rs.close();
ps.close();
conn.close();
}
}
---(end of broadcast)---
TIP 3: 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: [GENERAL] java.lang.OutOfMemoryError
On Tue, 15 Mar 2005, Suma Bhat wrote: I'm need to be able to insert a byte[] of size upto 25MB. With Heap size upto 512m this is failing, with a java.lang.OutOfMemoryError You need to use an 8.0 JDBC driver and a 7.4 or 8.0 server. 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: [GENERAL] SRF, JDBC and result info
On Tue, 8 Mar 2005 [EMAIL PROTECTED] wrote: Hi everybody! I have an SRF which is called from a JAVA app with JDBC. Everything works fine and I want now to be able to pass some result-related info to my app. It is not about the format of the results (ResultSetMetaData) or something like that. Is it possible to return some string (or other type of)info together with the result tuples (even if it requiers some hacking i.e. there is no provision for something like that)? Any ideas? The only idea that comes to mind is using RAISE NOTICE in your plpgsql function and Statement or ResultSet .getWarnings() on the Java side to retrieve that info. There really isn't any other out of band data path. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] JDBC and Portals . Clarification
On Fri, 25 Feb 2005, Dave Smith wrote: Is a portal using the V3 protocol the same as a cursor? I am trying to debug a slow query and I want the JDBC driver to use the cursor syntax. It says it is using a portal but the performance seems like just a regular sql statement. Yes, portals and cursors are pretty much the same thing. Depending on your query using a cursor may not actually speed it up, consider SELECT * FROM tab ORDER BY col; The whole result must be sorted before any row is returned. What it will do in this case is just reduce the memory consumption on the client by not fetching all the rows at once. Also please check the list of restrictions for the JDBC driver to actually use a portal/cursor backed ResultSet: http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] J2SE 1.5 Cache Rowset(JSR 114 )
On Fri, 7 Jan 2005, Brian Maguire wrote: Just as an FYI. Sun's Cache rowset which is new and part of the J2SE 1.5 is not compatable with Postgres. We have been working with Sun's JAVA development team to resolve the issues through our test cases and debugging. We hope that in a future patch update that the issues will be resolved. We'll keep you up to date. Any more details on the problem? A link to Sun's bug database? If you suspect the postgresql jdbc driver I'd be happy to take a look at a testcase. Kris Jurka ---(end of broadcast)--- TIP 3: 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: [GENERAL] SSL confirmation - No trusted certificate found
On Mon, 6 Dec 2004, Andrew M wrote: jdbc:postgresql://localhost:5432/mydatabase?ssl When I launch Jboss, which handles the connection to postgresql, I get the following error: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found Difficult to say. Perhaps JBoss is deciding to use an alternate truststore? Perhaps it doesn't like your cert setup. I would suggest first running a simple client program to test that it's working first. Also adding -Djavax.net.debug=ssl to the java command will help debugging ssl problems. Further the 8.0 JDBC driver can create SSL connnections without doing authentication by adding using an additional url parameter: sslfactory=org.postgresql.ssl.NonValidatingFactory You should try to get it to authenticate correctly, but this is another useful test point. Kris Jurka ---(end of broadcast)--- TIP 3: 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: [GENERAL] SSL confirmation - No trusted certificate found
On Mon, 6 Dec 2004, Andrew M wrote: I have implemented your earlier suggestion: sslfactory=org.postgresql.ssl.NonValidatingFactory and no error are generated, so I presume that the connection to the database is now ssl'd. So why bother going through the headache of creating a certificate if I can do it like mentioned above? It leaves you open to man in the middle attacks. You are no longer verifying that the server is who they say they are. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SSL confirmation - (could not accept SSL connection:
On Mon, 6 Dec 2004, Andrew M wrote: after much research to various mail-lists, it seems that there is a bug in the beta versions of postgreSQL 8 which is causing the problem. Is there anyway to create a SSLv3 specific certificate? How about sharing a little of that research with us. The whole JDBC and SSL setup worked fine for me last time I tested it (mid-October) and I have little reason to believe it is broken now. What bug are you seeing? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [JDBC] Invalid Character Data Problem
On Fri, 26 Nov 2004, Hunter Hillegas wrote: When I SELECT from a certain table, I see this JDBC exception: Invalid character data was found. This is most likely caused by stored data containing characters that are invalid for the character set the database was created in. The most common example of this is storing 8bit data in a SQL_ASCII database. The database is indeed of type SQL_ASCII. The table stores mailing list data and has about 400,000 rows. Looking at the data via psql, I see that some of the rows have strange characters in them, such as question marks where I would not expect them, etc... What are my options? Is there a way to identify the 'bad' records, or the ones causing trouble? To really solve this problem you need to have a correctly encoded database. This will involve a dump and restore process and possibly recoding your data. This is straightforward if you know what encoding your data is, although it will cause some downtime. To detect the bad data you can try various SELECTs with the JDBC driver and see what errors out. The function below will determine if a particular field has data with the high bit set which is something the database really doesn't know what to do with. SELECT pkcolumn, hashighbit(columna), hashighbit(columnb) FROM mytable; Kris Jurka CREATE OR REPLACE FUNCTION hashighbit(text) RETURNS boolean AS ' DECLARE i int; BEGIN i := LENGTH($1); WHILE i 0 LOOP IF ascii(substring($1, i, 1)) = 128 THEN RETURN true; END IF; i := i-1; END LOOP; RETURN false; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] updateString error in PostgreSQL7.4 with JDBC
On Wed, 10 Nov 2004, Suha Onay wrote: st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = st.executeQuery(select * ...; Then: rs.updateString(20 , ...); The error is: org.postgresql.util.PSQLException: Cannot update the result set because it is either before the start or after the end of the results. You must be on a row (or the insert row) to update it, you have not shown any positioning methods (like next()) being called on the ResultSet so it is left positioned before the first row. I don't know what the 7.3 driver was actually doing, but this error is the expected behavior of the driver, unless of course you are doing something else you haven't shown us. 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: [GENERAL] [JDBC] Using Postgres with Latin1 (ISO8859-1) and Unicode (utf-8)
On Mon, 8 Nov 2004, J. Michael Crawford wrote: Even in Java, where you can do all sorts of character-encoding translation, it can be impossible to translate data retrieved from Postgres if it's in the wrong encoding. We've tried changing the JVM encoding, altering the jdbc driver, translating encodings on the database read, and translating encodings after the read while building a new string, to no avail. We tried 25 combinations of each strategy (five different possible read encodings and five different possible string encodings), and nothing worked. We could get an application working in one JVM with one encoding, but another JVM would break, and no amount of translation would help. But when we finally told Postgres what to return, everythign worked like a charm. Just as with step two, the key is to use the SET CLIENT_ENCODING TO (encoding) sql command. If you're using an application where you can send SQL to the server, this is all you need. In something like MS Access, you'll have to move to a passthrough query. For Java, you'll need to send a command through JDBC: String DBEncoding = Unicode //use a real encoding, either returned from the jvm or explicitly stated PreparedStatement statement = dbCon.prepareStatement(SET CLIENT_ENCODING TO ' + DBEncoding + '); statement.execute(); This is bad advice for a Java client and does not work. The JDBC driver always expects data in unicode and issues a SET client_encoding of it's own at connection startup to make sure it gets unicode data. Changing this to another encoding will break the driver and in the cvs version a check has been added to error out if it detects you doing this. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] create view problem
On Sun, 17 Oct 2004, Simon Moses wrote: dear sir, i am trying to create view with following query This has nothing to do with JDBC or Java, so it should not be sent to the JDBC list. create view salesQ as select * from sales, customer where sales.customerkey = customer.customerkey; it is giving ERROR: column customerkey duplicated create view salesQ as select sales.*, customer.* from sales, customer where sales.customerkey = customer.customerkey; also giving same error. i cannot change field names or specify field names in view definition because many fields are there in both tables. how to create this view? You need to use aliases in your SELECT... SELECT sales.customerkey AS salescustomerkey, customer.customerkey AS customercustomerkey, ... FROM sales, customer WHERE ... Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [JDBC] Problem with boolean type
On Thu, 7 Oct 2004, Johann Robette wrote:
I use EJB to create a record in a table containing a Boolean field
answered.
The EJB method expects a Boolean object so I pass new Boolean(false) in
order to create it as false.
But I get the following error :
java.sql.SQLException: ERROR: column answered is of type boolean but
expression is of type text
I don't see how that could be happening. The attached test works fine for
me and exercises every way I see to set a boolean. Perhaps the EJB is
internally converting this to a setString() call? Could you investigate
more into what actual driver calls are being made?
Kris Jurkaimport java.sql.*;
public class BoolTest {
public static void main(String args[]) throws Exception {
Class.forName(org.postgresql.Driver);
Connection conn =
DriverManager.getConnection(jdbc:postgresql://localhost:5750/jurka,jurka,);
Statement stmt = conn.createStatement();
stmt.execute(CREATE TEMP TABLE tt (ans bool));
stmt.close();
PreparedStatement pstmt = conn.prepareStatement(INSERT INTO tt (ans)
VALUES (?));
pstmt.setObject(1, new Boolean(false));
pstmt.executeUpdate();
pstmt.setBoolean(1, false);
pstmt.executeUpdate();
pstmt.setObject(1, new Boolean(false), Types.BIT);
pstmt.executeUpdate();
pstmt.setObject(1, new Boolean(false), Types.BOOLEAN);
pstmt.executeUpdate();
pstmt.close();
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SELECT ans FROM tt);
while (rs.next()) {
System.out.println(rs.getBoolean(1));
}
rs.close();
stmt.close();
conn.close();
}
}
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Cursors and JDBC
On Mon, 4 Oct 2004, Wiebe de Jong wrote: I am trying to implement cursors using JDBC connector version 7.1b5 (postgresql-7.1b5.jar), but can't get it to work. Before saying anything else, I have to tell you to get off 7.1, especially a beta version of it. The first JDBC driver to have cursor support was the 7.4 series. It has been tested for backward compatibility to 7.2, but not 7.1. That said I believe it should work against a 7.1 server. So download a 7.4 jar file from http://jdbc.postgresql.org/download.html and see this documentation: http://www.postgresql.org/docs/7.4/static/jdbc-query.html#JDBC-QUERY-WITH-CURSOR Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.0 Feature List?
On Sat, 14 Aug 2004, Richard Welty wrote: the authors of 98%-99% of all introductory java textbooks have a lot to answer for. my favorite example of the lot is that they all teach programmers to use String in the following manner: String query = SELECT foo + FROM bar + WHERE baz = 'bletch'; There is actually nothing wrong with this particular example. I realize you are pointing out an issue that can happen, so this is a just for the record post. In this case the concatenation is done at compile time. See: http://www.java-performance-portal.org/article6.html Further the usage of string concatenation is easier to read/write so you need to actually consider wether the code in question is actually a hotspot and worth StringBuffer(ifying). Again your example is pointing to a database query in which case so many other things are going on it's unlikely to make any performance difference. Kris Jurka ---(end of broadcast)--- TIP 3: 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: [GENERAL] Resultset problem or BUG !
On Wed, 11 Aug 2004, Elie Nacache wrote: A data writed in a table but not commited is not accessible in modification by the resultset. The error returned is Cannot move to index of 0. It is entirely unclear what you are actually doing here. Are you using JDBC? Could you show us some sample code that demonstrates the problem? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg jdbc driver
On Mon, 9 Aug 2004, Dino Nardini wrote: Hello there, Is there no way to force the JDBC driver to simply pass the data as SQL-ASCII, or ignore the encoding altogether? No because Java itself needs it correctly encoded the JDBC driver needs to make that happen. I tried to dump and restore the database with UNICODE encoding but get errors on restore because there is quotes/double quotes within some of the text data. Is there an encoding that will handle both french language characters and quotes/double quotes? I've seen this problem with Microsoft smart quotes that are angled, but there should be no problem with plain ' and . When doing the dump / restore you may need to actually convert the data from whatever encoding you were using with something like iconv. If you are just struggling with the quotes perhaps a simple search and replace would be fine. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg jdbc driver
On Sat, 7 Aug 2004, Dino Nardini wrote: [Could the JDBC driver be causing the problems I'm seeing with my website after moving from ColdFusion with ODBC? ] Unlikely. A common problem people find when using PostgreSQL's JDBC driver is that they have setup their database with SQL_ASCII encoding which doesn't have any information on what high-bit characters actually represent. Many applications are OK with this and blindly pass data back and forth, but Java requires having correctly encoded data and the JDBC driver will error out with something like Invalid character data was found... You reported problems correctly displaying this data, not an error, so I doubt that is the issue. As I mentioned Java is (sometimes painfully) encoding aware. I'm not familar with ColdFusion, but you likely need to set the page encoding correctly for converting Java Strings into bytes. This can be accomplished in a number of ways, for example set globally via the file.encoding system property or on per page basis via methods in javax.servlet.ServletResponse such as setCharacterEncoding, setContentType, and setLocale. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] pg jdbc driver
On Sun, 8 Aug 2004, Dino Nardini wrote: However, when I tested the same data with ColdFusion MX (built on a Java platform), I ran into problems. The french characters were either throwing Invalid character... errors, or displaying as garbage. I'm guessing that CFMX was trying to convert the characters from SQL_ASCII to something else (UTF-8?) somewhere between the database and the web page. The invalid character data errors are coming from the JDBC driver. When it connects to the database it asks the server to send it data using UTF-8 regardless of the actual database encoding. The server cannot convert SQL_ASCII to UTF-8 and simply returns the SQL_ASCII data. The JDBC driver is expected UTF-8 data and chokes on this, so as you mentioned you will need to dump and restore into a database with a real encoding. This doesn't necessarily have to be unicode, but often makes the most sense. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] ./configure --with-java fails
On Tue, 6 Jul 2004, Markus Wollny wrote: I am trying to build PostgreSQL 7.4.3 with Java enabled; I've got Apache Ant version 1.5 and j2sdk1.4.1_05 installed: [javac] Modern compiler not found - looking for classic compiler BUILD FAILED file:/usr/src/postgresql/postgresql-7.4.3/conftest.xml:3: Cannot use classic compiler, as it is not available. A common solution is to set the environment variable JAVA_HOME to your jdk directory. Your setup looks good, perhaps changing the configure script to run ant with -debug would help? Help would be very much appreciated; on second thoughts: Is it at all necessary to build the JDBC-driver myself or can I safely use the precompiled .jar-file from http://jdbc.postgresql.org/download.html on my clients? And if I compile without the --with-java, will the resulting PostgreSQL-server-installation have any shortcomings in integrating with JDBC-applications? What exactly are the Java/JDBC tools aside from the JDBC-driver jar-file? Configuring with --with-java does nothing other than build the JDBC driver. There used to be some additional java tools, but these were removed before the 7.4 release. The provided jar files are no different than what you would build yourself so it's fine to use them. Kris Jurka ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN/NOTIFY with JDBC
On Wed, 12 May 2004, Glenn Sullivan wrote: Thanks for the response. I have been using Sun's JDBC. Do I take it that I need to create and use the Postgres JDBC to get the ability to do NOTIFY/LISTEN? You have been using the JDBC API provided by Sun as a number of interfaces (java.sql.*) which are implemented behind the scenes by the postgresql JDBC driver in concrete classes. Listen/Notify is a postgresql extension not covered in the standard java.sql.* API, so must cast the standard interfaces to postgresql implementations so that you can access the additional functionality provided. For example when using the postgresql JDBC driver java.sql.Connection is actually an instance of org.postgresql.PGConnection, so you can cast your Connection to a PGConnection to use the notification API. Unfortunately the PGConnection and other extensions are either poorly documented or completely undocumented. Combining my example with a brief look at the source code for PGConnection and PGNotification should hopefully get you going. Kris Jurka ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] query progress indicator
On Mon, 9 Feb 2004, David Garamond wrote: [= ] 56% ETA ... I know there is no such thing in Postgres right now (though there is pg_stat_activity). But is there database product that can do this? When pg is processing a query it doesn't know how long it will take, so it can't make an estimate. Consider a query on a very large table with no indexes such as SELECT * FROM tab WHERE col = 1 LIMIT 1; As it will be forced to do a sequential scan of the table there is no way of telling if a matching row will be found on the first row or never which will take wildly different amounts of time. Kris Jurka ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] LISTEN/NOTIFY with JDBC
On Tue, 11 May 2004, Glenn Sullivan wrote:
Hi,
I have been trying to get LISTEN/NOTIFY working in with JDBC. I cannot seem
to get notified. I looked in the e-mail archive and saw a lot of similiar
questions a couple of years ago. I never could find any answers in the
e-mail nor in the documentation. Perhaps I just missed it.
I have tried the following code snipit:
Connection db = DriverManager.getConnection(url, user, passwd);
Statement sql = db.createStatement();
sql.execute(LISTEN mytest);
db.clearWarnings();
for(int i=0; i 10 ; i ++) {
Thread.sleep(3000);
SQLWarning warn = db.getWarnings();
if(warn != null)
System.out.println(warn: + warn.getMessage());
else
System.out.println(warning null);
}
During the running of this loop, I run psql on the same database
and manually execute NOTIFY mytest;.
Notifications don't come back as warning's, but are implemented using pg
specific java code. Further, there is no asynchronous notification
support in the JDBC driver, so you can't just wait for them to show up.
Instead you must send a backend command every so often to see if a
notification is ready.
Code more like the below should work:
import org.postgresql.PGConnection;
import org.postgresql.PGNotification;
Connection conn = ... // get connection somehow
Statement stmt = conn.createStatement();
while (1) {
ResultSet rs = stmt.executeQuery(SELECT 1);
rs.close();
PGConnection pgconn = (PGConnection)conn;
PGNotification notif[] = conn.getNotifications();
for (int i=0; inotif.length; i++) {
System.out.println(notif[i].getName());
}
Thread.sleep(3000);
}
Kris Jurka
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Using Postgres from WSAD
On Mon, 3 May 2004, Stonebrook, Jeff wrote: Has anyone had success loading the Postgres JDBC drivers into WSAD ? Whenever I attempt a connection I receive the following error: [Reason: Problems encountered while loading the jdbc driver. Reason: javax.net.ssl.SSLSocket ] Please do not send images to the list when two lines of text will suffice. You have not specified what JVM or JDBC driver version you are using but my guess is that you have selected a JDBC driver that was compiled with support for SSL while you are not providing the required SSL classes in your runtime environment. Also what is WSAD? 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: [GENERAL] out of memory error
On Fri, 30 Apr 2004, Rachel McConnell wrote: Hello, I have a Java web application using Hibernate to connect to a PostgreSQL backend. I am seeing the below stack trace during processing of a set of data consisting of around 1000 objects; for a 200 object set I don't see the exception. I believe the salient point is the Out Of Memory bit - marked below by *'s The fact that this exception occurs during a call to org.postgresql.util.PSQLException.parseServerError() seems disturbing too. This is a server error message (which is why the stracktrace starts at parseServerError, although perhaps it shouldn't). This means the postgresql server ran out of memory, but you haven't provided any information as to why that is. You should check the server error log for more information. The key thing to check would be the actual query being run. Is it using a bad plan query plan? What other activity is going on at this time? How much memory do you have? Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Idle in Transaction and hung connections
On Thu, 29 Apr 2004, Gregory S. Williamson wrote: Tom -- Thanks for the suggestion, and the rapid response on something which may not be truely a postgres issue (perhaps more a JDBC thing)! This behavior is fixed in the 7.5 cvs version of the JDBC driver if you'd like to try it out. Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql idle
On Thu, 29 Apr 2004, Andrew Rawnsley wrote: I find that some clients (DBVisualizer for one) do exactly that - execute the COMMIT;BEGIN sequence, and leaves idle transactions on a consistent basis. The 7.5 JDBC driver has been fixed to avoid this problem. Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index on computed column
On Tue, 20 Apr 2004, Han Holl wrote: palga= create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0)); ERROR: parser: parse error at or near 'naamvrouw' at character 54 palga= This is postgresql-7.3.4-3.rhl9. You cannot create functional indexes with a constant in the 7.3 series. This capability was only added in the 7.4 series. You can work around this problem by creating a wrapper function that calls the real function with the constants and creating the index on the wrapper function. 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: [GENERAL] Postgres DB
On Fri, 23 Apr 2004, Sumita Biswas wrote: Hi All, Our application is using the Postgres 7.3.4-RH database that is packaged with the AS3.0 for CCM. There is a variable type called refcursor that is being used by CAR Functions in Postgres database. This variable works fine when we execute the postgres Function from the database i.e through psql. When we try to execute the Function from java code, it uses pg73jdbc3.jar which is bundled with the Postgres 7.3.4, and pg73jdbc3.jar does not support this variable type refcursor. It works fine with pg74jdbc3.jar which comes along with Postgres 7.4. Is this a bug? I don't know what you mean by is being used by CAR Functions in Postgres database, but I assume this is a client application you have installed. If so it is up to the application to specify its runtime requirements, which in this case include an updated jar file. If it did not specify this then the bug is with CAR, but not with postgres, client support often lags new backend functionality. Kris Jurka ---(end of broadcast)--- TIP 3: 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: [GENERAL] 7.4.2 on Solaris 9 - Error
On Fri, 19 Mar 2004, Carmen Gloria Sepulveda Dedes wrote: Hi. I want to install postgres 7.4.2 on solaris 9. I configure with: ./configure --enable-thread-safety and it works fine (no error). When I do gmake, I get this error: thread.c: In function `pqGetpwuid': thread.c:116: error: too many arguments to function `getpwuid_r' make[3]: *** [thread.o] Error 1 make[3]: Leaving directory `/home/postgres/postgresql-7.4.2/src/interfaces/libpq' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/postgres/postgresql-7.4.2/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/postgres/postgresql-7.4.2/src' make: *** [all] Error 2 Anybody knows the problem? This problem was just recently fixed in cvs and will go out in the 7.4.3 release. For no your best bet is to checkout the 7.4 branch from cvs, or search the pgsql-committers archives to find the patch you need to apply. Kris Jurka ---(end of broadcast)--- TIP 3: 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: [GENERAL] Transaction Isolation Level
On Thu, 25 Mar 2004, Grace C. Unson wrote: Hello. How is it possible that ResultSetObjInstance in this statement ResultSetObjInstance = PreparedObjInstance.executeQuery() contains this column and value respectively: TRANSACTION ISOLATION LEVEL , READ_COMMITTED ? --- Note: During the query operation, lots of write operations are going simultaneously in the DB. If this is the reason of the not-expected value above, why is it returned and not thrown as exception? This could be an indication of a thread safety problem in the driver. Are you using multiple threads on the same connection? The only code (other than your own) that could generate that ResultSet would be calling Connection.getTransactionIsolation() do you have any of these calls in your code? What version of the driver are you using? Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Character Encoding Confusion
On Mon, 8 Mar 2004, Markus Wollny wrote: Hi! As ODBC seems to be blissfully unaware of any character encodings whatsoever, so were we - our databases are encoded in SQL_ASCII, although we have stored german special chars (ÄÖÜäöü and ß), and from what I have read so far, these are stored as multibyte and thus exceed the SQL-ASCII specification. With ODBC we never noticed the mistake we'd made. Now with JDBC/ColdFusion MX 6.1, we see all sorts of weird characters on our web-application, but not the ones which are stored in the database. I tried setting different character sets for the JDBC-driver, using the URL-syntax jdbc:postgresql://123.456.789.012:5432/database?charSet=characterSet with charSet=iso-8859-1 or charSet=UTF-8 for example, but that just change anything. Now is there some way to elegantly resolve the issue without dropping and recreating the databases in order to change the encoding? Can we somehow get the JDBC-driver to act just as the ODBC-driver did - silently passing on the bad characters without changing anything? The JDBC driver needs the data encoded correctly, the ?charSet= option only works on 7.2 and earlier databases because then multibyte was not compiled in by default. This will require a dump and reload. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL 7.4.1 and pgdb.py
On Sun, 8 Feb 2004, Manuel Tejada wrote: Lamar Owen wrote: Since I don't necessarily keep up with what is going on in the Python client world, would people enlighten me as to which python client would be best to build RPMs for? I'm going to pull the python subpackage out of the main set, but I really would like to roll a set for the python clients, unless the maintainers of those now out of the main tarball clients have their own RPMs. As a user of PostgreSQL I totally agree with Gaetano Mendola. There is no reason to pull the python subpackage out of the main set, The decision to remove all interfaces from the main CVS tree was made in part to remove the responsibility of the core developers to maintain the build systems for various components and try to make sure that bugs and new backend functionality were addressed. While this allows core developers to focus solely on the backend it puts a lot of pressure on the packagers to track the various components they are now distributing. This is not the first packaging problem and it won't be the last if we rely on a very busy package maintainer to track each independent project. The only people who really know which version needs to go into an package are the projects' maintainers. Asking the interface projects to build and distribute their own packages is not going to work because they are not likely to have all of the requirements the existing packagers already have: expertise with the packaging system, access to machines to build this on a variety of platforms, and contacts with the upstream package distributors. Perhaps a system where each project maintainer could register the correct version of their package to go with each server version. This way in addition to the hackers email that goes out saying we're planning on making the 7.X.X release on Monday this would also go out to the project maintainers who would then produce a new version if necessary and register it on a website somewhere. Then when a packager is ready to produce a package he can check the website and immediately find for all packages the correct version of the package to distribute. Lamar, Oliver, interface maintainers, and others would that be useful? Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to kick out automatically stuck in queries
On Mon, 2 Feb 2004, [iso-8859-2] Együd Csaba wrote: Hi All, I'm wonder if there is any possibility to kick out automatically stuck in queries after say 10 minutes or so? I mean some kind of queries which calls eg. buggy functions with dead loops or something similar. Time to time I face such kind of problems which are solved automatically after some hours/days. Is there any options in config files or other settings to shorten this time? See the statement_timeout config parameter. http://www.postgresql.org/docs/7.3/static/runtime-config.html Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Unicode vs SQL_ASCII DBs
On Mon, 2 Feb 2004, John Sidney-Woollett wrote: Kris, thanks for you feedback. Can you give me any further info on the questions below? Kris Jurka said: 3) If I want accented characters to sort correctly, must I select UNICODE (or the appropriate ISO 8859 char set) over SQL_ASCII? You are confusing encoding with locale. Locales determines the correct sort order and you must choose an encoding that works with your locale. Except that in my test, the two differently encoded databases were in the same 7.4.1 cluster with the same locale, yet they sorted the *same* data differently - implying the encoding is a factor. Right, note the and you must choose an encoding that works with your locale. clause. A SQL_ASCII encoding and a UTF-8 locale don't work. I basically need english sorting, and accented character support without any JDBC access/conversion problems. Do you think that my current DB locale (en_GB.UTF-8) and UNICODE encoded database the best solution? Or can you suggest something better? If you need english sorting like en_GB then that is the best option, but if you just need regular sorting the C locale might be better. It is sometimes confusing how en_US (I assume GB is similar) sorts strings with spaces and punctuation and so on. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Unicode vs SQL_ASCII DBs
On Sat, 31 Jan 2004, John Sidney-Woollett wrote: Hi I need to store accented characters in a postgres (7.4) database, and access the data (mostly) using the postgres JDBC driver (from a web app). Does anyone know if: 2) Can SQL_ASCII be used for accented characters. Not with the JDBC driver. A client which is blissfully unaware of encoding issues can pass data into and out of an ascii db without knowing what the encoding is, but java must know. 3) If I want accented characters to sort correctly, must I select UNICODE (or the appropriate ISO 8859 char set) over SQL_ASCII? You are confusing encoding with locale. Locales determines the correct sort order and you must choose an encoding that works with your locale. 4) I'm not initially expecting arabic, chinese, cyrillic or other language types to be stored in the database. But if they were, would UNICODE be the best encoding scheme to use for future proofing the data? Yes. 7) Because the database is being used to backend a java web application, are there other issues that I need to be aware of, for example, do I have to convert all data received to UTF-8 before writing it into the database? And do I have to ensure that the response (from the webserver) content-type is always set to UTF-8 to be rendered correctly in a user's browser? The jdbc driver will correctly handle conversions between the database encoding and the encoding the jvm is run under. Receiving data from a web application is a little different because you must convert data from the client's encoding to the jvm's encoding for this to work. The simplest way to do this is just to make sure that you are using unicode in all three places (server,jvm, and client). Other things to note: LOWER()/UPPER() only work correctly in a single byte encoding (not unicode) If using binary data (bytea) via JDBC you may need to use a unicode db. I don't know if this has been fixed, but the server would attempt to do an encoding conversion on the binary data: http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00045.php Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] force drop of database others are accessing
On Sat, 24 Jan 2004, Lee Harr wrote: Every once in a while my automated script fails because some other system which should have disconnected failed to unhook. The old data and old connection are not important to me. The new run needs to go through. You can look for people connected and kick them out with kill -2 on their pid. The trick is to start your own script (which probably isn't failing exactly, but blocking, right?), and then (since you've locked the relevant objects that way, such that nobody else can get in) use the shell to kill -2 other connections. Evil and possibly dangerous, but you said you didn't care about the old data. Yea, grepping through the output from ps and killing the other backend process was my first thought. The only problem is that the user that owns the database (and runs the automatic report script) is not necessarily the one that is causing the problem. So that means somehow I need to kick off another script with elevated priveleges to take care of it ... I would rather not have to do that, but I do not see another way. If you are creating a database from scratch why not create a brand new database and drop the old one at some later point in time when no one is connected. This has the additional benefit of the old db being available while the new one is being built. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL Exception Relation xxx does not exist
On Wed, 21 Jan 2004, Alex wrote: Kris, thanks for the reply. I dont actually use temp tables in the function (not that I know of) but I did truncated and reloaded a few tables incl. recreating indices the previous day, however the line no. indicated in the serverlog does not point to these sql calls. I am now re-starting tomcat every night which is not a bad thing anyway but still am wondering what the real reason could be. The reason is probably the recreation of the indexes. When a plpgsql procedure is first executed it takes the queries in it, plans them, and saves that query plan so that every time afterward the procedure just uses the stored plan. The problem is that one of the plans depended on one of the indexes. When the index was dropped the procedure tried to run a plan that was no longer valid, generating the error you saw. Restarting tomcat closes and reopens the connection to the database, so the query in the procedure gets replanned to use the new index and things run smoothly. 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])
