From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: andrewbille@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19086: pg_dump --data-only selects and do not uses index definitions for the dumped tables.
Date: Wed, 15 Oct 2025 19:36:20 -0400
Message-ID: <1334977.1760571380@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvrffhKALh3jQM6Kmar5nt=2xgHC_0ErhTFvM32b_JT-Cw@mail.gmail.com>
References: <19086-871ff11017d03dc5@postgresql.org>
<aO6mUj9H2WpEFylG@nathan>
<1000041.1760471415@sss.pgh.pa.us>
<aO7IoIEnQDbtFzrs@nathan>
<CAApHDvoU2O-_zKNL-toHwkDcMXCzzvgRUTnA3baEEhCB4vqMRw@mail.gmail.com>
<aO_KwnHm2AVn0E4m@nathan>
<CAApHDvrcM4G0==52jFoAvmxk6ZRjXKq7nkik=fqq6BH=dSJsYA@mail.gmail.com>
<1324003.1760565328@sss.pgh.pa.us>
<CAApHDvrffhKALh3jQM6Kmar5nt=2xgHC_0ErhTFvM32b_JT-Cw@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 16 Oct 2025 at 10:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I wonder how much win could be had by postponing those function calls
>> so that they only act on indexes we're going to dump. It might be
>> a net loss in the default dump-everything case, though.
> Just to make sure I understand correctly, that means run a query in
> dumpIndex() specifically just for the index being dumped to call
> pg_get_indexdef()?
We could do it like that, but it's probably better to keep the
single-query approach. A way that could work is
(1) Drop pg_get_indexdef and pg_get_constraintdef from getIndexes'
initial query.
(2) As we scan the results of that query, build new OID-list
strings listing just the index OIDs and constraint OIDs that
we require definition strings for.
(3) Still within getIndexes, run those queries and insert the
results into the arrays built in step 1.
On its face, this will be slower than doing it all in one query,
at least in the normal case where we need most or all indexes.
But what I'm hoping is that we need at most one of the two
function calls for any one index, depending on whether it's a
constraint or not. That could buy back enough to justify the
extra overhead, perhaps.
(Hmm ... but on the third hand, if we only need one of the
two strings, couldn't we mechanize that by wrapping the
pg_get_indexdef call in CASE WHEN c.contype IS DISTINCT FROM 'x'
?)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, nathandbossart@gmail.com, andrewbille@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19086: pg_dump --data-only selects and do not uses index definitions for the dumped tables.
In-Reply-To: <1334977.1760571380@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox