Re: BUG #6088: copy to stdout cannot be stopped with kill(pid) or pg_terminate_backend

Hi and thanks for responding...

While i performing my tests I used pg_dump via local socket
(
pg_dump -F c -Z 0 -t changes -a db > /dev/null
)


Now:

billing=3D# SELECT * from db_activity ;
       age       | datname | procpid | usename | waiting |
query_start          | client_addr | client_port |
                   current_query
-----------------+---------+---------+---------+---------+-----------------=
--------------+-------------+-------------+--------------------------------=
--------------------------------------------------------------
 00:03:07.609308 | billing |   36353 | pgsql   | f       | 2011-07-02
15:50:21.508394+04 |             |          -1 | COPY bill.changes
(id, cdate, mdate, status, table_name, pk_id, old_row, new_row) TO
stdout;
(1 row)

billing=3D# SELECT pg_terminate_backend(36353);
 pg_terminate_backend
----------------------
 t
(1 row)

billing=3D# SELECT * from db_activity ;
       age       | datname | procpid | usename | waiting |
query_start          | client_addr | client_port |
                   current_query
-----------------+---------+---------+---------+---------+-----------------=
--------------+-------------+-------------+--------------------------------=
--------------------------------------------------------------
 00:07:59.735749 | billing |   36353 | pgsql   | f       | 2011-07-02
15:50:21.508394+04 |             |          -1 | COPY bill.changes
(id, cdate, mdate, status, table_name, pk_id, old_row, new_row) TO
stdout;
(1 row)

bt look completely same all time while I attached to postgres process
5 times over 20 min run:

 /usr/local/bin/gdb72 -pid=3D36353
--directory=3D/usr/local/pgsql/src/postgresql-8.4.7/src
...
#0  0x0000000801961b5c in read () from /lib/libc.so.7
#1  0x0000000000658968 in FileRead (file=3D102, buffer=3D0x8b246cae0
"=E2=95=AB\023", amount=3D8192) at fd.c:1151
#2  0x000000000066b2b2 in mdread (reln=3D0xdd088a518,
forknum=3DMAIN_FORKNUM, blocknum=3D2428854, buffer=3D0x8b246cae0 "=E2=95=AB=
\023") at
md.c:618
#3  0x000000000066ce5a in smgrread (reln=3D0xdd088a518,
forknum=3DMAIN_FORKNUM, blocknum=3D2428854, buffer=3D0x8b246cae0 "=E2=95=AB=
\023")
    at smgr.c:401
#4  0x0000000000653495 in ReadBuffer_common (smgr=3D0xdd088a518,
isLocalBuf=3D0 '\000', forkNum=3DMAIN_FORKNUM, blockNum=3D2428854,
    mode=3DRBM_NORMAL, strategy=3D0xdd08e0650, hit=3D0x7fffffffddb3 "") at
bufmgr.c:426
#5  0x0000000000652eea in ReadBufferExtended (reln=3D0xdd091e070,
forkNum=3DMAIN_FORKNUM, blockNum=3D2428854, mode=3DRBM_NORMAL,
    strategy=3D0xdd08e0650) at bufmgr.c:240
#6  0x0000000000468ec9 in heapgetpage (scan=3D0xdd08b7a18, page=3D2428854)
at heapam.c:221
#7  0x000000000046a706 in heapgettup_pagemode (scan=3D0xdd08b7a18,
dir=3DForwardScanDirection, nkeys=3D0, key=3D0x0) at heapam.c:808
#8  0x000000000046b006 in heap_getnext (scan=3D0xdd08b7a18,
direction=3DForwardScanDirection) at heapam.c:1320
#9  0x00000000005315c8 in CopyTo (cstate=3D0xdd0831030) at copy.c:1410
#10 0x000000000053112d in DoCopyTo (cstate=3D0xdd0831030) at copy.c:1268
#11 0x0000000000530c29 in DoCopy (stmt=3D0x8037e7cc8,
    queryString=3D0x8037e7030 "COPY bill.changes (id, cdate, mdate,
status, table_name, pk_id, old_row, new_row) TO stdout;")
    at copy.c:1163
#12 0x0000000000676888 in ProcessUtility (parsetree=3D0x8037e7cc8,
    queryString=3D0x8037e7030 "COPY bill.changes (id, cdate, mdate,
status, table_name, pk_id, old_row, new_row) TO stdout;",
    params=3D0x0, isTopLevel=3D1 '\001', dest=3D0x8037e7850,
completionTag=3D0x7fffffffe570 "") at utility.c:600
#13 0x00000000006753d4 in PortalRunUtility (portal=3D0xdd0885030,
utilityStmt=3D0x8037e7cc8, isTopLevel=3D1 '\001', dest=3D0x8037e7850,
    completionTag=3D0x7fffffffe570 "") at pquery.c:1192
#14 0x000000000067554f in PortalRunMulti (portal=3D0xdd0885030,
isTopLevel=3D1 '\001', dest=3D0x8037e7850, altdest=3D0x8037e7850,
    completionTag=3D0x7fffffffe570 "") at pquery.c:1297
#15 0x0000000000674bd0 in PortalRun (portal=3D0xdd0885030,
count=3D9223372036854775807, isTopLevel=3D1 '\001', dest=3D0x8037e7850,
    altdest=3D0x8037e7850, completionTag=3D0x7fffffffe570 "") at pquery.c:8=
23
#16 0x000000000066f0e6 in exec_simple_query (
    query_string=3D0x8037e7030 "COPY bill.changes (id, cdate, mdate,
status, table_name, pk_id, old_row, new_row) TO stdout;")
    at postgres.c:997
#17 0x0000000000672f72 in PostgresMain (argc=3D4, argv=3D0x803732930,
username=3D0x803732900 "pgsql") at postgres.c:3676
#18 0x000000000063b5cd in BackendRun (port=3D0x803703c00) at postmaster.c:3=
467
#19 0x000000000063ab2a in BackendStartup (port=3D0x803703c00) at postmaster=
.c:3081
#20 0x00000000006382dc in ServerLoop () at postmaster.c:1387
#21 0x0000000000637abb in PostmasterMain (argc=3D3, argv=3D0x7fffffffeb88)
at postmaster.c:1040
---Type <return> to continue, or q <return> to quit---
#22 0x00000000005c0b5a in main (argc=3D3, argv=3D0x7fffffffeb88) at main.c:=
188


Seems no blocking issues at all in backtrace.
Postgres just continue running and ignore signals.


On Sat, Jul 2, 2011 at 7:29 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> 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_du=
mp
>> 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 d=
ata
> 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 w=
ere
> 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 connecti=
on
> 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_du=
mp
> 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 size=
s.
> 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
>



--=20
Maxim Boguk
Senior Postgresql DBA.

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/
=D0=A1=D0=B8=D0=BB=D0=B0 =D1=81=D0=BE=D0=BB=D0=BE=D0=BC=D1=83 =D0=BB=D0=BE=
=D0=BC=D0=B8=D1=82, =D0=BD=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5 =D0=B2 =D0=
=BD=D0=B0=D1=88=D0=B5=D0=B9 =D0=B6=D0=B8=D0=B7=D0=BD=D0=B8 - =D1=81=D0=BE=
=D0=BB=D0=BE=D0=BC=D0=B0, =D0=B4=D0=B0 =D0=B8 =D1=81=D0=B8=D0=BB=D0=B0 =D0=
=B4=D0=B0=D0=BB=D0=B5=D0=BA=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5.