Index: src/upgrade-db-to-1.6-postgres.sql
===================================================================
--- src/upgrade-db-to-1.6-postgres.sql	(revision 2529)
+++ src/upgrade-db-to-1.6-postgres.sql	(revision 2530)
@@ -29,7 +29,7 @@
                                                                                                                                                              
 CREATE SEQUENCE xml_path_index_id_seq;
 CREATE TABLE xml_path_index (
-    nodeid INT8  default nextval('xml_path_index_id_seq'),
+        nodeid INT8  default nextval('xml_path_index_id_seq'),
         docid VARCHAR(250),     -- the document id
         path VARCHAR(1000),     -- precomputed path through tree
         nodedata VARCHAR(4000), -- the data for this node (e.g.,
@@ -38,8 +38,8 @@
                                   -- if it is a number
         parentnodeid INT8,      -- id of the parent of the node represented
                                 -- by this row
-   CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
-   CONSTRAINT xml_path_index_docid_fk
+        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
+        CONSTRAINT xml_path_index_docid_fk
                 FOREIGN KEY (docid) REFERENCES xml_documents
 );
  
@@ -70,10 +70,10 @@
         date_updated DATE,
         nodedatanumerical FLOAT8, -- the data for this node if
                                   -- if it is a number
-   CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
-   CONSTRAINT xml_nodes_revisions_root_fk
+        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
+        CONSTRAINT xml_nodes_revisions_root_fk
                 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
-   CONSTRAINT xml_nodes_revisions_parent_fk
+        CONSTRAINT xml_nodes_revisions_parent_fk
                 FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
 );
                                                                                                                                                              
@@ -95,12 +95,8 @@
  * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
  * of the documents and deleted documents
  */
-INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype,
-nodename, nodeprefix, nodedata, parentnodeid, rootnodeid,
-docid, date_created, date_updated, nodedatanumerical)
-SELECT * FROM xml_nodes WHERE rootnodeid NOT IN
-(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
 
+INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) SELECT n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents ON n.rootnodeid = xml_documents.rootnodeid WHERE xml_documents.rootnodeid IS NULL;
 
 /**
  * Create the key constraint in xml_revisions which points to 
@@ -114,12 +110,53 @@
  * Delete the records from xml_index table which point to old revisions in xml_index
  * This is possible for documents for which the indexing thread failed during UPDATE
  */
+DELETE FROM xml_index WHERE nodeid = xml_nodes_revisions.nodeid;
 
-DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes WHERE
-rootnodeid NOT IN (SELECT rootnodeid FROM xml_documents WHERE rootnodeid IS NOT NULL)); 
 
 /**
  * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 
  */
-DELETE FROM xml_nodes WHERE rootnodeid NOT IN
-(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
+
+// rename xml_nodes to xml_nodes_2
+alter table xml_nodes rename to xml_nodes_2;
+
+
+// Create a new xml_nodes table 
+CREATE TABLE xml_nodes (
+        nodeid INT8 default nextval('xml_nodes_id_seq'),
+                                        -- the unique node id (pk)
+        nodeindex INT8,         -- order of nodes within parent
+        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
+                                -- ELEMENT, ATTRIBUTE, TEXT)
+        nodename VARCHAR(250),  -- the name of an element or attribute
+        nodeprefix VARCHAR(50), -- the namespace prefix of the node
+        nodedata VARCHAR(4000), -- the data for this node (e.g.,
+                                -- for TEXT it is the content)
+        parentnodeid INT8,      -- index of the parent of this node
+        rootnodeid INT8,        -- index of the root node of this tree
+        docid VARCHAR(250),     -- index to the document id
+        date_created DATE,
+        date_updated DATE,
+        nodedatanumerical FLOAT8, -- the data for this node if
+                                  -- if it is a number
+   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
+   CONSTRAINT xml_nodes_root_fk
+                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
+   CONSTRAINT xml_nodes_parent_fk
+                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
+);
+
+
+// copy data from xml_nodes_2 to xml_nodes
+INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) select n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical from xml_nodes n LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid where r.rootnodeid is NULL;
+
+// create indexes
+CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
+CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
+CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
+
+// add constaints which were deleted while moving xml_nodes to xml_nodes_2
+ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
+ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
+
+
