Re: COPY FROM STDIN hang - Mailing list pgsql-jdbc
| From | Robert Creager |
|---|---|
| Subject | Re: COPY FROM STDIN hang |
| Date | |
| Msg-id | 768F41BA-8396-4644-9391-F7EB23D04D6A@logicalchaos.org Whole thread Raw |
| In response to | Re: COPY FROM STDIN hang (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: COPY FROM STDIN hang
(Robert Creager <robert@logicalchaos.org>)
|
| List | pgsql-jdbc |
On Mar 23, 2021, at 3:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Robert Creager <robert@logicalchaos.org> writes:On Mar 23, 2021, at 3:16 PM, Tom Lane <tgl@sss.pgh.pa.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.
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 |
\
pgsql-jdbc by date: