PostgreSQL Administration - Copy command not writing complete data to text file

Author

Copy command not writing complete data to text file

Kevin Keith

2005-09-22, 9:24 am

I am having some problems with the COPY... to FILE command - and I am
wondering if anyone has experienced similar problems in the past, and what
you may have done to resolve the problem?

The platform is Free BSD, Postgres version 7.4.5 and the program triggering
the COPY command is a CGI script.

I am copying out to a text files using PQexec - there are no more than 500
records in the tables being copied. What is happening, is one instance of
the COPY command is always stopping after dumping 16,384 bytes (16K) to the
text file - the last record is always truncated (i.e. the if the table has
20 columns, the last record may only have 7 columns in the flat file). There
is also no error returned in the program - to the program calling the copy
command everything gives an appearance of working.

What makes this problem difficult to resolve is that if I run the exact same
COPY command from the PSQL prompt, everything works with no problems.

Disk space is not an issue - there is sufficient memory to store the text
files many times over.

Has anyone else experienced similar issues, or can you point me to something
that may be causing this behavior to occur?

Thanks,

Kevin

____________________
____________________
____________________
_____
FREE pop-up blocking with the new MSN Toolbar – get it now!
http://toolbar.msn.click-url.com/go...5ave/direct/01/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr

2005-09-22, 11:24 am

On Thu, Sep 22, 2005 at 08:27:00AM -0500, Kevin Keith wrote:
> The platform is Free BSD, Postgres version 7.4.5 and the program triggering
> the COPY command is a CGI script.

What language and API are you using? In the next paragraph you
mention PQexec(), which implies C or C++ and libpq (some people
argue that a C/C++ program isn't a "script," so that word could be
misleading).

> I am copying out to a text files using PQexec - there are no more than 500
> records in the tables being copied. What is happening, is one instance of
> the COPY command is always stopping after dumping 16,384 bytes (16K) to the
> text file - the last record is always truncated (i.e. the if the table has
> 20 columns, the last record may only have 7 columns in the flat file).
> There is also no error returned in the program - to the program calling the
> copy command everything gives an appearance of working.

Could you post the code that does the COPY and error checking?
Better yet, a short but complete program that exhibits the problem.

> What makes this problem difficult to resolve is that if I run the exact
> same COPY command from the PSQL prompt, everything works with no problems.

Does the CGI program behave differently if you run it from the
command line instead of through the web server?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Kevin Keith

2005-09-22, 1:23 pm

It is a CGI (shell) script that is calling a C program. I found the issue -
which was external to Postgres.

I still have one remaining question - if the COPY to FILE command fails,
does it return an error? If the program uses:
int i;
i = PQexec("copy from <<table_name>> to << fully_qualified_file
_name>>");

and the table has (for example) 500 records, but only 350 were copied to the
file, what is returned in i? Or is the only method to check that all the
data was exported correctly to wc -l the file and select count(*) to check
against what was supposed to be exported?

Thanks,

Kevin

>From: Michael Fuhr <mike@fuhr.org>
>To: Kevin Keith <kevinrkeith@hotmail.com>
>CC: pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] Copy command not writing complete data to text file
>Date: Thu, 22 Sep 2005 08:52:36 -0600
>
>On Thu, Sep 22, 2005 at 08:27:00AM -0500, Kevin Keith wrote:
>triggering
>
>What language and API are you using? In the next paragraph you
>mention PQexec(), which implies C or C++ and libpq (some people
>argue that a C/C++ program isn't a "script," so that word could be
>misleading).
>
>500
>of
>the
>has
>the
>
>Could you post the code that does the COPY and error checking?
>Better yet, a short but complete program that exhibits the problem.
>
>problems.
>
>Does the CGI program behave differently if you run it from the
>command line instead of through the web server?
>
>--
>Michael Fuhr
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly

____________________
____________________
____________________
_____
On the road to retirement? Check out MSN Life Events for advice on how to
get there! http://lifeevents.msn.com/category.aspx?cid=Retirement

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr

2005-09-22, 8:24 pm

On Thu, Sep 22, 2005 at 11:49:24AM -0500, Kevin Keith wrote:
> It is a CGI (shell) script that is calling a C program. I found the issue -
> which was external to Postgres.

So what was it? Even if it wasn't a PostgreSQL problem it might
be educational to learn what happened.

> I still have one remaining question - if the COPY to FILE command fails,
> does it return an error? If the program uses:
> int i;
> i = PQexec("copy from <<table_name>> to << fully_qualified_file
_name>>");
>
> and the table has (for example) 500 records, but only 350 were copied to
> the file, what is returned in i?

PQexec() returns a PGresult *, not an int. You can pass that PGresult *
to PQresultStatus() to find out the command's status; if the command
failed you can pass the PGresult * to PQresultErrorMessage
() to get
a descriptive error message. See the libpq documentation for more
information.

On success, COPY returns a result status of PGRES_COMMAND_OK; I'd
expect it to return something else if it failed. For example, if
the disk is full then COPY fails with PGRES_FATAL_ERROR. If you
have a case where COPY reports success but writes an incomplete
file then please post the code -- that way we can see if the bug
is in your code or in PostgreSQL.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links