Re: [BUGS] Re: [HACKERS] pg_dump error - Mailing list pgsql-hackers

I (thought I) forwarded fixes for the pg_dump constraint syntax
bug to this list a couple of weeks ago.  I added a -c (compatible)
switch to pg_dump to force it to dump constraints in a syntax that
pgsql can understand.

Here's another copy of the diffs (against 6.3.2).

ccb

----------------
*** /usr/local/src/pgsql/6.3.2/src/bin/pg_dump/pg_dump.c    Thu Apr  9 19:02:24 1998
--- ./pg_dump.c    Tue Jun  9 14:27:36 1998
***************
*** 110,115 ****
--- 110,116 ----
  int            attrNames;            /* put attr names into insert strings */
  int            schemaOnly;
  int            dataOnly;
+ int                     compatConstraint;

  char        g_opaque_type[10];    /* name for the opaque type */

***************
*** 126,131 ****
--- 127,134 ----
      fprintf(stderr,
              "\t -a          \t\t dump out only the data, no schema\n");
      fprintf(stderr,
+                 "\t -c          \t\t generate pgsql-compatible CONSTRAINT syntax\n");
+     fprintf(stderr,
              "\t -d          \t\t dump data as proper insert strings\n");
      fprintf(stderr,
        "\t -D          \t\t dump data as inserts with attribute names\n");
***************
*** 551,567 ****
      g_comment_end[0] = '\0';
      strcpy(g_opaque_type, "opaque");

!     dataOnly = schemaOnly = dumpData = attrNames = 0;

      progname = *argv;

!     while ((c = getopt(argc, argv, "adDf:h:op:st:vzu")) != EOF)
      {
          switch (c)
          {
              case 'a':            /* Dump data only */
                  dataOnly = 1;
                  break;
              case 'd':            /* dump data as proper insert strings */
                  dumpData = 1;
                  break;
--- 554,574 ----
      g_comment_end[0] = '\0';
      strcpy(g_opaque_type, "opaque");

!     compatConstraint = dataOnly = schemaOnly = dumpData = attrNames = 0;

      progname = *argv;

!     while ((c = getopt(argc, argv, "acdDf:h:op:st:vzu")) != EOF)
      {
          switch (c)
          {
              case 'a':            /* Dump data only */
                  dataOnly = 1;
                  break;
+                 case 'c':                       /* generate constraint syntax that
+                                 can be read back into postgreSQL */
+                     compatConstraint = 1;
+                 break;
              case 'd':            /* dump data as proper insert strings */
                  dumpData = 1;
                  break;
***************
*** 1496,1502 ****
                  query[0] = 0;
                  if (name[0] != '$')
                      sprintf(query, "CONSTRAINT %s ", name);
!                 sprintf(query, "%sCHECK %s", query, expr);
                  tblinfo[i].check_expr[i2] = strdup(query);
              }
              PQclear(res2);
--- 1503,1514 ----
                  query[0] = 0;
                  if (name[0] != '$')
                      sprintf(query, "CONSTRAINT %s ", name);
!                 if( compatConstraint ) {
!                   sprintf(query, "%sCHECK (%s)", query, expr);
!                 }
!                 else {
!                   sprintf(query, "%sCHECK %s", query, expr);
!                 }
                  tblinfo[i].check_expr[i2] = strdup(query);
              }
              PQclear(res2);
***************
*** 2518,2523 ****
--- 2530,2546 ----
                  }
              }

+             if( compatConstraint ) {
+                 /* put the CONSTRAINTS inside the table def */
+                 for (k = 0; k < tblinfo[i].ncheck; k++)
+                 {
+                     sprintf(q, "%s%s %s",
+                         q,
+                         (actual_atts + k > 0) ? ", " : "",
+                         tblinfo[i].check_expr[k]);
+                 }
+             }
+
              strcat(q, ")");

              if (numParents > 0)
***************
*** 2533,2540 ****
                  strcat(q, ")");
              }

!             if (tblinfo[i].ncheck > 0)
              {
                  for (k = 0; k < tblinfo[i].ncheck; k++)
                  {
                      sprintf(q, "%s%s %s",
--- 2556,2564 ----
                  strcat(q, ")");
              }

!             if( !compatConstraint )
              {
+                 /* put the CONSTRAINT defs outside the table def */
                  for (k = 0; k < tblinfo[i].ncheck; k++)
                  {
                      sprintf(q, "%s%s %s",
***************
*** 2543,2548 ****
--- 2567,2573 ----
                              tblinfo[i].check_expr[k]);
                  }
              }
+
              strcat(q, ";\n");
              fputs(q, fout);
              if (acls)