Here is a patch to remote_worker.c which comes very near to dropping
and restoring indices on tables during the COPY_SET event.

The notion: Build a query (it's pretty big, but that cuts way down on
the C "string slinging") that calculates two things for each table:

1.  A query to drop each PK constraint/other index

2.  A query to restore each PK constraint/other index

Do the "drops" just before copying all the data, and then generate the
indices from scratch afterwards, and this should improve performance
of COPY_SET quite a bit.

It finds the queries, but I'm not yet sure why it doesn't work.

On "pgbench" samples, it gripes, upon recreating the primary key
constraint, that there already is one.

I'm seeing in logs that the PK _was_ just dropped, via the statement:
  "alter table public.accounts drop constraint accounts_pkey;

It is quite unclear why it then breaks :-(.

This is an eminently tempting change to get in; it would be very
helpful for those that have really big replication sets...

Index: remote_worker.c
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slon/remote_worker.c,v
retrieving revision 1.81
diff -c -u -r1.81 remote_worker.c
--- remote_worker.c	5 May 2005 16:03:16 -0000	1.81
+++ remote_worker.c	8 May 2005 21:45:37 -0000
@@ -2174,6 +2171,8 @@
 	char		conn_symname[64];
 	SlonDString query1;
 	SlonDString query2;
+	SlonDString query3;
+	SlonDString indexregenquery;
 	int			ntuples1;
 	int			ntuples2;
 	int			tupno1;
@@ -2267,6 +2266,8 @@
 	pro_dbconn = pro_conn->dbconn;
 	loc_dbconn = local_conn->dbconn;
 	dstring_init(&query1);
+	dstring_init(&query3);
+	dstring_init(&indexregenquery);
 	sprintf(seqbuf, INT64_FORMAT, event->ev_seqno);
 	
 
@@ -2728,6 +2729,61 @@
 		PQclear(res2);
 
 		/*
+		 * Drop indices from table on the local database
+		 */
+		slon_mkquery(&query1,
+			     "select 'alter table %s drop constraint ' || \"pg_catalog\".quote_ident(co.conname),  "
+			     " 'alter table %s add  ' || "
+			     " pg_get_constraintdef(co.oid)  "
+			     "from pg_class c, pg_constraint co, pg_namespace ns "
+			     "where  "
+			     " '%s' = \"pg_catalog\".quote_ident(ns.nspname) || '.' || \"pg_catalog\".quote_ident(c.relname) and "
+			     " co.connamespace = ns.oid and "
+			     " co.contype in ('p', 'u') and "
+			     " c.oid = co.conrelid and "
+			     " ns.oid = c.relnamespace "
+			     "UNION ALL  "
+			     "select 'drop index ' || \"pg_catalog\".quote_ident(ns.nspname) || '.' || \"pg_catalog\".quote_ident(ci.relname) || ';' , "
+			     " pg_get_indexdef(ci.oid) "
+			     "from  "
+			     " pg_class c, pg_namespace ns, pg_class ci, pg_index i "
+			     "where  "
+			     " '%s' = \"pg_catalog\".quote_ident(ns.nspname) || '.' || \"pg_catalog\".quote_ident(c.relname) and "
+			     " i.indrelid = c.oid and "
+			     " i.indexrelid = ci.oid and "
+			     "    not exists (select * from pg_constraint co where connamespace = ns.oid and "
+			     "		 conrelid = c.oid and contype in ('p', 'u') and co.conname = ci.relname);",
+
+			     tab_fqname, tab_fqname, tab_fqname, tab_fqname);
+		res2 = PQexec(loc_dbconn, dstring_data(&query1));
+		ntuples2 = PQntuples(res2);
+		slon_log(SLON_DEBUG2, "remoteWorkerThread_%d: Found %d indices for %s - %s\n",
+			 node->no_id, ntuples2, tab_fqname, dstring_data(&query1));
+
+		slon_mkquery(&indexregenquery, " ");
+
+		slon_log(SLON_DEBUG2, "remoteWorkerThread_%d: start dropping %d indices\n", node->no_id, ntuples2);
+		for (tupno2 = 0; tupno2 < ntuples2; tupno2++)
+		{
+			slon_mkquery(&query3,
+				     "%s;",
+				     PQgetvalue(res2, tupno2, 0));
+			if (query_execute(node, loc_dbconn, &query3) < 0)
+			{
+				slon_log(SLON_FATAL, "remoteWorkerThread_%d: drop index during copy failed: %d\n",
+					 node->no_id, dstring_data(&query3));
+				PQclear(res2);
+				slon_abort();
+			} else {
+				slon_log(SLON_DEBUG2, "remoteWorkerThread_%d: Dropped index %s\n",
+					 node->no_id, dstring_data(&query3));
+			}
+			
+			slon_appendquery(&indexregenquery,
+					 "%s;",
+					 PQgetvalue(res2, tupno2, 1));
+		}
+		/*
 		 * Begin a COPY from stdin for the table on the local DB
 		 */
 		slon_log(SLON_DEBUG4, "remoteWorkerThread_%d: "
@@ -3002,6 +3058,20 @@
 			 INT64_FORMAT " bytes copied for table %s\n",
 			 node->no_id, copysize, tab_fqname);
 
+		slon_log(SLON_DEBUG2, "now, regenerate indices...\n");
+		slon_log(SLON_DEBUG2, 
+			 "remoteWorkerThread_%d: "
+			 "regenerate indices: %s",
+			 node->no_id, dstring_data(&indexregenquery));
+		res2 = PQexec(loc_dbconn, dstring_data(&indexregenquery));
+		if (query_execute(node, loc_dbconn, &indexregenquery) < 0) 
+		{
+			PQclear(res2);
+			slon_disconnectdb(pro_conn);
+			dstring_free(&indexregenquery);
+			return -1;
+		}
+
 		/*
 		 * Analyze the table to update statistics
 		 */

-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)