Re: BUG #3680: memory leak when excuting a SQL "select count(id) from chinatelecom; "

Today I examine this case in Oracle database by Oracle client tools
executing same SQL.
After 2 minutes, the SQL is executed over, 300M memory is vanished. But
after 5 minutes, the free memory is increased to the original level (I also
use top command of Linux).
Same experiments I done for Postgresql. After long time, the free memory is
not increased, it still 300M memory vanished, so I want to know why it
happened?
ssurui(at)mailst(dot)xjtu(dot)edu(dot)cn

_____

发件人: pgsql-bugs-owner(at)postgresql(dot)org [mailto:pgsql-bugs-owner(at)postgresql(dot)
org] 代表 Douglas Toltzman
发送时间: 2007年10月17日 05:01 上午
收件人: pgsql-bugs(at)postgresql(dot)org
主题: Re: [BUGS] BUG #3680: memory leak when excuting a SQL "select
count(id) from chinatelecom;"

I'm not so sure you are correctly interpreting the memory information
provided by "top". In a virtual memory system, it is not uncommon for the
amount of RAM in use to cycle up and down, as programs grab and release
memory, and the system reclaims free memory as needed, but not necessarily
immediately.

If the amount of committed memory in used by the postmaster process
continues to go up indefinitely, threatening to exhaust all available system
memory, that may be a leak. What you are probably seeing is just normal use
of RAM resources. You weren't really specific about what numbers you were
using to make your determination, so I can only speculate.

On Oct 16, 2007, at 8:43 PM, ssurui wrote:

The following bug has been logged online:

Bug reference: 3680
Logged by: ssurui
Email address: ssurui(at)gmail(dot)com
PostgreSQL version: 8.2.3
Operating system: Redhat LINUX Advance server 3
Description: memory leak when excuting a SQL "select count(id) from
chinatelecom;"
Details:

I use postgresql 8.2.3 to build a charger system. chinatelecom is a table
name,use such command to create it :
CREATE TABLE chinatelecom
(
id serial NOT NULL,
company_id char(2) DEFAULT 'XX'::bpchar,
service_id char(2) DEFAULT 'XX'::bpchar,
inner_flag char(2) DEFAULT 'XX'::bpchar,
terminal_id char(8) DEFAULT '00000000'::bpchar,
worker_id char(8) DEFAULT '00000000'::bpchar,
contract_id varchar(30) DEFAULT '0'::character varying,
phone_number varchar(30) DEFAULT '0'::character varying,
money numeric DEFAULT 0,
trade_code varchar(3) DEFAULT '000'::character varying,
record_date date DEFAULT ('now'::text)::date,
la_serial_number varchar(12) DEFAULT '0'::character varying,
serial_number varchar(12) DEFAULT '0'::character varying,
record_time char(30),
CONSTRAINT chinatelecom_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE chinatelecom OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
chinatelecom TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE
chinatelecom TO public;

after the system runs about 4 months, the table has 2,000,000 records.

I use pgAdmin III to excute a simple SQL :
select count(*) from chinatelecom;

use top command observe the available memory, I find about 500MB memory is
vanished after the SQL is executed.

please tell me why it happened?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Douglas Toltzman
doug(at)oakstreetsoftware(dot)com
(910) 526-5938