Postgres Dev.

Postgres Internals

We have fix a bug listed on the Postgres TODO list.Described Here The bug is catagorized in referential integrity, however we have completed a system-wide change so schema names, or namespace names, are reported along with table names in various flavors of errors. We did not have time, however, to patch the regression suite to accept the new code. When the user types make check errors are generated because the newly qualified table printout is not expected.

The following includes some notes from the project along with the procedure for testing.

Notes

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.

Sample error report generated from an fixed version of ri_triggers.c :

3487                                  errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
3488                                                 strcat( strcat(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                                                   strcat( strcat(get_namespace_name(fk_rel->rd_rel->relnamespace), "."), RelationGetRelationName(fk_rel)))));

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/es.po");
  4 
  5 my $lines = <FILE>;
  6 my $count = 0;
  7 
  8 while(<FILE>)
  9 {
 10 
 11         if($_ =~m/#:/) {
 12                 $filename = $_;
 13         }
 14 
 15         if((($_ =~ m/%s/) && ($_ =~m/\son\s/)) || ($_ =~ m/\stable\s/)) {
 16                 print $count, ". ",  $filename,  $_, "\n";
 17                 $count = $count + 1;
 18         }
 19 
 20 }

Bryan's Change Locations

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

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

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

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

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

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

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

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

11. #: catalog/heap.c:2294
msgid "cannot truncate a table referenced in a foreign key constraint"

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

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

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

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

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

17. #: commands/cluster.c:149
msgid "there is no previously clustered index for table \"%s\""

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

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

20. #: commands/cluster.c:380
msgid "cannot cluster on partial index \"%s\""

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

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

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

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

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

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

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

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

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

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

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

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

37. #: commands/tablecmds.c:4011
msgid "cannot reference temporary table from permanent table constraint"

38. #: commands/tablecmds.c:4018
msgid "cannot reference permanent table from temporary table constraint"

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

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

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

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

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

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

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

46. #: commands/tablecmds.c:6333
msgid "child table is missing constraint \"%s\""

Mikio's Change Locations

47. #: commands/tablespace.c:306 commands/tablespace.c:1289
msgid "could not set permissions on directory \"%s\": %m"

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

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

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

51. #: commands/typecmds.c:2146 commands/typecmds.c:2155
msgid "cannot use table references in domain check constraint"

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

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

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

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

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

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

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

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

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

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

73. #: parser/parse_utilcmd.c:1355
"index expressions and predicates can refer only to the table being indexed"

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

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

76. #: postmaster/pgstat.c:3020
msgid "database hash table corrupted during cleanup --- abort"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

Notes

• I skipped table alias. (miki)