Re: [ADMIN] out of memory error

2012-05-23 Thread Vincent Dautremont
Well, Thank you very much for your help, it's greatly appreciated. At least I can now pinpoint the problem and search for a solution or another reason to upgrade to 9.1 ! Regards, Vincent. On Wed, May 23, 2012 at 5:33 PM, Tom Lane wrote: > Vincent Dautremont writes: > > you were right, > > I d

Re: [ADMIN] out of memory error

2012-05-23 Thread Tom Lane
Vincent Dautremont writes: > you were right, > I do see those CREATE OR REPLACE FUNCTION a bit more than 1 per second > (approx. 12 times for 10 seconds) Hah. Complain to the rubyrep people. It's most likely just a thinko about where they should issue that command. If they actually are changin

Re: [ADMIN] out of memory error

2012-05-23 Thread Vincent Dautremont
Hi, you were right, I do see those CREATE OR REPLACE FUNCTION a bit more than 1 per second (approx. 12 times for 10 seconds) 2012-05-23 21:15:45 WET LOG: execute : CREATE OR > REPLACE FUNCTION "rr_ptz_lock"() RETURNS TRIGGER AS $change_trigger$ > BEGIN >

Re: [ADMIN] out of memory error

2012-05-23 Thread Tom Lane
Vincent Dautremont writes: > I've found out that when my software does these updates the memory of the > postgres process grows constantly at 24 MB/hour. when I stop my software to > update these rows, the memory of the process stops to grow. > also I've noticed that when I stop rubyrep, this post

Re: [ADMIN] out of memory error

2012-05-23 Thread Vincent Dautremont
Thanks, So I've been able to find what's causing my postgres process memory amount to grow, but I don't know why it happens. So, my software is updating 6 rows/second on my main database. Rubyrep is running on my server with thebackup database doing a "replicate" The huge TopMemoryContext problem

Re: [ADMIN] out of memory error

2012-05-22 Thread Tom Lane
Vincent Dautremont writes: >> An entirely blue-sky guess as >> to what your code might be doing to trigger such a problem is if you >> were constantly replacing the same function's definition via CREATE OR >> REPLACE FUNCTION. > Do you mean that what would happen is that when we call the plpgsql

Re: [ADMIN] out of memory error

2012-05-22 Thread Vincent Dautremont
Thanks Tom, when you say, > An entirely blue-sky guess as > to what your code might be doing to trigger such a problem is if you > were constantly replacing the same function's definition via CREATE OR > REPLACE FUNCTION. > Do you mean that what would happen is that when we call the plpgsql funct

Re: [ADMIN] out of memory error

2012-05-22 Thread Tom Lane
Vincent Dautremont writes: > I think that i'm using the database for pretty basic stuffs. > It's mostly used with stored procedures to update/ insert / select a row of > each table. > On 3 tables (less than 10 rows each), clients does updates/select at 2Hz to > have pseudo real-time data up to dat

Re: [ADMIN] out of memory error

2012-05-22 Thread Vincent Dautremont
Well, I think that i'm using the database for pretty basic stuffs. It's mostly used with stored procedures to update/ insert / select a row of each table. On 3 tables (less than 10 rows each), clients does updates/select at 2Hz to have pseudo real-time data up to date. I've got a total of 6 clients

Re: [ADMIN] out of memory error

2012-05-22 Thread Tom Lane
Vincent Dautremont writes: > after a few days, i'm seeing the following logs in a database (postgresql > 8.3.15 on Windows) > running with rubyrep 1.2.0 for syncing a few table small that have frequent > update / insert/ delete. > I don't understand it and I'd like to know what happens and why. H

Re: [ADMIN] out of memory error

2010-09-03 Thread Silvio Brandani
Tom Lane ha scritto: Silvio Brandani writes: Tom Lane ha scritto: Is it really the *exact* same query both ways, or are you doing something like parameterizing the query in the application? Is it exactly the same, the query text is from the postgres log. I just try it in t

Re: [ADMIN] out of memory error

2010-09-03 Thread Silvio Brandani
Tom Lane ha scritto: Silvio Brandani writes: Tom Lane ha scritto: Is it really the *exact* same query both ways, or are you doing something like parameterizing the query in the application? Is it exactly the same, the query text is from the postgres log. I just try it in t

