[osmosis-dev] There is no way to automatically disable all indexes for a table

[Martin Schafran]

hi,

relying upon automatic dropping I lost some time, because they are not dropped 
in postgresql.
public void disableIndexes(List<String> tables) {
		switch (loginCredentials.getDbType()) {
        case POSTGRESQL:
			// There is no way to automatically disable all indexes for a table.
			break;
        case MYSQL:



what about hard coding those commands at the appropriate place?

for apidb import:

--###############  NODES
--DROPPING
DROP INDEX nodes_changeset_id_idx;

DROP INDEX nodes_tile_idx;

DROP INDEX nodes_timestamp_idx;
  
ALTER TABLE nodes
  DROP CONSTRAINT nodes_changeset_id_fkey;
  
ALTER TABLE nodes
  DROP CONSTRAINT nodes_redaction_id_fkey;
  
  
--CREATION
ALTER TABLE nodes
  ADD CONSTRAINT nodes_changeset_id_fkey FOREIGN KEY (changeset_id)
      REFERENCES changesets (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
      
ALTER TABLE nodes
  ADD CONSTRAINT nodes_redaction_id_fkey FOREIGN KEY (redaction_id)
      REFERENCES redactions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
      
CREATE INDEX nodes_changeset_id_idx
  ON nodes
  USING btree
  (changeset_id);
  
CREATE INDEX nodes_tile_idx
  ON nodes
  USING btree
  (tile);


CREATE INDEX nodes_timestamp_idx
  ON nodes
  USING btree
  ("timestamp");
  
      
      
-- #############  NODE TAGS

ALTER TABLE node_tags
  DROP CONSTRAINT node_tags_id_fkey;
  
  
ALTER TABLE node_tags
  ADD CONSTRAINT node_tags_id_fkey FOREIGN KEY (node_id, version)
      REFERENCES nodes (node_id, version) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

  
  
--#################   WAYS

DROP INDEX ways_changeset_id_idx;



DROP INDEX ways_timestamp_idx;


ALTER TABLE ways
  DROP CONSTRAINT ways_changeset_id_fkey;

  ALTER TABLE ways
  DROP CONSTRAINT ways_redaction_id_fkey;

  
  ALTER TABLE ways
  ADD CONSTRAINT ways_changeset_id_fkey FOREIGN KEY (changeset_id)
      REFERENCES changesets (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE ways
  ADD CONSTRAINT ways_redaction_id_fkey FOREIGN KEY (redaction_id)
      REFERENCES redactions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

  CREATE INDEX ways_changeset_id_idx
  ON ways
  USING btree
  (changeset_id);
    
  CREATE INDEX ways_timestamp_idx
  ON ways
  USING btree
  ("timestamp");
    
      
--#################   WAY TAGS
ALTER TABLE way_tags
  DROP CONSTRAINT way_tags_id_fkey;
  
  
ALTER TABLE way_tags
  ADD CONSTRAINT way_tags_id_fkey FOREIGN KEY (way_id, version)
      REFERENCES ways (way_id, version) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

  
--#################   WAY NODES

ALTER TABLE way_nodes
  DROP CONSTRAINT way_nodes_id_fkey;
  
  
ALTER TABLE way_nodes
  ADD CONSTRAINT way_nodes_id_fkey FOREIGN KEY (way_id, version)
      REFERENCES ways (way_id, version) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;




--#################   RELATIONS
DROP INDEX relations_changeset_id_idx;


DROP INDEX relations_timestamp_idx;

 
  
  ALTER TABLE relations
  DROP CONSTRAINT relations_changeset_id_fkey;
ALTER TABLE relations
  DROP CONSTRAINT relations_redaction_id_fkey;
  
  
ALTER TABLE relations
  ADD CONSTRAINT relations_changeset_id_fkey FOREIGN KEY (changeset_id)
      REFERENCES changesets (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE relations
  ADD CONSTRAINT relations_redaction_id_fkey FOREIGN KEY (redaction_id)
      REFERENCES redactions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

  CREATE INDEX relations_changeset_id_idx
  ON relations
  USING btree
  (changeset_id);

CREATE INDEX relations_timestamp_idx
  ON relations
  USING btree
  ("timestamp");
 
      
--#################   RELATION TAGS
ALTER TABLE relation_tags
  DROP CONSTRAINT relation_tags_id_fkey;
  
  
ALTER TABLE relation_tags
  ADD CONSTRAINT relation_tags_id_fkey FOREIGN KEY (relation_id, version)
      REFERENCES relations (relation_id, version) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;


--#################   RELATION MEMBERS
  
DROP INDEX relation_members_member_idx;


ALTER TABLE relation_members
  DROP CONSTRAINT relation_members_id_fkey;
  
  
ALTER TABLE relation_members
  ADD CONSTRAINT relation_members_id_fkey FOREIGN KEY (relation_id, version)
      REFERENCES relations (relation_id, version) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

  
CREATE INDEX relation_members_member_idx
  ON relation_members
  USING btree
  (member_type, member_id);




martin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 490 bytes
Desc: This is a digitally signed message part.
URL: <http://lists.openstreetmap.org/pipermail/osmosis-dev/attachments/20130610/dbbe343a/attachment.pgp>