Thread: Can't terminate hung COPY
PostgreSQL 8.4.16 on CentOS 5.9. I've run into a situation where executing a \COPY from psql will hang and at that point it's impossible to terminate the COPY command. I've tried pg_cancel_backend and pg_terminalte_backend - even sending the process itself a TERM signal. Sending the process KILL works, but of course that restarts the entire server which is far from ideal. Any idea on how to cleanly terminal the offending process at this point? Any idea on what may cause \COPY to hang and how to prevent it? Thanks Dave
2013/3/20 David Rees <drees76@gmail.com>: > PostgreSQL 8.4.16 on CentOS 5.9. > > I've run into a situation where executing a \COPY from psql will hang > and at that point it's impossible to terminate the COPY command. > > I've tried pg_cancel_backend and pg_terminalte_backend - even sending > the process itself a TERM signal. > > Sending the process KILL works, but of course that restarts the entire > server which is far from ideal. > > Any idea on how to cleanly terminal the offending process at this point? > > Any idea on what may cause \COPY to hang and how to prevent it? What happens if you use COPY ... FROM with the same data? Are you sure the process hangs (strange thing is that you can't terminate the backend)? Could it be that it looks like it hung, but it's actually COPYing a huge chunk of data? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador
On Wed, Mar 20, 2013 at 1:12 PM, Martín Marqués <martin.marques@gmail.com> wrote: > What happens if you use COPY ... FROM with the same data? I will try that, but if you look at pg_stat_activity the full command is a COPY <table> (<columns), FROM STDIN. > Are you sure the process hangs (strange thing is that you can't > terminate the backend)? Could it be that it looks like it hung, but > it's actually COPYing a huge chunk of data? The file that psql is reading from is tiny - less than 3kB so that's definitely not the issue... -Dave
On Wed, Mar 20, 2013 at 12:37 PM, David Rees <drees76@gmail.com> wrote: > PostgreSQL 8.4.16 on CentOS 5.9. > > I've run into a situation where executing a \COPY from psql will hang > and at that point it's impossible to terminate the COPY command. Some additional notes: Running psql on the same LAN for some reason works fine with the same COPY file. On a remote network it hangs. Tried against a PostgreSQL 9.2.3 server, same results. The file that is being copied from is a single row - one of the columns has decent number of \r\n in it, deleting all of either the \r or the \n in the file allows the \COPY to succeed. I'm trying to narrow it down to a simple test case... -Dave
David Rees <drees76@gmail.com> writes:
> On Wed, Mar 20, 2013 at 12:37 PM, David Rees <drees76@gmail.com> wrote:
>> PostgreSQL 8.4.16 on CentOS 5.9.
>>
>> I've run into a situation where executing a \COPY from psql will hang
>> and at that point it's impossible to terminate the COPY command.
> Some additional notes:
> Running psql on the same LAN for some reason works fine with the same
> COPY file. On a remote network it hangs.
That seems to point the finger at the network stack: what is probably
happening is it's failing to abort a recv() or send() when the process
receives a signal. You might see if you can confirm that diagnosis,
perhaps by watching the backend process with strace, or by attaching to
it after the fact with gdb and seeing where the stack trace leads.
regards, tom lane
On 2013-03-20, David Rees <drees76@gmail.com> wrote: > On Wed, Mar 20, 2013 at 12:37 PM, David Rees <drees76@gmail.com> wrote: >> PostgreSQL 8.4.16 on CentOS 5.9. >> >> I've run into a situation where executing a \COPY from psql will hang >> and at that point it's impossible to terminate the COPY command. > > Some additional notes: > > Running psql on the same LAN for some reason works fine with the same > COPY file. On a remote network it hangs. might be some sort of data dependant error, perhaps reboot your router. -- ⚂⚃ 100% natural