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; |
+-----+--------+---------------------------------------------------------+
