Re: Bug in pg_dump/restore -o - Mailing list pgsql-hackers

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am told this was broken in 7.1 too.

Good point.  That probably means it's not a "must fix" for 7.2, but
still, it'd be nice to have a solution.

Attached is a diff that seems to actually work in all three pgdump modes
(script output, -Fc, -Ft).  A disadvantage of this approach is that
there doesn't seem to be any clean way to issue a DROP for the temp
table, so it will persist in the target database until we finish the
psql script or pg_restore run (or at least until we have to do a
\connect).  This would create a problem if the name pgdump_oid
conflicted with any actual table name being restored.  We could choose
some more bizarre name to reduce the probability of such a conflict.

A potentially more serious problem is that if the archiving code chooses
to issue other operations between the schema restore and data restore
for the temp table, we might do a \connect and lose the temp table.
Come to think of it, a data-only restore request won't work either.

I had originally tried a single data-only archive entry with copy
command CREATE... COPY.  This would avoid the latter two problems.
However, while it seemed to work okay in -Fc mode, -Ft blew up:

$ pg_dump -Ft -b -o test >/tmp/dump.tar
pg_dump: [tar archiver] bad COPY statement - could not find "copy" in string "cr
eate temporary table pgdump_oid (dummy int4);
copy pgdump_oid with oids from stdin;

Philip, is this a fundamental problem, or do we just need to make
the tar archiver a little smarter about looking at the COPY strings?

            regards, tom lane
*** src/bin/pg_dump/pg_dump.c.orig    Fri Jan 11 18:21:55 2002
--- src/bin/pg_dump/pg_dump.c    Thu Jan 17 20:51:50 2002
***************
*** 89,94 ****
--- 89,95 ----
  static Oid    findLastBuiltinOid_V71(const char *);
  static Oid    findLastBuiltinOid_V70(void);
  static void setMaxOid(Archive *fout);
+ static int    setMaxOid_dumper(Archive *fout, char *oid, void *dctxv);

  static void AddAcl(char *aclbuf, const char *keyword);
  static char *GetPrivileges(Archive *AH, const char *s);
***************
*** 4538,4552 ****

  /*
   * setMaxOid -
!  * find the maximum oid and generate a COPY statement to set it
! */

  static void
  setMaxOid(Archive *fout)
  {
      PGresult   *res;
      Oid            max_oid;
-     char        sql[1024];

      res = PQexec(g_conn, "CREATE TEMPORARY TABLE pgdump_oid (dummy int4)");
      if (!res ||
--- 4539,4568 ----

  /*
   * setMaxOid -
!  * find the maximum oid and generate commands to reproduce it in the target
!  */

  static void
  setMaxOid(Archive *fout)
  {
+     ArchiveEntry(fout, "0", "Max OID",
+                  "<Init>", NULL,
+                  "CREATE TEMPORARY TABLE pgdump_oid (dummy int4);\n",
+                  "", "", "",
+                  NULL, NULL);
+
+     ArchiveEntry(fout, "0", "Max OID",
+                  "<Init Data>", NULL, "", "",
+                  "COPY pgdump_oid WITH OIDS FROM stdin;\n",
+                  "",
+                  setMaxOid_dumper, NULL);
+ }
+
+ static int
+ setMaxOid_dumper(Archive *fout, char *oid, void *dctxv)
+ {
      PGresult   *res;
      Oid            max_oid;

      res = PQexec(g_conn, "CREATE TEMPORARY TABLE pgdump_oid (dummy int4)");
      if (!res ||
***************
*** 4578,4594 ****
          exit_nicely();
      }
      PQclear(res);
      if (g_verbose)
          write_msg(NULL, "maximum system oid is %u\n", max_oid);
-     snprintf(sql, 1024,
-              "CREATE TEMPORARY TABLE pgdump_oid (dummy int4);\n"
-              "COPY pgdump_oid WITH OIDS FROM stdin;\n"
-              "%u\t0\n"
-              "\\.\n"
-              "DROP TABLE pgdump_oid;\n",
-              max_oid);

!     ArchiveEntry(fout, "0", "Max OID", "<Init>", NULL, sql, "", "", "", NULL, NULL);
  }

  /*
--- 4594,4609 ----
          exit_nicely();
      }
      PQclear(res);
+
      if (g_verbose)
          write_msg(NULL, "maximum system oid is %u\n", max_oid);

!     archprintf(fout,
!                "%u\t0\n"
!                "\\.\n",
!                max_oid);
!
!     return 1;
  }

  /*