Waiting for 1 transactions to finish.

[Trashan]

I use pg_repack version 1.3.4.
I had the following message nearly 80000 times (until the PID 3254 transaction is manually killed)

NOTICE: Waiting for 1 transactions to finish. First PID: 3254

When I perfom pg_repack command I use the option "-T 450" and I wonder
why the process wasn't killed after 450 secs ?

How can I fix it ?

Thank you and sorry for my grammatical erros ! :-)

[schmiddy]

So that "Waiting for x transactions to finish" message comes from here, and as you can see there is no respect paid to the -T / --wait-timeout option there.

I think the code has been like that forever, and I guess the reasoning goes something like: it's ok to kill other backends which are directly holding locks on our target table, because they really should know better than to try to hold locks on the target table during a repack. But it may be less obvious that other long-running transactions which don't touch the target table at all are holding up the repack, and that they deserve to be killed.

We have already gotten some complaints about pg_repack's default behavior of killing conflicting backends (see e.g. #76 and #90). So I'm pretty hesitant to introduce any more backend killing by default. We might think about adding a non-default option to enable killing of old transactions, though I'm also leery of over-burdening the client with dozens of options.

[Trashan]

Hi schmiddy,

Thanks a lot for your answer.
I agree with your reasoning.
So we decided to terminate pg_repack in this case.
Following commands are executed at a specific time :

select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where application_name='pg_repack';
DROP EXTENSION pg_repack CASCADE;
CREATE EXTENSION pg_repack;

It's a rare case and pg_repack is executed once per week on our tables (They are selected through pg_freespacemap contrib).

[cbandy]

PostgreSQL 9.6 has a new setting to terminate transactions that are idle too long.

[Trashan]

Thank you cbandy,
I'm putting away your helpful information for the future. (Sadly, we still in 9.2.18 ...)
It's always a pleasure to learn !