Postgres Dev.

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)