RE: [JDBC] RE: fastpath error?
Hello. I'm still having problems with this FastPath error. As I mentioned, autoCommit is always set to 'false' in our system, so could there be another source of this? Another angle - Can it be ignored? For example, if I get the error during lo_close and ignore it, am I okay? If so, what exception am I looking for? >From the stack trace, it seems that it's just a java.lang.Exception - not a SQLExceptionDo I just grab the text of the exception and look for "FastPath" or is there a better way? I really appreciate your help. chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of chris markiewicz Sent: Thursday, April 12, 2001 9:36 AM To: 'Peter Mount'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: [JDBC] RE: fastpath error? Regarding the fastpath error (ERROR: lo_close: invalid large obj descriptor (0)...i also get this with lo_tell, etc)... is there any possible cause that is not related to autoCommit? our app does a lot of transactional stuff so autocommit is set to false for all connections (i added some logging that shows me the autocommit attribute and it's false even when this error occurs)...i still see this problem occasionally/randomly. i appreciate your help. chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Mount Sent: Thursday, March 29, 2001 4:13 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: fastpath error? At 07:56 28/03/01 -0500, chris markiewicz wrote: >Hello. > >I am occasionally (seemingly randomly) seeing the following error when I try >to load a large object. > >FastPath call returned ERROR: lo_close: invalid large obj descriptor (0) You must setAutoCommit(false) before using any large object. Can someone give me some insight on this? Can I call lo_close explicitly if >i get this error? Can I ignore it or will that leave me with open >resources? There are no open resources at the time of the error. Because lo's need to be within a transaction when the transaction closes, so do the lo's. Peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] ExecSQL NullPtrEx...conn timeout?
i am seeing NullPtrExs with the following stack trace: java.lang.NullPointerException at org.postgresql.Connection.ExecSQL(Connection.java:312) at org.postgresql.jdbc2.Connection.commit(Connection.java:173) at com.commnav.sbh.framework.persist.JDBCConnection.disconnect(JDBCConnection.j ava:416) at com.commnav.sbh.framework.persist.ConnectionPoolImpl.expire(ConnectionPoolIm pl.java:245) at com.commnav.sbh.framework.ObjectPool.clean(ObjectPool.java:178) at com.commnav.sbh.framework.ObjectPool.run(ObjectPool.java:330) at java.lang.Thread.run(Thread.java:484) Our object pool disconnects unused open connections every few minutes. Do the connections themselves timeout? is this a parameter somewhere? I am wondering if I am trying to close a connection that is already closed? please respond directly to me - i'm not in the jdbc group. thanks chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] fastpath error?
Hello. I am occasionally (seemingly randomly) seeing the following error when I try to load a large object. FastPath call returned ERROR: lo_close: invalid large obj descriptor (0) at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:141) at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:191) at org.postgresql.largeobject.LargeObject.close(LargeObject.java:105) at org.postgresql.jdbc2.ResultSet.getBytes(ResultSet.java:371) at org.postgresql.jdbc2.ResultSet.getBytes(ResultSet.java:580) at com.commnav.sbh.framework.persist.JDBCEngine2.loadResultSet(JDBCEngine2.java :259) at com.commnav.sbh.framework.persist.JDBCEngine2.load(JDBCEngine2.java:156) at com.commnav.sbh.framework.persist.PersistenceObject.load(PersistenceObject.j ava:147) Can someone give me some insight on this? Can I call lo_close explicitly if i get this error? Can I ignore it or will that leave me with open resources? thanks chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] RE: finding and removing a constraint...
okay, i screwed up. i dropped some triggers from a table. now that table is not accessible to me. can't drop it...can't select...can't pg_dump. message is error: RelationBuildTriggers: 2 record(s) not found for rel accessor_group or a pg_dump results in: getTables(): relation 'accessor_group': 6 Triggers were expected, but got 4. is there any way that i can re-insert them? if i'm screwed, then how do i drop this table? i really appreciate your help. chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of chris markiewicz Sent: Tuesday, March 20, 2001 3:16 PM To: 'PostgreSQL-general' Subject: finding and removing a constraint... hello i am trying to remove a foreign key that i placed on a database. what is the easiest way to find/delete the constraint? thanks chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] finding and removing a constraint...
hello i am trying to remove a foreign key that i placed on a database. what is the easiest way to find/delete the constraint? thanks chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] length of insert stmt?
i am using jdbc7.0-1.2...postgres 7.0.2. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 08, 2001 10:28 AM To: Gavin Sherry Cc: chris markiewicz; [EMAIL PROTECTED] Subject: Re: [GENERAL] length of insert stmt? Gavin Sherry <[EMAIL PROTECTED]> writes: > You seem to have hit the 8Kb row limit. No, I think he's hit some limit on the size of a query string. Before about 7.0, there was a limit on the textual length of queries. We got rid of it in the backend and libpq, but I think some of the lesser-used interface libraries still think they can/should limit query length. Chris didn't say what version of what interface he was using, though... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] length of insert stmt?
hello i received an error when someone ran an input stmt with a very long sting. the field is of type 'text'. The error (along with the statement) are shown below. what is the proper way do execute this insert? The SQL Statement is too long - INSERT INTO accessor_group ( groupid, groupname, grouptype, groupclassname, groupdescription, hidden ) VALUES ( 22395, 'No TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something Something', 'community', 'com.commnav.sbh.objects.Group', 'No TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Make Homer Something SomethingNo TV and No Beer Mak
[GENERAL] database diff
hello i want to find the differences between two database schemas...is there a function for this or do i just pg_dump both of them and do a diff in unix? thanks chris
[GENERAL] clean rebuild process?
hello. i am trying to develop a relatively clean process for creating/populating a new database. i have all of the table creation scripts and am about to start writing scripts to populate the db with the necessary default data (an admin user, a guest user, default application info, default preferences, etc etc etc). is there any easy way to do this? my current approach is to write a bunch of insert statements in a text editor. i can do this, but it's error prone (or i guess i am). obviously if i already have a source db i can do some table copying, but i would like to make this work for a fresh install on a new machine. is there a better way? the limit of my imaginatin is this - some sort of command/utility that queries a table and generates the insert statements from the results. then i wouldn't have to worry about updating my default data scripts everytime data changes. i appreciate your help/suggestions. chris
[GENERAL] RE: performance...
vacuum analyze seems to have done the trick...this is the sort of thing that happens when a non-dba is doing dba work... sincerest thanks to all that responded! chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of adb Sent: Monday, January 29, 2001 4:36 PM To: chris markiewicz Cc: [EMAIL PROTECTED] Subject: Re: performance... First thing to try is to do explain select * from weather where weatherid = 12372; and see if it's doing a sequential scan on your table. If it is, the index and table stats may be out of date and I think you fix those using vacuum analyze. One other thing is that if you only have 1100 rows of the sizes you describe, even a table scan shouldn't take as long as you say unless the machine is either under serious load or doesn't have enough memory to run postgres without swapping. (or the disk is freaking out with io errors) Alex. On Mon, 29 Jan 2001, chris markiewicz wrote: > hello. > > this might be as much of a general database question as it is a postgres > question... > > i have a table with 5 columns...a primary key (integer), three small (10 > character) text fields, and one semi-large (1400 characters) text field. > note that only a small percentage (5% ?) of the rows contain 1400 characters > in the 5th column...the other 95% have approx 10 characters. it has 1100 > rows. > > the problem is this - queries (command line) often take a very long time - > anywhere from 5-15 seconds - to execute. the queries use only the primary > key and nothing else in the where clause. no joins. a sample query is: > > select * from weather where weatherid = 12372; > > from the command line, it seems that the first query can take a very long > time but subsequent queries happen quickly ( < 1 sec). i'm guessing that > this is the result of caching or something. > > do the long times make sense? what can i do to shorten them? would a > smaller text field help? i have no reason to think that this would be > faster or slower in another db, so it might be unrelated to postgres itself. > > i greatly appreciate your help. > > chris >
[GENERAL] too many clients?
Sorry, too many clients already hello. i am getting the above message when i try to connect. where do i find documentation about connection management? i want to be able to see all current connections and kill some of them. how do i do that? thanks chris
RE: [GENERAL] trouble with trigger/function???
Excellent, thanks. I really apperciate your help. One FINAL question... compiling...i get a compile error when i try to run my procedure. i then dumbed it down to use the example from the documentation...(note that i changed the language to plpgsql.) CREATE FUNCTION onefn() RETURNS int4 AS ' BEGIN SELECT 1 AS RESULT; END;' LANGUAGE 'plpgsql'; this gives me a compile error: NOTICE: plpgsql: ERROR during compile of onefn near line 1 "RROR: parse error at or near " I tried a handful of variations...including returning opaque...that gives me a different error (typeidTypeRelid: Invalid type - oid = 0) thanks chris -Original Message- From: Nelson Ferreira Jr [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 03, 2000 9:42 AM To: [EMAIL PROTECTED] Cc: 'Postgres (E-mail)' Subject: Re: [GENERAL] trouble with trigger/function??? Below is what you have to do to load PL/pgSQL into a database: Pay atention to the plpgsql.so path, if your PostgreSQL is instaled in any other plate than /usr/local/pgsql CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; Nelson chris markiewicz wrote: > Gotcha. So what's the story on pl/pgsql? The documentation says: > > "PL/pgSQL is a loadable procedural language for the Postgres database > system." > > What does 'loadable' mean here? Is it just a matter of executing CREATE > LANGUAGE? (I assume that there is more to it than that.) When I try to > create a procedure now, it tells me that plpgsql is not recognized. > > I really appreciate your help. > chris > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Nelson Ferreira Jr > Sent: Tuesday, October 03, 2000 8:11 AM > To: [EMAIL PROTECTED] > Cc: Postgres (E-mail) > Subject: Re: [GENERAL] trouble with trigger/function??? > > Hi, > > Triggers cannot be writen in SQL. I suggest you use PL/PGSQL instead. > In > fact you can use any server-side language, except SQL. > The return type of the function must be OPAQUE, it means that the NEW > variable is returned (NEW represents the row that is being inserted and so > fired the trigger) > > Nelson > > chris markiewicz wrote: > > > hello. > > > > i have been trying to create a trigger or trigger/function combination but > i > > have so far been unsuccessful. my task is simple, when someone inserts a > > row in the PERSON table, create a row in the RESOURCE table (note that i > > have to pass parameters). details below... > > > > first, the basic procedure and trigger are shown here: > > > > CREATE FUNCTION sp_person_resource() RETURNS bool > > AS 'insert into resource(rid, name, type, desc) values (1905, > ''chris'', > > ''person'', ''blah''); select true;' > > LANGUAGE 'sql'; > > > > CREATE TRIGGER trig_person_resource BEFORE INSERT ON person > > FOR EACH ROW EXECUTE PROCEDURE sp_person_resource(); > > > > i do not know what to use for a return type (i don't really need to return > > anything). but when i try to write a trigger to call that fn, it tells me > > that the fn must return opaque...i modify my proc but it tells me that > "sql > > fns cannot return type opaque." > > > > what's going on? and a side question, what is opaque? > > > > thanks > > chris
RE: [GENERAL] trouble with trigger/function???
Gotcha. So what's the story on pl/pgsql? The documentation says: "PL/pgSQL is a loadable procedural language for the Postgres database system." What does 'loadable' mean here? Is it just a matter of executing CREATE LANGUAGE? (I assume that there is more to it than that.) When I try to create a procedure now, it tells me that plpgsql is not recognized. I really appreciate your help. chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nelson Ferreira Jr Sent: Tuesday, October 03, 2000 8:11 AM To: [EMAIL PROTECTED] Cc: Postgres (E-mail) Subject: Re: [GENERAL] trouble with trigger/function??? Hi, Triggers cannot be writen in SQL. I suggest you use PL/PGSQL instead. In fact you can use any server-side language, except SQL. The return type of the function must be OPAQUE, it means that the NEW variable is returned (NEW represents the row that is being inserted and so fired the trigger) Nelson chris markiewicz wrote: > hello. > > i have been trying to create a trigger or trigger/function combination but i > have so far been unsuccessful. my task is simple, when someone inserts a > row in the PERSON table, create a row in the RESOURCE table (note that i > have to pass parameters). details below... > > first, the basic procedure and trigger are shown here: > > CREATE FUNCTION sp_person_resource() RETURNS bool > AS 'insert into resource(rid, name, type, desc) values (1905, ''chris'', > ''person'', ''blah''); select true;' > LANGUAGE 'sql'; > > CREATE TRIGGER trig_person_resource BEFORE INSERT ON person > FOR EACH ROW EXECUTE PROCEDURE sp_person_resource(); > > i do not know what to use for a return type (i don't really need to return > anything). but when i try to write a trigger to call that fn, it tells me > that the fn must return opaque...i modify my proc but it tells me that "sql > fns cannot return type opaque." > > what's going on? and a side question, what is opaque? > > thanks > chris
[GENERAL] trouble with trigger/function???
hello. i have been trying to create a trigger or trigger/function combination but i have so far been unsuccessful. my task is simple, when someone inserts a row in the PERSON table, create a row in the RESOURCE table (note that i have to pass parameters). details below... first, the basic procedure and trigger are shown here: CREATE FUNCTION sp_person_resource() RETURNS bool AS 'insert into resource(rid, name, type, desc) values (1905, ''chris'', ''person'', ''blah''); select true;' LANGUAGE 'sql'; CREATE TRIGGER trig_person_resource BEFORE INSERT ON person FOR EACH ROW EXECUTE PROCEDURE sp_person_resource(); i do not know what to use for a return type (i don't really need to return anything). but when i try to write a trigger to call that fn, it tells me that the fn must return opaque...i modify my proc but it tells me that "sql fns cannot return type opaque." what's going on? and a side question, what is opaque? thanks chris
[GENERAL] windows UI tool???
hello. can anyone recommend a windows ui tool for viewing data in postgres? essentially i want to use something other than the command line for looking at results. as an emample, i'm looking for the equivalent of TOAD for Oracle. is the easiest approach to connect in MS Access via odbc? thanks chris
RE: [GENERAL] Error in servlet
could this be a servlet/thread issue? i cannot tell from the code snippet,
but remember that variables in a servlet with class scope are essentially
static. (i am guessing that query, rs, stmt, etc are all class scope. this
is very dangerous, in the programming sense of the word...) i've had
similar (but not the same) problems before. as a general rule, i NEVER put
a class scope variable in a servlet unless i really mean to.
chris
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Andreas Tille
Sent: Thursday, September 14, 2000 11:21 AM
To: PostgreSQL General
Subject: [GENERAL] Error in servlet
Hello,
is there any known reason for the following behaviour:
I wrote a servlet with several database accesses via stored functions
and select statements. On a certain point of my servlet I get
the following:
[14/09/2000 16:31:51:216 CEST] Runtime.log:
org.postgresql.jdbc1.ResultSet@80ab4ff
[14/09/2000 16:31:51:217 CEST] java.lang.NullPointerException:
at org.postgresql.jdbc1.ResultSet.getString(ResultSet.java)
at WebSQL.pGetGroupMemberByLocation(WebSQL.java:484)
at Web.DoWeb(Web.java:272)
at ehec.service(ehec.java:24)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:311)
at
org.apache.jserv.JServConnection.processRequest(JServConnection.java:314)
at org.apache.jserv.JServConnection.run(JServConnection.java:188)
at java.lang.Thread.run(Thread.java)
I try to give a short description of the critical function in
WebSQL.java:484
- snip
public boolean pGetGroupMemberByLocation(Webdata data, int IdStandort,
String S_IdArbeitsgruppe, String
S_IdMitarbeiter)
{
try {
query = "SELECT * FROM Mitarbeiter; " ;
query = "SELECT count(*) As FName FROM standort; " ;
query = "SELECT '' || i.MenuItem || '' " +
"AS getmenu FROM Menu m " +
"INNER JOIN MenuItem i ON i.IdMenu = m.IdMenu WHERE m.IdMenu
= " + "54" +
" ORDER BY i.Sort ; " ;
rs = stmt.executeQuery(query);
if ( rs == null ) error.Common("Write message to logfile");
else error.Common("rs = " + rs); // This is the entry in Runtime.log
query = rs.getString(1);
error.Common("Are you living?");
snip
I tried different querys (see the strings "query"). First I reduced to
simple ones, later I used a more complex which worked inside the same
servlet some statements before and worked. They show all the same
behaviour to fail with the NullPointerException.
I'm using PostgreSQL 7.0.2, Apache, Jserv.
The behaviour is the same using
http://www.retep.org.uk/postgres/jdbc7.0-1.1.jar
http://www.retep.org.uk/postgres/jdbc7.0-1.2.jar
as JDBC driver.
I'm really in desperation with this stupid problem and tried
two days to solve it but failed. Any idea what might went wrong
here. It is a problems of threads od anything else. I can't see
any pointer which could be null here.
Really hoping for your help
Andreas.
