Re: BUG #13671: pg_terminate_backend(pid) does not work

On Fri, Oct 9, 2015 at 6:43 PM, <oktogen(at)mail(dot)ru> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13671
> Logged by: alex
> Email address: oktogen(at)mail(dot)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