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 ]
