Thread: COPY blocking \dt+?
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
On Tue, 5 May 2020 at 13:05, pinker <pinker@onet.eu> wrote: > 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? COPY does not. However, pgbench does TRUNCATE the tables before doing the COPY, so that'll be most likely what's causing your query to get blocked. David
thank you David. So it would need to run inside single transaction to cause lock, right? do you know if pgbench is opening transaction? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Tue, 5 May 2020 at 14:50, pinker <pinker@onet.eu> wrote: > So it would need to run inside single transaction to cause lock, right? do > you know if pgbench is opening transaction? The TRUNCATE and COPY are done in the same transaction. You can see the code in https://github.com/postgres/postgres/blob/REL_11_6/src/bin/pgbench/pgbench.c#L3599 David