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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026