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,
Pennsylvania19026