Hi all,

I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat 
Linux 7. I'm sending lots of insert statements to the postgresql server 
from another machine via JDBC. During that process postgresql continues to 
take up more and more memory and seemingly never returns it to the system. 
Oddly if I watch the postmaster and it's sub processes in ktop, I can't see 
which process takes up this memory. ktop shows that the postgresql related 
processes have a constant memory usage but the overall memory usage always 
increases as long as I continue to send insert statements.

When the database connection is closed, no memory is reclaimed, the overall 
memory usage stays the same. And when I close down all postgresql processes 
including postmaster, it's the same.
I'm rather new to Linux and postgresql so I'm not sure if I should call 
this a memory leak :-)
Has anybody experienced a similar thing?

thanks,
Alexander Jerusalemvknn 


Sun, 21 Jan 2001 13:18:54 +0100
Alexander Jerusalem <ajeru@gmx.net>
postgres memory management
Thank you for your answer Mark!

Now I have updated glibc to the latest version (2.2) and it's still the 
same. I don't have the time to change to a different Linux version just to 
try if that solves the problem. What else could I do?

thanks,
Alexander Jerusalem
ajeru@gmx.net


At 15:49 21.01.01, you wrote:
>First Things First. I would not use a .0 version of Redhat for anything. The
>7.0 version is very buggy. Switch to Redhat 6.2 or another distribution like
>Slackware 7.2.
>
> > Hi all,
> >
> > I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat
> > Linux 7. I'm sending lots of insert statements to the postgresql server
> > from another machine via JDBC. During that process postgresql continues to
> > take up more and more memory and seemingly never returns it to the system.
> > Oddly if I watch the postmaster and it's sub processes in ktop, I can't
>see
> > which process takes up this memory. ktop shows that the postgresql related
> > processes have a constant memory usage but the overall memory usage always
> > increases as long as I continue to send insert statements.
> >
> > When the database connection is closed, no memory is reclaimed, the
>overall
> > memory usage stays the same. And when I close down all postgresql
>processes
> > including postmaster, it's the same.
> > I'm rather new to Linux and postgresql so I'm not sure if I should call
> > this a memory leak :-)
> > Has anybody experienced a similar thing?
> >
> > thanks,
> > Alexander Jerusalemvknn
> >
> >


Sun, 21 Jan 2001 17:01:34 +0100
Alexander Jerusalem <ajeru@gmx.net>
Re: postgres memory management
On Sun, Jan 21, 2001 at 13:18:54 +0100, Alexander Jerusalem wrote:
> During that process postgresql continues to take up more and more memory
> and seemingly never returns it to the system. Oddly if I watch the
> postmaster and it's sub processes in ktop, I can't see which process takes
> up this memory. ktop shows that the postgresql related processes have a
> constant memory usage but the overall memory usage always increases as
> long as I continue to send insert statements.
> 
> When the database connection is closed, no memory is reclaimed, the
> overall memory usage stays the same. And when I close down all postgresql
> processes including postmaster, it's the same.

Linux uses memory that wouldn't otherwise be used as buffer/cache space
(watch the "cached" entry in "top"). This is nothing to worry about.

HTH,
Ray
-- 
USDoJ/Judge Jackson: "Microsoft has performed an illegal operation and will
be shut down."
	James Turinsky in alt.sysadmin.recovery

Sun, 21 Jan 2001 18:46:38 +0100
"J.H.M. Dassen (Ray)" <jdassen@cistron-office.nl>
Re: postgres memory management
On Sun, Jan 21, 2001 at 01:18:54PM +0100, Alexander Jerusalem wrote:
> When the database connection is closed, no memory is reclaimed, the overall 
> memory usage stays the same. And when I close down all postgresql processes 
> including postmaster, it's the same.
> I'm rather new to Linux and postgresql so I'm not sure if I should call 
> this a memory leak :-)

How much memory is being used? Do you ever go into swap? If not,
what's probably happening is Linux is using free memory to cache data
like I/O. Linux should automatically release this memory if it's
needed by a process. So as long as you have some free memory, I'd
say don't worry about it -- but if you start going into swap and
this memory isn't released, then you might have a problem.

