From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Can we get rid of repeated queries from pg_dump? |
Date: | 2021-08-26 08:44:30 |
Message-ID: | 20210826084430.GA26282@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-08-26 14:00:01 | Re: lower() and unaccent() not leakproof |
Previous Message | Daniel Gustafsson | 2021-08-26 08:40:49 | Re: lower() and unaccent() not leakproof |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2021-08-26 08:48:14 | Re: list of acknowledgments for PG14 |
Previous Message | Peter Eisentraut | 2021-08-26 08:41:30 | list of acknowledgments for PG14 |