fk_schemas.patch

Index: src/backend/utils/adt/ri_triggers.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v
retrieving revision 1.115
diff -u -r1.115 ri_triggers.c
--- src/backend/utils/adt/ri_triggers.c	5 Nov 2009 04:38:29 -0000	1.115
+++ src/backend/utils/adt/ri_triggers.c	15 Nov 2009 06:51:20 -0000
@@ -31,6 +31,7 @@
 #include "postgres.h"
 
 #include "access/xact.h"
+#include "catalog/namespace.h"
 #include "catalog/pg_constraint.h"
 #include "catalog/pg_operator.h"
 #include "catalog/pg_type.h"
@@ -237,6 +238,8 @@
 				   Relation pk_rel, Relation fk_rel,
 				   HeapTuple violator, TupleDesc tupdesc,
 				   bool spi_err);
+static bool searchPathContainsUnique(Relation rel);
+static char *getSchemaPrefix(Relation rel);
 
 
 /* ----------
@@ -257,6 +260,7 @@
 	Buffer		new_row_buf;
 	RI_QueryKey qkey;
 	SPIPlanPtr	qplan;
+	char		schema_prefix;
 	int			i;
 
 	/*
@@ -402,12 +406,15 @@
 					 * Not allowed - MATCH FULL says either all or none of the
 					 * attributes can be NULLs
 					 */
+					schema_prefix = getSchemaPrefix(trigdata->tg_relation);
 					ereport(ERROR,
 							(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
-							 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
+							 errmsg("insert or update on table \"%s%s\" violates foreign key constraint \"%s\"",
+								schema_prefix,
 							  RelationGetRelationName(trigdata->tg_relation),
 									NameStr(riinfo.conname)),
 							 errdetail("MATCH FULL does not allow mixing of null and nonnull key values.")));
+					pfree(schema_prefix);
 					heap_close(pk_rel, RowShareLock);
 					return PointerGetDatum(NULL);
 
@@ -2797,13 +2804,17 @@
 				if (isnull)
 					break;
 			}
-			if (isnull)
+			if (isnull) {
+				char *schema_prefix = getSchemaPrefix(fk_rel);
 				ereport(ERROR,
 						(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
-						 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
+						 errmsg("insert or update on table \"%s%s\" violates foreign key constraint \"%s\"",
+								schema_prefix,
 								RelationGetRelationName(fk_rel),
 								constrname),
 						 errdetail("MATCH FULL does not allow mixing of null and nonnull key values.")));
+				pfree(schema_prefix);
+			}
 		}
 
 		/*
@@ -3418,6 +3429,8 @@
 	bool		onfk;
 	int			idx,
 				key_idx;
+	char		*fk_schema_prefix;
+	char		*pk_schema_prefix;
 
 	if (spi_err)
 		ereport(ERROR,
@@ -3453,12 +3466,16 @@
 	 */
 	if (qkey->nkeypairs == 0)
 	{
+		fk_schema_prefix = getSchemaPrefix(fk_rel);
+		pk_schema_prefix = getSchemaPrefix(pk_rel);
 		ereport(ERROR,
 				(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
-				 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
-						RelationGetRelationName(fk_rel), constrname),
-				 errdetail("No rows were found in \"%s\".",
-						   RelationGetRelationName(pk_rel))));
+				 errmsg("insert or update on table \"%s%s\" violates foreign key constraint \"%s\"",
+						fk_schema_prefix, RelationGetRelationName(fk_rel), constrname),
+				 errdetail("No rows were found in \"%s%s\".",
+						   pk_schema_prefix, RelationGetRelationName(pk_rel))));
+		pfree(pk_schema_prefix);
+		pfree(fk_schema_prefix);
 	}
 
 	/* Get printable versions of the keys involved */
@@ -3484,23 +3501,27 @@
 		appendStringInfoString(&key_values, val);
 	}
 