BTW, you're using 'ktop', the KDE front end to 'top'? If you're
concerned about memory usage, I'd definately recommend not running
KDE, X, or any other GUI stuff.

HTH,

Neil

-- 
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Violence is to dictatorship as propaganda is to democracy.
        -- Noam Chomsky

Sun, 21 Jan 2001 12:49:49 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: postgres memory management
Neil, thank you for your answer,

I thought about that possibility and it is possible since that computer has 
512 MB RAM. But when I start and stop other programs like emacs the memory 
is freed as soon as I stop them. As to KDE: I'm not concerned about a lack 
of memory in general but I'm about to deploy an application on a server 
that I hope will be running for a long time without me having to restart it 
every two days because of a memory leak in some software. Anyway, I hope 
you're right, I'll just try it :-)

thanks,

Alexander Jerusalem
ajeru@gmx.net
vknn


At 18:49 21.01.01, Neil Conway wrote:
>On Sun, Jan 21, 2001 at 01:18:54PM +0100, Alexander Jerusalem wrote:
> > When the database connection is closed, no memory is reclaimed, the 
> overall
> > memory usage stays the same. And when I close down all postgresql 
> processes
> > including postmaster, it's the same.
> > I'm rather new to Linux and postgresql so I'm not sure if I should call
> > this a memory leak :-)
>
>How much memory is being used? Do you ever go into swap? If not,
>what's probably happening is Linux is using free memory to cache data
>like I/O. Linux should automatically release this memory if it's
>needed by a process. So as long as you have some free memory, I'd
>say don't worry about it -- but if you start going into swap and
>this memory isn't released, then you might have a problem.
>
>BTW, you're using 'ktop', the KDE front end to 'top'? If you're
>concerned about memory usage, I'd definately recommend not running
>KDE, X, or any other GUI stuff.
>
>HTH,
>
>Neil
>
>--
>Neil Conway <neilconway@home.com>
>Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
>Encrypted mail welcomed
>
>Violence is to dictatorship as propaganda is to democracy.
>         -- Noam Chomsky


Sun, 21 Jan 2001 22:27:45 +0100
Alexander Jerusalem <ajeru@gmx.net>
Re: postgres memory management
Hello,

I'm converting a mysql database to postgres.  Is there an equivalent 
for the enum data type?

Thanks,

Steve L

Sun, 21 Jan 2001 22:33:02 -0500
Steve Leibel <stevel@bluetuna.com>
'enum' equivalent?

On Sun, Jan 21, 2001 at 10:33:02PM -0500, Steve Leibel wrote:
> Hello,
> 
> I'm converting a mysql database to postgres.  Is there an equivalent 
> for the enum data type?

If you want to make a column limited to few certain values, you can
define it something like:

    mycolumn varchar(3) check (mycolumn in ('foo', 'bar', 'baz'))

That would be somewhat similar in effec to mysql's

    mycolumn enum('foo', 'bar', 'baz')

Zach 
-- 
xach@xach.com     Zachary Beane     http://www.xach.com/

Sun, 21 Jan 2001 23:01:04 -0500
Zachary Beane <xach@xach.com>
Re: 'enum' equivalent?
Steve Leibel writes:
> I'm converting a mysql database to postgres.  Is there an equivalent 
> for the enum data type?

No, but you can put the enum data in a separate table and join
them. This also makes the operation of adding entries to the enum list
better defined.

Dan

Mon, 22 Jan 2001 10:50:32 -0800 (PST)
Dan Lyke <danlyke@flutterby.com>
'enum' equivalent?
At 13:18 21/01/01 +0100, Alexander Jerusalem wrote:
>Hi all,
>
>I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat 
>Linux 7. I'm sending lots of insert statements to the postgresql server 
>from another machine via JDBC. During that process postgresql continues to 
>take up more and more memory and seemingly never returns it to the system. 
>Oddly if I watch the postmaster and it's sub processes in ktop, I can't 
>see which process takes up this memory. ktop shows that the postgresql 
>related processes have a constant memory usage but the overall memory 
>usage always increases as long as I continue to send insert statements.
>
>When the database connection is closed, no memory is reclaimed, the 
>overall memory usage stays the same. And when I close down all postgresql 
>processes including postmaster, it's the same.
>I'm rather new to Linux and postgresql so I'm not sure if I should call 
>this a memory leak :-)
>Has anybody experienced a similar thing?

