pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Aborting 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