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 }

The script produces the following output. I manually deleted the messages we don't care about

#: catalog/dependency.c:2301
msgid "uncataloged table %s"

#: catalog/dependency.c:2305
msgid "toast table %s"

#: catalog/heap.c:1781
msgid "only table \"%s\" can be referenced in check constraint"

#: catalog/heap.c:2297
msgid "Truncate table \"%s\" at the same time, or use TRUNCATE ... CASCADE."

#: commands/analyze.c:167
msgid "skipping \"%s\" --- only table or database owner can analyze it"

#: commands/analyze.c:494
msgid "automatic analyze of table \"%s.%s.%s\" system usage: %s"

#: commands/cluster.c:163 commands/tablecmds.c:5688
msgid "index \"%s\" for table \"%s\" does not exist"

#: commands/cluster.c:367
msgid "\"%s\" is not an index for table \"%s\""

#: commands/trigger.c:1092
msgid "trigger \"%s\" for table \"%s\" does not exist"

#: commands/comment.c:1125
msgid "constraint \"%s\" for table \"%s\" does not exist"

#: parser/parse_utilcmd.c:1066
msgid "multiple primary keys for table \"%s\" are not allowed"

#: commands/indexcmds.c:431
msgid "%s %s will create implicit index \"%s\" for table \"%s\""

#: commands/indexcmds.c:1339
msgid "shared table \"%s\" can only be reindexed in stand-alone mode"

#: commands/tablecmds.c:580
msgid "truncate cascades to table \"%s\""

#: commands/tablecmds.c:2740
msgid "\"%s\" is not a table or view"

#: commands/tablecmds.c:2776 commands/tablecmds.c:3480
msgid "\"%s\" is not a table or index"

#: commands/tablecmds.c:2931
msgid "cannot alter table \"%s\" because column \"%s\".\"%s\" uses its rowtype"

#: commands/tablecmds.c:3057 commands/tablecmds.c:6218
msgid "child table \"%s\" has different type for column \"%s\""

#: commands/tablecmds.c:4335
msgid "there is no primary key for referenced table \"%s\""

#: commands/tablecmds.c:4469
"there is no unique constraint matching given keys for referenced table \"%s\""

#: commands/tablecmds.c:5455 commands/tablecmds.c:6544
msgid "Sequence \"%s\" is linked to table \"%s\"."

#: commands/tablecmds.c:6120
msgid "table \"%s\" without OIDs cannot inherit from table \"%s\" with OIDs"

#: commands/tablecmds.c:6225
msgid "column \"%s\" in child table must be marked NOT NULL"

#: commands/tablecmds.c:6241
msgid "child table is missing column \"%s\""

#: commands/trigger.c:766
msgid "trigger \"%s\" for table \"%s\" does not exist, skipping"

#: commands/typecmds.c:1621
msgid "column \"%s\" of table \"%s\" contains null values"

#: commands/typecmds.c:1866
"column \"%s\" of table \"%s\" contains values that violate the new constraint"

#: commands/vacuum.c:1063
msgid "skipping \"%s\" --- only table or database owner can vacuum it"

#: commands/vacuum.c:3321 commands/vacuum.c:3393
"index \"%s\" contains %.0f row versions, but table contains %.0f row versions"

#: commands/vacuumlazy.c:238
"automatic vacuum of table \"%s.%s.%s\": index scans: %d\n"

#: executor/execCurrent.c:96
msgid "cursor \"%s\" is not a simply updatable scan of table \"%s\""

#: parser/parse_relation.c:2001
msgid "invalid reference to FROM-clause entry for table \"%s\""

#: parser/parse_relation.c:2004 parser/parse_relation.c:2030
msgid "Perhaps you meant to reference the table alias \"%s\"."

#: parser/parse_relation.c:2006 parser/parse_relation.c:2033
"There is an entry for table \"%s\", but it cannot be referenced from this "

#: parser/parse_relation.c:2013
msgid "missing FROM-clause entry in subquery for table \"%s\""

#: parser/parse_relation.c:2015
msgid "missing FROM-clause entry for table \"%s\""

#: parser/parse_relation.c:2025
msgid "adding missing FROM-clause entry in subquery for table \"%s\""

#: parser/parse_relation.c:2027
msgid "adding missing FROM-clause entry for table \"%s\""

#: parser/parse_utilcmd.c:423 parser/parse_utilcmd.c:433
"conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\""

#: parser/parse_utilcmd.c:443
msgid "multiple default values specified for column \"%s\" of table \"%s\""

