Re: psql \copy hanging

Le 27/08/2019 à 10:00, Luca Ferrari a écrit :
> On Tue, Aug 27, 2019 at 9:54 AM Arnaud L. <arnaud(dot)listes(at)codata(dot)eu> wrote:
>> Any other idea ? I'll change the lines order for tonight's run, but that
>> is not what I'd call a solution...
>
> Does it hangs against the same line content or the same line number?
> Are you able to run the script automatically during working hours (to
> avoid firewalling or upgrades running in parrallel to your nightly
> script execution)?
> Any chance something is querying the same data and a lock blocks the
> transaction (pg_locks)?

Hi Luca.
I can run the script just fine during working hours.
I checked pg_locks, and this pid is the only process requesting locks.
It has around 100 of them since it is a view querying multiple tables,
but I see nothing blocking. All locks are granted.
I have a csv output of pg_locks so I can post this if asked.

It hangs against the same line in the sql script, all lines being "\copy
(select ....) to 'file on unc share'".
This line is simply the longest running query because the view inside
the select outputs almost 1M rows and does some subqueries.

Also when the script is hung, output has not started (file size is 0).

> Just throwing on the table some desperate ideas....

Desperate ideas are very welcome !

Cheers
--
Arnaud