Can we get rid of repeated queries from pg_dump?

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема Can we get rid of repeated queries from pg_dump?
Дата
Msg-id 20210826084430.GA26282@depesz.com
обсуждение исходный текст
Ответы Re: Can we get rid of repeated queries from pg_dump?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Can we get rid of repeated queries from pg_dump?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Hi,
I have following case: local pg_dump (v15) connecting to remote
PostgreSQL (v12).

I'm trying to get just schema (pg_dump -s). It's taking very long, which
is kinda OK given that there is long distance and latency, but I got
curious and checked queries that the pg_dump was running (select * from
pg_stat_activity where application_name = 'pg_dump').

And I noticed that many of these queries repeat many times.

The ones that I noticed were:
SELECT pg_catalog.format_type('2281'::pg_catalog.oid, NULL)
around the time that
SELECT
    proretset,
    prosrc,
    probin,
    provolatile,
    proisstrict,
    prosecdef,
    lanname,
    proconfig,
    procost,
    prorows,
    pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
    pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
    pg_catalog.pg_get_function_result(p.oid) AS funcresult,
    proleakproof,
    array_to_string(protrftypes, ' ') AS protrftypes,
    proparallel,
    prokind,
    prosupport,
    NULL AS prosqlbody
FROM
    pg_catalog.pg_proc p,
    pg_catalog.pg_language l
WHERE
    p.oid = '60188'::pg_catalog.oid
    AND l.oid = p.prolang

was called too.

It seems that for every function, pg_dump is getting it's data, and then
runs format_type on each parameter/output type? I'm mostly guessing
here, as I didn't read the code.

Wouldn't it be possible to get all type formats at once, and cache them
in pg_dump? Or at the very least reuse already received information?

Unfortunately it seems I can't run pg_dump closer to the db server, and
the latency of queries is killing me.

It's been 15 minutes, and pg_dump (called: pg_dump -v -s -f schema.dump,
with env variables configuring db connection) hasn't written even single
byte to schema.dump)

depesz



В списке pgsql-general по дате отправления:

Предыдущее
От: Daniel Gustafsson
Дата:
Сообщение: Re: lower() and unaccent() not leakproof
Следующее
От: Tom Lane
Дата:
Сообщение: Re: lower() and unaccent() not leakproof