On 2/07/2011 12:42 PM, Maksym Boguk wrote:
> But I found I can not stop
> COPY bill.changes (id, cdate, mdate, status, table_name, pk_id, old_row,
> new_row) TO stdout;
> with pg_terminate_backend(procpid) or kill (procpid).
Which FreeBSD are you on? Version and architecture.
Can you obtain a backtrace of the backend you've sent the kill signal
to, after you've set the signal? Given the rest of what you've said,
I'll bet it's blocked in the kernel waiting to send data down a socket.
If the kernel's socket writes aren't interrupted by signals, there's
nothing much Pg can do about it.
> At the same time copy to stdout completely ignore fact other side (pg_dump
> itself) was killed long time ago.
That's got to be a buffering issue, surely. Once the TCP send buffers
fill up the backend would block in write() as the OS tries to send
enough data to make room in the send buffer for the next write.
If your OS keeps big buffers, Pg might've buffered many megabytes of
data before you killed the client side. It might keep on trying to send
that data for quite a long time if it doesn't get notified that the
other peer (pg_dump in this case) has vanished.
How were you running pg_dump? ssh was involved, so I'm wondering if you
were running pg_dump over an ssh tcp tunnel. Was that it? Or were you
running pg_dump on the server side and dumping the SQL text down the ssh
connection to the client? If the latter, then unless you killed ssh as
well the OS would merrily keep on sending the already-buffered data from
before pg_dump was killed; you'd have to kill sshd or the client side of
ssh to actually stop the data flow.
To help with this sort of thing, reduce your send and receive buffer
sizes. You'll pay a price in terms of throughput, but you'll gain
responsiveness.
Without more information, it's hard to know if this is anything except a
buffering issue. Possibly one that doesn't even have anything to do with
Pg - it could easily just be sshd writes being buffered.
--
Craig Ringer