Re: [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 Machine C on LAN2 it stalls with
> "idle in transaction" on the server.
>
> I've seen some snippets of commentary on timeouts, etc. and have a
> feeling I'm running into the same problem, but I can't tell whether
> there is a solution or not. Can someone please point out a place
> where I
> can read up on this situation in more detail so I can understand the
> issues better?

I suspect the only place to read about it would be in the mailing
list archives.

I suspect what's happening is that something between the two machines/
networks is killing the connection part-way through the backup.
pg_dump starts a serialized transaction early on, so any interruption
in the process will result in seeing the 'idle in transaction' status.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461