Can't terminate hung COPY

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general