I'm not sure myself. You can rule out JDBC (or Java) here as you say you 
are connecting from another machine.

When your JDBC app closes, does it call the connection's close() method? 
Does any messages like "Unexpected EOF from client" appear on the server side?

The only other thing that comes to mine is possibly something weird is 
happening with IPC. After you closed down postgres, does ipcclean free up 
any memory?

I'm cc'in the hackers list and the new jdbc list.

Peter


>thanks,
>Alexander Jerusalemvknn


Mon, 22 Jan 2001 20:40:53 +0000
Peter Mount <peter@retep.org.uk>
Re: postgres memory management
* Peter Mount <peter@retep.org.uk> [010122 13:21] wrote:
> At 13:18 21/01/01 +0100, Alexander Jerusalem wrote:
> >Hi all,
> >
> >I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat 
> >Linux 7. I'm sending lots of insert statements to the postgresql server 
> >from another machine via JDBC. During that process postgresql continues to 
> >take up more and more memory and seemingly never returns it to the system. 
> >Oddly if I watch the postmaster and it's sub processes in ktop, I can't 
> >see which process takes up this memory. ktop shows that the postgresql 
> >related processes have a constant memory usage but the overall memory 
> >usage always increases as long as I continue to send insert statements.
> >
> >When the database connection is closed, no memory is reclaimed, the 
> >overall memory usage stays the same. And when I close down all postgresql 
> >processes including postmaster, it's the same.
> >I'm rather new to Linux and postgresql so I'm not sure if I should call 
> >this a memory leak :-)
> >Has anybody experienced a similar thing?
> 
> I'm not sure myself. You can rule out JDBC (or Java) here as you say you 
> are connecting from another machine.
> 
> When your JDBC app closes, does it call the connection's close() method? 
> Does any messages like "Unexpected EOF from client" appear on the server side?
> 
> The only other thing that comes to mine is possibly something weird is 
> happening with IPC. After you closed down postgres, does ipcclean free up 
> any memory?

I don't know if this is valid for Linux, but it is how FreeBSD
works, for the most part used memory is never free'd, it is only
marked as reclaimable.  This is so the system can cache more data.
On a freshly booted FreeBSD box you'll have a lot of 'free' memory,
after the box has been running for a long time the 'free' memory
will probably never go higher that 10megs, the rest is being used
as cache.

The main things you have to worry about is:
a) really running out of memory (are you useing a lot of swap?)
b) not cleaning up IPC as Peter suggested.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Mon, 22 Jan 2001 13:29:55 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: [HACKERS] Re: postgres memory management
At 21:40 22.01.01, Peter Mount wrote:
>At 13:18 21/01/01 +0100, Alexander Jerusalem wrote:
>>Hi all,
>>
>>I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat 
>>Linux 7. I'm sending lots of insert statements to the postgresql server
>>>from another machine via JDBC. During that process postgresql continues to
>>take up more and more memory and seemingly never returns it to the 
>>system. Oddly if I watch the postmaster and it's sub processes in ktop, I 
>>can't see which process takes up this memory. ktop shows that the 
>>postgresql related processes have a constant memory usage but the overall 
>>memory usage always increases as long as I continue to send insert statements.
>>
>>When the database connection is closed, no memory is reclaimed, the 
>>overall memory usage stays the same. And when I close down all postgresql 
>>processes including postmaster, it's the same.
>>I'm rather new to Linux and postgresql so I'm not sure if I should call 
>>this a memory leak :-)
>>Has anybody experienced a similar thing?
>
>I'm not sure myself. You can rule out JDBC (or Java) here as you say you 
>are connecting from another machine.
>
>When your JDBC app closes, does it call the connection's close() method? 
>Does any messages like "Unexpected EOF from client" appear on the server side?
>
>The only other thing that comes to mine is possibly something weird is 
>happening with IPC. After you closed down postgres, does ipcclean free up 
>any memory?
>
>I'm cc'in the hackers list and the new jdbc list.
>
>Peter

