Convert indexes to primary keys

[mzealey]

We are starting to use pglogical for cross-site replication, and it requires all tables to have a primary key.. In postgres a pk is identical to a unique key where all columns are NOT NULL, but declaratively more meaningful.

The following patch shows how this can be done using the most basic pg schema. I know there are various other pg schema iterations elsewhere in the code-base (both the new version, and the new ORM schema versioning) but implementing on those is left as an exercise for the reader.

As most of these PK's are replacing existing non-unique btree indexes there is minimal disk space/iops impact. Having a PK constraint in place likely makes the tables more efficient by enforcing a NOT NULL constraint on a number of columns which don't have this set at present (but should do, for example some of the sequence-based columns).

Equivalent in-place conversion can be actioned via:

alter table archive add primary key (id); 
alter table caps_features add primary key (node, subnode, feature); 
alter table pubsub_node_option add primary key (nodeid, name);           
alter table pubsub_node_owner add primary key (nodeid); 
alter table rostergroups add primary key (username, jid, grp); 
alter table spool add primary key (seq); 
alter table privacy_list_data add column seq bigserial primary key;

drop index if exists pk_rosterg_user_jid; 
drop index if exists i_pubsub_node_option_nodeid; 
drop index if exists i_pubsub_node_owner_nodeid; 
drop index if exists i_caps_features_node_subnode; 

[licaon-kter]

And pg.new.sql too?

[coveralls]

coverage: 32.642% (-0.01%) from 32.655% when pulling 5eace7823d3547d8fa75e85075dd81bb890eae1a on moya-app:mz/ensure-pks into 3ad30c3ff59633f695b59861d8e978ac3b22387b on processone:master.

[Neustradamus]

@mzealey: Can you look for pg.new.sql too?

There is a second PR too:

• https://github.com/processone/ejabberd/pull/3860

[Neustradamus]

@mzealey: Have you seen my previous comment?

[Neustradamus]

@mzealey: Any progress on it?