Re: [GENERAL] idle in transaction, why

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> Gosh I wish I could learn to proof-read my posts. >> My support crew graciously set >> >> idle_transaction_timeout = 1 >> >> Now to ponder if I need zero or some large number. > > The unit

Re: [GENERAL] idle in transaction, why

On 11/07/2017 09:09 AM, Scott Marlowe wrote: On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent wrote: On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. A transaction tha

Re: [GENERAL] idle in transaction, why

On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent wrote: > > >> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: >> >> I would figure values in "minutes" to be more realistic depending on the >> workload and characteristics of the application. >> >> A transaction that has several seconds of "think

Re: [GENERAL] idle in transaction, why

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> Gosh I wish I could learn to proof-read my posts. >> My support crew graciously set >> >> idle_transaction_timeout = 1 >> >> Now to ponder if I need zero or some large number. > > The uni

Re: [GENERAL] idle in transaction, why

Rob Sargent schrieb am 06.11.2017 um 23:09: > Gosh I wish I could learn to proof-read my posts. > My support crew graciously set > > idle_transaction_timeout = 1 > > Now to ponder if I need zero or some large number. The unit of that setting is milliseconds (if no unit is specified). zero disab

Re: [GENERAL] idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote: On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I thi

Re: [GENERAL] idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote: On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I thi

Re: [GENERAL] idle in transaction, why

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> >> Rob Sargent writes: >>> >>>idle_in_transaction_session_timeout | 0 | default | >>> || A value of 0 turns off the timeout. | user >> >> Meh. I think we're barking up the wr

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I c

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I can find, there is no error message reading 'id

Re: [GENERAL] idle in transaction, why

Rob Sargent writes: > idle_in_transaction_session_timeout | 0 | default | > || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I can find, there is no error message reading 'idle transaction timeout' in the

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:17 PM, Tom Lane wrote: "David G. Johnston" writes: You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is coming from. You don't have t

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:09 PM, David G. Johnston wrote: On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent >wrote: Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4 The postgresql.conf file has #i

Re: [GENERAL] idle in transaction, why

"David G. Johnston" writes: > You should probably login as your application user and do "show > idle_in_transaction_session_timeout" to see what a clean session has for a > value and then figure out from there where that value is coming from. You don't have to guess about the latter: the pg_setti

Re: [GENERAL] idle in transaction, why

On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent wrote: > Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and > this JDBC driver postgresql:42.1.4 > > > The postgresql.conf file has > > #idle_in_transaction_session_timeout = 0# in milliseconds, 0 is > disabled > > ​There

Re: [GENERAL] idle in transaction query makes server unresponsive

Chris Travers wrote: > If it is truly idle in a transaction, maybe it has locks that are holding up other transactions? > > Locks are usually held until commit time, except advisory locks iirc but those have to be explicitly > checked, so if you don't know if you are using them you probably aren't

Re: [GENERAL] idle in transaction query makes server unresponsive

If it is truly idle in a transaction, maybe it has locks that are holding up other transactions? Locks are usually held until commit time, except advisory locks iirc but those have to be explicitly checked, so if you don't know if you are using them you probably aren't. Long-running transactions

Re: [GENERAL] idle in transaction query makes server unresponsive

Scot Kreienkamp wrote: >> You could try to "strace" the postmaster during a connection attempt >> and see what happens. Maybe that helps to spot the place where >> things go wrong. > [Scot Kreienkamp] > I'm willing to give it a try, but I've never done it before. What do I need to do? "man stra

Re: [GENERAL] idle in transaction query makes server unresponsive

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Albe Laurenz > Sent: Wednesday, September 26, 2012 5:15 AM > To: Scot Kreienkamp; pgsql-general@postgresql.org > Subject: Re: [GENERAL] idle in

Re: [GENERAL] idle in transaction query makes server unresponsive

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Thomas Kellerer > Sent: Tuesday, September 25, 2012 5:25 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] idle in transaction

Re: [GENERAL] idle in transaction query makes server unresponsive

Scot Kreienkamp wrote: > I have a problem that I've been struggling with for quite some time. Every once in a while I will get > a connection that goes to idle in transaction on an in-house programmed application that connects with > JDBC. That happens fairly regularly and the programmers are tryi

Re: [GENERAL] idle in transaction query makes server unresponsive

Scot Kreienkamp wrote on 25.09.2012 22:35: The application is using a pooler and generally runs around 100 connections, but I've seen it as high as 200 during the day for normal use. It's on a large server; 64 cores total and about 500 gigs of memory. That's one of the reasons I left it at 512

