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