Postgres Dev.
Jump to navigation
Jump to search
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)
