Re: Problem using COPY command to load data

On 11/11/08 2:25 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Glen Beane <Glen(dot)Beane(at)jax(dot)org> writes:
>> I am using the copy_from command from the python psycopg2 library to do some
>> bulk data loading of a postgres database. This had been working OK, until
>> my script barfed because I was being careless, and it seemed to leave the
>> database in a strange state that I can't recover from.
>
> What PG version is this exactly?
8.3.3

> What does pg_stat_activity show?

This is the only non-idle connection:

16498 | mgi_biomart_intermediate | 31356 | 16386 | biomart | COPY
markers FROM stdin USING DELIMITERS ' ' | f | 2008-11-11
17:10:36.124919-05 | 2008-11-11 17:10:36.125522-05 | 2008-11-11
17:10:32.213159-05 | 127.0.1.1 | 35021

> Is
> there any indication of un-granted locks in pg_locks?

gbeane=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted
---------------+----------+----------+------+-------+------------+----------
-----+---------+-------+----------+--------------------+-------+------------
------+---------
virtualxid | | | | | 5/3 |
| | | | 5/3 | 31361 | ExclusiveLock
| t
relation | 16385 | 10969 | | | |
| | | | 5/3 | 31361 | AccessShareLock
| t
relation | 16498 | 16507 | | | |
| | | | 3/1151 | 31356 | RowExclusiveLock
| t
virtualxid | | | | | 3/1151 |
| | | | 3/1151 | 31356 | ExclusiveLock
| t
transactionid | | | | | |
637 | | | | 3/1151 | 31356 |
ExclusiveLock | t
relation | 16498 | 16510 | | | |
| | | | 3/1151 | 31356 | RowExclusiveLock
| t

> The most direct evidence about why it's stuck would probably be had by
> attaching to the backend process with gdb and getting a stack trace.
>

I'll take a look with gdb when I have a moment.

> regards, tom lane

--
Glen L. Beane
Software Engineer
The Jackson Laboratory
Phone (207) 288-6153