Re: psql \copy hanging

Le 22/08/2019 à 10:47, Arnaud L. a écrit :
> On Windows, I have an unattended script that exports data overnight by
> issuing psql commands like :
> psql -f mycommands.sql
> whith mycommands.sql containing a bunch of \copy lines like :
> \copy (SELECT * FROM someview) TO
> '\\windowsserver\windowsshare\somefile.csv'
>
> When I run this script by hand, everything is fine but when it runs
> overnight it hangs and allways on the same line.
> There is no lock in the database, and the connection is marked as active
> in the backend and running the statement "COPY (SELECT * FROM myview) TO
> STDOUT".
> So where can things be hanging ?
> My first guess (appart from db lock) was that the target file was locked
> by the filesystem. But wouldn't psql know about it and fail with an error ?

OK, so it happended again... I'm really puzzled.
There is no lock on the FS that I can find (openfiles reports 0 lock on
this file).
The client psql process is sitting idle, doing nothing.
In PostgreSQL, the connection is active with the query being simple copy
(select * from view) to stdout.
In pg_stat_activity wait_event_type IS NULL, state is active,
query_start is 2019-08-27 00:18:20.471957+02 and state_change is
2019-08-27 00:18:20.47196+02...
Nothing in postgresql LOG around that time.
Later I have : 2019-08-27 01:00:04 CEST LOG: could not receive data
from client: An existing connection was forcibly closed by the remote host.
But I realy doubt this is related...

Any other idea ? I'll change the lines order for tonight's run, but that
is not what I'd call a solution...

Thanks a lot !

--
Arnaud