Re: psql \copy hanging

"Arnaud L." <arnaud(dot)listes(at)codata(dot)eu> writes:

> Hi list
>
> 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.

Is the overnight run being done as the same DB user you are testing
with?

If not, then perhaps the automated user deviates in resource settings
and/or permissions, in turn resulting in sluggish performance caused by
thrashing and/or inferior query plan.

> 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 ?
>
> Thanks for your help !
>
> --
> Arnaud
>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net