Re: psql \copy hanging - Mailing list pgsql-general

Le 28/08/2019 à 09:43, Luca Ferrari a écrit :
> I don't want to be pedantic, but I would have tried with a single
> change at a time.
> And my bet is: the local file would do the trick (i.e., it is a weird
> share problem).

You're not don't worry. This process is quite important in our workflow 
(not critical), so I can't really afford to make tests for weeks.

Moving the offending line to the end of the script is what I'd consider 
"single change". To me it makes something clear : the problem occurs 
only at the very specific time this command is running.


> If you are going to invest some time, you could also try to write a
> small file on the share just before the copy starts, so that you are
> guaranteed the share is working. Something like:
> 
> echo $(date) >> $SHARE/log.txt
> psql 'copy ...'
> echo 'done' >> $SHARE/log.txt

Well, I do know that the share is working just before this command runs 
because ALL the commands in the script write to this specific share.
The script is basically outputting many views results in a single share, 
something like :
\copy (select * from view1) TO '\\myserver\myshare\file1.csv'
\copy (select * from view2) TO '\\myserver\myshare\file2.csv'
...
\copy (select * from view99) TO '\\myserver\myshare\file99.csv'
So the \copy command right before (say view15 for instance) is writing 
to this same share just milliseconds before the the problematic command 
(view16) tries to do the same.
Since this particular view takes some time to execute, there is some 
time between the moment the file gets created on the share (which 
happens immediately when the \copy command runs if I get it right), and 
the moment psql receives content and starts writing to it (my tests 
suggest 1min to 1min30s). Either psql doesn't receive anything 
(possible, since the connection is marked as active but it does not look 
as if it's doing anything at all), or there has been some timeout.
It could have been tcp keepalive, but in Windows the default is 2h I 
believe and postgresql uses system default if nothing is specified in 
conf (which is my case).

So with all this in mind I'd rather think I have a problem with either 
psql's \copy or with my query on the server side. But I'm not rulling 
anything out of course.

One other thing I could try is using COPY TO STDOUT \g. From what I 
understand in the documentation this would not be 100% similar to what 
\copy is doing.
Anyway, if it works with the current setup I won't dig into it much deeper.

Cheers
--
Arnaud