NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commitd498e05

committed

Preserve relfilenode of pg_largeobject and its index across pg_upgrade.

Commit9a974cb did this for usertables, but pg_upgrade treats pg_largeobject as a user table, and soneeds the same treatment. Without this fix, if you rewrite thepg_largeobject table and then perform an upgrade with pg_upgrade, thetable will apparently be empty on the new cluster, while all of yourobjects will end up with an orphaned file.With this fix, instead of the old cluster's pg_largeobject files endingup orphaned, the original files fro the new cluster do. That's mostlyharmless because we expect the table to be empty, but we might wantto arrange to remove the as part of the upgrade. Since we're stilldebating the best way of doing that, I (rhaas) have decided to postponedealing with that problem and get the basic fix committed.Justin Pryzby, reviewed by Shruthi Gowda and by me.Discussion:http://postgr.es/m/20220701231413.GI13040@telsasoft.com

1 parentbf1f4a3 commitd498e05Copy full SHA for d498e05

File tree

2 files changed

+24

-15

lines changed

src/bin
- pg_dump
  - pg_dump.c
- pg_upgrade/t
  - 002_pg_upgrade.pl

2 files changed

+24

-15

lines changed

`‎src/bin/pg_dump/pg_dump.c`

Lines changed: 22 additions & 15 deletions

Original file line number	Diff line number	Diff line change
`@@ -3134,42 +3134,49 @@ dumpDatabase(Archive *fout)`
`3134`	`3134`
`3135`	`3135`	`/*`
`3136`	`3136`	`* pg_largeobject comes from the old system intact, so set its`
`3137`		`- * relfrozenxidsandrelminmxids.`
	`3137`	`+ * relfrozenxids, relminmxidsandrelfilenode.`
`3138`	`3138`	`*/`
`3139`	`3139`	`if (dopt->binary_upgrade)`
`3140`	`3140`	`{`
`3141`	`3141`	`PGresult *lo_res;`
`3142`	`3142`	`PQExpBuffer loFrozenQry = createPQExpBuffer();`
`3143`	`3143`	`PQExpBuffer loOutQry = createPQExpBuffer();`
`3144`	`3144`	`inti_relfrozenxid,`
	`3145`	`+i_relfilenode,`
	`3146`	`+i_oid,`
`3145`	`3147`	`i_relminmxid;`
`3146`	`3148`
`3147`	`3149`	`/*`
`3148`	`3150`	`* pg_largeobject`
`3149`	`3151`	`*/`
`3150`	`3152`	`if (fout->remoteVersion >= 90300)`
`3151`		`-appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid\n"`
	`3153`	`+appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid, relfilenode, oid\n"`
`3152`	`3154`	`"FROM pg_catalog.pg_class\n"`
`3153`		`- "WHERE oid= %u;\n",`
`3154`		`- LargeObjectRelationId);`
	`3155`	`+ "WHERE oidIN (%u, %u);\n",`
	`3156`	`+ LargeObjectRelationId, LargeObjectLOidPNIndexId);`
`3155`	`3157`	`else`
`3156`		`-appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, 0 AS relminmxid\n"`
	`3158`	`+appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, 0 AS relminmxid, relfilenode, oid\n"`
`3157`	`3159`	`"FROM pg_catalog.pg_class\n"`
`3158`		`- "WHERE oid= %u;\n",`
`3159`		`- LargeObjectRelationId);`
	`3160`	`+ "WHERE oidIN (%u, %u);\n",`
	`3161`	`+ LargeObjectRelationId, LargeObjectLOidPNIndexId);`
`3160`	`3162`
`3161`		`-lo_res =ExecuteSqlQueryForSingleRow(fout, loFrozenQry->data);`
	`3163`	`+lo_res =ExecuteSqlQuery(fout, loFrozenQry->data, PGRES_TUPLES_OK);`
`3162`	`3164`
`3163`	`3165`	`i_relfrozenxid = PQfnumber(lo_res, "relfrozenxid");`
`3164`	`3166`	`i_relminmxid = PQfnumber(lo_res, "relminmxid");`
	`3167`	`+i_relfilenode = PQfnumber(lo_res, "relfilenode");`
	`3168`	`+i_oid = PQfnumber(lo_res, "oid");`
	`3169`	`+`
	`3170`	`+appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, preserve values for pg_largeobject and its index\n");`
	`3171`	`+for (int i = 0; i < PQntuples(lo_res); ++i)`
	`3172`	`+appendPQExpBuffer(loOutQry, "UPDATE pg_catalog.pg_class\n"`
	`3173`	`+ "SET relfrozenxid = '%u', relminmxid = '%u', relfilenode = '%u'\n"`
	`3174`	`+ "WHERE oid = %u;\n",`
	`3175`	`+ atooid(PQgetvalue(lo_res, i, i_relfrozenxid)),`
	`3176`	`+ atooid(PQgetvalue(lo_res, i, i_relminmxid)),`
	`3177`	`+ atooid(PQgetvalue(lo_res, i, i_relfilenode)),`
	`3178`	`+ atooid(PQgetvalue(lo_res, i, i_oid)));`
`3165`	`3179`
`3166`		`-appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid\n");`
`3167`		`-appendPQExpBuffer(loOutQry, "UPDATE pg_catalog.pg_class\n"`
`3168`		`- "SET relfrozenxid = '%u', relminmxid = '%u'\n"`
`3169`		`- "WHERE oid = %u;\n",`
`3170`		`- atooid(PQgetvalue(lo_res, 0, i_relfrozenxid)),`
`3171`		`- atooid(PQgetvalue(lo_res, 0, i_relminmxid)),`
`3172`		`- LargeObjectRelationId);`
`3173`	`3180`	`ArchiveEntry(fout, nilCatalogId, createDumpId(),`
`3174`	`3181`	`ARCHIVE_OPTS(.tag = "pg_largeobject",`
`3175`	`3182`	`.description = "pg_largeobject",`

`‎src/bin/pg_upgrade/t/002_pg_upgrade.pl`

Lines changed: 2 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -208,6 +208,8 @@ sub generate_db`
`208`	`208`	`}`
`209`	`209`	`}`
`210`	`210`
	`211`	`+$oldnode->safe_psql("regression","VACUUM FULL pg_largeobject;");`
	`212`	`+`
`211`	`213`	`# In a VPATH build, we'll be started in the source directory, but we want`
`212`	`214`	`# to run pg_upgrade in the build directory so that any files generated finish`
`213`	`215`	`# in it, like delete_old_cluster.{sh,bat}.`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commitd498e05

File tree

2 files changed

2 files changed

`‎src/bin/pg_dump/pg_dump.c`

`‎src/bin/pg_upgrade/t/002_pg_upgrade.pl`

0 commit comments