Difference between revisions of "Postgres Dev."
(→Current Efforts) |
(→Current Efforts) |
||
Line 20: | Line 20: | ||
ri_triggers.c houses all cases where fk constraints fail. There are 5 or 6 variations. The primary error message is the same in each case, and a detail section gets more specific about why the constraint blocked the SQL action. | ri_triggers.c houses all cases where fk constraints fail. There are 5 or 6 variations. The primary error message is the same in each case, and a detail section gets more specific about why the constraint blocked the SQL action. | ||
− | Fixed error report: | + | Fixed error report in ri_triggers.c : |
<pre> | <pre> |
Revision as of 11:10, 30 April 2009
Contents
Postgres Internals Notes
Current Efforts
get_namespace_name(oid)
- Returns a character string representing the namespace name.
RelationGetRelationName(relation)
- Prints out the name of the relation. This function is called directly in the printf of the error message in ri_triggers.c
relation->rd_rel->relnamespace
- There's a member variable relnamespace in Form_pg_class data type.
It is of type id0, representing a unique namespace identifier. As a first stab, I just replaced the appearance of RelationGetRelationName(relation) with relation->rd_red->relnamespace and when the error occurs this number is printed.
- fk violation error messages located in:
postgresql-8.3.7/src/backend/utils/adt/ri_triggers.c:
ri_triggers.c houses all cases where fk constraints fail. There are 5 or 6 variations. The primary error message is the same in each case, and a detail section gets more specific about why the constraint blocked the SQL action.
Fixed error report in ri_triggers.c :
3485 ereport(ERROR, 3486 (errcode(ERRCODE_FOREIGN_KEY_VIOLATION), 3487 errmsg("insert or update on table \"%s.%s\" violates foreign key constraint \"%s\"", 3488 get_namespace_name(fk_rel->rd_rel->relnamespace), RelationGetRelationName(fk_rel), constrname), 3489 errdetail("Key (%s)=(%s) is not present in table \"%s\".", 3490 key_names, key_values, 3491 RelationGetRelationName(pk_rel))));
The problem we have to figure out here is how to combine the namespace name and table name into a single variable. Adding another %s will require changing each localization file, along with the source code. Making such a substantial change probably won't be possible.
Test Environment
We have created a foreign key violation test enviroment. This this enviroment consists of two relations, where the a value in orders depends on a values in the table products. The query that causes our fk constraint failure is:
INSERT INTO orders VALUES(0, 23, 23);
An error gets returned because the constraint was violated. Originally, only the relation name was reported. Our fix qualifies this table with its parent namespace. The error used to look like:
ERROR: insert or update on table "orders" violates foreign key constraint "orders_product_no_fkey" DETAIL: Key (product_no)=(23) is not present in table "products".
Now it looks like:
ERROR: insert or update on table "public.orders" violates foreign key constraint "orders_product_no_fkey" DETAIL: Key (product_no)=(23) is not present in table "products".
All of the foreign key constraint error cases can be found in ri_triggers.c file. There are many other types of errors that will need to be modified, however, and we are working on a way to automate this process
Automation
The bug description on the mailing list seemed to find this fix more tedious than outright difficult. Now that we've seen the fix work, it seems like a good idea to try to automate corrections across the whole system.
1 #!/usr/bin/perl 2 3 open(FILE, "/Users/purcebr/Desktop/postgres/postgresql-8.3.7/src/backend/po/hu.po"); 4 5 my $lines = <FILE>; 6 7 while(<FILE>) 8 { 9 10 if($_ =~m/#:/) { 11 $filename = $_; 12 } 13 14 if($_ =~ m/ table /) { 15 print $filename, $_, "\n"; 16 } 17 18 }
Open Questions
- What's the difference between a tablespace and a namespace? - addressed
- Is there a way to take a namespace identifier and get a namespace object? - yep. it's a function called get_namespace_name(oid namespace_id)