Re: [GENERAL] idle in transaction query makes server unresponsive

On 09/25/12 1:35 PM, Scot Kreienkamp wrote: The problem is how do I investigate this when PG is entirely unresponsive? Why is it becoming unresponsive, and how do I prevent the PG server from becoming unresponsive? I think I'd push that 9.1.latest upgrade ASAP, and then see if this problem

Re: [GENERAL] idle in transaction query makes server unresponsive

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of John R Pierce > Sent: Tuesday, September 25, 2012 3:53 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] idle in transaction

Re: [GENERAL] idle in transaction query makes server unresponsive

On 09/25/12 12:23 PM, Scot Kreienkamp wrote: I have a problem that I've been struggling with for quite some time. Every once in a while I will get a connection that goes to idle in transaction on an in-house programmed application that connects with JDBC. That happens fairly regularly and t

Re: [GENERAL] "idle in transaction" entry in pg_logs

On Fri, Nov 11, 2011 at 4:18 AM, Raghavendra < raghavendra@enterprisedb.com> wrote: > Respected, > > All the time we see 'idle in transaction' in pg_stat_activity and dig > details with process pid from pg_logs for the query,query execution time > etc.. > Instead of searching with process pid,

Re: [GENERAL] idle in transaction process

On Sun, Aug 21, 2011 at 10:52 PM, tamanna madaan wrote: > Can anyone please suggest me on this . Both the PostgreSQL and Slony versions you are running have known bugs that cause problems. Update them first and see if your problem goes away. -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] idle in transaction process

On 08/18/11 2:17 AM, tamanna madaan wrote: Yes , restarting the slon resolves the issue. But. there are other processes also in my application which connect to postgres . Those processes can also cause "idle in transaction" postgres connection . So, I was wondering if I can make use of tcp_kee

Re: [GENERAL] idle in transaction process

Can anyone please suggest me on this . Thanks.. Tamanna On Thu, Aug 18, 2011 at 2:47 PM, tamanna madaan < tamanna.mad...@globallogic.com> wrote: > Hi > > Yes , restarting the slon resolves the issue. But. there are other > processes also in my application which connect to postgres . Those > pro

Re: [GENERAL] idle in transaction process

Hi Yes , restarting the slon resolves the issue. But. there are other processes also in my application which connect to postgres . Those processes can also cause "idle in transaction" postgres connection . So, I was wondering if I can make use of tcp_keepalives_idle , tcp_keepalives_interval and

Re: [GENERAL] idle in transaction process

Best Regards, Abbas On Mon, Aug 15, 2011 at 11:14 PM, tamanna madaan < tamanna.mad...@globallogic.com> wrote: > Hi All > > I am using postgres-8.4.0 on a cluster setup with slony-2.0.4 being used > for replication. > Recently , I saw a "idle in transaction" postgres process as below. > > postgre

Re: [GENERAL] Idle In Transaction

Anthony Presley writes: > Ok, I've written a script to find some of this information when an > in transaction has been hanging out too long, and then run some > SQL commands. Since there's a lot there, I've added it to PasteBin: > http://pastebin.com/TpfKd9Ya It would help if you'd shown the

Re: [GENERAL] Idle In Transaction

On Tue, 2010-07-13 at 17:38 -0400, Tom Lane wrote: > Anthony Presley writes: > > Every so often (usually in the early morning), we are seeing an " > > in transaction" show up. This appears to lock / block other statements > > from going through, though I'm not sure why. If left unchecked, we end

Re: [GENERAL] Idle In Transaction

On Wed, Jul 14, 2010 at 08:48:20AM -0500, Anthony Presley wrote: > IE, the duration ends up on a different line, and basically none of the > statements ever match in your perl script. > Any guess here? You can modify the script to match format, but I have simpler solution - don't use syslog - at l

Re: [GENERAL] Idle In Transaction

Hubert, :-) Your script was one of the first that I found, thanks to the power of Google. My issue with your script is that, for one reason or another, when piping the logs through rsyslog, we end up with log lines that your perl code won't help, like: Jul 12 04:02:10 artemis postgres[27803]: [

Re: [GENERAL] Idle In Transaction

On Tue, Jul 13, 2010 at 02:53:25PM -0500, Anthony Presley wrote: > I'm bordering on insanity, trying to track down an IDLE in transaction > problem. you might find this helpful: http://www.depesz.com/index.php/2008/08/28/hunting-idle-in-transactions/ Pozdrawiam, Hubert Lubaczewski -- Linkedin:

