While looking at this report
http://archives.postgresql.org/pgsql-bugs/2009-08/msg00083.php
I spotted another error message which could use improvement:
CREATE TABLE foo(a int PRIMARY KEY DEFERRABLE);
CREATE TABLE bar(a int REFERENCES foo(a));
ERROR: there is no unique constraint matching given keys for
referenced table "foo"
Whereas if you define the FK slightly differently so that it goes
through transformFkeyGetPrimaryKey() instead, you get a much better
error message:
CREATE TABLE bar(a int REFERENCES foo);
ERROR: cannot use a deferrable primary key for referenced table "foo"
The attached patch to transformFkeyCheckAttrs() makes the former case
generate a similar error to the latter.
- Dean
*** ./src/backend/commands/tablecmds.c.orig 2009-08-12 09:47:34.000000000 +0100
--- ./src/backend/commands/tablecmds.c 2009-08-12 10:08:13.000000000 +0100
***************
*** 5117,5122 ****
--- 5117,5123 ----
{
Oid indexoid = InvalidOid;
bool found = false;
+ bool found_deferrable = false;
List *indexoidlist;
ListCell *indexoidscan;
***************
*** 5148,5154 ****
* expressions, too
*/
if (indexStruct->indnatts == numattrs &&
! indexStruct->indisunique && indexStruct->indimmediate &&
heap_attisnull(indexTuple, Anum_pg_index_indpred) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs))
{
--- 5149,5155 ----
* expressions, too
*/
if (indexStruct->indnatts == numattrs &&
! indexStruct->indisunique &&
heap_attisnull(indexTuple, Anum_pg_index_indpred) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs))
{
***************
*** 5198,5203 ****
--- 5199,5219 ----
break;
}
}
+
+ /*
+ * Refuse to use a deferrable unique/primary key. This is per
+ * SQL spec, and there would be a lot of interesting semantic
+ * problems if we tried to allow it.
+ */
+ if (found && !indexStruct->indimmediate)
+ {
+ /*
+ * Remember that we found an otherwise matching index, so
+ * that we can generate a more appropriate error message.
+ */
+ found_deferrable = true;
+ found = false;
+ }
}
ReleaseSysCache(indexTuple);
if (found)
***************
*** 5205,5214 ****
}
if (!found)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_FOREIGN_KEY),
! errmsg("there is no unique constraint matching given keys for referenced table \"%s\"",
! RelationGetRelationName(pkrel))));
list_free(indexoidlist);
--- 5221,5238 ----
}
if (!found)
! {
! if (found_deferrable)
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg("cannot use a deferrable unique constraint for referenced table \"%s\"",
! RelationGetRelationName(pkrel))));
! else
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_FOREIGN_KEY),
! errmsg("there is no unique constraint matching given keys for referenced table \"%s\"",
! RelationGetRelationName(pkrel))));
! }
list_free(indexoidlist);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
