Re: psql \copy hanging

On 10/3/19 7:13 AM, Arnaud L. wrote:
> Le 03/10/2019 à 15:54, Adrian Klaver a écrit :
>> On 10/2/19 11:51 PM, Arnaud L. wrote:
>>> Well, this problem is still bugging me, and this time I've tried with
>>> a local file. Unfortunately, it did not help.
>>> To further rule out filesystem problems, I first took care to delete
>>> the target files before copying to it, but it did not help either.
>>>
>>> So now I'm quite confident that the problem is either psql or even
>>> postgresql itself.
>>>
>>> Does anyone know of anything I could try to try to fix or debug this ?
>>
>> Going back to the original thread I noticed it was not specified what
>> program was being used to run the script in the overnight session.
>>
>> So what is being used to run the script overnight?
>
>
> Yes, sorry for having lost the original thread, my mailbox has a quite
> stupid automatic purge schedule...
>
> The script is run in a windows batch file.
> Basically, export.bat contains :
>
> SET PGUSER=myuser
> SET PGPASSWORD=mypwd
> SET PGCLIENTENCODING=UTF8
> SET MYPGSERVER=myserverurl
> SET MYPGDB=mydatabase
> psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB%

I may have missed it before, but where is the Postgres server located?

Given that this seems to be some sort of resource issue and that the
below contains commands that are not dependent on each other, have you
thought of splitting myscript.sql into two scripts? If nothing else the
n(total line)-problem line in one script and the problem line in
another. That would help nail down whether that line is truly the
problem or if it is an interaction with running the other 50+ lines.

>
> And myscript.sql contains :
> \copy (SELECT * FROM view1) TO '\\server\share\view1.txt'
> \copy (SELECT * FROM view2) TO '\\server\share\view2.txt'
> ...
> etc with ~60 views
>
> Today, I've update the problematic \copy line to be :
> COPY (SELECT * FROM view) TO STDOUT \g '\\server\share\view.txt'
>
> I'll keep you informed (even though a successfull run is not a guarantee
> of success, because the original script did sometimes work).
>
> Regards
> --
> Arnaud
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com