pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow Update
3+ messages / 3 participants
[nested] [flat]
* Slow Update
@ 2005-03-09 14:44 Ricardo Valença de Assis <valenca@campusvirtual.br>
0 siblings, 1 reply; 3+ messages in thread
From: Ricardo Valença de Assis @ 2005-03-09 14:44 UTC (permalink / raw)
To: pgsql-admin
Hi everydoby!
I have a table with more than 26000 rows and I need to use update a
column of this tables on all lines according with a column. So, I need to
use update 26000 times correct? I tried to use a this command:
"UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where
VARIABLES is the list separeted by commas. But the list has about 26000
entries, so I got a message of too long parameters. Is there a way to use
UPDATE pushing values from a file? Is there a way to run update more faster?
It is taking about 10 seconds for each UPDATE... Does anyone knows another
form to do this task?
Thanks
----- Original Message -----
From: "Daniel Rubio" <drubior@tinet.org>
To: <pgsql-admin@postgresql.org>
Sent: Wednesday, March 09, 2005 8:33 AM
Subject: Re: [ADMIN] Too many clients----A big problem for my team
> I had the same problem a few weeks ago.
>
> Was happening that our developers using Tomcat, were using connection
> pools, and stoping and restarting their aplications which causes to
> leave these connection opened and opening some new.
>
> Look in the servlet engine (or what they use) for the number of
> connections they made (in Tomcat it's a parameter), say them not to
> leave the connections opened, and not to stop and restart the java
> application when they make changes, they must restart the "engine" (it
> closes all the opened connections).
>
> It worked for me ...
>
> ganapatiram wrote:
>
> > Hi Experts,
> >
> > I work with postgresql 8.0 version. I have a team of 14
> > developers. When all the team connects the DB i see this error very very
> > frequently which is a severe problem for me and team all the day. The
> > error is:
> >
> >
> > *Please report this exception: java.sql.SQLException: FATAL: sorry, too
> > many clients already*
> >
> > **
> >
> > *when i do trial and error changes i see this error differently but the
> > essense of the error remains same. *
> >
> > **
> >
> > * *I have max_connections=200 and i have 5 users for my DB. I
> > dont understand what to do in order to resolve this. Please suggest me
> > something by which i can overcome this problem. I referred few threads
> > but none of them are of my case.
> >
> >
> >
> >
> >
> > Waiting for your valuable suggestion..........
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Thanks and Regards
> >
> > Ram
> >
>
>
> --
> ********************************************************
> Daniel Rubio Rodríguez
> OASI (Organisme Autònom Per la Societat de la Informació)
> c/ Assalt, 12
> 43003 - Tarragona
> Tef.: 977.244.007 - Fax: 977.224.517
> e-mail: drubio a oasi.org
> ********************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow Update
@ 2005-03-09 15:19 Bruno Wolff III <bruno@wolff.to>
parent: Ricardo Valença de Assis <valenca@campusvirtual.br>
0 siblings, 1 reply; 3+ messages in thread
From: Bruno Wolff III @ 2005-03-09 15:19 UTC (permalink / raw)
To: Ricardo Valença de Assis <valenca@campusvirtual.br>; +Cc: pgsql-admin
On Wed, Mar 09, 2005 at 11:44:33 -0300,
Ricardo Valença de Assis <valenca@campusvirtual.br> wrote:
> Hi everydoby!
>
> I have a table with more than 26000 rows and I need to use update a
> column of this tables on all lines according with a column. So, I need to
> use update 26000 times correct? I tried to use a this command:
> "UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where
> VARIABLES is the list separeted by commas. But the list has about 26000
> entries, so I got a message of too long parameters. Is there a way to use
> UPDATE pushing values from a file? Is there a way to run update more faster?
> It is taking about 10 seconds for each UPDATE... Does anyone knows another
> form to do this task?
If you really want to do this for all rows in the table just do:
UPDATE tablename SET column1=0;
>
> ----- Original Message -----
> From: "Daniel Rubio" <drubior@tinet.org>
> To: <pgsql-admin@postgresql.org>
> Sent: Wednesday, March 09, 2005 8:33 AM
> Subject: Re: [ADMIN] Too many clients----A big problem for my team
Why did you include this message that had nothing to do with your question?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow Update
@ 2005-03-09 21:05 Adrian Engelbrecht <adrianengelbrecht@hotmail.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 3+ messages in thread
From: Adrian Engelbrecht @ 2005-03-09 21:05 UTC (permalink / raw)
To: bruno@wolff.to; valenca@campusvirtual.br; +Cc: pgsql-admin
<html><div style='background-color:'><P>If it is necessary for you to comapre each row with a list of 26 possible values (VARIABLES) in the IN clause and not do a replacement of all values in column1 with "0" as suggested previously, you might want to place the 26000 variables in a temporary table that is either sorted physically in the required search order, or indexed, then place a select on the IN clause from that table.</P>
<P>I'm not surprised it's taking a long time to run the update. Firstly, it's an update, and secondly, for each of the 26000 rows in the table, it is comparing with 26000 variables, so it is doing 26000 x 26000 "selects".<BR><BR></P><BR><BR><BR>
<DIV>
<P>Adrian</P>
<P>ICQ 120480893</P>
<P><A href="https://www.paypal.com/refer/pal=N6T2FQ7WRPHH4">https://www.paypal.com/refer/pal=N6T2FQ7WRPHH...: Bruno Wolff III <bruno@wolff.to> To: Ricardo Valença de Assis <valenca@campusvirtual.br> CC: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Slow Update Date: Wed, 9 Mar 2005 09:19:18 -0600 On Wed, Mar 09, 2005 at 11:44:33 -0300, Ricardo Valença de Assis <valenca@campusvirtual.br> wrote: > Hi everydoby! > > I have a table with more than 26000 rows and I need to use update a > column of this tables on all lines according with a column. So, I need to > use update 26000 times correct? I tried to use a this command: > "UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where > VARIABLES is the list separeted by commas. But the list has about 26000 > entries, so I got a message of too long parameters. Is there a way
to use > UPDATE pushing values from a file? Is there a way to run update more faster? > It is taking about 10 seconds for each UPDATE... Does anyone knows another > form to do this task? If you really want to do this for all rows in the table just do: UPDATE tablename SET column1=0; > > ----- Original Message ----- > From: "Daniel Rubio" <drubior@tinet.org> > To: <pgsql-admin@postgresql.org> > Sent: Wednesday, March 09, 2005 8:33 AM > Subject: Re: [ADMIN] Too many clients----A big problem for my team Why did you include this message that had nothing to do with your question? ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org </div><br clear=all><hr>Looking for love? Check out <a href="http://g.msn.com/8HMBENNZ/2749??PS=47575"; target="_top">XtraMSN Personals</a> </html>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-09 21:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-09 14:44 Slow Update Ricardo Valença de Assis <valenca@campusvirtual.br>
2005-03-09 15:19 ` Bruno Wolff III <bruno@wolff.to>
2005-03-09 21:05 ` Adrian Engelbrecht <adrianengelbrecht@hotmail.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