Re: cursor interface to libpq
От | Kirby Bohling (TRSi) |
---|---|
Тема | Re: cursor interface to libpq |
Дата | |
Msg-id | Pine.GSO.4.21.0009201026100.10741-100000@oasis.novia.net обсуждение исходный текст |
Ответ на | Re: cursor interface to libpq (Thomas Lockhart <lockhart@alumni.caltech.edu>) |
Список | pgsql-interfaces |
Thomas, Right now I have a database that has right around 4 Million rows in the primary table. I am porting it away from MySQL basically because I want transactions. When the table is written as insert statements it is around 3.2Gb, when that is gzipped it comes out to 1.3Gb. That table size will probably continue to grow at a rate of 200,000 rows a week. I don't know the exact size of each row, but it is under the 8K limit. Right now I am working on a FreeBSD box with 1.5Gb of swap and 256MB of Ram. I believe that I could get that upgraded to 1Gb of ram, and add as much swap space as I wanted. But to be honest I really don't want to store every row of every select statement in memory. I believe that the database is around 1.5Gb on disk almost all of it in the one table. The whole thought of having to keep the entire table in memory at once to do a backup is scary. I don't expect anyone at work to appreciate having to match the disk space of the database in order to do backups. I will either have to switch RDBM's, or unwrap the pg_dump to use the lower level primatives of parseInput, getAnotherTuple, or better yet implement it with the cursors at the query level and then I could get by until a better solution came along. I can deal with most everything with the exception of the backups. Any advice, or expectations of memory requirements would be appreciated. Sorry, if this is the wrong forum for this dicussion, but my original question started here. Thanks, Kirby On Wed, 20 Sep 2000, Thomas Lockhart wrote: > > I am trying to run a select statement, and I keep running out of > > memory. I have noticed that libpq has the nice feature that I don't have > > to request each row one at a time. After a little investigate, I found > > that libpq appears to get the entire result set at once. Well, at least > > PQgetvalue() doesn't appear to do anything besides return a pointer to a > > string. There is no lazy evaluation. It doesn't just fetch the row I > > need, and flush old ones as memory permits. > > Use a cursor at the query level to keep the result set in the backend. > But you will still (possibly) run out of memory, since the *backend* > must keep the result set in memory and/or on disk. Make sure you have > enough swap space, disk space, and main memory to handle the size of > database you have. > > > Is there any interface that I can link to a C/C++ program that > > will allow me row at a time access to the data? Not being able to run > > select statements is going to be a fairly sizeable problem for me. > > Look at cursors, but that doesn't eliminate the need for enough memory. > > Expect to buy more memory, and make sure you have enough swap space. > Send more details on what you have currently and what your database > looks like and we can give you better feedback on your resource > requirements. > > - Thomas >
В списке pgsql-interfaces по дате отправления: