Re: Dump/Restore of non-default PKs

Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> writes:
> I propose that we change pg_dump so that when it creates a PK it does
> so in 2 commands:
> 1. CREATE [UNIQUE] INDEX iname ...
> 2. ALTER TABLE .. ADD PRIMARY KEY USING INDEX iname;

> Step
> (1) recreates the index, respecting its AM, even if that is not a btree
> (2) works and there is no problem with defaults

> Doing this as 2 steps instead of one doesn't add any more time because
> (2) is just a metadata-only change, not an index build.

I don't believe the claim that this adds no cost. Maybe it's negligible
in context, but you've provided no evidence of that. (Parallel restore,
where the ALTER would have to be a separate worker task, would probably
be the worst case here.)

Also, I assume your ambition would extend to supporting UNIQUE (but
non-PKEY) constraints, so that would have to be done this way too.

A potential advantage of doing things this way is that if we make
pg_dump treat the index and the constraint as fully independent
objects, that might allow some logic simplifications in pg_dump.
Right now I think there are various weirdnesses in there that
exist precisely because we don't want to dump them separately.

One concern is that this'd create a hard compatibility break for
loading dump output into servers that predate whenever we added
ADD PRIMARY KEY USING INDEX. However, it looks like that syntax
is accepted back to 9.1, so probably that's no issue in practice.
Maybe a bigger concern for people who want to port to other
RDBMSes?

regards, tom lane