Thread: BUG #13671: pg_terminate_backend(pid) does not work
The following bug has been logged on the website:
Bug reference: 13671
Logged by: alex
Email address: oktogen@mail.ru
PostgreSQL version: 9.4.5
Operating system: ubuntu
Description:
The command pg_terminate_backend(pid) does not terminate sql session
in case incorrect formed COPY command(without terminate-symbol)
Example
--SESSION #1
CREATE TABLE public.comments (
user_id INTEGER NOT NULL,
description TEXT NOT NULL,
path public.ltree NOT NULL,
CONSTRAINT comments_idx PRIMARY KEY(user_id, description, path)
)WITHOUT OIDS;
COPY comments (user_id, description, path) FROM stdin;
1 efbb974bcf5af8f2ba8f352225ecff63 0001
2 8dcdb42684773f413b40d63f5ef0722e 0001.0001.0001
--
--SESSION #2
-- Now SESSION # 1 can not be forced to complete the outside.
-- learn pid process which need terminate
SELECT a.pid,
a.usename,
a.application_name,
a.client_addr,
clock_timestamp() - a.query_start AS duration,
a.state,
a.query,
a.waiting
FROM pg_stat_activity a;
--attempt to finish the session with incorrect COPY command
SELECT pg_terminate_backend(6476) AS output;
-- output
-------------
-- TRUE
SELECT
a.pid,
a.state,
a.query
FROM pg_stat_activity a
WHERE a.pid = 6476;
-- output(failed attempt)
+-----+--------+---------------------------------------------------------+
| pid | state | query |
+-----+--------+---------------------------------------------------------+
|6476 | active | COPY comments (user_id, description, path) FROM stdin; |
+-----+--------+---------------------------------------------------------+
On Fri, Oct 9, 2015 at 6:43 PM, <oktogen@mail.ru> wrote: > The following bug has been logged on the website: > > Bug reference: 13671 > Logged by: alex > Email address: oktogen@mail.ru > PostgreSQL version: 9.4.5 > Operating system: ubuntu > Description: > > The command pg_terminate_backend(pid) does not terminate sql session > in case incorrect formed COPY command(without terminate-symbol) > Example > --SESSION #1 > CREATE TABLE public.comments ( > user_id INTEGER NOT NULL, > description TEXT NOT NULL, > path public.ltree NOT NULL, > CONSTRAINT comments_idx PRIMARY KEY(user_id, description, path) > )WITHOUT OIDS; > > COPY comments (user_id, description, path) FROM stdin; > 1 efbb974bcf5af8f2ba8f352225ecff63 0001 > 2 8dcdb42684773f413b40d63f5ef0722e 0001.0001.0001 > -- > > --SESSION #2 > -- Now SESSION # 1 can not be forced to complete the outside. > -- learn pid process which need terminate > SELECT a.pid, > a.usename, > a.application_name, > a.client_addr, > clock_timestamp() - a.query_start AS duration, > a.state, > a.query, > a.waiting > FROM pg_stat_activity a; > > --attempt to finish the session with incorrect COPY command > SELECT pg_terminate_backend(6476) AS output; > -- output > ------------- > -- TRUE > > SELECT > a.pid, > a.state, > a.query > FROM pg_stat_activity a > WHERE a.pid = 6476; > -- output(failed attempt) > +-----+--------+---------------------------------------------------------+ > | pid | state | query | > +-----+--------+---------------------------------------------------------+ > |6476 | active | COPY comments (user_id, description, path) FROM stdin; | > +-----+--------+---------------------------------------------------------+ > Thanks for the bug report. This issue happens only for version 9.4 and below. In 9.5 and Head branches, because of the following commit, that introduces the wait logic in reading/writing from client, thus it detects the interrupts and terminates the backend. But in version 9.4 and below, the wait logic doesn't exist because of this reason, the backend is not terminated. commit - 80788a431e9bff06314a054109fdea66ac538199 Simplify waiting logic in reading from / writing to client. Similar fix is needed in the back branches. Comments? Regards, Hari Babu Fujitsu Australia
On 2015-10-09 19:56:16 +1100, Haribabu Kommi wrote: > Thanks for the bug report. This issue happens only for version 9.4 and below. > In 9.5 and Head branches, because of the following commit, that introduces the > wait logic in reading/writing from client, thus it detects the interrupts and > terminates the backend. But in version 9.4 and below, the wait logic > doesn't exist > because of this reason, the backend is not terminated. > > commit - 80788a431e9bff06314a054109fdea66ac538199 > Simplify waiting logic in reading from / writing to client. Are you sure that it is this commit? That's just unifying existing logic between ssl/nossl, no? > Similar fix is needed in the back branches. Comments? I don't think we easily can - the whole interrupt/signal handling overhaul was quite invasive. Greetings, Andres Freund
On Sat, Oct 10, 2015 at 12:36 AM, Andres Freund <andres@anarazel.de> wrote: > On 2015-10-09 19:56:16 +1100, Haribabu Kommi wrote: >> Thanks for the bug report. This issue happens only for version 9.4 and below. >> In 9.5 and Head branches, because of the following commit, that introduces the >> wait logic in reading/writing from client, thus it detects the interrupts and >> terminates the backend. But in version 9.4 and below, the wait logic >> doesn't exist > >> because of this reason, the backend is not terminated. >> >> commit - 80788a431e9bff06314a054109fdea66ac538199 >> Simplify waiting logic in reading from / writing to client. > > Are you sure that it is this commit? That's just unifying existing > logic between ssl/nossl, no? Yes, The above commit is just unifying the logic between ssl/nossl. In a quick look, I though that the above commit has introduced the wait logic. But actually the following commits has introduced the wait logic. commit- 387da18874afa17156ee3af63766f17efb53c4b9 Use a nonblocking socket for FE/BE communication and block using latches. commit - 4f85fde8eb860f263384fffdca660e16e77c7f76 Introduce and use infrastructure for interrupt processing during client reads. >> Similar fix is needed in the back branches. Comments? > > I don't think we easily can - the whole interrupt/signal handling > overhaul was quite invasive. Thanks for the details. I understand the difficulty. Is it worth of adding a note in the documentation section of administration functions such as cancel and terminate backend functions like the attached? Regards, Hari Babu Fujitsu Australia