Re: [ADMIN] out of memory error

2010-09-02 Thread Tom Lane
Silvio Brandani writes: > Tom Lane ha scritto: >> Is it really the *exact* same query both ways, or are you doing >> something like parameterizing the query in the application? > Is it exactly the same, the query text is from the postgres log. > I just try it in test environment and we have same

Re: [ADMIN] out of memory error

2010-09-02 Thread Silvio Brandani
Tom Lane ha scritto: Silvio Brandani writes: Still problems of Out of Memory: the query is the following and if I run it from psql is working fine, but from application I get error : Is it really the *exact* same query both ways, or are you doing something like parameterizing the q

Re: [ADMIN] out of memory error

2010-09-02 Thread Tom Lane
Silvio Brandani writes: >> Still problems of Out of Memory: >> the query is the following and if I run it from psql is working fine, >> but from application I get error : Is it really the *exact* same query both ways, or are you doing something like parameterizing the query in the application?

Re: [ADMIN] out of memory error

2010-09-02 Thread Silvio Brandani
Silvio Brandani ha scritto: Still problems of Out of Memory: the query is the following and if I run it from psql is working fine, but from application I get error : SELECT MAX(oec.ctnr_nr) ::char(13) as Ctnr_nr,MAX(oec.file_ref) ::char(7) as File_Ref,MAX(oec.move_type) ::char(5) as Ctnr_ty

Re: [ADMIN] out of memory error

2010-09-02 Thread Silvio Brandani
Still problems of Out of Memory: the query is the following and if I run it from psql is working fine, but from application I get error : SELECT MAX(oec.ctnr_nr) ::char(13) as Ctnr_nr,MAX(oec.file_ref) ::char(7) as File_Ref,MAX(oec.move_type) ::char(5) as Ctnr_type,MAX(oec.ct_feet) ::char(3)

Re: [ADMIN] out of memory error

2010-08-06 Thread Alvaro Herrera
Excerpts from Silvio Brandani's message of vie ago 06 07:56:53 -0400 2010: > it seems the execution plan is different for this query when run from > the application versus the psql . How can I check the execution plan of > a query run by a user?? > I can set explain analyze for the query via psq

Re: [ADMIN] out of memory error

2010-08-06 Thread Silvio Brandani
Silvio Brandani ha scritto: Bob Lunney ha scritto: Silvio , I had a similar problem when starting the database from an account that didn't have the appropriate ulimits set. Check the ulimit values using ulimit -a. HTH, Bob Lunney --- On Thu, 8/5/10, Silvio Brandani wrote: From: Silvi

Re: [ADMIN] out of memory error

2010-08-06 Thread Silvio Brandani
Bob Lunney ha scritto: Silvio , I had a similar problem when starting the database from an account that didn't have the appropriate ulimits set. Check the ulimit values using ulimit -a. HTH, Bob Lunney --- On Thu, 8/5/10, Silvio Brandani wrote: From: Silvio Brandani Subject: [ADMIN]

Re: [ADMIN] out of memory error

2010-08-05 Thread Bob Lunney
Silvio , I had a similar problem when starting the database from an account that didn't have the appropriate ulimits set. Check the ulimit values using ulimit -a. HTH, Bob Lunney --- On Thu, 8/5/10, Silvio Brandani wrote: > From: Silvio Brandani > Subject: [ADMIN] out of memory error > To

Re: [ADMIN] out of memory error

2010-08-05 Thread Scott Marlowe
2010/8/5 Silvio Brandani : >> > > I have tried to increase the parameters but still fail. what is strange is > that with psql the query works fine and give result immediatly, with > application through odbc the query fail That's usually the opposite of what you want to do here. -- Sent via pgsql

Re: [ADMIN] out of memory error

2010-08-05 Thread Tom Lane
Silvio Brandani writes: >> "Kevin Grittner" writes: >>> What query? [ query with aggregates and GROUP BY ] Does EXPLAIN show that it's trying to use a hash aggregation plan? If so, try turning off enable_hashagg. I think the hash table might be ballooning far past the number of entries the pla

Re: [ADMIN] out of memory error

