Page MenuHomeSoftware Heritage
Paste P1449

queries running on belvedere
ActivePublic
Actions

Authored by vsellier on Sep 13 2022, 10:31 AM.
```
PID DATABASE APP USER CLIENT CPU% MEM% READ/s WRITE/s TIME+ Waiting IOW state Query
1440708 barman_receive_w barman_streaming 192.168.100.18/3 0.8 0.0 1.35M 0B 641 h WalSenderMain N active START_REPLICATION SLOT "barman" 2859A/46000000 TIMELINE 1
2677979 softwareheritage softwareheritage postgres 192.168.100.103/ 0.8 0.0 1.44M 0B 49 h WalSenderWaitFor N active START_REPLICATION SLOT "softwareheritage_somerset" LOGICAL 28953/F6811D28 (proto_version '1', publication_names '"softwareheritage"')
774624 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 598:53.50 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x5eb8379aad4ba330e903f5e63b71da873121f04e'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774641 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 403:40.93 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x0512381eebfee6ed82fe9b2dd5781771064b2e66'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774907 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 386:02.37 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x9d14a1d3753038070a9436343a37f315f39d4841'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774631 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 375:57.06 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\xc72f7daf1d792eb09d883bc4c3c7909bd78bd4e7'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774908 softwareheritage guest 127.0.0.1/32 0.0 0.1 0B 0B 169:01.46 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\xdf4230e458463eb9b62b345b5196e6396d782038'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774642 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 140:03.32 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\xad1c0f44e8293a83d7d72d34df2f029a7741199c'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775096 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 138:53.68 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x0f02bf88dc66029bfbaa5211c5bfd2a9d798db0e'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774225 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 136:26.26 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x69d9c30b04b9be4e729ec79f13909b3b9477117a'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774740 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 51:47.19 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x1ae888a8540a4884442ef127aa6eca81fa9e7efc'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774231 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 41:30.04 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x8d9529ca6ee2aaf6836811f99d5d8c967d037a37'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775097 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 36:28.08 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\xe725bb93007219139bb83c3e63b964d8f0feedc4'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775099 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 34:38.92 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x9c786fdb38cfe14030a5452a82be1bc02281ac62'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775221 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 34:11.63 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x7a6dcd56fa2c44e2b98613c072e92d061a19864a'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775098 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 31:04.57 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x7a07c630771222dfca44b8a70c5b8ea8d5167e20'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774445 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 29:41.62 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x89f4ff9121e68bac92d528f209490a86e18a7eb5'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774215 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 25:36.55 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x7ad280925aa7ad96c920000d99119e6fc53a73b7'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774449 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 22:22.22 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x2a493212c601f2ad8d1ac2f4e0293064eb766a61'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774217 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 18:37.76 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x5ddbec2b2f477630893d3ffbd4c5cdea24b03b4f'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774906 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 17:59.31 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x3e1273134e22d64304808fd437e62f1366400736'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774726 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 16:50.39 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x6638b50f9c763f3a9b2c2b37527057d6b672f18c'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775106 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 16:47.24 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x7b84a664cc0286c6ca9a8958a5a885d91d385c98'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
775217 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 16:35.39 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x7863ec6f8d281accebb1091216c28d1d282405a5'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774955 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 16:16.36 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x4fb4952332215fb4fd2b19c5bab0d87e64539d03'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774219 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 14:16.49 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\xafacfbb9595b96f1d0a9581bbd12a132cb7d8d91'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
774954 softwareheritage guest 127.0.0.1/32 0.0 0.0 0B 0B 13:01.03 ClientRead N idle in trans WITH dir AS (SELECT dir_entries, file_entries FROM directory WHERE id='\x369089926b8ace630bc7bc1b1c0629bbb24ddade'::bytea), ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir), ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir) (SELECT 'dir' AS type, e.target, e.name FROM ls_d LEFT JOIN
directory_entry_dir e ON ls_d.entry_id=e.id) UNION ALL (SELECT 'file' AS type, e.target, e.name FROM ls_f LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)
....
```
Before that there were a lot of queries like
```
select id, unnest(parents) as parent_id from
swh_revision_list(ARRAY['\xa0ea1643b13fd38fa5ca79a45f955870d06abeb4'::bytea::bytea], 1000);
```
but they were killed when the provenance client were stopped on met

Event Timeline

vsellier created this paste.Sep 13 2022, 10:31 AM
vsellier added a comment.Sep 13 2022, 10:36 AM
postgres=# select count(*) from pg_stat_activity where query like '%UNNEST(%';
 count 
-------
    64
(1 row)
vsellier added a comment.Sep 13 2022, 10:41 AM
postgres=# select count(*) from pg_stat_activity where query like '%UNNEST(%';
 count 
-------
    64
(1 row)

postgres=# select pid, query from pg_stat_activity where query like '%UNNEST(%' limit 1;
  pid   |                                                    query                                                    
--------+-------------------------------------------------------------------------------------------------------------
 774191 |                                                                                                            +
        |                     WITH                                                                                   +
        |                     dir AS (SELECT dir_entries, file_entries                                               +
        |                               FROM directory WHERE id='\x876eba4d69d7f54587b55b0f66a6e0cc3fdab1d3'::bytea),+
        |                     ls_d AS (SELECT DISTINCT UNNEST(dir_entries) AS entry_id FROM dir),                    +
        |                     ls_f AS (SELECT DISTINCT UNNEST(file_entries) AS entry_id FROM dir)                    +
        |                     (SELECT 'dir' AS type, e.target, e.name                                                +
        |                        FROM ls_d                                                                           +
        |                        LEFT JOIN directory_entry_dir e ON ls_d.entry_id=e.id)                              +
        |                     UNION ALL                                                                              +
        |                     (SELECT 'file' AS type, e.target, e.name                                               +
        |                        FROM ls_f                                                                           +
        |                        LEFT JOIN directory_entry_file e ON ls_f.entry_id=e.id)                             +
        |                     
(1 row)

postgres=# select pg_terminate_backend(774191);
 pg_terminate_backend 
----------------------
 t
(1 row)

postgres=# select count(*) from pg_stat_activity where query like '%UNNEST(%' and pid <> pg_backend_pid();
 count 
-------
    62
(1 row)

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where query like '%UNNEST(%' and pid <> pg_backend_pid();
...
62 rows
About · Developer information · Legal