Postgres Dev.

Postgres Internals Notes

Current Efforts

• Declaration of RelationGetRelationNamespace:

postgresql-8.3.7/src/include/utils/rel.h

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. I just replaced the appearance of RelationGetRelationName(relation) with relation->rd_red->relnamespace and when the error occurs this number is printed.

• Error "violates foreign key constraint 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.

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.

• po files

We have created a local test environment and accompanying script that fails on a fk constraint. When the SQL call fails, our modified error message gets printed with the namespace identification number in place of the relation name.

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?