Thanks for your answer!

Yes I'm calling Connection.close(). I don't get any error messages but 
maybe I just don't see them because postgresql is started automatically at 
run level 3. I'm not sure where the output goes. (pg_log contains only 
garbage or maybe it's a binary file) I tried ipcclean right now and it 
doesn't free the memory but it gives me some messages that I cannot interpret:

Shared memory 0 ... skipped. Process still exists (pid ).
Shared memory 1 ... skipped. Process still exists (pid ).
Shared memory 2 ... skipped. Process still exists (pid ).
Shared memory 3 ... skipped. Process still exists (pid ).
Semaphore 0 ... resource(s) deleted
Semaphore 1 ... resource(s) deleted

Oddly, when I try to run ipcclean a second time, it says: ipcclean: You 
still have a postmaster running. Which is not the case as ps -e proves.

Alexander Jerusalem
ajeru@gmx.net
vknn


Tue, 23 Jan 2001 00:53:54 +0100
Alexander Jerusalem <ajeru@gmx.net>
Re: postgres memory management
At 22:29 22.01.01, Alfred Perlstein wrote:
>* Peter Mount <peter@retep.org.uk> [010122 13:21] wrote:
> > At 13:18 21/01/01 +0100, Alexander Jerusalem wrote:
> > >Hi all,
> > >
> > >I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat
> > >Linux 7. I'm sending lots of insert statements to the postgresql server
> > >from another machine via JDBC. During that process postgresql 
> continues to
> > >take up more and more memory and seemingly never returns it to the 
> system.
> > >Oddly if I watch the postmaster and it's sub processes in ktop, I can't
> > >see which process takes up this memory. ktop shows that the postgresql
> > >related processes have a constant memory usage but the overall memory
> > >usage always increases as long as I continue to send insert statements.
> > >
> > >When the database connection is closed, no memory is reclaimed, the
> > >overall memory usage stays the same. And when I close down all postgresql
> > >processes including postmaster, it's the same.
> > >I'm rather new to Linux and postgresql so I'm not sure if I should call
> > >this a memory leak :-)
> > >Has anybody experienced a similar thing?
> >
> > I'm not sure myself. You can rule out JDBC (or Java) here as you say you
> > are connecting from another machine.
> >
> > When your JDBC app closes, does it call the connection's close() method?
> > Does any messages like "Unexpected EOF from client" appear on the 
> server side?
> >
> > The only other thing that comes to mine is possibly something weird is
> > happening with IPC. After you closed down postgres, does ipcclean free up
> > any memory?
>
>I don't know if this is valid for Linux, but it is how FreeBSD
>works, for the most part used memory is never free'd, it is only
>marked as reclaimable.  This is so the system can cache more data.
>On a freshly booted FreeBSD box you'll have a lot of 'free' memory,
>after the box has been running for a long time the 'free' memory
>will probably never go higher that 10megs, the rest is being used
>as cache.
>
>The main things you have to worry about is:
>a) really running out of memory (are you useing a lot of swap?)
>b) not cleaning up IPC as Peter suggested.

Thanks for your answer!

I'm rather new to Linux, so I can't tell if it's that way on Linux. But I 
noticed that other programs free some memory when I quit them. But it's 
true that I'm not running out of memory. I have 300 MB of free RAM and no 
swap space is used. As I wrote in reply to Peters mail, ipcclean doesn't 
change anything.

Alexander Jerusalem
ajeru@gmx.net
vknn