#: postmaster/autovacuum.c:2141
msgid "automatic vacuum of table \"%s.%s.%s\""

#: postmaster/autovacuum.c:2144
msgid "automatic analyze of table \"%s.%s.%s\""

#: rewrite/rewriteDefine.c:383
msgid "could not convert table \"%s\" to a view because it is not empty"

#: rewrite/rewriteDefine.c:390
msgid "could not convert table \"%s\" to a view because it has triggers"

#: rewrite/rewriteDefine.c:392
"In particular, the table cannot be involved in any foreign key relationships."

#: rewrite/rewriteDefine.c:397
msgid "could not convert table \"%s\" to a view because it has indexes"

#: rewrite/rewriteDefine.c:403
msgid "could not convert table \"%s\" to a view because it has child tables"

#: utils/adt/ri_triggers.c:3451 utils/adt/ri_triggers.c:3488
msgid "insert or update on table \"%s\" violates foreign key constraint \"%s\""

#: utils/adt/ri_triggers.c:3061
msgid "no pg_constraint entry for trigger \"%s\" on table \"%s\""

#: utils/adt/ri_triggers.c:3490
msgid "Key (%s)=(%s) is not present in table \"%s\"."

#: utils/adt/ri_triggers.c:3496
"update or delete on table \"%s\" violates foreign key constraint \"%s\" on "

#: utils/adt/ri_triggers.c:3499
msgid "Key (%s)=(%s) is still referenced from table \"%s\"."

The following is another list matched on %s, and \son\s to capture more vague references to relations.

#: access/heap/heapam.c:953
msgid "could not obtain lock on relation \"%s\""

#: access/heap/heapam.c:3109
msgid "could not obtain lock on row in relation \"%s\""

#: catalog/dependency.c:189 catalog/dependency.c:242
msgid "cannot drop %s because other objects depend on it"

#: catalog/dependency.c:375
msgid "failed to drop all objects depending on %s"

#: catalog/dependency.c:692 catalog/dependency.c:857
msgid "%s depends on %s"

#: catalog/dependency.c:1864
msgid "constraint %s on "

#: catalog/dependency.c:2098
msgid "rule %s on "

#: catalog/dependency.c:2133
msgid "trigger %s on "

#: catalog/pg_depend.c:207
msgid "cannot remove dependency on %s because it is a system object"

#: catalog/pg_shdepend.c:672
msgid "there are objects dependent on %s"

#: commands/cluster.c:386
"cannot cluster on index \"%s\" because access method does not support "

#: commands/cluster.c:406
"cannot cluster on index \"%s\" because access method does not handle null "

#: commands/cluster.c:422
"cannot cluster on expressional index \"%s\" because its index access method "

#: commands/cluster.c:437
msgid "cannot cluster on invalid index \"%s\""

#: commands/tablecmds.c:5091
msgid "%s depends on column \"%s\""

#: rewrite/rewriteHandler.c:1697
msgid "cannot perform INSERT RETURNING on relation \"%s\""

#: rewrite/rewriteHandler.c:1704
msgid "cannot perform UPDATE RETURNING on relation \"%s\""

#: rewrite/rewriteHandler.c:1711
msgid "cannot perform DELETE RETURNING on relation \"%s\""

#: storage/lmgr/deadlock.c:908
msgid "Process %d waits for %s on %s; blocked by process %d."

#: storage/lmgr/proc.c:954
"process %d avoided deadlock for %s on %s by rearranging queue order after %"

#: storage/lmgr/proc.c:966
"process %d detected deadlock while waiting for %s on %s after %ld.%03d ms"

#: storage/lmgr/proc.c:972
msgid "process %d still waiting for %s on %s after %ld.%03d ms"

#: storage/lmgr/proc.c:976
msgid "process %d acquired %s on %s after %ld.%03d ms"

#: storage/lmgr/proc.c:992
msgid "process %d failed to acquire %s on %s after %ld.%03d ms"

#: utils/adt/ri_triggers.c:3451 utils/adt/ri_triggers.c:3488
msgid "insert or update on table \"%s\" violates foreign key constraint \"%s\""

#: utils/adt/ri_triggers.c:3061
msgid "no pg_constraint entry for trigger \"%s\" on table \"%s\""

#: utils/adt/ri_triggers.c:3418
"referential integrity query on \"%s\" from constraint \"%s\" on \"%s\" gave "

#: utils/adt/ri_triggers.c:3496
"update or delete on table \"%s\" violates foreign key constraint \"%s\" on "

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)