From a208ea72bced456c4234644e4c9eda8969e8bdc9 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 16 Aug 2002 23:01:21 +0000 Subject: [PATCH] Modify pg_dump to dump foreign-key constraints as constraints, not as sets of triggers. Also modify psql \d command to show foreign key constraints as such and hide the triggers. pg_get_constraintdef() function added to backend to support these. From Rod Taylor, code review and some editorialization by Tom Lane. --- doc/src/sgml/func.sgml | 22 ++- src/backend/utils/adt/ruleutils.c | 218 +++++++++++++++++++++++++++++- src/bin/pg_dump/pg_dump.c | 131 +++++++++++++++++- src/bin/psql/describe.c | 59 +++++++- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 4 +- src/include/utils/builtins.h | 3 +- 7 files changed, 421 insertions(+), 20 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f136088fe8..bf2ce4667e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -5521,6 +5521,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); text Get CREATE INDEX command for index + + pg_get_constraintdef(constraintOID) + text + Get definition of a constraint + pg_get_userbyid(userid) name @@ -5542,16 +5547,25 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); pg_get_indexdef + + pg_get_constraintdef + + pg_get_userbyid These functions extract information from the system catalogs. - pg_get_viewdef(), pg_get_ruledef(), and - pg_get_indexdef() respectively reconstruct the creating - command for a view, rule, or index. (Note that this is a decompiled + pg_get_viewdef(), + pg_get_ruledef(), + pg_get_indexdef(), and + pg_get_constraintdef() respectively reconstruct the + creating command for a view, rule, index, or constraint. + (Note that this is a decompiled reconstruction, not the verbatim text of the command.) + At present pg_get_constraintdef() only works for + foreign-key constraints. pg_get_userbyid() extracts a user's name given a usesysid value. diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index e9c197aa35..ec711e9959 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -3,7 +3,7 @@ * back to source text * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v 1.115 2002/08/16 20:55:09 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v 1.116 2002/08/16 23:01:19 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -40,10 +40,14 @@ #include #include +#include "access/genam.h" +#include "catalog/catname.h" #include "catalog/heap.h" #include "catalog/index.h" +#include "catalog/indexing.h" #include "catalog/namespace.h" #include "catalog/pg_cast.h" +#include "catalog/pg_constraint.h" #include "catalog/pg_index.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" @@ -60,6 +64,8 @@ #include "parser/parsetree.h" #include "rewrite/rewriteManip.h" #include "rewrite/rewriteSupport.h" +#include "utils/array.h" +#include "utils/fmgroids.h" #include "utils/lsyscache.h" @@ -116,6 +122,8 @@ static char *query_getviewrule = "SELECT * FROM pg_catalog.pg_rewrite WHERE ev_c * ---------- */ static text *pg_do_getviewdef(Oid viewoid); +static void decompile_column_index_array(Datum column_index_array, Oid relId, + StringInfo buf); static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc); static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc); static void get_query_def(Query *query, StringInfo buf, List *parentnamespace, @@ -528,6 +536,214 @@ pg_get_indexdef(PG_FUNCTION_ARGS) } +/* + * pg_get_constraintdef + * + * Returns the definition for the constraint, ie, everything that needs to + * appear after "ALTER TABLE ... ADD CONSTRAINT ". + * + * XXX The present implementation only works for foreign-key constraints, but + * it could and should handle anything pg_constraint stores. + */ +Datum +pg_get_constraintdef(PG_FUNCTION_ARGS) +{ + Oid constraintId = PG_GETARG_OID(0); + text *result; + StringInfoData buf; + int len; + Relation conDesc; + SysScanDesc conscan; + ScanKeyData skey[1]; + HeapTuple tup; + Form_pg_constraint conForm; + + /* + * Fetch the pg_constraint row. There's no syscache for pg_constraint + * so we must do it the hard way. + */ + conDesc = heap_openr(ConstraintRelationName, AccessShareLock); + + ScanKeyEntryInitialize(&skey[0], 0x0, + ObjectIdAttributeNumber, F_OIDEQ, + ObjectIdGetDatum(constraintId)); + + conscan = systable_beginscan(conDesc, ConstraintOidIndex, true, + SnapshotNow, 1, skey); + + tup = systable_getnext(conscan); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "Failed to find constraint with OID %u", constraintId); + conForm = (Form_pg_constraint) GETSTRUCT(tup); + + initStringInfo(&buf); + + switch (conForm->contype) + { + case CONSTRAINT_FOREIGN: + { + Datum val; + bool isnull; + const char *string; + + /* Start off the constraint definition */ + appendStringInfo(&buf, "FOREIGN KEY ("); + + /* Fetch and build referencing-column list */ + val = heap_getattr(tup, Anum_pg_constraint_conkey, + RelationGetDescr(conDesc), &isnull); + if (isnull) + elog(ERROR, "pg_get_constraintdef: Null conkey for constraint %u", + constraintId); + + decompile_column_index_array(val, conForm->conrelid, &buf); + + /* add foreign relation name */ + appendStringInfo(&buf, ") REFERENCES %s(", + generate_relation_name(conForm->confrelid)); + + /* Fetch and build referenced-column list */ + val = heap_getattr(tup, Anum_pg_constraint_confkey, + RelationGetDescr(conDesc), &isnull); + if (isnull) + elog(ERROR, "pg_get_constraintdef: Null confkey for constraint %u", + constraintId); + + decompile_column_index_array(val, conForm->confrelid, &buf); + + appendStringInfo(&buf, ")"); + + /* Add match type */ + switch (conForm->confmatchtype) + { + case FKCONSTR_MATCH_FULL: + string = " MATCH FULL"; + break; + case FKCONSTR_MATCH_PARTIAL: + string = " MATCH PARTIAL"; + break; + case FKCONSTR_MATCH_UNSPECIFIED: + string = ""; + break; + default: + elog(ERROR, "pg_get_constraintdef: Unknown confmatchtype '%c' for constraint %u", + conForm->confmatchtype, constraintId); + string = ""; /* keep compiler quiet */ + break; + } + appendStringInfo(&buf, "%s", string); + + /* Add ON UPDATE and ON DELETE clauses */ + switch (conForm->confupdtype) + { + case FKCONSTR_ACTION_NOACTION: + string = "NO ACTION"; + break; + case FKCONSTR_ACTION_RESTRICT: + string = "RESTRICT"; + break; + case FKCONSTR_ACTION_CASCADE: + string = "CASCADE"; + break; + case FKCONSTR_ACTION_SETNULL: + string = "SET NULL"; + break; + case FKCONSTR_ACTION_SETDEFAULT: + string = "SET DEFAULT"; + break; + default: + elog(ERROR, "pg_get_constraintdef: Unknown confupdtype '%c' for constraint %u", + conForm->confupdtype, constraintId); + string = ""; /* keep compiler quiet */ + break; + } + appendStringInfo(&buf, " ON UPDATE %s", string); + + switch (conForm->confdeltype) + { + case FKCONSTR_ACTION_NOACTION: + string = "NO ACTION"; + break; + case FKCONSTR_ACTION_RESTRICT: + string = "RESTRICT"; + break; + case FKCONSTR_ACTION_CASCADE: + string = "CASCADE"; + break; + case FKCONSTR_ACTION_SETNULL: + string = "SET NULL"; + break; + case FKCONSTR_ACTION_SETDEFAULT: + string = "SET DEFAULT"; + break; + default: + elog(ERROR, "pg_get_constraintdef: Unknown confdeltype '%c' for constraint %u", + conForm->confdeltype, constraintId); + string = ""; /* keep compiler quiet */ + break; + } + appendStringInfo(&buf, " ON DELETE %s", string); + + break; + } + + /* + * XXX Add more code here for other contypes + */ + default: + elog(ERROR, "pg_get_constraintdef: unsupported constraint type '%c'", + conForm->contype); + break; + } + + /* Record the results */ + len = buf.len + VARHDRSZ; + result = (text *) palloc(len); + VARATT_SIZEP(result) = len; + memcpy(VARDATA(result), buf.data, buf.len); + + /* Cleanup */ + pfree(buf.data); + systable_endscan(conscan); + heap_close(conDesc, AccessShareLock); + + PG_RETURN_TEXT_P(result); +} + + +/* + * Convert an int16[] Datum into a comma-separated list of column names + * for the indicated relation; append the list to buf. + */ +static void +decompile_column_index_array(Datum column_index_array, Oid relId, + StringInfo buf) +{ + Datum *keys; + int nKeys; + int j; + + /* Extract data from array of int16 */ + deconstruct_array(DatumGetArrayTypeP(column_index_array), + true, 2, 's', + &keys, &nKeys); + + for (j = 0; j < nKeys; j++) + { + char *colName; + + colName = get_attname(relId, DatumGetInt16(keys[j])); + + if (j == 0) + appendStringInfo(buf, "%s", + quote_identifier(colName)); + else + appendStringInfo(buf, ", %s", + quote_identifier(colName)); + } +} + + /* ---------- * get_expr - Decompile an expression tree * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index f12be8cbfd..d677d1eccb 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -22,7 +22,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.283 2002/08/16 21:03:42 tgl Exp $ + * $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.284 2002/08/16 23:01:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -108,6 +108,7 @@ static void dumpACL(Archive *fout, const char *type, const char *name, const char *tag, const char *nspname, const char *usename, const char *acl, const char *objoid); +static void dumpConstraints(Archive *fout, TableInfo *tblinfo, int numTables); static void dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables); static void dumpRules(Archive *fout, TableInfo *tblinfo, int numTables); static void formatStringLiteral(PQExpBuffer buf, const char *str, @@ -608,6 +609,7 @@ main(int argc, char **argv) if (!dataOnly) /* dump indexes and triggers at the end * for performance */ { + dumpConstraints(g_fout, tblinfo, numTables); dumpTriggers(g_fout, tblinfo, numTables); dumpRules(g_fout, tblinfo, numTables); } @@ -5814,6 +5816,117 @@ dumpOneSequence(Archive *fout, TableInfo *tbinfo, destroyPQExpBuffer(delqry); } +/* + * dumpConstraints + * + * Dump out constraints after all table creation statements in + * an alter table format. Currently handles foreign keys only. + * + * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that + * the current table data is not processed + */ +static void +dumpConstraints(Archive *fout, TableInfo *tblinfo, int numTables) +{ + int i, + j; + PQExpBuffer query; + PQExpBuffer delqry; + PGresult *res; + int i_condef, + i_conoid, + i_conname; + int ntups; + + /* pg_constraint was created in 7.3, so nothing to do if older */ + if (g_fout->remoteVersion < 70300) + return; + + query = createPQExpBuffer(); + delqry = createPQExpBuffer(); + + for (i = 0; i < numTables; i++) + { + TableInfo *tbinfo = &tblinfo[i]; + + if (tbinfo->ntrig == 0 || !tbinfo->dump) + continue; + + if (g_verbose) + write_msg(NULL, "dumping triggers for table %s\n", + tbinfo->relname); + + /* select table schema to ensure regproc name is qualified if needed */ + selectSourceSchema(tbinfo->relnamespace->nspname); + + resetPQExpBuffer(query); + appendPQExpBuffer(query, + "SELECT oid, conname, " + "pg_catalog.pg_get_constraintdef(oid) as condef " + "FROM pg_catalog.pg_constraint " + "WHERE conrelid = '%s'::pg_catalog.oid " + "AND contype = 'f'", + tbinfo->oid); + res = PQexec(g_conn, query->data); + if (!res || + PQresultStatus(res) != PGRES_TUPLES_OK) + { + write_msg(NULL, "query to obtain list of foreign key definitions failed: %s", PQerrorMessage(g_conn)); + exit_nicely(); + } + ntups = PQntuples(res); + + i_conoid = PQfnumber(res, "oid"); + i_conname = PQfnumber(res, "conname"); + i_condef = PQfnumber(res, "condef"); + + for (j = 0; j < ntups; j++) + { + const char *conOid = PQgetvalue(res, j, i_conoid); + const char *conName = PQgetvalue(res, j, i_conname); + const char *conDef = PQgetvalue(res, j, i_condef); + + resetPQExpBuffer(query); + appendPQExpBuffer(query, "ALTER TABLE ONLY %s ", + fmtId(tbinfo->relname, force_quotes)); + appendPQExpBuffer(query, "ADD CONSTRAINT %s %s;\n", + fmtId(conName, force_quotes), + conDef); + + /* DROP must be fully qualified in case same name appears in pg_catalog */ + resetPQExpBuffer(delqry); + appendPQExpBuffer(delqry, "ALTER TABLE ONLY %s.", + fmtId(tbinfo->relnamespace->nspname, force_quotes)); + appendPQExpBuffer(delqry, "%s ", + fmtId(tbinfo->relname, force_quotes)); + appendPQExpBuffer(delqry, "DROP CONSTRAINT %s;\n", + fmtId(conName, force_quotes)); + + ArchiveEntry(fout, conOid, + conName, + tbinfo->relnamespace->nspname, + tbinfo->usename, + "CONSTRAINT", NULL, + query->data, delqry->data, + NULL, NULL, NULL); + + resetPQExpBuffer(query); + appendPQExpBuffer(query, "CONSTRAINT %s ", + fmtId(conName, force_quotes)); + appendPQExpBuffer(query, "ON %s", + fmtId(tbinfo->relname, force_quotes)); + + dumpComment(fout, query->data, + tbinfo->relnamespace->nspname, tbinfo->usename, + conOid, "pg_constraint", 0, NULL); + } + + PQclear(res); + } + + destroyPQExpBuffer(query); + destroyPQExpBuffer(delqry); +} static void dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables) @@ -5854,6 +5967,7 @@ dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables) resetPQExpBuffer(query); if (g_fout->remoteVersion >= 70300) { + /* We ignore triggers that are tied to a foreign-key constraint */ appendPQExpBuffer(query, "SELECT tgname, " "tgfoid::pg_catalog.regproc as tgfname, " @@ -5861,8 +5975,13 @@ dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables) "tgisconstraint, tgconstrname, tgdeferrable, " "tgconstrrelid, tginitdeferred, oid, " "tgconstrrelid::pg_catalog.regclass as tgconstrrelname " - "from pg_catalog.pg_trigger " - "where tgrelid = '%s'::pg_catalog.oid", + "from pg_catalog.pg_trigger t " + "where tgrelid = '%s'::pg_catalog.oid " + "and (not tgisconstraint " + " OR NOT EXISTS" + " (SELECT 1 FROM pg_catalog.pg_depend d " + " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) " + " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))", tbinfo->oid); } else @@ -5886,7 +6005,11 @@ dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables) exit_nicely(); } ntups = PQntuples(res); - if (ntups != tbinfo->ntrig) + /* + * We may have less triggers than recorded due to constraint triggers + * which are dumped by dumpConstraints + */ + if (ntups > tbinfo->ntrig) { write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n", tbinfo->ntrig, tbinfo->relname, ntups); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 5b446e673d..9ba7bcc36f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3,7 +3,7 @@ * * Copyright 2000-2002 by PostgreSQL Global Development Group * - * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.61 2002/08/15 16:36:06 momjian Exp $ + * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.62 2002/08/16 23:01:19 tgl Exp $ */ #include "postgres_fe.h" #include "describe.h" @@ -914,9 +914,11 @@ describeOneTableDetails(const char *schemaname, PGresult *result1 = NULL, *result2 = NULL, *result3 = NULL, - *result4 = NULL; - int index_count = 0, - check_count = 0, + *result4 = NULL, + *result5 = NULL; + int check_count = 0, + index_count = 0, + foreignkey_count = 0, rule_count = 0, trigger_count = 0; int count_footers = 0; @@ -968,13 +970,18 @@ describeOneTableDetails(const char *schemaname, rule_count = PQntuples(result3); } - /* count triggers */ + /* count triggers (but ignore foreign-key triggers) */ if (tableinfo.triggers) { printfPQExpBuffer(&buf, "SELECT t.tgname\n" "FROM pg_catalog.pg_trigger t\n" - "WHERE t.tgrelid = '%s'", + "WHERE t.tgrelid = '%s' " + "and (not tgisconstraint " + " OR NOT EXISTS" + " (SELECT 1 FROM pg_catalog.pg_depend d " + " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) " + " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))", oid); result4 = PSQLexec(buf.data); if (!result4) @@ -983,7 +990,23 @@ describeOneTableDetails(const char *schemaname, trigger_count = PQntuples(result4); } - footers = xmalloc((index_count + check_count + rule_count + trigger_count + 1) + /* count foreign-key constraints (there are none if no triggers) */ + if (tableinfo.triggers) + { + printfPQExpBuffer(&buf, + "SELECT conname,\n" + " pg_catalog.pg_get_constraintdef(oid) as condef\n" + "FROM pg_catalog.pg_constraint r\n" + "WHERE r.conrelid = '%s' AND r.contype = 'f'", + oid); + result5 = PSQLexec(buf.data); + if (!result5) + goto error_return; + else + foreignkey_count = PQntuples(result5); + } + + footers = xmalloc((index_count + check_count + rule_count + trigger_count + foreignkey_count + 1) * sizeof(*footers)); /* print indexes */ @@ -1041,6 +1064,27 @@ describeOneTableDetails(const char *schemaname, footers[count_footers++] = xstrdup(buf.data); } + /* print foreign key constraints */ + for (i = 0; i < foreignkey_count; i++) + { + char *s = _("Foreign Key constraints"); + + if (i == 0) + printfPQExpBuffer(&buf, _("%s: %s %s"), + s, + PQgetvalue(result5, i, 0), + PQgetvalue(result5, i, 1)); + else + printfPQExpBuffer(&buf, _("%*s %s %s"), + (int) strlen(s), "", + PQgetvalue(result5, i, 0), + PQgetvalue(result5, i, 1)); + if (i < foreignkey_count - 1) + appendPQExpBuffer(&buf, ","); + + footers[count_footers++] = xstrdup(buf.data); + } + /* print rules */ for (i = 0; i < rule_count; i++) { @@ -1078,6 +1122,7 @@ describeOneTableDetails(const char *schemaname, PQclear(result2); PQclear(result3); PQclear(result4); + PQclear(result5); } printTable(title.data, headers, diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 8f06566e77..62c73a4b61 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: catversion.h,v 1.148 2002/08/13 20:40:44 momjian Exp $ + * $Id: catversion.h,v 1.149 2002/08/16 23:01:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200208131 +#define CATALOG_VERSION_NO 200208161 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 0c0a1a7bde..2e71bfb07f 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_proc.h,v 1.254 2002/08/15 02:51:27 momjian Exp $ + * $Id: pg_proc.h,v 1.255 2002/08/16 23:01:20 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2174,6 +2174,8 @@ DATA(insert OID = 1642 ( pg_get_userbyid PGNSP PGUID 12 f f t f s 1 19 "23" DESCR("user name by UID (with fallback)"); DATA(insert OID = 1643 ( pg_get_indexdef PGNSP PGUID 12 f f t f s 1 25 "26" pg_get_indexdef - _null_ )); DESCR("index description"); +DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 f f t f s 1 25 "26" pg_get_constraintdef - _null_ )); +DESCR("constraint description"); DATA(insert OID = 1716 ( pg_get_expr PGNSP PGUID 12 f f t f s 2 25 "25 26" pg_get_expr - _null_ )); DESCR("deparse an encoded expression"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 621ab80315..3588673c2a 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: builtins.h,v 1.191 2002/08/15 02:51:27 momjian Exp $ + * $Id: builtins.h,v 1.192 2002/08/16 23:01:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -375,6 +375,7 @@ extern Datum pg_get_ruledef(PG_FUNCTION_ARGS); extern Datum pg_get_viewdef(PG_FUNCTION_ARGS); extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS); extern Datum pg_get_indexdef(PG_FUNCTION_ARGS); +extern Datum pg_get_constraintdef(PG_FUNCTION_ARGS); extern Datum pg_get_userbyid(PG_FUNCTION_ARGS); extern Datum pg_get_expr(PG_FUNCTION_ARGS); extern char *deparse_expression(Node *expr, List *dpcontext, -- 2.40.0