Thread: [GENERAL]COPY still running
Hello i start yesterday afternoon a COPY command into a table. the file is 15 Mb i've set triggers before and after insert on the table the before trigger just perform data check. the after trigger update a 10 000 rows linked table. the COPY command runs all the night ad has not yet finished........... what is the problem???? PostgreSQL 6.5.2 running on Pentium II debian server use 98% CPU... -- ______________________________ Arnaud FLORENT IRIS Technologies phone: (33) 03 20 65 85 80 fax: (33) 03 20 65 85 81 GSM: (33) 06 15 14 32 90 mailto:aflorent@iris-tech.fr ______________________________
> Hello i start yesterday afternoon a COPY command into a table. > the file is 15 Mb > i've set triggers before and after insert on the table > the before trigger just perform data check. > the after trigger update a 10 000 rows linked table. > > the COPY command runs all the night ad has not yet finished........... > > what is the problem???? > > PostgreSQL 6.5.2 running on Pentium II debian server use 98% CPU... I had a similar thing once, while compiling a program, it took the computer 28 hours to complete. (on a P200) My best guess is is that it's still running, make a quick check that the main process is still running. The action you are doing requires not just alot of CPU-power, but also a lot of HD-read and writes. Another check you could do is to see if the database itself is growing, to do this, look if the total disk-usage of the database is growing, if it is, then you just gave a whole lot of work-load to the server. If either of these 2 checks come up negative, then you crashed the server, however my experience with PostgreSQL is, is that when it crashes, CPU-usage drops, not rises.... with kind regards, Joost Roeleveld
J. Roeleveld" a écrit : > > Hello i start yesterday afternoon a COPY command into a table. > > the file is 15 Mb > > i've set triggers before and after insert on the table > > the before trigger just perform data check. > > the after trigger update a 10 000 rows linked table. > > > > the COPY command runs all the night ad has not yet finished........... > > > > what is the problem???? > > > > PostgreSQL 6.5.2 running on Pentium II debian server use 98% CPU... > > I had a similar thing once, while compiling a program, it took the computer > 28 hours to complete. (on a P200) > > My best guess is is that it's still running, make a quick check that the > main > process is still running. The action you are doing requires not just alot of > CPU-power, but also a lot of HD-read and writes. the postmaster is still running and the database grows (it is not very fast but it grows and is now 60Mb large......) is thare a way to stop the copy command wihtout crashing the database? -- ______________________________ Arnaud FLORENT IRIS Technologies phone: (33) 03 20 65 85 80 fax: (33) 03 20 65 85 81 GSM: (33) 06 15 14 32 90 mailto:aflorent@iris-tech.fr ______________________________
> I had a similar thing once, while compiling a program, it took the computer > 28 hours to complete. (on a P200) > > My best guess is is that it's still running, make a quick check that the > main > process is still running. The action you are doing requires not just alot of > CPU-power, but also a lot of HD-read and writes. the postmaster is still running and the database grows (it is not very fast but it grows and is now 60Mb large......) glad to hear, it's not crashed... :) is thare a way to stop the copy command wihtout crashing the database? It depends, if you issued the command within 'psql' then yes, by pressing <ctrl>+C however, this will cause the entire transaction to be reversed, and will very likely take the same amount of time, my advise would still be to let this command to finish normally... with kind regards, Joost Roeleveld
Arnaud FLORENT writes: > Hello i start yesterday afternoon a COPY command into a table. > the file is 15 Mb > i've set triggers before and after insert on the table > the before trigger just perform data check. > the after trigger update a 10 000 rows linked table. > > the COPY command runs all the night ad has not yet finished........... > > what is the problem???? > You probably have indexes defined on this table. You should drop all the indexes before the copy and create them after the copy if the number of rows in the copy commands exceeds the number of the current rows in the table. I have dropped the indexes while copy'ing which seems to be no problem. Just create them after the copy is finished. Ymmv... Dirk
Dirk Lutzebaeck a écrit : > Arnaud FLORENT writes: > > Hello i start yesterday afternoon a COPY command into a table. > > the file is 15 Mb > > i've set triggers before and after insert on the table > > the before trigger just perform data check. > > the after trigger update a 10 000 rows linked table. > > > > the COPY command runs all the night ad has not yet finished........... > > > > what is the problem???? > > > > You probably have indexes defined on this table. You should drop all > the indexes before the copy and create them after the copy if the > number of rows in the copy commands exceeds the number of the current > rows in the table. > > I have dropped the indexes while copy'ing which seems to be no > problem. Just create them after the copy is finished. Ymmv... > sure, but i think unique constraints will be lost without index on my table...... -- ______________________________ Arnaud FLORENT IRIS Technologies phone: (33) 03 20 65 85 80 fax: (33) 03 20 65 85 81 GSM: (33) 06 15 14 32 90 mailto:aflorent@iris-tech.fr ______________________________
i have dropped a trigger on insert but the function is still called on insert? what's wrong? -- ______________________________ Arnaud FLORENT IRIS Technologies phone: (33) 03 20 65 85 80 fax: (33) 03 20 65 85 81 GSM: (33) 06 15 14 32 90 mailto:aflorent@iris-tech.fr ______________________________