Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
we use a hashtable with connections as keys and timestamps as values...we do that so that we can clean up connections that haven't been used in a while...i know that some DBs invalidate their connections after a certain amount of time, so we have a process that periodically walks through the timestamps and cleans as necessary... anyway, this could be handled just as easily with a linked list of wrapped connections. can either of you suggest why the hashtable approach might be causing a problem? or is it probably elsewhere in my code? dave, if you have no trouble with donating your code, i would be more than happy to take a look at it...i'd really appreciate that. thanks for your help. chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Cramer Sent: Thursday, September 06, 2001 9:10 PM To: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... AFAIR, I simply wrap the sql connection for ease of handling, and then put available connections in the linked list. When I want one I remove it from the linked list, and put it into a hashtable, keyed by the toString method on the connection. When it is returned, I remove it from the hash table and put it back into the linked list Since it's a linked list, it can grow and shrink at will. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of [EMAIL PROTECTED] Sent: September 6, 2001 8:14 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I also built a linked list of available connections today and my problem has completly gone away. I have an intensly threaded app and it has been running now for several hours doing 1000's of inserts without the problem I was having before. I don't know how dave implemented his list but I have an inner class with a connection and a boolean marking whether it is in use. each insert gets a connection that is not in use from the linked list and marks it in use until it is finished. I also built in the ability for the list to grow and shrink dynamically based on need. So if all connections are in use I will create more and after a specified period of time I delete the ones that haven't been used. This seems to be working quite nicely now. t.r. -Original Message- From: Dave Cramer [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 6:05 PM To: [EMAIL PROTECTED]; Missner, T. R.; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I'm curious; why are you using a hashtable for available connections, and how do you use it? I have been using a connection pool that uses a linked list, in LIFO mode to store available connections. I can donate the pool code if need be, it has been running for over a year with no problems. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of chris markiewicz Sent: September 6, 2001 2:58 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... spoke too soon. i see the error again (with 7.1) ...i no longer get the transaction isolation level issue, but i still get the "NOTICE: current trans..." error. some improvement, i guess. whether it's active? that isn't ever really an issue with our approach, unless i'm misunderstanding your question...this is an oversimplification, but: we have a connection pool that has a hashtable of "available" connections...there is also a user-connection hashtable with users as keys and connections as values. if a user wants to hit the db, the system first checks the user-connection hashtable. if no conn is associated with the user, it grabs a connection from "available" connection hashtable and puts it in the user-connection hashtable. the user then uses that connection etc etc...when the transaction is closed (by our system code) the connection is "committed," removed from the the user-connection hashtable, and returned to the "available" hashtable. we don't use any threading, so there would never be two threads trying to use the same connection. should i be checking for anything else? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 2:29 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I am in the process of rewriting my connection pooling object. I'll share the results when i am finished later today I suspect. How are you determining whether a transaction is active? getAutoCommit? -Original Message- From: chris markiewi
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
AFAIR, I simply wrap the sql connection for ease of handling, and then put available connections in the linked list. When I want one I remove it from the linked list, and put it into a hashtable, keyed by the toString method on the connection. When it is returned, I remove it from the hash table and put it back into the linked list Since it's a linked list, it can grow and shrink at will. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of [EMAIL PROTECTED] Sent: September 6, 2001 8:14 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I also built a linked list of available connections today and my problem has completly gone away. I have an intensly threaded app and it has been running now for several hours doing 1000's of inserts without the problem I was having before. I don't know how dave implemented his list but I have an inner class with a connection and a boolean marking whether it is in use. each insert gets a connection that is not in use from the linked list and marks it in use until it is finished. I also built in the ability for the list to grow and shrink dynamically based on need. So if all connections are in use I will create more and after a specified period of time I delete the ones that haven't been used. This seems to be working quite nicely now. t.r. -Original Message- From: Dave Cramer [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 6:05 PM To: [EMAIL PROTECTED]; Missner, T. R.; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I'm curious; why are you using a hashtable for available connections, and how do you use it? I have been using a connection pool that uses a linked list, in LIFO mode to store available connections. I can donate the pool code if need be, it has been running for over a year with no problems. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of chris markiewicz Sent: September 6, 2001 2:58 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... spoke too soon. i see the error again (with 7.1) ...i no longer get the transaction isolation level issue, but i still get the "NOTICE: current trans..." error. some improvement, i guess. whether it's active? that isn't ever really an issue with our approach, unless i'm misunderstanding your question...this is an oversimplification, but: we have a connection pool that has a hashtable of "available" connections...there is also a user-connection hashtable with users as keys and connections as values. if a user wants to hit the db, the system first checks the user-connection hashtable. if no conn is associated with the user, it grabs a connection from "available" connection hashtable and puts it in the user-connection hashtable. the user then uses that connection etc etc...when the transaction is closed (by our system code) the connection is "committed," removed from the the user-connection hashtable, and returned to the "available" hashtable. we don't use any threading, so there would never be two threads trying to use the same connection. should i be checking for anything else? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 2:29 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I am in the process of rewriting my connection pooling object. I'll share the results when i am finished later today I suspect. How are you determining whether a transaction is active? getAutoCommit? -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 11:40 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... trm i have been able to successfully avoid the problem for the last 20 minutes or so...my connection pooling system calls commit() on connections before returning them to the "available" pool (even if they were already 'committed')...i added a conditional to check whether or not it was already committed - if it was, i don't run the commit. this seems to prevent the "transaction isolation level" exception that always preceded my other exceptions. might this apply to your pooling system as well? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 10:33 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
I also built a linked list of available connections today and my problem has completly gone away. I have an intensly threaded app and it has been running now for several hours doing 1000's of inserts without the problem I was having before. I don't know how dave implemented his list but I have an inner class with a connection and a boolean marking whether it is in use. each insert gets a connection that is not in use from the linked list and marks it in use until it is finished. I also built in the ability for the list to grow and shrink dynamically based on need. So if all connections are in use I will create more and after a specified period of time I delete the ones that haven't been used. This seems to be working quite nicely now. t.r. -Original Message- From: Dave Cramer [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 6:05 PM To: [EMAIL PROTECTED]; Missner, T. R.; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I'm curious; why are you using a hashtable for available connections, and how do you use it? I have been using a connection pool that uses a linked list, in LIFO mode to store available connections. I can donate the pool code if need be, it has been running for over a year with no problems. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of chris markiewicz Sent: September 6, 2001 2:58 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... spoke too soon. i see the error again (with 7.1) ...i no longer get the transaction isolation level issue, but i still get the "NOTICE: current trans..." error. some improvement, i guess. whether it's active? that isn't ever really an issue with our approach, unless i'm misunderstanding your question...this is an oversimplification, but: we have a connection pool that has a hashtable of "available" connections...there is also a user-connection hashtable with users as keys and connections as values. if a user wants to hit the db, the system first checks the user-connection hashtable. if no conn is associated with the user, it grabs a connection from "available" connection hashtable and puts it in the user-connection hashtable. the user then uses that connection etc etc...when the transaction is closed (by our system code) the connection is "committed," removed from the the user-connection hashtable, and returned to the "available" hashtable. we don't use any threading, so there would never be two threads trying to use the same connection. should i be checking for anything else? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 2:29 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I am in the process of rewriting my connection pooling object. I'll share the results when i am finished later today I suspect. How are you determining whether a transaction is active? getAutoCommit? -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 11:40 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... trm i have been able to successfully avoid the problem for the last 20 minutes or so...my connection pooling system calls commit() on connections before returning them to the "available" pool (even if they were already 'committed')...i added a conditional to check whether or not it was already committed - if it was, i don't run the commit. this seems to prevent the "transaction isolation level" exception that always preceded my other exceptions. might this apply to your pooling system as well? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 10:33 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I really don't understand why this is happening either. my current guess is that there is something going on in the way we are reusing connections. To answer your question I don't think there is any relationship between the 2 connections just that after the first one is corrupted I use the second one until it gets corrupted, then create new ones and start again. I have to assume that we are doing something wrong in our use of these connections and am focusing my effort in that direction right now. Sure wish one of the experts would chime in on this. t.r. -Original Message
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
Chris, I'm curious; why are you using a hashtable for available connections, and how do you use it? I have been using a connection pool that uses a linked list, in LIFO mode to store available connections. I can donate the pool code if need be, it has been running for over a year with no problems. Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of chris markiewicz Sent: September 6, 2001 2:58 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... spoke too soon. i see the error again (with 7.1) ...i no longer get the transaction isolation level issue, but i still get the "NOTICE: current trans..." error. some improvement, i guess. whether it's active? that isn't ever really an issue with our approach, unless i'm misunderstanding your question...this is an oversimplification, but: we have a connection pool that has a hashtable of "available" connections...there is also a user-connection hashtable with users as keys and connections as values. if a user wants to hit the db, the system first checks the user-connection hashtable. if no conn is associated with the user, it grabs a connection from "available" connection hashtable and puts it in the user-connection hashtable. the user then uses that connection etc etc...when the transaction is closed (by our system code) the connection is "committed," removed from the the user-connection hashtable, and returned to the "available" hashtable. we don't use any threading, so there would never be two threads trying to use the same connection. should i be checking for anything else? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 2:29 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I am in the process of rewriting my connection pooling object. I'll share the results when i am finished later today I suspect. How are you determining whether a transaction is active? getAutoCommit? -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 11:40 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... trm i have been able to successfully avoid the problem for the last 20 minutes or so...my connection pooling system calls commit() on connections before returning them to the "available" pool (even if they were already 'committed')...i added a conditional to check whether or not it was already committed - if it was, i don't run the commit. this seems to prevent the "transaction isolation level" exception that always preceded my other exceptions. might this apply to your pooling system as well? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 10:33 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I really don't understand why this is happening either. my current guess is that there is something going on in the way we are reusing connections. To answer your question I don't think there is any relationship between the 2 connections just that after the first one is corrupted I use the second one until it gets corrupted, then create new ones and start again. I have to assume that we are doing something wrong in our use of these connections and am focusing my effort in that direction right now. Sure wish one of the experts would chime in on this. t.r. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 7:17 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... tr things have gotten a little worse...i recently upgraded to postgresql 7.1 and now i am seeing this behavior even WITHOUT large objects. it happens much less frequently without LOs, but it still happens. i never saw this behavior in 7.0. are you seeing the same behavior? i guess i don't understand the following: right, each connection is a different process. so if you use the second connection, why should it fail too? it should have no relationship to the first connection, since it is a different process. i was discussing this with a co-worker and he suggested that the first connection might be "spilling" into the second (when you grab the first two connections, they may inhabit contiguous memory spaces...the first one gets corrupted and corrupts the second...i ad
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
I am in the process of rewriting my connection pooling object. I'll share the results when i am finished later today I suspect. How are you determining whether a transaction is active? getAutoCommit? -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 11:40 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... trm i have been able to successfully avoid the problem for the last 20 minutes or so...my connection pooling system calls commit() on connections before returning them to the "available" pool (even if they were already 'committed')...i added a conditional to check whether or not it was already committed - if it was, i don't run the commit. this seems to prevent the "transaction isolation level" exception that always preceded my other exceptions. might this apply to your pooling system as well? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 10:33 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I really don't understand why this is happening either. my current guess is that there is something going on in the way we are reusing connections. To answer your question I don't think there is any relationship between the 2 connections just that after the first one is corrupted I use the second one until it gets corrupted, then create new ones and start again. I have to assume that we are doing something wrong in our use of these connections and am focusing my effort in that direction right now. Sure wish one of the experts would chime in on this. t.r. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 7:17 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... tr things have gotten a little worse...i recently upgraded to postgresql 7.1 and now i am seeing this behavior even WITHOUT large objects. it happens much less frequently without LOs, but it still happens. i never saw this behavior in 7.0. are you seeing the same behavior? i guess i don't understand the following: right, each connection is a different process. so if you use the second connection, why should it fail too? it should have no relationship to the first connection, since it is a different process. i was discussing this with a co-worker and he suggested that the first connection might be "spilling" into the second (when you grab the first two connections, they may inhabit contiguous memory spaces...the first one gets corrupted and corrupts the second...i admit that i am no unix expert so while i can picture such a thing, i have no idea whether or not it is possible.) anyway, you grab the third connection and it's not corrupt. does that make any sense? chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 12:45 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... What I have is a connection pool. I initially grab 2 connections, one primary one secondary. If the insert fails on the primary connection I try the secondary, if it fails I simply delete the 2 I have and grab 2 more from the connection pool. After creating the new ones I have never seen it fail the first few times which is what leads me to believe that something in the connection itself is getting corrupted over time. Note that each connection is a separate process in the unix environment. I believe it is this process that is getting corrupted note necessarily the java connection wrapper. Of course this is all guess work right now. I'll let you know if I come up with a better solution or are able to determine why these connections act like they are getting corrupt. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 5:42 AM To: Missner, T. R.; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... follow-up question - you say that if you get one exception, you try the insert with another connection...if that doesn't work you delete the existing connections and get new ones... i must be missing something - why wouldn't using the second connection always work? if you get rid of the offending connection (the first one), why does the second one have a problem? or am i confusing your connection object with the actual connection? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, Sep
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
> the 7.1 driver that i am using was downloaded from > http://jdbc.fastcrypt.com. is that the right spot? Yep, that is the one. --- > > chris > > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 06, 2001 12:40 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > > > "chris markiewicz" <[EMAIL PROTECTED]> writes: > > i do agree that since everyone isn't complaining, the problem is likely in > > my code. the odd thing is that i wasn't seeing this behavior with the 7.0 > > stuff, only with the 7.1. > > Hmm. Are you using the JDBC driver that was released with 7.1? That > seems to have been rather buggy. You might want to grab the latest > version of the driver (I forget the URL but it's been mentioned > repeatedly on this list). > > regards, tom lane > > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
trm i have been able to successfully avoid the problem for the last 20 minutes or so...my connection pooling system calls commit() on connections before returning them to the "available" pool (even if they were already 'committed')...i added a conditional to check whether or not it was already committed - if it was, i don't run the commit. this seems to prevent the "transaction isolation level" exception that always preceded my other exceptions. might this apply to your pooling system as well? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 10:33 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I really don't understand why this is happening either. my current guess is that there is something going on in the way we are reusing connections. To answer your question I don't think there is any relationship between the 2 connections just that after the first one is corrupted I use the second one until it gets corrupted, then create new ones and start again. I have to assume that we are doing something wrong in our use of these connections and am focusing my effort in that direction right now. Sure wish one of the experts would chime in on this. t.r. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 7:17 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... tr things have gotten a little worse...i recently upgraded to postgresql 7.1 and now i am seeing this behavior even WITHOUT large objects. it happens much less frequently without LOs, but it still happens. i never saw this behavior in 7.0. are you seeing the same behavior? i guess i don't understand the following: right, each connection is a different process. so if you use the second connection, why should it fail too? it should have no relationship to the first connection, since it is a different process. i was discussing this with a co-worker and he suggested that the first connection might be "spilling" into the second (when you grab the first two connections, they may inhabit contiguous memory spaces...the first one gets corrupted and corrupts the second...i admit that i am no unix expert so while i can picture such a thing, i have no idea whether or not it is possible.) anyway, you grab the third connection and it's not corrupt. does that make any sense? chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 12:45 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... What I have is a connection pool. I initially grab 2 connections, one primary one secondary. If the insert fails on the primary connection I try the secondary, if it fails I simply delete the 2 I have and grab 2 more from the connection pool. After creating the new ones I have never seen it fail the first few times which is what leads me to believe that something in the connection itself is getting corrupted over time. Note that each connection is a separate process in the unix environment. I believe it is this process that is getting corrupted note necessarily the java connection wrapper. Of course this is all guess work right now. I'll let you know if I come up with a better solution or are able to determine why these connections act like they are getting corrupt. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 5:42 AM To: Missner, T. R.; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... follow-up question - you say that if you get one exception, you try the insert with another connection...if that doesn't work you delete the existing connections and get new ones... i must be missing something - why wouldn't using the second connection always work? if you get rid of the offending connection (the first one), why does the second one have a problem? or am i confusing your connection object with the actual connection? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 3:35 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception an
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
"chris markiewicz" <[EMAIL PROTECTED]> writes: > i do agree that since everyone isn't complaining, the problem is likely in > my code. the odd thing is that i wasn't seeing this behavior with the 7.0 > stuff, only with the 7.1. Hmm. Are you using the JDBC driver that was released with 7.1? That seems to have been rather buggy. You might want to grab the latest version of the driver (I forget the URL but it's been mentioned repeatedly on this list). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
the 7.1 driver that i am using was downloaded from http://jdbc.fastcrypt.com. is that the right spot? chris -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 12:40 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... "chris markiewicz" <[EMAIL PROTECTED]> writes: > i do agree that since everyone isn't complaining, the problem is likely in > my code. the odd thing is that i wasn't seeing this behavior with the 7.0 > stuff, only with the 7.1. Hmm. Are you using the JDBC driver that was released with 7.1? That seems to have been rather buggy. You might want to grab the latest version of the driver (I forget the URL but it's been mentioned repeatedly on this list). regards, tom lane ---(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: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
new info...maybe it's too early to say, but it seems that i only have this problem with 7.1...i just went back to 7.0 db and jdbc driver and now i am running without incident... t.r. - what are you running? have any connection parameters changed? is there anything i need to change in order to upgrade to 7.1? (other than that rs.absolute(index + 1) thing)? for a brief period, i was running 7.0 with the 7.1 drivers and i was getting the same behavior...don't know if that means anything to anyone... a brief history of how i got here: for the past year i have been running 7.0 with one problem - fastpath errors. i recently decided to make the upgrade to 7.1 with the hopes that i might get more info on my FP problem. so i switched and started getting the errors that we have been discussing here - whether or not i work with Large Objects. i have noticed one other pattern - this might be very important: it seems like my set of errors is always preceded by one of those "must set transaction isolation level..." messages. to be more precise, it is always preceded by the system trying to commit all connections that are associated with a user so that they can throw them back to the "available" connection pool. when this commit() is run, i get the transaction isolation level exception, then everything falls apart. will this happen if i call commit on a conn that was already committed? what causes that error to be thrown??? thanks chris -Original Message- From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin Sent: Thursday, September 06, 2001 11:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I understand. However, doesn't the exception automatically do some kind of rollback? I can't remember where I read such a thing... Antonio chris markiewicz wrote: > antonio > > thanks. it actually does apply to some degree. i have noticed that rolling > back the connection will sometimes prevent subsequent errors, but for some > reason it is not completely predictable... > > the bigger issue is that the db is throwing errors that i don't understand. > i might have just discovered a pattern - i'll post it as soon as i am more > certain of it. but rolling back whenever i get an unpredictable exception > will kill any transaction. this is a problem. it will also require some > localized but non-trivial rework in the persistence layer. i really don't > want to do that unless i have no other avenues. > > thanks for your response. > > chris > > -Original Message- > From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin > Sent: Thursday, September 06, 2001 11:18 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > I'm no expert at all, but, --forgive me!!-- have you tried to roll back upon > the first exception you get? > > Or maybe try to get the warnings in your ResultSet to see if something is > there, though I do not remember having done so. > > I remember having something similar when I tried to read from a table that > did > not exist (mangled name). That request throwed an exception, and if I > ignored > the exception, all subsequent requests did show this kind of behaviour, or a > similar one. > > Forget my message if it doesn't apply to your problem, please. > > Antonio Fiol > > [EMAIL PROTECTED] wrote: > > > I really don't understand why this is happening either. > > my current guess is that there is something going on > > in the way we are reusing connections. To answer your > > question I don't think there is any relationship between the > > 2 connections just that after the first one is corrupted > > I use the second one until it gets corrupted, then > > create new ones and start again. I have to assume > > that we are doing something wrong in our use of > > these connections and am focusing my effort in that > > direction right now. > > > > Sure wish one of the experts would chime in on this. > > > > t.r. > > > > -Original Message- > > From: chris markiewicz [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, September 06, 2001 7:17 AM > > To: Missner, T. R.; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > > > tr > > > > things have gotten a little worse...i recently upgraded to postgresql 7.1 > > and now i am seeing this behavior even WITHOUT large objects. it happens > > much less frequently without LOs, but it still happens. i never saw this &g
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
tom i do agree that since everyone isn't complaining, the problem is likely in my code. the odd thing is that i wasn't seeing this behavior with the 7.0 stuff, only with the 7.1. (i was having the fastpath problem with 7.0, but that's a different issue.) i'm looking... thanks chris -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 12:10 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... "chris markiewicz" <[EMAIL PROTECTED]> writes: > i guess i don't understand the following: right, each connection is a > different process. so if you use the second connection, why should it fail > too? it should have no relationship to the first connection, since it is a > different process. > i was discussing this with a co-worker and he suggested that the first > connection might be "spilling" into the second (when you grab the first two > connections, they may inhabit contiguous memory spaces...the first one gets > corrupted and corrupts the second...i admit that i am no unix expert so > while i can picture such a thing, i have no idea whether or not it is > possible.) anyway, you grab the third connection and it's not > corrupt. It seems to me that this must be happening on the client side somewhere. On the server side, two different connections will lead to completely separate processes which are very well insulated from each other. OTOH, we've seen numerous reports of problems with connection-pooling applications that got confused about which connection they were using for what. I don't have any real evidence to guess whether the fault is in your own application code or in the JDBC driver. However, if it were in the driver we'd probably be hearing more reports of trouble ... so I'd suggest looking to your own code first ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
I understand. However, doesn't the exception automatically do some kind of rollback? I can't remember where I read such a thing... Antonio chris markiewicz wrote: > antonio > > thanks. it actually does apply to some degree. i have noticed that rolling > back the connection will sometimes prevent subsequent errors, but for some > reason it is not completely predictable... > > the bigger issue is that the db is throwing errors that i don't understand. > i might have just discovered a pattern - i'll post it as soon as i am more > certain of it. but rolling back whenever i get an unpredictable exception > will kill any transaction. this is a problem. it will also require some > localized but non-trivial rework in the persistence layer. i really don't > want to do that unless i have no other avenues. > > thanks for your response. > > chris > > -Original Message- > From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin > Sent: Thursday, September 06, 2001 11:18 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > I'm no expert at all, but, --forgive me!!-- have you tried to roll back upon > the first exception you get? > > Or maybe try to get the warnings in your ResultSet to see if something is > there, though I do not remember having done so. > > I remember having something similar when I tried to read from a table that > did > not exist (mangled name). That request throwed an exception, and if I > ignored > the exception, all subsequent requests did show this kind of behaviour, or a > similar one. > > Forget my message if it doesn't apply to your problem, please. > > Antonio Fiol > > [EMAIL PROTECTED] wrote: > > > I really don't understand why this is happening either. > > my current guess is that there is something going on > > in the way we are reusing connections. To answer your > > question I don't think there is any relationship between the > > 2 connections just that after the first one is corrupted > > I use the second one until it gets corrupted, then > > create new ones and start again. I have to assume > > that we are doing something wrong in our use of > > these connections and am focusing my effort in that > > direction right now. > > > > Sure wish one of the experts would chime in on this. > > > > t.r. > > > > -Original Message- > > From: chris markiewicz [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, September 06, 2001 7:17 AM > > To: Missner, T. R.; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > > > tr > > > > things have gotten a little worse...i recently upgraded to postgresql 7.1 > > and now i am seeing this behavior even WITHOUT large objects. it happens > > much less frequently without LOs, but it still happens. i never saw this > > behavior in 7.0. are you seeing the same behavior? > > > > i guess i don't understand the following: right, each connection is a > > different process. so if you use the second connection, why should it > fail > > too? it should have no relationship to the first connection, since it is > a > > different process. > > i was discussing this with a co-worker and he suggested that the first > > connection might be "spilling" into the second (when you grab the first > two > > connections, they may inhabit contiguous memory spaces...the first one > gets > > corrupted and corrupts the second...i admit that i am no unix expert so > > while i can picture such a thing, i have no idea whether or not it is > > possible.) anyway, you grab the third connection and it's not corrupt. > > does that make any sense? > > > > chris > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, September 05, 2001 12:45 PM > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > > > What I have is a connection pool. > > I initially grab 2 connections, one primary one secondary. > > If the insert fails on the primary connection I try the > > secondary, if it fails I simply delete the 2 I have and grab > > 2 more from the connection pool. After creating the new ones > > I have never seen it fail the first few times which is what > > leads me to believe that something in the connection itself is &g
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
that's a good point...when this error occurs, i suppose the connection is defunct and any transaction is already lost. chris -Original Message- From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin Sent: Thursday, September 06, 2001 11:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... I understand. However, doesn't the exception automatically do some kind of rollback? I can't remember where I read such a thing... Antonio chris markiewicz wrote: > antonio > > thanks. it actually does apply to some degree. i have noticed that rolling > back the connection will sometimes prevent subsequent errors, but for some > reason it is not completely predictable... > > the bigger issue is that the db is throwing errors that i don't understand. > i might have just discovered a pattern - i'll post it as soon as i am more > certain of it. but rolling back whenever i get an unpredictable exception > will kill any transaction. this is a problem. it will also require some > localized but non-trivial rework in the persistence layer. i really don't > want to do that unless i have no other avenues. > > thanks for your response. > > chris > > -Original Message- > From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin > Sent: Thursday, September 06, 2001 11:18 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > I'm no expert at all, but, --forgive me!!-- have you tried to roll back upon > the first exception you get? > > Or maybe try to get the warnings in your ResultSet to see if something is > there, though I do not remember having done so. > > I remember having something similar when I tried to read from a table that > did > not exist (mangled name). That request throwed an exception, and if I > ignored > the exception, all subsequent requests did show this kind of behaviour, or a > similar one. > > Forget my message if it doesn't apply to your problem, please. > > Antonio Fiol > > [EMAIL PROTECTED] wrote: > > > I really don't understand why this is happening either. > > my current guess is that there is something going on > > in the way we are reusing connections. To answer your > > question I don't think there is any relationship between the > > 2 connections just that after the first one is corrupted > > I use the second one until it gets corrupted, then > > create new ones and start again. I have to assume > > that we are doing something wrong in our use of > > these connections and am focusing my effort in that > > direction right now. > > > > Sure wish one of the experts would chime in on this. > > > > t.r. > > > > -Original Message- > > From: chris markiewicz [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, September 06, 2001 7:17 AM > > To: Missner, T. R.; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > > > tr > > > > things have gotten a little worse...i recently upgraded to postgresql 7.1 > > and now i am seeing this behavior even WITHOUT large objects. it happens > > much less frequently without LOs, but it still happens. i never saw this > > behavior in 7.0. are you seeing the same behavior? > > > > i guess i don't understand the following: right, each connection is a > > different process. so if you use the second connection, why should it > fail > > too? it should have no relationship to the first connection, since it is > a > > different process. > > i was discussing this with a co-worker and he suggested that the first > > connection might be "spilling" into the second (when you grab the first > two > > connections, they may inhabit contiguous memory spaces...the first one > gets > > corrupted and corrupts the second...i admit that i am no unix expert so > > while i can picture such a thing, i have no idea whether or not it is > > possible.) anyway, you grab the third connection and it's not corrupt. > > does that make any sense? > > > > chris > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, September 05, 2001 12:45 PM > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; > > [EMAIL PROTECTED] > > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... > > > > What I have is a connection pool. > > I initially grab 2
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
I really don't understand why this is happening either. my current guess is that there is something going on in the way we are reusing connections. To answer your question I don't think there is any relationship between the 2 connections just that after the first one is corrupted I use the second one until it gets corrupted, then create new ones and start again. I have to assume that we are doing something wrong in our use of these connections and am focusing my effort in that direction right now. Sure wish one of the experts would chime in on this. t.r. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 7:17 AM To: Missner, T. R.; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... tr things have gotten a little worse...i recently upgraded to postgresql 7.1 and now i am seeing this behavior even WITHOUT large objects. it happens much less frequently without LOs, but it still happens. i never saw this behavior in 7.0. are you seeing the same behavior? i guess i don't understand the following: right, each connection is a different process. so if you use the second connection, why should it fail too? it should have no relationship to the first connection, since it is a different process. i was discussing this with a co-worker and he suggested that the first connection might be "spilling" into the second (when you grab the first two connections, they may inhabit contiguous memory spaces...the first one gets corrupted and corrupts the second...i admit that i am no unix expert so while i can picture such a thing, i have no idea whether or not it is possible.) anyway, you grab the third connection and it's not corrupt. does that make any sense? chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 12:45 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... What I have is a connection pool. I initially grab 2 connections, one primary one secondary. If the insert fails on the primary connection I try the secondary, if it fails I simply delete the 2 I have and grab 2 more from the connection pool. After creating the new ones I have never seen it fail the first few times which is what leads me to believe that something in the connection itself is getting corrupted over time. Note that each connection is a separate process in the unix environment. I believe it is this process that is getting corrupted note necessarily the java connection wrapper. Of course this is all guess work right now. I'll let you know if I come up with a better solution or are able to determine why these connections act like they are getting corrupt. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 5:42 AM To: Missner, T. R.; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... follow-up question - you say that if you get one exception, you try the insert with another connection...if that doesn't work you delete the existing connections and get new ones... i must be missing something - why wouldn't using the second connection always work? if you get rid of the offending connection (the first one), why does the second one have a problem? or am i confusing your connection object with the actual connection? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 3:35 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception and try the insert again with another connection. If it still fails I delete the connections I have and create new ones and the insert ALWAYS works after that. So it appears to me that something is happening to my connection object that is causing it to get corrupted to some extent. Once a connection throws this exception it seems much more likely to do so again which is why I am deleting connections and creating new ones if I have 2 failures back to back. This isn't the answer you are looking for but I hoped it might help you think of other ways to troubleshoot your problem. Since I put this work around in I have been able to run a very high rate and haven't lost any data on the insert side. My guess is that these Fastpath transactions are very slow compared to non fastpath tran
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
tr things have gotten a little worse...i recently upgraded to postgresql 7.1 and now i am seeing this behavior even WITHOUT large objects. it happens much less frequently without LOs, but it still happens. i never saw this behavior in 7.0. are you seeing the same behavior? i guess i don't understand the following: right, each connection is a different process. so if you use the second connection, why should it fail too? it should have no relationship to the first connection, since it is a different process. i was discussing this with a co-worker and he suggested that the first connection might be "spilling" into the second (when you grab the first two connections, they may inhabit contiguous memory spaces...the first one gets corrupted and corrupts the second...i admit that i am no unix expert so while i can picture such a thing, i have no idea whether or not it is possible.) anyway, you grab the third connection and it's not corrupt. does that make any sense? chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 12:45 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... What I have is a connection pool. I initially grab 2 connections, one primary one secondary. If the insert fails on the primary connection I try the secondary, if it fails I simply delete the 2 I have and grab 2 more from the connection pool. After creating the new ones I have never seen it fail the first few times which is what leads me to believe that something in the connection itself is getting corrupted over time. Note that each connection is a separate process in the unix environment. I believe it is this process that is getting corrupted note necessarily the java connection wrapper. Of course this is all guess work right now. I'll let you know if I come up with a better solution or are able to determine why these connections act like they are getting corrupt. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 5:42 AM To: Missner, T. R.; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... follow-up question - you say that if you get one exception, you try the insert with another connection...if that doesn't work you delete the existing connections and get new ones... i must be missing something - why wouldn't using the second connection always work? if you get rid of the offending connection (the first one), why does the second one have a problem? or am i confusing your connection object with the actual connection? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 3:35 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception and try the insert again with another connection. If it still fails I delete the connections I have and create new ones and the insert ALWAYS works after that. So it appears to me that something is happening to my connection object that is causing it to get corrupted to some extent. Once a connection throws this exception it seems much more likely to do so again which is why I am deleting connections and creating new ones if I have 2 failures back to back. This isn't the answer you are looking for but I hoped it might help you think of other ways to troubleshoot your problem. Since I put this work around in I have been able to run a very high rate and haven't lost any data on the insert side. My guess is that these Fastpath transactions are very slow compared to non fastpath transactions and it is easy to use a connection that hasn't finished what it is doing. I even used a synchronized method to hopefully block on the connection but this didn't fix the problem either. Must be some threading going on in the connection class. I haven't had the time to go through the code but I will at some point. I know someone out there has some insight that could help. t.r. missner level(3) communications -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 11:40 AM To: [EMAIL PROTECTED]; 'Postgres Jdbc (E-mail)' Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of e
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
follow-up question - you say that if you get one exception, you try the insert with another connection...if that doesn't work you delete the existing connections and get new ones... i must be missing something - why wouldn't using the second connection always work? if you get rid of the offending connection (the first one), why does the second one have a problem? or am i confusing your connection object with the actual connection? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 3:35 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception and try the insert again with another connection. If it still fails I delete the connections I have and create new ones and the insert ALWAYS works after that. So it appears to me that something is happening to my connection object that is causing it to get corrupted to some extent. Once a connection throws this exception it seems much more likely to do so again which is why I am deleting connections and creating new ones if I have 2 failures back to back. This isn't the answer you are looking for but I hoped it might help you think of other ways to troubleshoot your problem. Since I put this work around in I have been able to run a very high rate and haven't lost any data on the insert side. My guess is that these Fastpath transactions are very slow compared to non fastpath transactions and it is easy to use a connection that hasn't finished what it is doing. I even used a synchronized method to hopefully block on the connection but this didn't fix the problem either. Must be some threading going on in the connection class. I haven't had the time to go through the code but I will at some point. I know someone out there has some insight that could help. t.r. missner level(3) communications -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 11:40 AM To: [EMAIL PROTECTED]; 'Postgres Jdbc (E-mail)' Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of error. (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started doing that, but i am still getting the problem. i have the setup shown below...i've learned that i can usually get around the fastpath error if i try the query a few times...so i make it call up to 10 times if there is an error...the po.load() call runs the (SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034272) query from the last message i sent (see below)...if that throws an exception, i catch it, rollback, and contine...i am still seeing the same behavior though... public void loadComplex(User user) throws SBHException { int iterations = 10; try { //## TODO: //## occasionally get a fastpath exception here...try to //## call load() as many as 10 times if there is an exception... //## this is here only as a test... //## chris markiewicz int i = 0; boolean exceptionfound = false; boolean goodResult = false; while (!goodResult && i < iterations) { try { i++; dv = (DocumentVersion)po.load(versionid, user); goodResult = true; } catch (Exception e) { System.out.println("SBHDocument.loadComplex exception attempt:"+i); try {po.rollback(user);} catch (Exception e3) {...log here...} exceptionfound = true; Log.log("SBHDocument.loadComplex attempt:"+i); Log.log("SBHDocument.loadComplex e:"+e); if (i == (iterations - 1)) { throw e; } } } if (exceptionfound) { ...log here... } } catch (TransactionException te) { throw te; } catch (SBHSecurityException sse) { throw sse; } catch (SBHException sbe) { throw sbe; } catch (Exception e) { throw new SBHException(e); } } here is the log calling that query many times:
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception and try the insert again with another connection. If it still fails I delete the connections I have and create new ones and the insert ALWAYS works after that. So it appears to me that something is happening to my connection object that is causing it to get corrupted to some extent. Once a connection throws this exception it seems much more likely to do so again which is why I am deleting connections and creating new ones if I have 2 failures back to back. This isn't the answer you are looking for but I hoped it might help you think of other ways to troubleshoot your problem. Since I put this work around in I have been able to run a very high rate and haven't lost any data on the insert side. My guess is that these Fastpath transactions are very slow compared to non fastpath transactions and it is easy to use a connection that hasn't finished what it is doing. I even used a synchronized method to hopefully block on the connection but this didn't fix the problem either. Must be some threading going on in the connection class. I haven't had the time to go through the code but I will at some point. I know someone out there has some insight that could help. t.r. missner level(3) communications -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 11:40 AM To: [EMAIL PROTECTED]; 'Postgres Jdbc (E-mail)' Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of error. (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started doing that, but i am still getting the problem. i have the setup shown below...i've learned that i can usually get around the fastpath error if i try the query a few times...so i make it call up to 10 times if there is an error...the po.load() call runs the (SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034272) query from the last message i sent (see below)...if that throws an exception, i catch it, rollback, and contine...i am still seeing the same behavior though... public void loadComplex(User user) throws SBHException { int iterations = 10; try { //## TODO: //## occasionally get a fastpath exception here...try to //## call load() as many as 10 times if there is an exception... //## this is here only as a test... //## chris markiewicz int i = 0; boolean exceptionfound = false; boolean goodResult = false; while (!goodResult && i < iterations) { try { i++; dv = (DocumentVersion)po.load(versionid, user); goodResult = true; } catch (Exception e) { System.out.println("SBHDocument.loadComplex exception attempt:"+i); try {po.rollback(user);} catch (Exception e3) {...log here...} exceptionfound = true; Log.log("SBHDocument.loadComplex attempt:"+i); Log.log("SBHDocument.loadComplex e:"+e); if (i == (iterations - 1)) { throw e; } } } if (exceptionfound) { ...log here... } } catch (TransactionException te) { throw te; } catch (SBHSecurityException sse) { throw sse; } catch (SBHException sbe) { throw sbe; } catch (Exception e) { throw new SBHException(e); } } here is the log calling that query many times: DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DE
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
t.r. thank you very much for this information...while i suppose you're right that it's not as good as a fix, it is much better than the brick wall that i've been facing (my app cannot be shipped in its current state). your workaround should be relatively easy to implement and i can't argue with your success rate! thanks again, and i'll let you know if i learn anything new. chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 3:35 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception and try the insert again with another connection. If it still fails I delete the connections I have and create new ones and the insert ALWAYS works after that. So it appears to me that something is happening to my connection object that is causing it to get corrupted to some extent. Once a connection throws this exception it seems much more likely to do so again which is why I am deleting connections and creating new ones if I have 2 failures back to back. This isn't the answer you are looking for but I hoped it might help you think of other ways to troubleshoot your problem. Since I put this work around in I have been able to run a very high rate and haven't lost any data on the insert side. My guess is that these Fastpath transactions are very slow compared to non fastpath transactions and it is easy to use a connection that hasn't finished what it is doing. I even used a synchronized method to hopefully block on the connection but this didn't fix the problem either. Must be some threading going on in the connection class. I haven't had the time to go through the code but I will at some point. I know someone out there has some insight that could help. t.r. missner level(3) communications -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 11:40 AM To: [EMAIL PROTECTED]; 'Postgres Jdbc (E-mail)' Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of error. (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started doing that, but i am still getting the problem. i have the setup shown below...i've learned that i can usually get around the fastpath error if i try the query a few times...so i make it call up to 10 times if there is an error...the po.load() call runs the (SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034272) query from the last message i sent (see below)...if that throws an exception, i catch it, rollback, and contine...i am still seeing the same behavior though... public void loadComplex(User user) throws SBHException { int iterations = 10; try { //## TODO: //## occasionally get a fastpath exception here...try to //## call load() as many as 10 times if there is an exception... //## this is here only as a test... //## chris markiewicz int i = 0; boolean exceptionfound = false; boolean goodResult = false; while (!goodResult && i < iterations) { try { i++; dv = (DocumentVersion)po.load(versionid, user); goodResult = true; } catch (Exception e) { System.out.println("SBHDocument.loadComplex exception attempt:"+i); try {po.rollback(user);} catch (Exception e3) {...log here...} exceptionfound = true; Log.log("SBHDocument.loadComplex attempt:"+i); Log.log("SBHDocument.loadComplex e:"+e); if (i == (iterations - 1)) { throw e; } } } if (exceptionfound) { ...log here... } } catch (TransactionException te) { throw te; } catch (SBHSecurityException sse) { throw sse; } catch (SBHException sbe) { throw sbe; } catch (Exception e) { throw new SBHException(e); } } here is the log calling that query many times: DEBUG: StartTransactionCommand DEBUG: query:
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
What I have is a connection pool. I initially grab 2 connections, one primary one secondary. If the insert fails on the primary connection I try the secondary, if it fails I simply delete the 2 I have and grab 2 more from the connection pool. After creating the new ones I have never seen it fail the first few times which is what leads me to believe that something in the connection itself is getting corrupted over time. Note that each connection is a separate process in the unix environment. I believe it is this process that is getting corrupted note necessarily the java connection wrapper. Of course this is all guess work right now. I'll let you know if I come up with a better solution or are able to determine why these connections act like they are getting corrupt. -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 05, 2001 5:42 AM To: Missner, T. R.; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... follow-up question - you say that if you get one exception, you try the insert with another connection...if that doesn't work you delete the existing connections and get new ones... i must be missing something - why wouldn't using the second connection always work? if you get rid of the offending connection (the first one), why does the second one have a problem? or am i confusing your connection object with the actual connection? thanks chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 3:35 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... Chris, I had the same problem on the insert side and thought it went away but it is back and I am convinced it is related to more than one thread using the same connection at the same time. What I have done as a work around in the interim ( until I write my own connection manager class ) is catch the exception and try the insert again with another connection. If it still fails I delete the connections I have and create new ones and the insert ALWAYS works after that. So it appears to me that something is happening to my connection object that is causing it to get corrupted to some extent. Once a connection throws this exception it seems much more likely to do so again which is why I am deleting connections and creating new ones if I have 2 failures back to back. This isn't the answer you are looking for but I hoped it might help you think of other ways to troubleshoot your problem. Since I put this work around in I have been able to run a very high rate and haven't lost any data on the insert side. My guess is that these Fastpath transactions are very slow compared to non fastpath transactions and it is easy to use a connection that hasn't finished what it is doing. I even used a synchronized method to hopefully block on the connection but this didn't fix the problem either. Must be some threading going on in the connection class. I haven't had the time to go through the code but I will at some point. I know someone out there has some insight that could help. t.r. missner level(3) communications -Original Message- From: chris markiewicz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 11:40 AM To: [EMAIL PROTECTED]; 'Postgres Jdbc (E-mail)' Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL... hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of error. (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started doing that, but i am still getting the problem. i have the setup shown below...i've learned that i can usually get around the fastpath error if i try the query a few times...so i make it call up to 10 times if there is an error...the po.load() call runs the (SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034272) query from the last message i sent (see below)...if that throws an exception, i catch it, rollback, and contine...i am still seeing the same behavior though... public void loadComplex(User user) throws SBHException { int iterations = 10; try { //## TODO: //## occasionally get a fastpath exception here...try to //## call load() as many as 10 times if there is an exception... //## this is here only as a test... //## chris markiewicz int i = 0; boolean exceptionfound = false; boolean goodResult = false; while (!goodResult && i < iterations) { try { i++;
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
hello. yet another follow-on to my questions...is there a way to determine whether i am going to get this "NOTICE" before i execute a query? check some status code or something? thanks chris -Original Message- From: Chris Markiewicz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 1:40 PM To: [EMAIL PROTECTED]; 'Postgres Jdbc (E-mail)' Cc: [EMAIL PROTECTED] Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL... hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of error. (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started doing that, but i am still getting the problem. i have the setup shown below...i've learned that i can usually get around the fastpath error if i try the query a few times...so i make it call up to 10 times if there is an error...the po.load() call runs the (SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034272) query from the last message i sent (see below)...if that throws an exception, i catch it, rollback, and contine...i am still seeing the same behavior though... public void loadComplex(User user) throws SBHException { int iterations = 10; try { //## TODO: //## occasionally get a fastpath exception here...try to //## call load() as many as 10 times if there is an exception... //## this is here only as a test... //## chris markiewicz int i = 0; boolean exceptionfound = false; boolean goodResult = false; while (!goodResult && i < iterations) { try { i++; dv = (DocumentVersion)po.load(versionid, user); goodResult = true; } catch (Exception e) { System.out.println("SBHDocument.loadComplex exception attempt:"+i); try {po.rollback(user);} catch (Exception e3) {...log here...} exceptionfound = true; Log.log("SBHDocument.loadComplex attempt:"+i); Log.log("SBHDocument.loadComplex e:"+e); if (i == (iterations - 1)) { throw e; } } } if (exceptionfound) { ...log here... } } catch (TransactionException te) { throw te; } catch (SBHSecurityException sse) { throw sse; } catch (SBHException sbe) { throw sbe; } catch (Exception e) { throw new SBHException(e); } } here is the log calling that query many times: DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of chris markiewicz Sent: Tuesday, September 04, 2001 11:42 AM To: Postgres Jdbc (E-mail) Cc: [EMAIL PROTECTED] Subject: [JDBC] error - NOTICE: current transaction is aborted, queries ignored until end of transaction block Hello. I have been having problems with postgresql large objects...i
Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
hello. i have some more details on this problem...i found the reference to calling rollback when you get this sort of error. (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started doing that, but i am still getting the problem. i have the setup shown below...i've learned that i can usually get around the fastpath error if i try the query a few times...so i make it call up to 10 times if there is an error...the po.load() call runs the (SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034272) query from the last message i sent (see below)...if that throws an exception, i catch it, rollback, and contine...i am still seeing the same behavior though... public void loadComplex(User user) throws SBHException { int iterations = 10; try { //## TODO: //## occasionally get a fastpath exception here...try to //## call load() as many as 10 times if there is an exception... //## this is here only as a test... //## chris markiewicz int i = 0; boolean exceptionfound = false; boolean goodResult = false; while (!goodResult && i < iterations) { try { i++; dv = (DocumentVersion)po.load(versionid, user); goodResult = true; } catch (Exception e) { System.out.println("SBHDocument.loadComplex exception attempt:"+i); try {po.rollback(user);} catch (Exception e3) {...log here...} exceptionfound = true; Log.log("SBHDocument.loadComplex attempt:"+i); Log.log("SBHDocument.loadComplex e:"+e); if (i == (iterations - 1)) { throw e; } } } if (exceptionfound) { ...log here... } } catch (TransactionException te) { throw te; } catch (SBHSecurityException sse) { throw sse; } catch (SBHException sbe) { throw sbe; } catch (Exception e) { throw new SBHException(e); } } here is the log calling that query many times: DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT versionid, versionid, versioncomment, versionlabel, creatorid, documentid, versionorder, datestamp, versioncontent FROM document_version WHERE versionid = 1034277 NOTICE: current transaction is aborted, queries ignored until end of transaction block DEBUG: CommitTransactionCommand chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of chris markiewicz Sent: Tuesday, September 04, 2001 11:42 AM To: Postgres Jdbc (E-mail) Cc: [EMAIL PROTECTED] Subject: [JDBC] error - NOTICE: current transaction is aborted, queries ignored until end of transaction block Hello. I have been having problems with postgresql large objects...i have been searching the archives of this group and i have posted a question or two but i have so far be unable to resolve the issues. right now, i have a screen that loads a few LOs...they occasionally don't make it to the screen. i see a couple of different errors in my log files (which i included below). i turned on debugging (d2) and i see the following on many of my queries (see below). DEBUG: StartTransactionCommand DEBUG: query: SELECT DISTINCT sbhd.timestamp AS "modifydate", sbhd.versionid AS "versionid", sbhd.do