Thread: Custom index access method for primary keys
Hi! I’m working on an index access method that I intend to be generic and to replace btree for my specific use case. I noticed though that it doesn’t seem to be possible to specify an access method when creating a new table and using PRIMARY KEY.
Same applies to altering a table to add a primary key using an existing index with a non-btree access method. That hits this check and errors: https://github.com/postgres/postgres/blob/4be6a74cfb1ef4c50740e4c2f97a3b478c16de73/src/backend/parser/parse_utilcmd.c#L2480
Wanted to ask if this an intentional limitation and if there is a reason why an index access method (if it supports uniqueness) would not be usable for a primary key?
Fabian Lindfors <fabian@flapplabs.se> writes:
> Hi! I’m working on an index access method that I intend to be
> generic and to replace btree for my specific use case. I noticed
> though that it doesn’t seem to be possible to specify an access
> method when creating a new table and using PRIMARY KEY.
There's some work going on right now to remove the core code's
assumptions that only btree can handle unique/pkey constraints
and sorting [1]. I'm not sure if it'll be entirely complete for
v18 but the goalposts have been moved quite far. I think you can
probably expect to be able to do
CREATE TABLE mytable ...;
CREATE UNIQUE INDEX myindex ON mytable USING myam (...);
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX myindex;
It doesn't look like we yet allow direct "USING myam" in
PRIMARY KEY, but sooner or later we might get to that.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/E72EAA49-354D-4C2E-8EB9-255197F55330%40enterprisedb.com