Home CVS Mailinglists Documentation Download
[Slony1-general] Name Handling Patch
Christopher Browne cbbrowne
Tue Jun 7 18:01:10 PDT 2005 
Testing has shown some "breakage" with regards to version 7.3; there was
a too-clever function for doing quoting of table and sequence names
which only properly worked if it could use array functionality
introduced in 7.4.

The function slon_quote_input() has been revised to use a much simpler
parsing scheme.

There are several places where remote_worker.c should have been using
slon_quote_input instead of pg_catalog.quote_ident.

The attached patch addresses these changes.

I have tested it on PG 7.3.9 and PG 8.0 on Debian/Linux with good
success; will shortly be testing in some other environments.

The patch is attached so that others can test it as well before we put
it into CVS.
-------------- next part --------------
Index: src/backend/slony1_base.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_base.sql,v
retrieving revision 1.26
diff -c -u -r1.26 slony1_base.sql
--- src/backend/slony1_base.sql	4 May 2005 20:54:24 -0000	1.26
+++ src/backend/slony1_base.sql	7 Jun 2005 16:54:33 -0000
@@ -360,7 +360,7 @@
 	p_seqname	alias for $1;
 	v_seq_row	record;
 begin
-	for v_seq_row in execute ''select last_value from '' || p_seqname
+	for v_seq_row in execute ''select last_value from '' || @NAMESPACE at .slon_quote_input(p_seqname)
 	loop
 		return v_seq_row.last_value;
 	end loop;
Index: src/backend/slony1_funcs.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_funcs.sql,v
retrieving revision 1.63
diff -c -u -r1.63 slony1_funcs.sql
--- src/backend/slony1_funcs.sql	4 May 2005 20:54:24 -0000	1.63
+++ src/backend/slony1_funcs.sql	7 Jun 2005 16:54:34 -0000
@@ -289,52 +289,84 @@
 --	This function will be used to quote user input.
 -- ----------------------------------------------------------------------
 
