Okay, I think I see the problem: the backend is hanging up here:
#0 0x420dac82 in send () from /lib/i686/libc.so.6
#1 0x080dbe69 in secure_write ()
#2 0x080defd6 in pq_flush ()
#3 0x081618fe in send_message_to_frontend ()
#4 0x081610ac in elog ()
#5 0x4038a6be in ?? () -- plpgsql, didn't have the symbols loaded
#6 0x403898b9 in ?? ()
#7 0x40389791 in ?? ()
#8 0x40389b04 in ?? ()
#9 0x40389832 in ?? ()
#10 0x40389791 in ?? ()
#11 0x40389f23 in ?? ()
#12 0x4038986a in ?? ()
#13 0x40389791 in ?? ()
#14 0x4038962f in ?? ()
#15 0x403891a0 in ?? ()
#16 0x40386751 in ?? ()
#17 0x080c280f in ExecCallTriggerFunc ()
#18 0x080c2909 in ExecBRInsertTriggers ()
#19 0x080b34da in CopyFrom ()
#20 0x080b2563 in DoCopy ()
#21 0x081198fb in pg_exec_query_string ()
#22 0x0811a685 in PostgresMain ()
Looking at the log, it's apparent that you've got a trigger that is
emitting lots of NOTICEs. What I see happening is that psql is busy
doing its side of the COPY and is not swallowing the NOTICE traffic.
It would eat it at the end of the COPY --- but the kernel will only
buffer a limited amount of stuff in a pipe, and so after awhile it
blocks the backend.
When you kill the psql process, the kernel discards the output pipe data
and lets the backend run again. Now it starts chewing on whatever
the psql side had managed to push into the input pipe before blocking.
Eventually it will run out of that data, detect EOF, and exit. The
trigger seems to be slow enough that that'll take awhile, though.
If there's any bug here, it's that the libpq COPY functions are too
singleminded to check for NOTICE output while they are pushing input
data to the backend. Not sure if it's worth making them do so or not.
My recommendation would be to get rid of the NOTICEs in your trigger
in any case...
regards, tom lane
