BUG #13671: pg_terminate_backend(pid) does not work

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

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

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

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

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