Thread: Re: Questions for the COPY expert

Dawn,
I'm not that much of an expert :-)
I am forwarding your message to the "pgsql-general"
mailing list, where the real experts can be found.
I'm sure some of those can provide enlightenment.
I suspect that the limitations you observe have more
to do with the performance of networks and operating
systems than of COPY itself.
Regards

--- Dawn Vu <dvu@satx.rr.com> wrote:
> Hi Jeff,
>
> Read your usingcopy.php, it looks like you have a
> lot of
> experience with it.  Wonder if you can answer the
> following
> questions for me:
>
> 1.  COPY [tablename] TO '/../datafile' seems to have
> a
>     limitation when datafile is bigger than 2G.  Is
> this a
>     postgresql limitation?  I can get around it but
> want to
>     know if there is a way to let the file grow as
> large as
>     OS filesize limit.  Any idea?
>
> 2.  I use COPY to transfer data from one machine to
> another
>     over the network using stdin and stdout without
> having
>     to write data to intermediate file.  Does COPY
> read and
>     load all data into memory at one time or does it
> do a
>     chunk at a time and then pipeline data over the
> network?
>     The reason why I ask this question is because I
> was
>     transferring 41M+ records and one hour into it,
> the process
>     got a calloc error.  What seems to be the
> limitation here?
>     What memory aspects do I need to examine?  More
>     DB buffers? Configuration setting(s)?
>
> -DV
>


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/

Jeff Eckermann <jeff_eckermann@yahoo.com> forwards:
> --- Dawn Vu <dvu@satx.rr.com> wrote:

>> 1.  COPY [tablename] TO '/../datafile' seems to have a
>> limitation when datafile is bigger than 2G.  Is
>> this a postgresql limitation?

There is no such limitation in the Postgres sources.  However, if
you've built on a platform where 64-bit file offset support is not
default, you may well find that such a problem exists in the compiled
code.  What is your platform, anyway?

>> 2.  I use COPY to transfer data from one machine to
>> another
>> over the network using stdin and stdout without
>> having
>> to write data to intermediate file.

Should work fine.

>> The reason why I ask this question is because I
>> was
>> transferring 41M+ records and one hour into it,
>> the process
>> got a calloc error.  What seems to be the
>> limitation here?

Can't tell with so little information.  What platform?  What Postgres
version?  What is the table schema?

            regards, tom lane