Tue, 23 Jan 2001 00:58:49 +0100
Alexander Jerusalem <ajeru@gmx.net>
Re: [HACKERS] Re: postgres memory management
I just looked at the code in the close() method and all it is doing is
closing the socket connection to the server.  However in looking at the
doc on the backend/frontend protocol it appears that the client (JDBC in
this case) is supposed to send a connection termination message first,
then close the socket.  I beleive that the termination message is
supposed to be a one byte value of 'X'.  If I read this correctly, then
it does appear that the JDBC connection class does have a bug.

thanks,
--Barry


Alexander Jerusalem wrote:
> 
> At 21:40 22.01.01, Peter Mount wrote:
> >At 13:18 21/01/01 +0100, Alexander Jerusalem wrote:
> >>Hi all,
> >>
> >>I'm experiencing some strange behaviour with postgresql 7.0.3 on Red Hat
> >>Linux 7. I'm sending lots of insert statements to the postgresql server
> >>>from another machine via JDBC. During that process postgresql continues to
> >>take up more and more memory and seemingly never returns it to the
> >>system. Oddly if I watch the postmaster and it's sub processes in ktop, I
> >>can't see which process takes up this memory. ktop shows that the
> >>postgresql related processes have a constant memory usage but the overall
> >>memory usage always increases as long as I continue to send insert statements.
> >>
> >>When the database connection is closed, no memory is reclaimed, the
> >>overall memory usage stays the same. And when I close down all postgresql
> >>processes including postmaster, it's the same.
> >>I'm rather new to Linux and postgresql so I'm not sure if I should call
> >>this a memory leak :-)
> >>Has anybody experienced a similar thing?
> >
> >I'm not sure myself. You can rule out JDBC (or Java) here as you say you
> >are connecting from another machine.
> >
> >When your JDBC app closes, does it call the connection's close() method?
> >Does any messages like "Unexpected EOF from client" appear on the server side?
> >
> >The only other thing that comes to mine is possibly something weird is
> >happening with IPC. After you closed down postgres, does ipcclean free up
> >any memory?
> >
> >I'm cc'in the hackers list and the new jdbc list.
> >
> >Peter
> 
> Thanks for your answer!
> 
> Yes I'm calling Connection.close(). I don't get any error messages but
> maybe I just don't see them because postgresql is started automatically at
> run level 3. I'm not sure where the output goes. (pg_log contains only
> garbage or maybe it's a binary file) I tried ipcclean right now and it
> doesn't free the memory but it gives me some messages that I cannot interpret:
> 
> Shared memory 0 ... skipped. Process still exists (pid ).
> Shared memory 1 ... skipped. Process still exists (pid ).
> Shared memory 2 ... skipped. Process still exists (pid ).
> Shared memory 3 ... skipped. Process still exists (pid ).
> Semaphore 0 ... resource(s) deleted
> Semaphore 1 ... resource(s) deleted
> 
> Oddly, when I try to run ipcclean a second time, it says: ipcclean: You
> still have a postmaster running. Which is not the case as ps -e proves.
> 
> Alexander Jerusalem
> ajeru@gmx.net
> vknn

Mon, 22 Jan 2001 16:48:20 -0800
Barry Lind <barry@xythos.com>
Re: [GENERAL] postgres memory management
Hi Alexander,

I've noticed that the PG 7.03 ipcclean script uses "ps x | grep -s
'postmaster'" to determine if a postmaster daemon is still running,
which at least for Mandrake Linux 7.2 doesn't work as expected.  With
this version of linux, the ps & grep combination will find itself and
then ipcclean will complain about an existing postmaster.

I found the solution to this being to edit the ipcclean script and
change the "ps x | grep -s 'postmaster'" part to "ps -e | grep -s
'postmaster'".  This then works correctly with Mandrake 7.2.

Regards and best wishes,

Justin Clift

<snip>
> 
> Oddly, when I try to run ipcclean a second time, it says: ipcclean: You
> still have a postmaster running. Which is not the case as ps -e proves.
> 
> Alexander Jerusalem
> ajeru@gmx.net
> vknn