Re: [GENERAL] Idle In Transaction

Anthony Presley writes: > Every so often (usually in the early morning), we are seeing an " > in transaction" show up. This appears to lock / block other statements > from going through, though I'm not sure why. If left unchecked, we end > up with all of our connections being overrun. Well, the

Re: [GENERAL] Idle in Transaction

Normally I get "Idle in Transaction" when there is already a connection which is holding a lock on a database object (e.g. a table). You've to check if there is a query holding a lock which causes all the other transactions to go in "Idle in Transaction" state. Look for other locks than AccesShareL

Re: [GENERAL] Idle in transaction

ad there! I know what to do to fix the issue. Thanks for all your help Sid -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Friday, July 17, 2009 2:55 PM To: Sharma, Sid Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Idle in transaction On Fri,

Re: [GENERAL] Idle in transaction

On Fri, 2009-07-17 at 12:09 -0400, Sharma, Sid wrote: > I'm sure it is outdated. When I suggested a new application I should > have said a new module within an existing application. In other words, > the db pre-exists. We are rewriting our entire application(s) and moving > our database to DB2 and

Re: [GENERAL] Idle in transaction

ff, I still need to issue explicit commits or rollbacks? -----Original Message- From: Bill Moran [mailto:wmo...@potentialtech.com] Sent: Friday, July 17, 2009 10:44 AM To: Sharma, Sid Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Idle in transaction In response to &qu

Re: [GENERAL] Idle in transaction

On Fri, Jul 17, 2009 at 8:17 AM, Sharma, Sid wrote: > Sorry. Forgot to mention the postgres version > > PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Oh, and update to the latest 8.1.x version. There are some nasty bugs in 8.1.3 if I recall. Won't fix this issue, but why run old un

Re: [GENERAL] Idle in transaction

On Fri, Jul 17, 2009 at 8:05 AM, Sharma, Sid wrote: > Hi > > I’m a postgres newbie. I just implemented a new web application using > postgres. > > When I look at the db connections (via ps), I notice that all existing > connections are in ‘Idle in Transaction’ state. > > They never go to idle state

Re: [GENERAL] Idle in transaction

Sharma, Sid wrote: Sorry. Forgot to mention the postgres version PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Why on earth are you using that version? At the very least upgrade to 8.1.17 and if this is a new app, why not use 8.4? When I look at the db connections (via ps), I

Re: [GENERAL] Idle in transaction

On Friday 17 July 2009 07:17:15 am Sharma, Sid wrote: > Sorry. Forgot to mention the postgres version > > PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 > > > > > > From: Sharma, Sid > Sent: Friday, July 17, 2009 10:05 AM > To: pgsql-general@postgresql.

Re: [GENERAL] Idle in transaction

In response to "Sharma, Sid" : > > I'm a postgres newbie. I just implemented a new web application using > postgres. You mention that you're using PG 8.1.3, which is very old. You'll save yourself a lot of headaches if you at least upgrade to the latest 8.1. But that is not part of your issue, i

Re: [GENERAL] Idle in transaction

Sorry. Forgot to mention the postgres version PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 From: Sharma, Sid Sent: Friday, July 17, 2009 10:05 AM To: pgsql-general@postgresql.org Subject: Idle in transaction Hi I'm a postgres newbie. I jus

Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 08:40:28PM -0400, Scot Kreienkamp wrote: > Anyway, I'm trying to attack it from the database side out since I am > not a programmer and can't help with that part. I can do simple CGIs > with bash, but I don't know Java or C or even Perl yet for that matter. > Since you guys

Re: [GENERAL] Idle in transaction help

Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Friday, July 10, 2009 7:02 PM To: Scot Kreienkamp Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Idle in transaction help On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp wrote: > Thanks scott, but I wrote a

Re: [GENERAL] Idle in transaction help

On Jul 10, 2009, at 3:34 PM, Scott Marlowe wrote: Assuming that tracking down the process that's connected might help, you can use pg_stat_activity to find the port that the client is connecting from, then on the client machine, use lsof to hunt down the process that is connecting via that port

Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp wrote: > Thanks scott, but I wrote a cgi to combine all of the process info and allow > me to kill errant queries. So I know how to track down the pid. Thanks for > trying to help though. :-) So, what are you looking for, a stack trace dump from jav

Re: [GENERAL] Idle in transaction help

