Change composite primary key column order, avoid exposing table without primary key

Question

Short of dropping the primary key index and re-creating it, how can I alter a composite primary key on a table?

I would love to be able to change the primary key column order and have it re-index and apply immediately in a single statement (so that there is no "downtime" for the primary key).

If it is not possible to do it in a single statement, what is the best way to do it in a single transaction such that the table is not exposed to access for any period of time without a primary key in place?

For example, take a table that has primary key as follows:

PRIMARY KEY (col1, col2, col3, col4)

I want to change the order to (but not add or remove columns):

PRIMARY KEY (col4, col1, col2, col3)

Answer 1

There is no way to do this in a single statement. You will need a transaction block to do this:

-- Create a new index for the new primary key.
CREATE UNIQUE INDEX new_index_name ON table_name (col4, col1, col2, col3);

-- Now enter a transaction block to replace the primary with the new one.
BEGIN;
ALTER TABLE table_name DROP CONSTRAINT pkey_name; -- old index is automatically dropped with the constraint
ALTER TABLE table_name ADD CONSTRAINT constr_name PRIMARY KEY USING INDEX new_index_name;
COMMIT;

There is some "downtime" while replacing the primary key but this is relatively small. The hard work is done in creating the new index but this can be done outside of the transaction block. Any problems are caught when building the index, so before the transaction block is entered and your "downtime" starts. When the index is valid, replacing the primary key is quick.