COPY blocking \dt+? - Mailing list pgsql-general
| From | pinker |
|---|---|
| Subject | COPY blocking \dt+? |
| Date | |
| Msg-id | 1588640740846-0.post@n3.nabble.com Whole thread Raw |
| Responses |
Re: COPY blocking \dt+?
(David Rowley <dgrowleyml@gmail.com>)
|
| List | pgsql-general |
Hi,
I'm running standard pgbench and what's kinda strange copy pgbench_accounts
from stdin is blocking my other query which is \dt+.
Does copy hold any exclusive lock or there is something wrong with my
system?
i'm using pgbench=> SELECT version();
-[ RECORD 1 ]-------------------------------------------------------
version | PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit
-[ RECORD 1
]----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
datid | 27483
datname | pgbench
pid | 931408
usesysid | 14419
usename | gucio
application_name | pgbench
client_addr | 212.180.214.106
client_hostname |
client_port | 23041
backend_start | 2020-05-05 00:47:12.182801+00
xact_start | 2020-05-05 00:47:12.542264+00
query_start | 2020-05-05 00:53:26.402305+00
state_change | 2020-05-05 00:53:26.402305+00
wait_event_type |
wait_event |
state | active
backend_xid | 3919
backend_xmin | 3919
query | copy pgbench_accounts from stdin
backend_type | client backend
-[ RECORD 2
]----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
datid | 27483
datname | pgbench
pid | 932736
usesysid | 14419
usename | gucio
application_name | psql
client_addr | 212.180.214.106
client_hostname |
client_port | 8718
backend_start | 2020-05-05 00:48:10.031429+00
xact_start | 2020-05-05 00:56:34.324414+00
query_start | 2020-05-05 00:56:34.324414+00
state_change | 2020-05-05 00:56:34.324414+00
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 3919
query | SELECT n.nspname as "Schema",
+
| c.relname as "Name",
+
| CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN
'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table
' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",+
| pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
+
|
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
+
| pg_catalog.obj_description(c.oid, 'pg_class') as
"Description"
+
| FROM pg_catalog.pg_class c
+
| LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
+
| WHERE c.relkind IN ('r','p','')
+
| AND n.nspname <> 'pg_catalog'
+
| AND n.nspname <> 'information_schema'
+
| AND n.nspname !~ '^pg_toast'
+
| AND pg_catalog.pg_table_is_visible(c.oid)
+
| ORDER BY 1,2;
backend_type | client backend
and the output of locks query from wiki:
-[ RECORD 1
]-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
blocked_pid | 932736
blocked_user | gucio
blocking_pid | 931408
blocking_user | gucio
blocked_statement | SELECT n.nspname as "Schema",
+
| c.relname as "Name",
+
| CASE c.relkind WHEN 'r' THEN
'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN
'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",+
|
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
+
|
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
+
| pg_catalog.obj_description(c.oid,
'pg_class') as "Description"
+
| FROM pg_catalog.pg_class c
+
| LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+
| WHERE c.relkind IN ('r','p','')
+
| AND n.nspname <> 'pg_catalog'
+
| AND n.nspname <>
'information_schema'
+
| AND n.nspname !~ '^pg_toast'
+
| AND
pg_catalog.pg_table_is_visible(c.oid)
+
| ORDER BY 1,2;
current_statement_in_blocking_process | copy pgbench_accounts from stdin
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
pgsql-general by date: