Thread: Is there a way to tell how far along a COPY is in the process?

On Nov 5, 2007, at 1:32 PM, Keaton Adams wrote:

>
> I’m looking for a way to see how many rows have been processed
> while a COPY is actually running.  I can’t seem to find a pg_stat
> table/view that will give me this level of visibility into the
> process.
>
> Is there any way to do this, to tell the number of rows processed
> during a COPY into a table while the COPY is still running?

No, because changes made by any given transaction are not visible to
other transactions until that transaction commits, which would be
when the COPY completes if it is the only statement in the
transaction.  What you could do is split the file whose data you're
COPYing in into smaller pieces and run separate, sequential COPYs if
you really need something along those lines.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Keaton Adams wrote:
>
> I’m looking for a way to see how many rows have been processed while a
> COPY is actually running.  I can’t seem to find a pg_stat table/view
> that will give me this level of visibility into the process.
>
> Is there any way to do this, to tell the number of rows processed during
> a COPY into a table while the COPY is still running?
>
> Thanks,
>
> Keaton

I use this little perl function:

sub runscript($)
{
    my $fname = pop;
    open(F, $fname) or die;
    print "executing $fname\n";
    my $sql = <F>;
    $db->do($sql) or die 'cant start copy';
    my $c = 0;
    while (<F>)
    {
        $db->pg_putline($_);
        if ($c % 10_000 == 0) {
            print "$c\r";
            if ($stop) { die; }
        }
        $c++;
    }
    print "$c total\n";
    $db->pg_endcopy;
    unlink($fname);
}


The first line in the file needs to be the sql copy command, like:

print F "copy junk(id, name, address) from stdin;\n";

The following lines are the data, like:

print F "$id\t$name\t$add\n";


-Andy