Re: psql \copy hanging
On 10/10/19 12:00 AM, Arnaud L. wrote: Le 08/10/2019 à 16:59, Arnaud L. a écrit : Yes, I already did that, and unfortunately there's no activy. There is absolutely no activity in procmon using psql.exe as a filter. process-xp is not giving more information, processor usage is 0%. My apologies, I obviously did something wrong last time I checked this process with process-xp and procmon. Now I see that there IS activity on the problematic process ! 100% CPU and some (but not much) disk activity. I think I'll try to let it run for some time to get the auto_explain do its work. If I kill the backend now, I won't see anything I believe. You can look at: pg_locks https://www.postgresql.org/docs/11/view-pg-locks.html and pg_stat_activity https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW in real time to see if there is any useful information. I dont now it it'll ever complete this query though, it usually takes ~100 seconds, and here it has already been running for 9 hours. Regards -- Arnaud -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql \copy hanging
Le 08/10/2019 à 16:59, Arnaud L. a écrit : Yes, I already did that, and unfortunately there's no activy. There is absolutely no activity in procmon using psql.exe as a filter. process-xp is not giving more information, processor usage is 0%. My apologies, I obviously did something wrong last time I checked this process with process-xp and procmon. Now I see that there IS activity on the problematic process ! 100% CPU and some (but not much) disk activity. I think I'll try to let it run for some time to get the auto_explain do its work. If I kill the backend now, I won't see anything I believe. I dont now it it'll ever complete this query though, it usually takes ~100 seconds, and here it has already been running for 9 hours. Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 16:55, Daniel Verite a écrit : Arnaud L. wrote: Anyway, it hung using this syntax during last night's run. I'll give it another try tonight just to be sure. When psql.exe is hanging, maybe you could use a tool like Process Monitor [1] or Process Explorer [2] to get insights about what it's stuck on or what it's doing exactly. [1] https://docs.microsoft.com/en-us/sysinternals/downloads/procmon [2] https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer Yes, I already did that, and unfortunately there's no activy. There is absolutely no activity in procmon using psql.exe as a filter. process-xp is not giving more information, processor usage is 0%. Regards -- Arnaud
Re: psql \copy hanging
Arnaud L. wrote: > Anyway, it hung using this syntax during last night's run. > I'll give it another try tonight just to be sure. When psql.exe is hanging, maybe you could use a tool like Process Monitor [1] or Process Explorer [2] to get insights about what it's stuck on or what it's doing exactly. [1] https://docs.microsoft.com/en-us/sysinternals/downloads/procmon [2] https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
Re: psql \copy hanging
Le 08/10/2019 à 16:03, Adrian Klaver a écrit : This is going to be hard to troubleshoot if you change your commands. Previously you had: psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB% Changing more then one thing at a time makes it that much more difficult to isolate the issue. Yes, true. Actually I do one change at a time, I'm just no posting every single attempt. So I tried to feed the script using console redirection rather than -f as you can see. That was the only change. I would create an entirely separate batch file that runs just problematicline.sql. OK, that's easy. Actually the batch file is not doing much more than running this psql command, but that's really not a problem/ Given that you suspect Postgres it would be helpful to see the query that underlies the view you are copying. You might want to look at autoexplain: https://www.postgresql.org/docs/11/auto-explain.html as a way of getting information at run time. OK that's nice. Since I don't want to mess with the whole server configuration, I added some auto_explain settings to my script. So for tonight, my script looks like this : LOAD 'auto_explain'; SET auto_explain.log_min_duration = 0; SET auto_explain.log_analyze = true; COPY (SELECT * FROM myview) TO STDOUT \g 'myserver\\myshare\\myfile.txt' And it'll run in a separate batch. Thanks for your help Adrian ! Regards -- Arnaud
Re: psql \copy hanging
On 10/8/19 12:06 AM, Arnaud L. wrote: Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It hung during last night's run. I had modified my batch script to run the \copy commands separately, i.e. it now reads as : psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql psql -h myserver -a mydb < problematicline.sql This is going to be hard to troubleshoot if you change your commands. Previously you had: psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB% Changing more then one thing at a time makes it that much more difficult to isolate the issue. I would create an entirely separate batch file that runs just problematicline.sql. Given that you suspect Postgres it would be helpful to see the query that underlies the view you are copying. You might want to look at autoexplain: https://www.postgresql.org/docs/11/auto-explain.html as a way of getting information at run time. It hung at the problematic line, so during the second psql command. I'm really at loss... I *believe* that the problem lies either in psql or in PostgreSQL, but I really don't know what to try now. Regards -- Arnaud -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql \copy hanging
Le 08/10/2019 à 12:55, Daniel Verite a écrit : Testing this with 11.5, it works for me. Make sure you're running the latest minor release (on the client side in this case), because a related fix was issued last February. For the 11 branch it was in version 11.2. OK, my bad, backslashes in a windows-style share path have to be escaped. Anyway, it hung using this syntax during last night's run. I'll give it another try tonight just to be sure. Regards -- Arnaud
Re: psql \copy hanging
Le 08/10/2019 à 12:55, Daniel Verite a écrit : Arnaud L. wrote: As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Testing this with 11.5, it works for me. Make sure you're running the latest minor release (on the client side in this case), because a related fix was issued last February. For the 11 branch it was in version 11.2. I'm on 11.5-1 on the client side, but I had added an output redirection for this batch file to try to understand what was happening during the night runs, and that might be the reason why \g fails (i.e. some interference between the console redirections). I'll give it another try without these redirections. Regards -- Arnaud
Re: psql \copy hanging
Arnaud L. wrote: > As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a > script file (i.e. it does not work if the command is passed in a file > via the -f argument). > The command runs fine, no error is raised either by the client or the > server, but no file is written. Testing this with 11.5, it works for me. Make sure you're running the latest minor release (on the client side in this case), because a related fix was issued last February. For the 11 branch it was in version 11.2. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
Re: psql \copy hanging
Le 08/10/2019 à 09:28, Pavel Stehule a écrit : you can write simple C application with COPY API https://www.postgresql.org/docs/12/libpq-copy.html Unfortunately, I don't know C. Then you can eliminate or ensure locality of problem. more, you can use server side copy. Superuser can read data from server file system. Yes, but in this case the file has to be written to a network share, and the windows user under wich PostgreSQL runs (Network Service) cannot be given write permission on this share. That's the reason for the use of \copy. Now that I think about it, *maybe* this started happening after a server upgrade. Since this is intermittent, I'm not really sure about this, but some time ago we moved our server to a different hardware and upgraded from 9.3 to 11 at the same time. The dates don't perfectly match though, we upgraded around 8th of august and the problem arose ~2 weeks later for the first time. The client was upgraded around that same time period (not exactly the same time if I remember correctly). Regards -- Arnaud
Re: psql \copy hanging
út 8. 10. 2019 v 9:06 odesílatel Arnaud L. napsal: > Le 07/10/2019 à 16:36, Adrian Klaver a écrit : > > So you are saying that you have not run the problematic line by itself? > > It hung during last night's run. > > I had modified my batch script to run the \copy commands separately, > i.e. it now reads as : > psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql > psql -h myserver -a mydb < problematicline.sql > > It hung at the problematic line, so during the second psql command. > > I'm really at loss... I *believe* that the problem lies either in psql > or in PostgreSQL, but I really don't know what to try now. > you can write simple C application with COPY API https://www.postgresql.org/docs/12/libpq-copy.html Then you can eliminate or ensure locality of problem. more, you can use server side copy. Superuser can read data from server file system. Regards Pavel > > Regards > -- > Arnaud > > >
Re: psql \copy hanging
Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It hung during last night's run. I had modified my batch script to run the \copy commands separately, i.e. it now reads as : psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql psql -h myserver -a mydb < problematicline.sql It hung at the problematic line, so during the second psql command. I'm really at loss... I *believe* that the problem lies either in psql or in PostgreSQL, but I really don't know what to try now. Regards -- Arnaud
Re: psql \copy hanging
On 10/7/19 12:41 AM, Arnaud L. wrote: Le 04/10/2019 à 19:08, Adrian Klaver a écrit : On 10/4/19 12:19 AM, Arnaud L. wrote: OK I can do that. I thought I nailed it down to this line because it started failing when this line was ~5th in the script, and it kept failing on that very same line after I moved it at the very end of the script (that's where it is now). Which tends to point to it as the problem. The question is whether it exhibits that behavior on its own or only when in combination with the other commands. Yes. It ran fine this last night. I had moved the line back to its original place, so now everything is exactly like it was before it started showing this behaviour. So you are saying that you have not run the problematic line by itself? So, still apparently random... Yeah not sure how that is supposed to work: [...] production_(postgres)# \copy (select * from cell_per) TO STDOUT \g 'cell.txt' ERROR: syntax error at or near "\" LINE 1: COPY ( select * from cell_per ) TO STDOUT \g 'cell.txt' This works with real SQL commands, so it should be "COPY" here, not "\copy". I was not paying attention, thanks for the heads up. Regards -- Arnaud -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql \copy hanging
Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It depends what you mean by that. I've run this line by itself many times. Everytime the script has failed in fact. But until today I had not splitted the batch script to call two separate SQL scripts with one containing only the problematic line, no. I've changed it this morning, so we'll see how it goes now. Regards -- Arnaud
Re: psql \copy hanging
Le 04/10/2019 à 19:08, Adrian Klaver a écrit : On 10/4/19 12:19 AM, Arnaud L. wrote: OK I can do that. I thought I nailed it down to this line because it started failing when this line was ~5th in the script, and it kept failing on that very same line after I moved it at the very end of the script (that's where it is now). Which tends to point to it as the problem. The question is whether it exhibits that behavior on its own or only when in combination with the other commands. Yes. It ran fine this last night. I had moved the line back to its original place, so now everything is exactly like it was before it started showing this behaviour. So, still apparently random... As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Yeah not sure how that is supposed to work: [...] production_(postgres)# \copy (select * from cell_per) TO STDOUT \g 'cell.txt' ERROR: syntax error at or near "\" LINE 1: COPY ( select * from cell_per ) TO STDOUT \g 'cell.txt' This works with real SQL commands, so it should be "COPY" here, not "\copy". Regards -- Arnaud
Re: psql \copy hanging
On 10/4/19 12:19 AM, Arnaud L. wrote: Le 03/10/2019 à 16:32, Adrian Klaver a écrit : I may have missed it before, but where is the Postgres server located? On the same local area network. Not on the computer running the script (so direct COPY TO is not an option). 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. OK I can do that. I thought I nailed it down to this line because it started failing when this line was ~5th in the script, and it kept failing on that very same line after I moved it at the very end of the script (that's where it is now). Which tends to point to it as the problem. The question is whether it exhibits that behavior on its own or only when in combination with the other commands. As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Yeah not sure how that is supposed to work: production_(postgres)# select version(); version PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.4.1 20190424 [gcc-7-branch revision 270538], 64-bit (1 row) ^ production_(postgres)# \copy (select * from cell_per) TO 'cell.txt' COPY 68 production_(postgres)# \copy (select * from cell_per) TO STDOUT \g 'cell.txt' ERROR: syntax error at or near "\" LINE 1: COPY ( select * from cell_per ) TO STDOUT \g 'cell.txt' ^ production_(postgres)# \copy (select * from cell_per) TO STDOUT\g 'cell.txt' ERROR: syntax error at or near "'cell.txt'" LINE 1: COPY ( select * from cell_per ) TO STDOUT 'cell.txt' Regards -- Arnaud -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql \copy hanging
Le 03/10/2019 à 16:32, Adrian Klaver a écrit : I may have missed it before, but where is the Postgres server located? On the same local area network. Not on the computer running the script (so direct COPY TO is not an option). 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. OK I can do that. I thought I nailed it down to this line because it started failing when this line was ~5th in the script, and it kept failing on that very same line after I moved it at the very end of the script (that's where it is now). As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the client or the server, but no file is written. Regards -- Arnaud
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.kla...@aklaver.com
Re: psql \copy hanging
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% 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
Re: psql \copy hanging
On 10/2/19 11:51 PM, Arnaud L. wrote: Hi list, 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). 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? Thanks a lot for your help! Regards -- Arnaud -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql \copy hanging
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
Re: psql \copy hanging
On Wed, Aug 28, 2019 at 9:09 AM Arnaud L. wrote: > OK, so this was enough for last night's schedule to run without problem. > I still don't get it so I'm not satisfied with this solution, but at > least it works. > I'll keep the list informed if something new arises. 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). 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 Luca
Re: psql \copy hanging
Le 27/08/2019 à 13:17, Arnaud L. a écrit : I move the offending line at the end of the script, so it will run some minutes later, maybe this will be enough. OK, so this was enough for last night's schedule to run without problem. I still don't get it so I'm not satisfied with this solution, but at least it works. I'll keep the list informed if something new arises. Thanks to everyone for helping ! Cheers -- Arnaud
Re: psql \copy hanging
Interesting topic. Is there any easy way to debug, and get correct error messages? Regards, Shao On Tue, 27 Aug 2019 at 12:17, Arnaud L. wrote: > Le 27/08/2019 à 13:13, Luca Ferrari a écrit : > > My personal experience with shares and network that are not so much > > stable is that they can block, and that is why I'm suggesting to try > > on the local filesystem to see if that is a share-related problem or a > > resource problem. Also scheduling the script at a different time could > > help, because it could be at that time the machine (and it could mean > > the sharing machine) is busy at the point it does not respond. > > I'm quite confident that changing the schedule would help (since the > script runs fine during work hours), but the time-table is already quite > busy. > I move the offending line at the end of the script, so it will run some > minutes later, maybe this will be enough. > I'll also try to write it directly on a local folder to rule this out. > > Cheers > -- > Arnaud > > >
Re: psql \copy hanging
Le 27/08/2019 à 13:13, Luca Ferrari a écrit : My personal experience with shares and network that are not so much stable is that they can block, and that is why I'm suggesting to try on the local filesystem to see if that is a share-related problem or a resource problem. Also scheduling the script at a different time could help, because it could be at that time the machine (and it could mean the sharing machine) is busy at the point it does not respond. I'm quite confident that changing the schedule would help (since the script runs fine during work hours), but the time-table is already quite busy. I move the offending line at the end of the script, so it will run some minutes later, maybe this will be enough. I'll also try to write it directly on a local folder to rule this out. Cheers -- Arnaud
Re: psql \copy hanging
On Tue, Aug 27, 2019 at 12:34 PM Arnaud L. wrote: > I could do this but it would make the script a lot more complicated. > There are a lot of views that I \copy directly to this share, and this > is the only one that poses any problem. I would however give it a try. I would schedule a nightly execution with a local path to see if that completes. > Sorry, I don't undertand either. Your question was "Does it hangs > against the same line content or the same line number?". > I run an sql script that contains a bunch of \copy commands. It is > always the same \copy that is hanging. Now I got it: your script is hanging against a specific \copy command, while I was thinking it was hanging once it was dumping a specific table line from your copy. > I could write the file locally, but for robustness I'm trying not to > rely on the local FS so that this script could be run from any machine > on the network without modifications. My personal experience with shares and network that are not so much stable is that they can block, and that is why I'm suggesting to try on the local filesystem to see if that is a share-related problem or a resource problem. Also scheduling the script at a different time could help, because it could be at that time the machine (and it could mean the sharing machine) is busy at the point it does not respond. Luca
Re: psql \copy hanging
Le 27/08/2019 à 10:57, Luca Ferrari a écrit : On Tue, Aug 27, 2019 at 10:48 AM Arnaud L. wrote: I can run the script just fine during working hours. I meant thru your scheduler (cron or something). Yes, it runs fine too. There is indeed something happening on the server at this specific time but I don't know what. It hangs against the same line in the sql script, all lines being "\copy (select ) to 'file on unc share'". This is a new detail to me: what if you output to a local file and move it after on the share? I could do this but it would make the script a lot more complicated. There are a lot of views that I \copy directly to this share, and this is the only one that poses any problem. I still don't get: is the line content the longest or the highest numbered in the output? Sorry, I don't undertand either. Your question was "Does it hangs against the same line content or the same line number?". I run an sql script that contains a bunch of \copy commands. It is always the same \copy that is hanging. Also when the script is hung, output has not started (file size is 0). This makes me think there is some problem with the share, not the script. Again: test against a local file that you move (rsync?) to the share after. Everything is windows based, so rsync is not really an option. There are alternatives of course. I could write the file locally, but for robustness I'm trying not to rely on the local FS so that this script could be run from any machine on the network without modifications. Cheers -- Arnaud
Re: psql \copy hanging
On Tue, Aug 27, 2019 at 10:48 AM Arnaud L. wrote: > I can run the script just fine during working hours. I meant thru your scheduler (cron or something). > It hangs against the same line in the sql script, all lines being "\copy > (select ) to 'file on unc share'". This is a new detail to me: what if you output to a local file and move it after on the share? > This line is simply the longest running query because the view inside > the select outputs almost 1M rows and does some subqueries. > I still don't get: is the line content the longest or the highest numbered in the output? > Also when the script is hung, output has not started (file size is 0). This makes me think there is some problem with the share, not the script. Again: test against a local file that you move (rsync?) to the share after. Luca
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. 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
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
Re: psql \copy hanging
Le 22/08/2019 à 16:23, Arnaud L. a écrit : Le 22/08/2019 à 16:21, Adrian Klaver a écrit : The only thing I can think of at the moment is to put the offending line in a separate script and see what happens. Then I think I'll first try to switch the line orders. I'll execute this action last and see if it changes anything. If it doesn't, I'll break the script into smaller parts. It ran fine over the week-end so I'll consider this as temporary glitch. It's always dissatisfying not to know what has happened, but I'm not going to try too hard to reproduce this. Thanks for helping and sorry for the noise ! Cheers -- Arnaud
Re: psql \copy hanging
Le 22/08/2019 à 20:00, Jerry Sievers a écrit : 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. Hi Jerry, yes, they both run under the same user. I've checked my other scheduled tasks and I don't see what could interfere, but then this is quite complex so I could easily overlook something. Unfortunately, tonight's run failed for a totally different reason... Thanks for your help Cheers -- Arnaud
Re: psql \copy hanging
"Arnaud L." 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.consult...@comcast.net
Re: psql \copy hanging
Le 22/08/2019 à 16:21, Adrian Klaver a écrit : The only thing I can think of at the moment is to put the offending line in a separate script and see what happens. Then I think I'll first try to switch the line orders. I'll execute this action last and see if it changes anything. If it doesn't, I'll break the script into smaller parts. Thanks for you help ! -- Arnaud
Re: psql \copy hanging
On 8/22/19 7:07 AM, Arnaud L. wrote: Le 22/08/2019 à 16:00, Adrian Klaver a écrit : The first thing I think of when seeing this sort of behavior on Windows is anti-virus software. Doing you have any running on the machine? You say it always hangs on the same line. Is that line doing something different from the others? Say writing to a different location. Thanks for your insights Adrian. There is no AV software on the machine as it is a Server version of Windows. (I know some people run AV on Windows Server, but I don't) All the lines write to the same location which is a network share, but the hanging one is clearly the longest query to execute and the output file is one of the biggest one (though not the biggest). The only thing I can think of at the moment is to put the offending line in a separate script and see what happens. Note that the share points to a local folder. The fact the psql writes to a share is only for robustness so that this script could be executed from any machine. Cheers -- Arnaud -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql \copy hanging
Le 22/08/2019 à 16:00, Adrian Klaver a écrit : The first thing I think of when seeing this sort of behavior on Windows is anti-virus software. Doing you have any running on the machine? You say it always hangs on the same line. Is that line doing something different from the others? Say writing to a different location. Thanks for your insights Adrian. There is no AV software on the machine as it is a Server version of Windows. (I know some people run AV on Windows Server, but I don't) All the lines write to the same location which is a network share, but the hanging one is clearly the longest query to execute and the output file is one of the biggest one (though not the biggest). Note that the share points to a local folder. The fact the psql writes to a share is only for robustness so that this script could be executed from any machine. Cheers -- Arnaud
Re: psql \copy hanging
On 8/22/19 1:47 AM, Arnaud L. wrote: 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. 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 ? The first thing I think of when seeing this sort of behavior on Windows is anti-virus software. Doing you have any running on the machine? You say it always hangs on the same line. Is that line doing something different from the others? Say writing to a different location. 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 -- Adrian Klaver adrian.kla...@aklaver.com
