Обсуждение: Aborting transactions
Hi, we use postgres for student's education and others. Now we had an issue with some SQL statement in connection with some triggers, that probably caused an infinite loop in processing for at least 10 hours or so on some two to ten line tables. Unfortunately, the students used the phpPgAdmin web interface, so they could not abort the running SQL statement. They could not even continue to work with the database from phpPgAdmin. So the question arose, how to abort running statements at least via the postgres account. I remember some methods from Ingres "some" years ago, but I did not find anything similar in postgresql. Is there any chance to monitor the current transactions and abort them, in case of need? Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme
Thomas Mack <mack@ifis.cs.tu-bs.de> writes:
> Is there any chance to monitor the current transactions and abort them,
> in case of need?
Sure, just SIGINT whichever backend you want to cancel its query.
Use "ps" or pg_stat_activity to figure out the right PID to kill.
regards, tom lane
>Thomas Mack <mack@ifis.cs.tu-bs.de> writes: >> Is there any chance to monitor the current transactions and abort them, >> in case of need? > >Sure, just SIGINT whichever backend you want to cancel its query. >Use "ps" or pg_stat_activity to figure out the right PID to kill. > I don't have a pg_stat_activity on this 7.4.6, so using ps gives me: postgres@is20 ~ 4 > ps -ef | grep postmaster postgres 4499 4498 0 08:59:18 ? 0:04 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 23268 4498 0 12:50:39 ? 0:03 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 4713 4498 0 09:25:05 ? 0:05 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 20836 4498 0 15:22:51 ? 0:02 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 20859 4498 0 18:51:48 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 25116 4498 0 13:40:26 ? 0:04 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 12050 4498 0 21:57:30 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 4498 4489 0 08:59:18 ? 0:47 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 18589 4498 0 21:09:09 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 11104 4498 0 21:34:24 ? 0:03 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 4500 4499 0 08:59:18 ? 0:48 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 14684 4498 0 19:46:16 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 25527 4498 0 13:47:25 ? 0:03 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 13863 4498 0 19:20:56 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 11910 4498 0 21:54:18 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 23450 4498 0 12:53:48 ? 0:05 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 24216 4498 0 13:16:27 ? 0:04 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 21584 4498 0 19:08:55 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 25904 4498 0 13:54:19 ? 0:04 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 17848 4498 0 17:35:52 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 10893 4498 0 21:28:04 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 12210 12147 0 22:02:27 pts/22 0:00 grep postmaster postgres 18650 4498 0 21:10:34 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 1563 4498 0 19:15:38 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 16993 4498 0 20:26:05 ? 0:08 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 14210 4498 0 19:30:01 ? 0:10 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 14457 4498 0 19:38:37 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 14030 4498 0 19:23:51 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 16675 4498 0 20:18:21 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 14914 4498 0 19:49:00 ? 0:01 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres 17576 4498 0 20:41:26 ? 0:00 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e postgres@is20 ~ 5 > So, what is what? I will switch to 8.1 later this year, is it better with that version? Best regards, Thomas Mack TU Braunschweig, Institut für Informationssysteme
mack@ifis.cs.tu-bs.de writes:
> I don't have a pg_stat_activity on this 7.4.6, so using ps gives me:
> postgres@is20 ~ 4 > ps -ef | grep postmaster
> postgres 4499 4498 0 08:59:18 ? 0:04 /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e
What platform is that? If it's Solaris, see our doc/FAQ_Solaris about
getting more useful info from "ps".
regards, tom lane
Am Montag, 26. Juni 2006 22:15 schrieb Tom Lane: > mack@ifis.cs.tu-bs.de writes: > > I don't have a pg_stat_activity on this 7.4.6, so using ps gives me: > > > > postgres@is20 ~ 4 > ps -ef | grep postmaster > > postgres 4499 4498 0 08:59:18 ? 0:04 > > /usr/local/pgsql/bin/postmaster -i -p 5356 -o -e > > What platform is that? If it's Solaris, see our doc/FAQ_Solaris about > getting more useful info from "ps". > Yes, it's Solaris 10. Looking at http://www.postgresql.org/docs/faqs.FAQ_Solaris.html , I did not find anything related. Same with the doc/FAQ_Solaris from the postgresql-8.1.4 sources. ... Ok, I tried on a Linux server with 8.0.0, where I could get some useful informations out of ps. I tried /usr/ucb/ps on Solaris, which revealed more information, but I did not see a test query from a remote client for some magic reason (select * from t1, t2 with 250,000 and 512,000 rows). I think, I can live with it for now, as I will upgrade later this year. Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme
Thomas Mack <mack@ifis.cs.tu-bs.de> writes: > Am Montag, 26. Juni 2006 22:15 schrieb Tom Lane: >> What platform is that? If it's Solaris, see our doc/FAQ_Solaris about >> getting more useful info from "ps". >> > Yes, it's Solaris 10. Looking at > http://www.postgresql.org/docs/faqs.FAQ_Solaris.html , I did not find > anything related. Same with the doc/FAQ_Solaris from the postgresql-8.1.4 > sources. Oh, sorry, I assumed it was in the Solaris FAQ, but actually the info is at the bottom of this page: http://www.postgresql.org/docs/8.1/static/monitoring.html There are several different conditions that can render "ps" useless on Solaris :-(, and one of them is sprinkling the postmaster start command with a lot of switches. Put that stuff in postgresql.conf, instead, so you can invoke the postmaster as just "postmaster". regards, tom lane
Am Dienstag, 27. Juni 2006 15:47 schrieb Tom Lane: > Thomas Mack <mack@ifis.cs.tu-bs.de> writes: > > Am Montag, 26. Juni 2006 22:15 schrieb Tom Lane: > >> What platform is that? If it's Solaris, see our doc/FAQ_Solaris > >> about getting more useful info from "ps". > > > > Yes, it's Solaris 10. Looking at > > http://www.postgresql.org/docs/faqs.FAQ_Solaris.html , I did not find > > anything related. Same with the doc/FAQ_Solaris from the > > postgresql-8.1.4 sources. > > Oh, sorry, I assumed it was in the Solaris FAQ, but actually the info > is at the bottom of this page: > Ok, it might be reasonable to duplicate it in the Solaris FAQ though... > http://www.postgresql.org/docs/8.1/static/monitoring.html > > There are several different conditions that can render "ps" useless on > Solaris :-(, and one of them is sprinkling the postmaster start command > with a lot of switches. Put that stuff in postgresql.conf, instead, > so you can invoke the postmaster as just "postmaster". > Oh yes. This explains, why I was missing quite some connections even with /usr/ucb/ps. Might be good to tell in the documentation to move the switches to postgresql.conf . Might otherwise produce additional questions... Oh, well, the 7.4 documentation states these things already as it does in 8.1. Sorry, don't know why I missed it. Maybe I had some other ideas in mind. Not monitoring, but administering or something like this. Thanks for your help, Thomas Mack TU Braunschweig, Institut für Informationssysteme