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

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > > > Jamie Fox wrote:
> > >
> > > > > I can also see that the pg_largeobject table is different, in the pg_restore
> > > > > version the Rows (estimated) is 316286 and Rows (counted) is the same, in
> > > > > the pg_migrator version the Rows (counted) is only 180507.
> > >
> > > > Wow, I didn't test large objects specifically, and I am confused why
> > > > there would be a count discrepancy. I will need to do some research
> > > > unless someone else can guess about the cause.
> > >
> > > Maybe pg_largeobject is not getting frozen?
> >
> > That would explain the change in count, but I thought we froze
> > _everything_, and had to.
>
> 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.

--
  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    14 Jul 2009 02:58:32 -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"
***************
*** 1732,1737 ****
--- 1733,1743 ----

      if (binary_upgrade)
      {
+         PGresult   *lo_res;
+         PQExpBuffer loFrozenQry = createPQExpBuffer();
+         PQExpBuffer loOutQry = createPQExpBuffer();
+         int            i_relfrozenxid;
+
          appendPQExpBuffer(creaQry, "\n-- For binary upgrade, set datfrozenxid.\n");
          appendPQExpBuffer(creaQry, "UPDATE pg_catalog.pg_database\n"
                            "SET datfrozenxid = '%u'\n"
***************
*** 1739,1744 ****
--- 1745,1788 ----
                            frozenxid);
          appendStringLiteralAH(creaQry, datname, AH);
          appendPQExpBuffer(creaQry, ";\n");
+
+         /*
+          *    pg_largeobject comes from the old system intact, so set
+          *    its 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);
      }

      appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",