Re: pgsql: Use TRUNCATE to preserve relfilenode for pg_largeobject + index.

On Fri, Jul 29, 2022 at 1:46 AM Robert Haas <rhaas(at)postgresql(dot)org> wrote:
>
> Use TRUNCATE to preserve relfilenode for pg_largeobject + index.
>
> Commit 9a974cbcba005256a19991203583a94b4f9a21a9 arranged to preserve
> the relfilenode of user tables across pg_upgrade, but failed to notice
> that pg_upgrade treats pg_largeobject as a user table and thus it needs
> the same treatment. Otherwise, large objects will appear to vanish
> after a pg_upgrade.
>
> Commit d498e052b4b84ae21b3b68d5b3fda6ead65d1d4d fixed this problem
> by teaching pg_dump to UPDATE pg_class.relfilenode for pg_largeobject
> and its index. However, because an UPDATE on the catalog rows doesn't
> change anything on disk, this can leave stray files behind in the new
> cluster. They will normally be empty, but it's a little bit untidy.
>
> Hence, this commit arranges to do the same thing using DDL. Specifically,
> it makes TRUNCATE work for the pg_largeobject catalog when in
> binary-upgrade mode, and it then uses that command in binary-upgrade
> dumps as a way of setting pg_class.relfilenode for pg_largeobject and
> its index. That way, the old files are removed from the new cluster.

@ -3167,15 +3168,36 @@ dumpDatabase(Archive *fout)

....
appendPQExpBuffer(loOutQry, "UPDATE pg_catalog.pg_class\n"
- "SET relfrozenxid = '%u', relminmxid = '%u', relfilenode = '%u'\n"
+ "SET relfrozenxid = '%u', relminmxid = '%u'\n"
"WHERE oid = %u;\n",
atooid(PQgetvalue(lo_res, i, i_relfrozenxid)),
atooid(PQgetvalue(lo_res, i, i_relminmxid)),
- atooid(PQgetvalue(lo_res, i, i_relfilenode)),
- atooid(PQgetvalue(lo_res, i, i_oid)));
+ atooid(PQgetvalue(lo_res, i, i_relfilenode)));

Is this correct? I mean why are we updating only those tuples where
oid= i_relfilenode? I think it should use
atooid(PQgetvalue(lo_res, i, i_oid) in where clause not
atooid(PQgetvalue(lo_res, i, i_relfilenode)? Or am I missing
something?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com