Re: COPY FROM STDIN hang

> On Mar 23, 2021, at 3:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Robert Creager <robert(at)logicalchaos(dot)org> writes:
>> On Mar 23, 2021, at 3:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> You mean the backend hangs? Can you get a stack trace from that?
>
>> The entire backend doesn’t hang, we’re able to process queries on other connections. It’s just the COPY FROM thread that’s hung.
>
> Hmm, that set of traces doesn't really look like it's hung,
> it might just be taking a long time.

13 hours is a long time.

>
> A different theory, which is hard to prove without debug symbols
> to provide more detail in the trace, is that it might be looping
> due to following a closed loop of btree page right-links. Which
> shouldn't happen normally, but perhaps this index is corrupt.
> Can you try reindexing the indexes of the COPY's target table?

The REINDEX hangs on locks, I’d have to kill the COPY and reproduce it to get it running, wouldn’t I.
SELECT pid,
client_port,
now() - query_start AS "runtime",
query_start,
datname,
state,
wait_event_type,
query,
usename
FROM pg_stat_activity
WHERE query !~ 'pg_stat_activity' AND
state != 'idle'
ORDER BY state, runtime DESC;

pid client_port runtime query_start datname state wait_event_type query usename
12976 14322 0 years 0 mons 0 days 14 hours 47 mins 1.687744 secs 2021-03-23 07:02:22.892034 tapesystem active NULL COPY ds3.s3_object_property (id, key, object_id, value) FROM STDIN WITH DELIMITER AS '|' Administrator
48638 32868 0 years 0 mons 0 days 0 hours 6 mins 23.497398 secs 2021-03-23 21:43:01.082380 tapesystem active Lock REINDEX TABLE ds3.s3_object_property Administrator
WITH RECURSIVE
c(requested, CURRENT) AS (VALUES ('AccessShareLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('RowShareLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('RowShareLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('RowExclusiveLock' :: TEXT, 'ShareLock' :: TEXT),
('RowExclusiveLock' :: TEXT, 'ShareRowExclusiveLock' :: TEXT),
('RowExclusiveLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('RowExclusiveLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('ShareUpdateExclusiveLock' :: TEXT, 'ShareUpdateExclusiveLock' :: TEXT),
('ShareUpdateExclusiveLock' :: TEXT, 'ShareLock' :: TEXT),
('ShareUpdateExclusiveLock' :: TEXT, 'ShareRowExclusiveLock' :: TEXT),
('ShareUpdateExclusiveLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('ShareUpdateExclusiveLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('ShareLock' :: TEXT, 'RowExclusiveLock' :: TEXT),
('ShareLock' :: TEXT, 'ShareUpdateExclusiveLock' :: TEXT),
('ShareLock' :: TEXT, 'ShareRowExclusiveLock' :: TEXT),
('ShareLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('ShareLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('ShareRowExclusiveLock' :: TEXT, 'RowExclusiveLock' :: TEXT),
('ShareRowExclusiveLock' :: TEXT, 'ShareUpdateExclusiveLock' :: TEXT),
('ShareRowExclusiveLock' :: TEXT, 'ShareLock' :: TEXT),
('ShareRowExclusiveLock' :: TEXT, 'ShareRowExclusiveLock' :: TEXT),
('ShareRowExclusiveLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('ShareRowExclusiveLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'RowShareLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'RowExclusiveLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'ShareUpdateExclusiveLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'ShareLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'ShareRowExclusiveLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('ExclusiveLock' :: TEXT, 'AccessExclusiveLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'AccessShareLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'RowShareLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'RowExclusiveLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'ShareUpdateExclusiveLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'ShareLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'ShareRowExclusiveLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'ExclusiveLock' :: TEXT),
('AccessExclusiveLock' :: TEXT, 'AccessExclusiveLock' :: TEXT)),
l AS
(
SELECT (locktype, DATABASE, relation :: REGCLASS :: TEXT, page, tuple, virtualxid, transactionid, classid, objid, objsubid) AS target,
virtualtransaction,
pid,
mode,
GRANTED
FROM pg_catalog.pg_locks
),
t AS
(
SELECT blocker.target AS blocker_target,
blocker.pid AS blocker_pid,
blocker.mode AS blocker_mode,
blocked.target AS target,
blocked.pid AS pid,
blocked.mode AS mode
FROM l blocker
JOIN l blocked
ON (NOT blocked.GRANTED
AND blocker.GRANTED
AND blocked.pid != blocker.pid
AND blocked.target IS NOT DISTINCT FROM blocker.target)
JOIN c ON (c.requested = blocked.mode AND c.CURRENT = blocker.mode)
),
r AS
(
SELECT blocker_target,
blocker_pid,
blocker_mode,
'1' :: INT AS depth,
target,
pid,
mode,
blocker_pid :: TEXT || ',' || pid :: TEXT AS seq
FROM t
UNION ALL
SELECT blocker.blocker_target,
blocker.blocker_pid,
blocker.blocker_mode,
blocker.depth + 1,
blocked.target,
blocked.pid,
blocked.mode,
blocker.seq || ',' || blocked.pid :: TEXT
FROM r blocker
JOIN t blocked
ON (blocked.blocker_pid = blocker.pid)
WHERE blocker.depth < 1000
)

blocker_target blocker_pid blocker_mode depth target pid mode seq
(relation,16954,ds3.s3_object_property,,,,,,,) 12976 RowExclusiveLock 1 (relation,16954,ds3.s3_object_property,,,,,,,) 48638 ShareLock 12976,48638
\