Re: Dump/Restore of non-default PKs

On Mon, Apr 18, 2022 at 1:00 PM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
wrote:

> At the moment you cannot create a unique index other than a btree. (As
> discussed on other threads, I am pursuing unique hash indexes for
> PostgreSQL, one step at a time).
> You get "ERROR index foo_idx is not a btree"
>
> According to parse_utilcmd.c line 2310, this is because it would break
> pg_dump, which needs ADD CONSTRAINT to create the same kind of index
> again. Fair enough.
>
> This is needed because ADD CONSTRAINT just uses the defaults index
> type. We could simply allow a GUC for
> default_primary_key_access_method, but that is overkill and there
> seems to be an easy and more general solution:
>
> 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.
>
> Any objections to a patch to implement this thought?
>

Why not just get rid of the limitation that constraint definitions don't
support non-default methods?

I.e., add syntax to index_parameters so that the underlying index can be
defined directly.

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

We should add:

[ USING INDEX METHOD index_method ]

index_method := { BTREE | GIN | GIST | HASH | SPGIST | BRIN }

David J.