pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate is very slow on a bigger table
7+ messages / 6 participants
[nested] [flat]
* Update is very slow on a bigger table
@ 2004-04-15 21:07 Dimitar Georgievski <dgeorgievski@horizonk.com>
2004-04-15 22:51 ` Re: Update is very slow on a bigger table Tom Lane <tgl@sss.pgh.pa.us>
2004-04-15 22:53 ` Re: Update is very slow on a bigger table CoL <col@mportal.hu>
2004-04-16 17:47 ` Re: Update is very slow on a bigger table Yudie <yudie@axiontech.com>
0 siblings, 3 replies; 7+ messages in thread
From: Dimitar Georgievski @ 2004-04-15 21:07 UTC (permalink / raw)
To: pgsql-sql
hi,
i'm running an update procedure on a table with approximately 100,000
records. The problem is the procedure is running endlessly even when the
update is very simple like in the following example:
update mytable
set client_id = 1
where mytable_id = 1
mytable_id is the primary key of mytable.
the process under which the update is running reports among the usual
process data "UPDATE waiting".
I'm using PostgreSQL 7.3.4 on SuSE 9.0. From what I have seen it doesn't
look like that the host computer has problem with the resources.
Any idea why is this happening?
thanks,
dimitar
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update is very slow on a bigger table
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
@ 2004-04-15 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-16 14:40 ` Re: Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2004-04-15 22:51 UTC (permalink / raw)
To: Dimitar Georgievski <dgeorgievski@horizonk.com>; +Cc: pgsql-sql
Dimitar Georgievski <dgeorgievski@horizonk.com> writes:
> the process under which the update is running reports among the usual
> process data "UPDATE waiting".
I don't think the UPDATE is doing anything at all --- that ps output
implies that it's waiting for some other backend to release an exclusive
lock on the table, or an update lock on one of the rows to be updated.
The pg_locks view might help you determine who's the culprit.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update is very slow on a bigger table
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
2004-04-15 22:51 ` Re: Update is very slow on a bigger table Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-16 14:40 ` Dimitar Georgievski <dgeorgievski@horizonk.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dimitar Georgievski @ 2004-04-16 14:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom,
Yes. it looks like there was some locking condition in the database. in
the meantime it disappeared and the updates run quickly.
thanks for your help. this is the first time i'm working with PostgreSQL
and wasn't sure where to look for possible causes of the problem.
thanks,
dimitar
On Thu, 2004-04-15 at 18:51, Tom Lane wrote:
> Dimitar Georgievski <dgeorgievski@horizonk.com> writes:
> > the process under which the update is running reports among the usual
> > process data "UPDATE waiting".
>
> I don't think the UPDATE is doing anything at all --- that ps output
> implies that it's waiting for some other backend to release an exclusive
> lock on the table, or an update lock on one of the rows to be updated.
>
> The pg_locks view might help you determine who's the culprit.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update is very slow on a bigger table
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
@ 2004-04-15 22:53 ` CoL <col@mportal.hu>
2 siblings, 0 replies; 7+ messages in thread
From: CoL @ 2004-04-15 22:53 UTC (permalink / raw)
To: pgsql-sql
hi,
Dimitar Georgievski wrote, On 4/15/2004 23:07:
> hi,
>
> i'm running an update procedure on a table with approximately 100,000
> records. The problem is the procedure is running endlessly even when the
> update is very simple like in the following example:
>
> update mytable
> set client_id = 1
> where mytable_id = 1
>
> mytable_id is the primary key of mytable.
>
> the process under which the update is running reports among the usual
> process data "UPDATE waiting".
>
> I'm using PostgreSQL 7.3.4 on SuSE 9.0. From what I have seen it doesn't
> look like that the host computer has problem with the resources.
>
> Any idea why is this happening?
could be, after you send some info: your table schema, your explain
analyze result, triggers, or any other info. Any other transaction
running again mytable while you try to update?
C.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update is very slow on a bigger table
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
@ 2004-04-16 17:47 ` Yudie <yudie@axiontech.com>
2004-04-17 15:13 ` Re: Update is very slow on a bigger table Bruno Wolff III <bruno@wolff.to>
2 siblings, 1 reply; 7+ messages in thread
From: Yudie @ 2004-04-16 17:47 UTC (permalink / raw)
To: pgsql-sql
Hi guys,
I would like to create a trigger that execute some programs,
is there a way to create a plpgsql function that execute shell command or
any other way to run a program?
Thanks for your help.
Yudie
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update is very slow on a bigger table
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
2004-04-16 17:47 ` Re: Update is very slow on a bigger table Yudie <yudie@axiontech.com>
@ 2004-04-17 15:13 ` Bruno Wolff III <bruno@wolff.to>
2004-04-19 15:33 ` Re: Update is very slow on a bigger table scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2004-04-17 15:13 UTC (permalink / raw)
To: Yudie <yudie@axiontech.com>; +Cc: pgsql-sql
On Fri, Apr 16, 2004 at 12:47:58 -0500,
Yudie <yudie@axiontech.com> wrote:
> Hi guys,
> I would like to create a trigger that execute some programs,
> is there a way to create a plpgsql function that execute shell command or
> any other way to run a program?
> Thanks for your help.
Only untrusted languages can be used to run external programs. perlu might
be something you can use.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Update is very slow on a bigger table
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
2004-04-16 17:47 ` Re: Update is very slow on a bigger table Yudie <yudie@axiontech.com>
2004-04-17 15:13 ` Re: Update is very slow on a bigger table Bruno Wolff III <bruno@wolff.to>
@ 2004-04-19 15:33 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 7+ messages in thread
From: scott.marlowe @ 2004-04-19 15:33 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Yudie <yudie@axiontech.com>; pgsql-sql
On Sat, 17 Apr 2004, Bruno Wolff III wrote:
> On Fri, Apr 16, 2004 at 12:47:58 -0500,
> Yudie <yudie@axiontech.com> wrote:
> > Hi guys,
> > I would like to create a trigger that execute some programs,
> > is there a way to create a plpgsql function that execute shell command or
> > any other way to run a program?
> > Thanks for your help.
>
> Only untrusted languages can be used to run external programs. perlu might
> be something you can use.
Also, take a look at plsh if all you need is to run shell functions:
http://developer.postgresql.org/~petere/pgplsh/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-04-19 15:33 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-15 21:07 Update is very slow on a bigger table Dimitar Georgievski <dgeorgievski@horizonk.com>
2004-04-15 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-16 14:40 ` Dimitar Georgievski <dgeorgievski@horizonk.com>
2004-04-15 22:53 ` CoL <col@mportal.hu>
2004-04-16 17:47 ` Yudie <yudie@axiontech.com>
2004-04-17 15:13 ` Bruno Wolff III <bruno@wolff.to>
2004-04-19 15:33 ` scott.marlowe <scott.marlowe@ihs.com>
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