Jul 10 18:34:14 2009 Subject: Re: [GENERAL] Idle in transaction help On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp wrote: > Hi everyone, > > I need some help with tracking down idle in transaction problems.  We have a > custom application that is leaving queries in idle in transactio

Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp wrote: > Hi everyone, > > I need some help with tracking down idle in transaction problems.  We have a > custom application that is leaving queries in idle in transaction status for > unknown reasons.  The developers are working on ways to track it d

Re: [GENERAL] Idle in transaction help

Scot Kreienkamp wrote: It is Java. I asked our programmers to check on the JDBC version as I had seen that on the list previously. It is using postgresql-8.2-504. Is that one of the problem versions? I had thought it was new enough that it would not be subject to that problem. well, the cu

Re: [GENERAL] Idle in transaction help

4:21 PM To: Scot Kreienkamp Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Idle in transaction help Scot Kreienkamp wrote: > > Hi everyone, > > I need some help with tracking down idle in transaction problems. We > have a custom application that is leaving queries in id

Re: [GENERAL] Idle in transaction help

Scot Kreienkamp wrote: Hi everyone, I need some help with tracking down idle in transaction problems. We have a custom application that is leaving queries in idle in transaction status for unknown reasons. The developers are working on ways to track it down, but right now the options on thei

Re: [GENERAL] Idle in transaction - Explination ..

On 1/25/07, Weslee Bilodeau <[EMAIL PROTECTED]> wrote: Where I work I'm in charge of more then a few PostgreSQL databases. I understand why idle in transaction is bad, however I have some developers who I'm having a real difficult time fully explaining to them why its bad. Oh, and by bad I mean

Re: [GENERAL] Idle in transaction - Explination ..

Well, in very short terms: a "idle" transaction is not committed. This means, when it's a writing transaction, that in the best case you have one or more row locks blocking access to the updated/inserted rows and in the worst case one or more table locks, which will block access to a table compl

Re: [GENERAL] Idle in transaction - Explination ..

On Wed, Jan 24, 2007 at 01:15:43PM -0800, Weslee Bilodeau wrote: > Where I work I'm in charge of more then a few PostgreSQL databases. > > I understand why idle in transaction is bad, however I have some > developers who I'm having a real difficult time fully explaining to them > why its bad. It'

Re: [GENERAL] Idle in transaction state.

Tom Lane wrote: "Peter L. Berghold" <[EMAIL PROTECTED]> writes: What I'm seeing is the first time my web application is being run there is a bunch of processes running around that look like: "postgres: peter peter_trialdb 127.0.0.1(46222) idle" "idle" is fine, "idle in transaction" is not so f

Re: [GENERAL] Idle in transaction state.

Peter, On 9-Sep-06, at 2:14 PM, Peter L. Berghold wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi folks, I've run into an issue that I'm sure there's a fix for, I just haven't quite figured it out on my own. First the overview of the environment: Linux (CentOS release 4.2)

Re: [GENERAL] Idle in transaction state.

"Peter L. Berghold" <[EMAIL PROTECTED]> writes: > What I'm seeing is the first time my web application is being run there > is a bunch of processes running around that look like: > "postgres: peter peter_trialdb 127.0.0.1(46222) idle" "idle" is fine, "idle in transaction" is not so fine, because t

Re: [GENERAL] [IDLE IN TRANSACTION] doing a remote pg_dump

On Apr 5, 2006, at 10:47 AM, Dick Wieland wrote: I'm running pgsql 8.1.3 and ran into an unexpected situation running pg_dump remotely. We have 2 LAN's situated some distance apart. I can run pg_dump against machine A on LAN1 from machine B on LAN1 with no problem. But if I try to run it from Mac

Re: [GENERAL] "Idle in Transaction" and hung connections

Thu 4/29/2004 7:27 PM To: Gregory S. Williamson Cc: Tom Lane; [EMAIL PROTECTED] Subject: Re: [GENERAL] "Idle in Transaction" and hung connections On Thu, 29 Apr 2004, Gregory S. Williamson wrote: > Tom -- > > Thanks for the suggestion, and the rapid respons

Re: [GENERAL] "Idle in Transaction" and hung connections

On Thu, 29 Apr 2004, Gregory S. Williamson wrote: > Tom -- > > Thanks for the suggestion, and the rapid response on something which may > not be truely a postgres issue (perhaps more a JDBC thing)! > This behavior is fixed in the 7.5 cvs version of the JDBC driver if you'd like to try it out.