pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAborting transactions
7+ messages / 3 participants
[nested] [flat]
* Aborting transactions
@ 2006-06-26 15:09 Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas Mack @ 2006-06-26 15:09 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Aborting transactions
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
@ 2006-06-26 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 20:10 ` Re: Aborting transactions mack@ifis.cs.tu-bs.de
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-06-26 16:10 UTC (permalink / raw)
To: Thomas Mack <mack@ifis.cs.tu-bs.de>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Aborting transactions
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-26 20:10 ` mack@ifis.cs.tu-bs.de
2006-06-26 20:15 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: mack@ifis.cs.tu-bs.de @ 2006-06-26 20:10 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-admin
>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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Aborting transactions
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 20:10 ` Re: Aborting transactions mack@ifis.cs.tu-bs.de
@ 2006-06-26 20:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-27 08:18 ` Re: Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-06-26 20:15 UTC (permalink / raw)
To: mack@ifis.cs.tu-bs.de; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Aborting transactions
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 20:10 ` Re: Aborting transactions mack@ifis.cs.tu-bs.de
2006-06-26 20:15 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-27 08:18 ` Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-27 13:47 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas Mack @ 2006-06-27 08:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Aborting transactions
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 20:10 ` Re: Aborting transactions mack@ifis.cs.tu-bs.de
2006-06-26 20:15 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
2006-06-27 08:18 ` Re: Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
@ 2006-06-27 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-27 14:17 ` Re: Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-06-27 13:47 UTC (permalink / raw)
To: Thomas Mack <mack@ifis.cs.tu-bs.de>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Aborting transactions
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 20:10 ` Re: Aborting transactions mack@ifis.cs.tu-bs.de
2006-06-26 20:15 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
2006-06-27 08:18 ` Re: Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-27 13:47 ` Re: Aborting transactions Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-27 14:17 ` Thomas Mack <mack@ifis.cs.tu-bs.de>
0 siblings, 0 replies; 7+ messages in thread
From: Thomas Mack @ 2006-06-27 14:17 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-06-27 14:17 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-26 15:09 Aborting transactions Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-26 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-26 20:10 ` mack@ifis.cs.tu-bs.de
2006-06-26 20:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-27 08:18 ` Thomas Mack <mack@ifis.cs.tu-bs.de>
2006-06-27 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-27 14:17 ` Thomas Mack <mack@ifis.cs.tu-bs.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox