Re: Insert not finishing

Johann Spies wrote:
> I am running python scripts to read tag-formated files and put the data into tables.
>
>
> Sometimes a script (I am running several of them in parallel on a server) just hangs. That happened
> now again and when I checked I saw this:
>
> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS
> current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
> procpid |
> current_query
> ---------+--------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------------------
> ---------------------------------------------------
> 31697 | SELECT rkw_art_link.id, rkw_art_link.rart_id, rkw_art_link.kw_id FROM rkw_art_link WHERE
> ((rkw_art_link.kw_id = 'a9285811-8b14-421b-b7ee-2c0a7dc9c52c') AND (rkw_art_link.rart_id =
> '000182809000214')) ORDER BY rkw_art_link.id LIMIT 1 OFFSET 0;
> 32234 | INSERT INTO rkeywords(de,uuid) VALUES ('ANGIOTENSIN RECEPTOR BLOCKER','4b1c8ae2-d837-4d25-
> a32f-4891db267b9b');
> 30791 | INSERT INTO rkeywords(de,uuid) VALUES ('SYNCOPE','45044b9e-3cab-4f7b-83b9-f32cbed3cb24');
> 31799 | <IDLE> in transaction
> 16893 | <IDLE>
> 31624 | INSERT INTO rkeywords(de,uuid) VALUES ('DIASTOLIC FUNCTION','0c3633b4-05d1-4871-bbc1-
> b5e4c797e0db');
> 31884 | INSERT INTO rkeywords(de,uuid) VALUES ('MAPK','51b7d331-5652-4d05-9386-bc4e714b398d');
> 31715 | <IDLE> in transaction
> 31749 | <IDLE>
> 321 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
> pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS
> backendid) AS s;
> (10 rows)
>
>
> Number 31884 is the problem one. It just sits there everytime I run this query and it never seems to
> get completed.
>
>
> How do I find out what is the problem? And how do I 'push' it to carry on?

It is probably waiting for a lock to be released.

Query pg_locks to find out which session is holding the lock.

What is the table definition for rkeywords?

If the sessions that are idle in transaction hold the locks,
try to find out a) what they did before and
b) why the transaction does not get finished.

Yours,
Laurenz Albe