Обсуждение: COPY seems to work, but no data in the table

I executed a successful COPY, or at least psql responded "COPY", but
when I looked at the table with SELECT *, no rows were found.

What's going on there?

On Mon, Jul 15, 2002 at 07:36:58PM -0700, Brian Dougherty wrote:
> I executed a successful COPY, or at least psql responded "COPY", but
> when I looked at the table with SELECT *, no rows were found.
>
> What's going on there?

Can you give us a full example? For instance, the definition of the
table, the command you're using to COPY in data, and a sample of
the source data.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

In article <20020716162506.GB27614@klamath.dyndns.org>, Neil Conway wrote:
> On Mon, Jul 15, 2002 at 07:36:58PM -0700, Brian Dougherty wrote:
>> I executed a successful COPY, or at least psql responded "COPY", but
>> when I looked at the table with SELECT *, no rows were found.
>>
>> What's going on there?
>
> Can you give us a full example? For instance, the definition of the
> table, the command you're using to COPY in data, and a sample of
> the source data.
>


I have run in to the exact same problem (check in the archives...
"missing foreign key fails silently using COPY")

oh. Here is is:

http://archives.postgresql.org/pgsql-bugs/2002-02/msg00173.php

(you can view source if the page appears blank...)

What I found was that if a foreign key constraint was not satisfied
while COPYing data in to a table, the whole COPY would fail, but
there was never any error message.

As I recall it, Tom Lane looked at this problem at the time and said
that it would be less than simple to fix.

Lee Harr <missive@frontiernet.net> writes:
> oh. Here is is:
> http://archives.postgresql.org/pgsql-bugs/2002-02/msg00173.php

> What I found was that if a foreign key constraint was not satisfied
> while COPYing data in to a table, the whole COPY would fail, but
> there was never any error message.

> As I recall it, Tom Lane looked at this problem at the time and said
> that it would be less than simple to fix.

The example I cited in my followup works in CVS tip ... or at least
it reports a failure message:

regression=# create table foo(f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE TABLE
regression=# create table bar(f2 int references foo);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
regression=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> \.
ERROR:  $1 referential integrity violation - key referenced from bar not found in foo
lost synchronization with server, resetting connection
regression=#

I believe it works the same way in 7.2.1, but don't have an
exactly-7.2.1-release code tree handy to check.  (This is a post-7.2
bug fix though, 7.2 will fail :-()

            regards, tom lane

Tom Lane wrote:
 > ERROR:  $1 referential integrity violation - key referenced from bar
 > not found in foo
 > lost synchronization with server, resetting connection
 > regression=#
 >
 > I believe it works the same way in 7.2.1, but don't have an
 > exactly-7.2.1-release code tree handy to check.  (This is a post-7.2
 > bug fix though, 7.2 will fail :-()

I do:

test=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

test=# create table foo(f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
test=# create table bar(f2 int references foo);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
 >> 1
 >> \.
ERROR:  <unnamed> referential integrity violation - key referenced from
bar not found in foo
lost synchronization with server, resetting connection

Same result.

Joe