Re: [GENERAL] large object does not exist after pg_migrator - Mailing list pgsql-hackers

Bruce Momjian wrote:
> > After a quick chat with Bruce it was determined that we don't freeze
> > anything (it would be horrid for downtime if we did so in pg_migrator;
> > and it would be useless if ran anywhere else).  What we do is migrate
> > pg_clog from the old cluster to the new.  So never mind that hypothesis.
> >
> > Bruce noticed that the pg_dump/pg_migrator combo is failing to restore
> > pg_largeobject's relfrozenxid.  We're not sure how this is causing the
> > errors Jamie is seeing, because what I think should happen is that scans
> > of the table should fail with failures to open pg_clog files
> > such-and-such, but not missing tuples ...
>
> Jamie, is it possible for you to apply the attached patch to the 8.4
> server, install the new pg_dump, and run the test again to see if
> pg_largeobject is fixed?  This patch properly sets the relfrozenxid in
> the system tables for each database.

I have applied the attached patch to have pg_dump restore
pg_largeobject.relfrozenxid in binary upgrade mode;   backpatched to
8.4.X.

This doesn't fix the reported problem, but it is still a bug.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.540
diff -c -c -r1.540 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    2 Jul 2009 21:34:32 -0000    1.540
--- src/bin/pg_dump/pg_dump.c    20 Jul 2009 20:51:34 -0000
***************
*** 34,39 ****
--- 34,40 ----
  #include "access/sysattr.h"
  #include "catalog/pg_cast.h"
  #include "catalog/pg_class.h"
+ #include "catalog/pg_largeobject.h"
  #include "catalog/pg_proc.h"
  #include "catalog/pg_trigger.h"
  #include "catalog/pg_type.h"
***************
*** 1739,1744 ****
--- 1740,1746 ----
                            frozenxid);
          appendStringLiteralAH(creaQry, datname, AH);
          appendPQExpBuffer(creaQry, ";\n");
+
      }

      appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
***************
*** 1764,1769 ****
--- 1766,1816 ----
                   NULL,            /* Dumper */
                   NULL);            /* Dumper Arg */

+     /*
+      *    pg_largeobject comes from the old system intact, so set
+      *    its relfrozenxid.
+      */
+     if (binary_upgrade)
+     {
+         PGresult   *lo_res;
+         PQExpBuffer loFrozenQry = createPQExpBuffer();
+         PQExpBuffer loOutQry = createPQExpBuffer();
+         int            i_relfrozenxid;
+
+         appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid\n"
+                             "FROM pg_catalog.pg_class\n"
+                             "WHERE oid = %d;\n",
+                             LargeObjectRelationId);
+
+         lo_res = PQexec(g_conn, loFrozenQry->data);
+         check_sql_result(lo_res, g_conn, loFrozenQry->data, PGRES_TUPLES_OK);
+
+         if (PQntuples(lo_res) != 1)
+         {
+             write_msg(NULL, "dumpDatabase(): could not find pg_largeobject.relfrozenxid\n");
+             exit_nicely();
+         }
+
+         i_relfrozenxid = PQfnumber(lo_res, "relfrozenxid");
+
+         appendPQExpBuffer(loOutQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid.\n");
+         appendPQExpBuffer(loOutQry, "UPDATE pg_catalog.pg_class\n"
+                           "SET relfrozenxid = '%u'\n"
+                           "WHERE oid = %d;\n",
+                           atoi(PQgetvalue(lo_res, 0, i_relfrozenxid)),
+                           LargeObjectRelationId);
+         ArchiveEntry(AH, nilCatalogId, createDumpId(),
+                      "pg_largeobject", NULL, NULL, "",
+                      false, "pg_largeobject", SECTION_PRE_DATA,
+                      loOutQry->data, "", NULL,
+                      NULL, 0,
+                      NULL, NULL);
+
+         PQclear(lo_res);
+         destroyPQExpBuffer(loFrozenQry);
+         destroyPQExpBuffer(loOutQry);
+     }
+
      /* Dump DB comment if any */
      if (g_fout->remoteVersion >= 80200)
      {