+	fk_schema_prefix = getSchemaPrefix(fk_rel);
+	pk_schema_prefix = getSchemaPrefix(pk_rel);
 	if (onfk)
 		ereport(ERROR,
 				(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
-				 errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
-						RelationGetRelationName(fk_rel), constrname),
-				 errdetail("Key (%s)=(%s) is not present in table \"%s\".",
+				 errmsg("insert or update on table \"%s%s\" violates foreign key constraint \"%s\"",
+						fk_schema_prefix, RelationGetRelationName(fk_rel), constrname),
+				 errdetail("Key (%s)=(%s) is not present in table \"%s%s\".",
 						   key_names.data, key_values.data,
-						   RelationGetRelationName(pk_rel))));
+						   pk_schema_prefix, RelationGetRelationName(pk_rel))));
 	else
 		ereport(ERROR,
 				(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
-				 errmsg("update or delete on table \"%s\" violates foreign key constraint \"%s\" on table \"%s\"",
-						RelationGetRelationName(pk_rel),
-						constrname, RelationGetRelationName(fk_rel)),
-			errdetail("Key (%s)=(%s) is still referenced from table \"%s\".",
+				 errmsg("update or delete on table \"%s%s\" violates foreign key constraint \"%s\" on table \"%s%s\"",
+						pk_schema_prefix, RelationGetRelationName(pk_rel),
+						constrname, fk_schema_prefix, RelationGetRelationName(fk_rel)),
+			errdetail("Key (%s)=(%s) is still referenced from table \"%s%s\".",
 					  key_names.data, key_values.data,
-					  RelationGetRelationName(fk_rel))));
+					  pk_schema_prefix, RelationGetRelationName(fk_rel))));
+	pfree(pk_schema_prefix);
+	pfree(fk_schema_prefix);
 }
 
 /* ----------
@@ -4014,3 +4035,57 @@
 
 	return RI_TRIGGER_NONE;
 }
+
+static bool
+searchPathContainsUnique(Relation rel)
+{
+	ListCell *l;
+	List *searchPath = fetch_search_path(true);
+	char *relname = RelationGetRelationName(rel);
+	int count = 0;
+
+	if (searchPath == NIL)
+		return false;
+	foreach(l, searchPath)
+	{
+		Oid			namespaceId = lfirst_oid(l);
+
+		if (OidIsValid(get_relname_relid(relname, namespaceId)))
+			count++;
+
+		if( count >= 2 )
+			break;
+	}
+
+	list_free(searchPath);
+
+	return count == 1;
+}
+
+/*
+ * Given a relation OID, return a palloc'd string which contains a
+ * schema prefix if the relation is absent or non-unique within the search path
+ */
+static char *
+getSchemaPrefix(Relation rel)
+{
+	if (!searchPathContainsUnique(rel)) {
+		int len;
+		char *schema_prefix;
+		char *schemaname = get_namespace_name(RelationGetNamespace(rel));
+
+		if (schemaname == NULL)
+			return pstrdup("");
+
+		len = strlen(schemaname);
+
+		schema_prefix = palloc(len + 2);
+		strcpy(schema_prefix, schemaname);
+		strcpy(&schema_prefix[len], ".");
+		pfree(schemaname);
+
+		return schema_prefix;
+	}
+
+	return pstrdup("");
+}
Index: src/test/regress/expected/foreign_key.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/foreign_key.out,v
retrieving revision 1.48
diff -u -r1.48 foreign_key.out
--- src/test/regress/expected/foreign_key.out	14 Jun 2008 18:04:34 -0000	1.48
+++ src/test/regress/expected/foreign_key.out	15 Nov 2009 06:51:22 -0000
@@ -1287,3 +1287,25 @@
 (3 rows)
 
 COMMIT;
+-- test foreign key namespace printing
+CREATE TABLE fknssrc (
+  id INT PRIMARY KEY
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fknssrc_pkey" for table "fknssrc"
+CREATE SCHEMA non_searched_schema;
+CREATE TABLE non_searched_schema.fknsref ( 
+  fknssrc_id INT REFERENCES fknssrc ON UPDATE CASCADE ON DELETE SET NULL
+);
+INSERT INTO non_searched_schema.fknsref VALUES (1000);
+ERROR:  insert or update on table "non_searched_schema.fknsref" violates foreign key constraint "fknsref_fknssrc_id_fkey"
+DETAIL:  Key (fknssrc_id)=(1000) is not present in table "fknssrc".
+SET search_path TO public, non_searched_schema;
+-- make the original table non-unique
+CREATE TABLE non_searched_schema.fknssrc (
+  id INT PRIMARY KEY
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fknssrc_pkey" for table "fknssrc"
+-- and note the addition of the public schema
+INSERT INTO non_searched_schema.fknsref VALUES (1000);
+ERROR:  insert or update on table "fknsref" violates foreign key constraint "fknsref_fknssrc_id_fkey"
+DETAIL:  Key (fknssrc_id)=(1000) is not present in table "public.fknssrc".
Index: src/test/regress/sql/foreign_key.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/foreign_key.sql,v
retrieving revision 1.22
diff -u -r1.22 foreign_key.sql
--- src/test/regress/sql/foreign_key.sql	14 Jun 2008 18:04:34 -0000	1.22
+++ src/test/regress/sql/foreign_key.sql	15 Nov 2009 06:51:22 -0000
@@ -921,3 +921,26 @@
 DELETE FROM users WHERE id = 2;
 SELECT * FROM tasks;
 COMMIT;
+
+-- test foreign key namespace printing
+CREATE TABLE fknssrc (
+  id INT PRIMARY KEY
+);
+
+CREATE SCHEMA non_searched_schema;
+
+CREATE TABLE non_searched_schema.fknsref ( 
+  fknssrc_id INT REFERENCES fknssrc ON UPDATE CASCADE ON DELETE SET NULL
+);
+
+INSERT INTO non_searched_schema.fknsref VALUES (1000);
+
+SET search_path TO public, non_searched_schema;
+
+-- make the original table non-unique
+CREATE TABLE non_searched_schema.fknssrc (
+  id INT PRIMARY KEY
+);
+
+-- and note the addition of the public schema
+INSERT INTO non_searched_schema.fknsref VALUES (1000);