Why is psql \copy process stuck?

From: Jack Orenstein <jao(at)geophile(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why is psql \copy process stuck?
Date: 2005-05-14 16:14:02
Message-ID: 428623CA.2070800@geophile.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have two postgres 7.4.6 databases, running on different hosts (Linux
2.6.11 kernel), and I'm copying data from one to the other as follows:

psql -h 10.3.1.154 mydb -U username -c "\copy sometable to stdout" | \
psql mydb -U username -c "\copy sometable from stdin"

Such copies are pretty frequent, and have always worked well.

Today, the processes doing the copy have been stuck for a few
hours. ps axw says

21167 ? S 0:00 /usr/local/pgsql/bin/psql -h 10.3.1.154 mydb -U username -c \copy
sometable to stdout
21168 ? S 0:00 /usr/local/pgsql/bin/psql mydb -U username -c \copy sometable from stdin
21169 ? S< 0:00 postgres: risdba ris [local] COPY

The amount of data is small, about 13,000 rows, an average of 150-200
bytes each.

pg_locks shows this on the local host:

table | transaction | pid | mode | granted
---------------+-------------+-------+---------------------+---------
...
sometable | | 21169 | RowExclusiveLock | t
...

(I joined pg_locks with other catalog tables to get the table name.)

One other pid is waiting to get an AccessExclusiveLock on sometable,
but that's it. pg_locks doesn't have any other entries with granted =
f.

On the remote host, I see no sign of a COPY going on in pg_locks, (no lock
of any kind for sometable).

So it looks like the copy from stdin completed, as far as the remote
db is concerned, but that the process reading from the remote db,
21167, doesn't realize this, and the copy from process (21168) is
simply waiting for more data.

The postgres logs don't indicate any trouble on either host around the
time that the copy starts, or for the next few minutes.

Why is process 21167 stuck?

Jack Orenstein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-05-14 16:40:38 Re: Why is psql \copy process stuck?
Previous Message Richard Huxton 2005-05-14 08:17:29 Re: Changing column data type on an existing table