2010-08-05 Thread Silvio Brandani
Tom Lane ha scritto: "Kevin Grittner" writes: Silvio Brandani wrote: a query on our production database give following errror: 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on request of size 48.

Re: [ADMIN] out of memory error

2010-08-05 Thread Tom Lane
"Kevin Grittner" writes: > Silvio Brandani wrote: >> a query on our production database give following errror: >> >> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory >> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on >> request of size 48. > What query? On what OS?

Re: [ADMIN] out of memory error

2010-08-05 Thread Silvio Brandani
Victor Hugo ha scritto: Hi Silvio, I don't know if this is relevant. But, work_mem and some other parameters inside postgresql.conf are not set. Here is a portion of the file: shared_buffers = 32MB temp_buffers = 8MB max_prepared_transactions = 5 work_mem = 1MB maintenance_work_mem = 16MB max_s

Re: [ADMIN] out of memory error

2010-08-05 Thread Kevin Grittner
Silvio Brandani wrote: > a query on our production database give following errror: > > 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory > 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on > request of size 48. What query? On what OS? Is this a 32-bit or 64-bit buil

Re: [ADMIN] out of memory error

2010-08-05 Thread Victor Hugo
Hi Silvio, I don't know if this is relevant. But, work_mem and some other parameters inside postgresql.conf are not set. Here is a portion of the file: shared_buffers = 32MB temp_buffers = 8MB max_prepared_transactions = 5 work_mem = 1MB maintenance_work_mem = 16MB max_stack_depth = 2MB []´s Vi

Re: [ADMIN] Out of memory error

2006-06-23 Thread Tom Lane
"Abu Mushayeed" <[EMAIL PROTECTED]> writes: > AFTER A WHILE THE SYSTEM COMES BACK AND SAYS IN THE LOG FILE: Please turn off your caps lock key :-( > AggContext: -1501569024 total in 351 blocks; 69904 free (507 chunks); > -1501638928 used > DynaHashTable: 302047256 total in 46 blocks; 275720 free

Re: [ADMIN] out of memory error with large insert

2006-03-21 Thread Sriram Dandapani
esday, March 21, 2006 2:38 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] out of memory error with large insert "Sriram Dandapani" <[EMAIL PROTECTED]> writes: > On a large transaction involving an insert of 8 million rows, after a > while Post

Re: [ADMIN] out of memory error with large insert

2006-03-21 Thread Tom Lane
"Sriram Dandapani" <[EMAIL PROTECTED]> writes: > On a large transaction involving an insert of 8 million rows, after a > while Postgres complains of an out of memory error. If there are foreign-key checks involved, try dropping those constraints and re-creating them afterwards. Probably faster th

Re: [ADMIN] out of memory error

2005-03-26 Thread pginfo
Hi, I send some additional info : pginfo wrote:sklad05=# explain analyze select S.IDS_NUM,S.IDS_SKLAD,SUM(S.KOL),S.sernum FROM A_GAR_PROD_R S where ids < 9742465 GROUP BY S.IDS_NUM,S.IDS_SKLAD ,s.sernum limit 10; QUERY

Re: [ADMIN] Out of memory error

2004-06-15 Thread Tom Lane
ow <[EMAIL PROTECTED]> writes: > insert into tableA > select * from tableB > where testdate >= '2000-01-01' and testdate <= '2002-12-31' > What's happening is that pgSql gradually takes all (well, almost) physical and > swap memory and then, I think, is getting killed by the kernel. > 1) Is this

Re: [ADMIN] out of memory error

2004-06-09 Thread Naomi Walker
Jie Liang wrote: Does 7.3* support this? Can you tell me a bit more about it, please? Hash aggregate..? >I had a similar problem after upgrade to 7.4.2, >Try: >SET enable_hashagg = false; >Before you execute that SELECT stmt >If you don't want disable it in postgresql.conf > >Jie Liang > >

Re: [ADMIN] out of memory error

2004-06-07 Thread Jie Liang
I had a similar problem after upgrade to 7.4.2, Try: SET enable_hashagg = false; Before you execute that SELECT stmt If you don't want disable it in postgresql.conf Jie Liang -Original Message- From: Adi Alurkar [mailto:[EMAIL PROTECTED] Sent: Monday, June 07, 2004 5:01 PM To: [EMAIL P