Tue, 23 Jan 2001 13:03:58 +1100
Justin Clift <aa2@bigpond.net.au>
Re: postgres memory management
Justin Clift writes:
 > I found the solution to this being to edit the ipcclean script and
 > change the "ps x | grep -s 'postmaster'" part to "ps -e | grep -s
 > 'postmaster'".  This then works correctly with Mandrake 7.2.

A standard way of finding a process by name without the grep itself
appearing is use something like "grep '[p]ostmaster'".
-- 
Pete Forman                 -./\.- Disclaimer: This post is originated
WesternGeco                   -./\.-  by myself and does not represent
pete.forman@westerngeco.com     -./\.-  opinion of Schlumberger, Baker
http://www.crosswinds.net/~petef  -./\.-  Hughes or their divisions.

Tue, 23 Jan 2001 10:59:28 +0000
Pete Forman <pete.forman@westerngeco.com>
Re: postgres memory management
Hi Clift,

you are right, I have the same problem on RedHat. After I inserted -e it 
works so far. But there's something else that seems strange to me I'm not 
quite sure if I'm reading this right since I understand only half of what 
happens in this script. After the comment that says "Don't do anything if 
process still running..." on line there is the following sequence of lines:

ps hj$ipcs_pid >/dev/null 2>&1
if [ $? -eq 0 ]; then
         echo "skipped...."

As I understand it the if statement tests the output of the previous ps 
statement. The strange thing is that the variable $ipcs_pid is never used 
anywhere before this line, so I think it's always null (or whatever this 
scripting language defaults to). There are three other variables ipcs_id, 
ipcs_cpid and ipcs_lpid but no ipcs_pid. If I'm right here, it seems that 
this script does effectively nothing in terms of shared memory.

Please tell me if I'm on a completely wrong track :-)

Alexander Jerusalem
ajeru@gmx.net
vknn



At 03:03 23.01.01, Justin Clift wrote:
>Hi Alexander,
>
>I've noticed that the PG 7.03 ipcclean script uses "ps x | grep -s
>'postmaster'" to determine if a postmaster daemon is still running,
>which at least for Mandrake Linux 7.2 doesn't work as expected.  With
>this version of linux, the ps & grep combination will find itself and
>then ipcclean will complain about an existing postmaster.
>
>I found the solution to this being to edit the ipcclean script and
>change the "ps x | grep -s 'postmaster'" part to "ps -e | grep -s
>'postmaster'".  This then works correctly with Mandrake 7.2.
>
>Regards and best wishes,
>
>Justin Clift
>
><snip>
> >
> > Oddly, when I try to run ipcclean a second time, it says: ipcclean: You
> > still have a postmaster running. Which is not the case as ps -e proves.
> >
> > Alexander Jerusalem
> > ajeru@gmx.net
> > vknn


Tue, 23 Jan 2001 12:19:50 +0100
Alexander Jerusalem <ajeru@gmx.net>
Re: postgres memory management
Alexander Jerusalem <ajeru@gmx.net> writes:
> you are right, I have the same problem on RedHat. After I inserted -e it 
> works so far. But there's something else that seems strange to me I'm not 
> quite sure if I'm reading this right since I understand only half of what 
> happens in this script. After the comment that says "Don't do anything if 
> process still running..." on line there is the following sequence of lines:

> ps hj$ipcs_pid >/dev/null 2>&1
> if [ $? -eq 0 ]; then
>          echo "skipped...."

> As I understand it the if statement tests the output of the previous ps 
> statement. The strange thing is that the variable $ipcs_pid is never used 
> anywhere before this line, so I think it's always null (or whatever this 
> scripting language defaults to). There are three other variables ipcs_id, 
> ipcs_cpid and ipcs_lpid but no ipcs_pid. If I'm right here, it seems that 
> this script does effectively nothing in terms of shared memory.

I think you are right --- the Linux portion of this script is broken.
Aside from the bogus variable, the awk call at the top of the loop is
wrong (printf has three arguments and only two percents).  Given those
two typos, there are probably more.

Feel free to submit a patch to make it actually work ...

			regards, tom lane

Tue, 23 Jan 2001 11:47:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: postgres memory management 