-create or replace function @NAMESPACE at .slon_quote_input (text) returns text
-as '
-declare
-    p_tab_fqname alias for $1;
-    v_temp_fqname text default '''';
-    v_pre_quoted text[] default ''{}'';
-    v_pre_quote_counter smallint default 0;
-    v_count_fqname smallint default 0;
-    v_fqname_split text[];
-    v_quoted_fqname text default '''';
-begin
-    v_temp_fqname := p_tab_fqname;
-
-    LOOP
-	v_pre_quote_counter := v_pre_quote_counter + 1;
-	v_pre_quoted[v_pre_quote_counter] := 
-	    substring(v_temp_fqname from ''%#"\"%\"#"%'' for ''#'');
-	IF v_pre_quoted[v_pre_quote_counter] <> '''' THEN
-	    v_temp_fqname := replace(v_temp_fqname,
-	        v_pre_quoted[v_pre_quote_counter], ''@'' ||
-		v_pre_quote_counter);
-	ELSE
-	    EXIT;
-	END IF;
-    END LOOP;
-
-    v_fqname_split := string_to_array(v_temp_fqname , ''.'');
-    v_count_fqname := array_upper (v_fqname_split, 1);
-
-    FOR i in 1..v_count_fqname LOOP
-        IF substring(v_fqname_split[i],1,1) = ''@'' THEN
-            v_quoted_fqname := v_quoted_fqname || 
-		v_pre_quoted[substring (v_fqname_split[i] from 2)::int];
-        ELSE
-            v_quoted_fqname := v_quoted_fqname || ''"'' || 
-		v_fqname_split[i] || ''"'';
-        END IF;
-
-        IF i < v_count_fqname THEN
-            v_quoted_fqname := v_quoted_fqname || ''.'' ;
-        END IF;
-    END LOOP;
+--create or replace function @NAMESPACE at .slon_quote_input (text) returns text
+--as '
+--declare
+--    p_tab_fqname alias for $1;
+--    v_temp_fqname text default '''';
+--    v_pre_quoted text[] default ''{}'';
+--    v_pre_quote_counter smallint default 0;
+--    v_count_fqname smallint default 0;
+--    v_fqname_split text[];
+--    v_quoted_fqname text default '''';
+--begin
+--    v_temp_fqname := p_tab_fqname;
+
+--    LOOP
+--	v_pre_quote_counter := v_pre_quote_counter + 1;
+--	v_pre_quoted[v_pre_quote_counter] := 
+--	    substring(v_temp_fqname from ''%#"\"%\"#"%'' for ''#'');
+--	IF v_pre_quoted[v_pre_quote_counter] <> '''' THEN
+--	    v_temp_fqname := replace(v_temp_fqname,
+--	        v_pre_quoted[v_pre_quote_counter], ''@'' ||
+--		v_pre_quote_counter);
+--	ELSE
+--	    EXIT;
+--	END IF;
+--    END LOOP;
+
+--    v_fqname_split := string_to_array(v_temp_fqname , ''.'');
+--    v_count_fqname := array_upper (v_fqname_split, 1);
+
+--    FOR i in 1..v_count_fqname LOOP
+--        IF substring(v_fqname_split[i],1,1) = ''@'' THEN
+--            v_quoted_fqname := v_quoted_fqname || 
+--		v_pre_quoted[substring (v_fqname_split[i] from 2)::int];
+--        ELSE
+--            v_quoted_fqname := v_quoted_fqname || ''"'' || 
+--		v_fqname_split[i] || ''"'';
+--        END IF;
+
+--        IF i < v_count_fqname THEN
+--            v_quoted_fqname := v_quoted_fqname || ''.'' ;
+--        END IF;
+--    END LOOP;
 	
-    return v_quoted_fqname;
-end;
-' language plpgsql;
+--    return v_quoted_fqname;
+--end;
+--' language plpgsql;
+
+
+create or replace function @NAMESPACE at .slon_quote_input(text) returns text as '
+  declare
+     p_tab_fqname alias for $1;
+     v_nsp_name text;
+     v_tab_name text;
+     v_pq2 integer;
+begin
+  if (p_tab_fqname like ''"%"."%"'') then
+    v_pq2 := position (''"'' in substr(p_tab_fqname, 2));
+    v_nsp_name := substr(p_tab_fqname, 2, v_pq2 - 1);
+    v_tab_name := substr(p_tab_fqname, v_pq2 + 4, length(p_tab_fqname) - (v_pq2 + 4));
+  elsif (p_tab_fqname like ''"%".%'') then
+    v_pq2 := position (''"'' in substr(p_tab_fqname, 2));
+    v_nsp_name := substr(p_tab_fqname, 2, v_pq2 - 1);
+    v_tab_name := substr(p_tab_fqname, v_pq2 + 3, length(p_tab_fqname) - (v_pq2 + 2));
+  elsif (p_tab_fqname like ''%."%"'') then
+    v_pq2 := position (''.'' in substr(p_tab_fqname, 2));
+    v_nsp_name := substr(p_tab_fqname, 1, v_pq2);
+    v_tab_name := substr(p_tab_fqname, v_pq2 + 3, length(p_tab_fqname) - (v_pq2 + 3));
+  elsif (p_tab_fqname like ''%.%'') then
+    v_pq2 := position (''.'' in substr(p_tab_fqname, 2));
+    v_nsp_name := substr(p_tab_fqname, 1, v_pq2);
+    v_tab_name := substr(p_tab_fqname, v_pq2 + 2);
+  elsif (p_tab_fqname like ''"%"'') then
+    return p_tab_fqname;
+  else
+     return ''"'' || p_tab_fqname || ''"'';
+  end if;
+  return ''"'' || v_nsp_name || ''"."'' || v_tab_name || ''"'';
+end;' language plpgsql;
 
 comment on function @NAMESPACE at .slon_quote_input(text) is
   'quote all words that aren''t quoted yet';
Index: src/backend/slony1_funcs.v73.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_funcs.v73.sql,v
retrieving revision 1.6
diff -c -u -r1.6 slony1_funcs.v73.sql
--- src/backend/slony1_funcs.v73.sql	6 Oct 2004 17:38:50 -0000	1.6
+++ src/backend/slony1_funcs.v73.sql	7 Jun 2005 16:54:34 -0000
@@ -21,9 +21,8 @@
 declare
 	p_tab_fqname		alias for $1;
 begin
-	execute ''delete from only '' || p_tab_fqname;
+	execute ''delete from only '' || @NAMESPACE at .slon_quote_input(p_tab_fqname);
 	return 1;
 end;
 ' language plpgsql;
 
-
Index: src/slon/remote_worker.c
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slon/remote_worker.c,v
retrieving revision 1.85
diff -c -u -r1.85 remote_worker.c
--- src/slon/remote_worker.c	25 May 2005 16:10:41 -0000	1.85
+++ src/slon/remote_worker.c	7 Jun 2005 16:54:36 -0000
@@ -757,8 +757,8 @@
 				rtcfg_dropSet(set_id);
 
 				slon_appendquery(&query1,
-								 "select %s.dropSet_int(%d); ",
-								 rtcfg_namespace, set_id);
+						 "select %s.dropSet_int(%d); ",
+						 rtcfg_namespace, set_id);
 
 				/* The table deleted needs to be
 				 * dropped from log shipping too */
@@ -2383,8 +2383,8 @@
 	 */
 	slon_mkquery(&query1,
 		     "select T.tab_id, "
-		     "    \"pg_catalog\".quote_ident(PGN.nspname) || '.' || "
-		     "    \"pg_catalog\".quote_ident(PGC.relname) as tab_fqname, "
+		     "    %s.slon_quote_input('\"' || PGN.nspname || '\".\"' || "
+		     "    PGC.relname || '\"') as tab_fqname, "
 		     "    T.tab_idxname, T.tab_comment "
 		     "from %s.sl_table T, "
 		     "    \"pg_catalog\".pg_class PGC, "
@@ -2393,7 +2393,9 @@
 		     "    and T.tab_reloid = PGC.oid "
 		     "    and PGC.relnamespace = PGN.oid "
 		     "order by tab_id; ",
-		     rtcfg_namespace, set_id);
+		     rtcfg_namespace, 
+		     rtcfg_namespace, 
+		     set_id);
 	res1 = PQexec(pro_dbconn, dstring_data(&query1));
 	if (PQresultStatus(res1) != PGRES_TUPLES_OK)
 	{
@@ -2491,8 +2493,8 @@
 	 */
 	slon_mkquery(&query1,
 		     "select SQ.seq_id, "
-		     "		\"pg_catalog\".quote_ident(PGN.nspname) || '.' || "
-		     "		\"pg_catalog\".quote_ident(PGC.relname), "
+		     "    %s.slon_quote_input('\"' || PGN.nspname || '\".\"' || "
+		     "    PGC.relname || '\"') as tab_fqname, "
 		     "		SQ.seq_comment "
 		     "	from %s.sl_sequence SQ, "
 		     "		\"pg_catalog\".pg_class PGC, "
@@ -2500,7 +2502,9 @@
 		     "	where SQ.seq_set = %d "
 		     "		and PGC.oid = SQ.seq_reloid "
 		     "		and PGN.oid = PGC.relnamespace; ",
-		     rtcfg_namespace, set_id);
+		     rtcfg_namespace, 
+		     rtcfg_namespace, 
+		     set_id);
 	res1 = PQexec(pro_dbconn, dstring_data(&query1));
 	if (PQresultStatus(res1) != PGRES_TUPLES_OK)
 	{
@@ -2545,8 +2549,8 @@
 	 */
 	slon_mkquery(&query1,
 		     "select T.tab_id, "
-		     "    \"pg_catalog\".quote_ident(PGN.nspname) || '.' || "
-		     "    \"pg_catalog\".quote_ident(PGC.relname) as tab_fqname, "
+		     "    %s.slon_quote_input('\"' || PGN.nspname || '\".\"' || "
+		     "    PGC.relname || '\"') as tab_fqname, "
 		     "    T.tab_idxname, T.tab_comment "
 		     "from %s.sl_table T, "
 		     "    \"pg_catalog\".pg_class PGC, "
@@ -2555,7 +2559,9 @@
 		     "    and T.tab_reloid = PGC.oid "
 		     "    and PGC.relnamespace = PGN.oid "
 		     "order by tab_id; ",
-		     rtcfg_namespace, set_id);
+		     rtcfg_namespace, 
+		     rtcfg_namespace, 
+		     set_id);
 	res1 = PQexec(pro_dbconn, dstring_data(&query1));
 	if (PQresultStatus(res1) != PGRES_TUPLES_OK)
 	{
@@ -2954,7 +2960,7 @@
 		}
 		PQputline(loc_dbconn, "\\.\n");
 		if (archive_dir) {
-			rc = submit_string_to_archive("\\\.");
+			rc = submit_string_to_archive("\\.");
 		}
 		/*
 		 * End the COPY to stdout on the provider
@@ -3092,8 +3098,8 @@
 	 */
 	slon_mkquery(&query1,
 		     "select SL.seql_seqid, SL.seql_last_value, "
-		     "	\"pg_catalog\".quote_ident(PGN.nspname) || '.' || "
-		     "	\"pg_catalog\".quote_ident(PGC.relname) "
+		     "    %s.slon_quote_input('\"' || PGN.nspname || '\".\"' || "
+		     "    PGC.relname || '\"') as tab_fqname "
 		     "	from %s.sl_sequence SQ, %s.sl_seqlog SL, "
 		     "		\"pg_catalog\".pg_class PGC, "
 		     "		\"pg_catalog\".pg_namespace PGN "
@@ -3102,7 +3108,9 @@
 		     "		and SL.seql_ev_seqno = '%s' "
 		     "		and PGC.oid = SQ.seq_reloid "
 		     "		and PGN.oid = PGC.relnamespace; ",
-		     rtcfg_namespace, rtcfg_namespace,
+		     rtcfg_namespace, 
+		     rtcfg_namespace, 
+		     rtcfg_namespace,
 		     set_id, seqbuf);
 	res1 = PQexec(pro_dbconn, dstring_data(&query1));
 	if (PQresultStatus(res1) != PGRES_TUPLES_OK)
@@ -3691,16 +3699,18 @@
 			 * Select the tables in that set ...
 			 */
 			slon_mkquery(&query,
-						 "select T.tab_id, T.tab_set, "
-					 "    \"pg_catalog\".quote_ident(PGN.nspname) || '.' || "
-				 "    \"pg_catalog\".quote_ident(PGC.relname) as tab_fqname "
-						 "from %s.sl_table T, "
-						 "    \"pg_catalog\".pg_class PGC, "
-						 "    \"pg_catalog\".pg_namespace PGN "
-						 "where T.tab_set = %d "
-						 "    and PGC.oid = T.tab_reloid "
-						 "    and PGC.relnamespace = PGN.oid; ",
-						 rtcfg_namespace, sub_set);
+				     "select T.tab_id, T.tab_set, "
+				     "    %s.slon_quote_input('\"' || PGN.nspname || '\".\"' || "
+				     "    PGC.relname || '\"') as tab_fqname "
+				     "from %s.sl_table T, "
+				     "    \"pg_catalog\".pg_class PGC, "
+				     "    \"pg_catalog\".pg_namespace PGN "
+				     "where T.tab_set = %d "
+				     "    and PGC.oid = T.tab_reloid "
+				     "    and PGC.relnamespace = PGN.oid; ",
+				     rtcfg_namespace, 
+				     rtcfg_namespace, 
+				     sub_set);
 			res2 = PQexec(local_dbconn, dstring_data(&query));
 			if (PQresultStatus(res2) != PGRES_TUPLES_OK)
 			{
More information about the Slony1-general mailing list

Content © 2007 Slony Development Group - Hosting provided by Command Prompt, Inc.