Re: [HACKERS] INSERT INTO ... SELECT eats all my memory

Do we have a problem here? Can someone explain it? Is it the
conversion of the types?

Hi,

I have a fairly big table (a tacacs log) of about 250,000 tuples.
I created a new log table with more rows and with different types (for example
some fields have changed from int4 to int8 or from varchar to inet).

I tryied to copy all the data from one table to the other using

INSERT INTO log SELECT list_of_fields FROM log2;

list_of_fields is an ordered list of the fields to import from log2 and default
values to insert into log (mostly nulls).

If I try to insert all the 250,000 tuples, postgres eats all my memory and
fails.
If I try to insert a subset (20,000 tuples), I saw the memory usage grow up to
18 MB and it succeded.

It looks like postgres tryies to put the result of the SELECT in memory before
starting to INSERT.

This makes INSERT almost unusable for bulk copying.

I found another problem... there's apparently no conversion function from
varchar to inet... how can I do the conversion ?

Here's the SQL statement:

insert into log select username, server, pop, remaddr, port, service, NULL,
privilege, authenmethod, authentype, authenservice, logtime, starttime,
elapsedtime, bytesin, bytesout, paksin, paksout, callerid, callednumber, NULL,
NULL, NULL, NULL, NULL, NULL from log2;

Tryied on 6.4.2 and 6.5beta1 on Linux 2.2.6

Bye!

--
Daniele

-------------------------------------------------------------------------------
Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026