agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 4/6] wip: stricter validation for 64bit xids
43+ messages / 5 participants
[nested] [flat]
* [PATCH v3 4/6] wip: stricter validation for 64bit xids
@ 2023-01-09 19:25 Andres Freund <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Andres Freund @ 2023-01-09 19:25 UTC (permalink / raw)
64bit xids can't represent xids before xid 0, but 32bit xids can. This has
lead to a number of bugs, some data corrupting. To make it easier to catch
such bugs, disallow 64bit where the upper 32bit are all set, which is what one
gets when naively trying to convert such 32bit xids. Additionally explicitly
disallow 64bit xids that are already aren't allowed because they'd map to
special 32bit xids.
This changes the input routines for 64bit xids to error out in more cases.
Discussion: https://postgr.es/m/[email protected]
---
src/include/access/transam.h | 136 ++++++++++++++++++++++++++++--
src/backend/utils/adt/xid.c | 7 ++
src/backend/utils/adt/xid8funcs.c | 17 ++--
src/test/regress/expected/xid.out | 44 ++++++++--
src/test/regress/sql/xid.sql | 12 ++-
5 files changed, 192 insertions(+), 24 deletions(-)
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index f5af6d30556..0d4bf57e63a 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -44,15 +44,6 @@
#define TransactionIdStore(xid, dest) (*(dest) = (xid))
#define StoreInvalidTransactionId(dest) (*(dest) = InvalidTransactionId)
-#define EpochFromFullTransactionId(x) ((uint32) ((x).value >> 32))
-#define XidFromFullTransactionId(x) ((uint32) (x).value)
-#define U64FromFullTransactionId(x) ((x).value)
-#define FullTransactionIdEquals(a, b) ((a).value == (b).value)
-#define FullTransactionIdPrecedes(a, b) ((a).value < (b).value)
-#define FullTransactionIdPrecedesOrEquals(a, b) ((a).value <= (b).value)
-#define FullTransactionIdFollows(a, b) ((a).value > (b).value)
-#define FullTransactionIdFollowsOrEquals(a, b) ((a).value >= (b).value)
-#define FullTransactionIdIsValid(x) TransactionIdIsValid(XidFromFullTransactionId(x))
#define InvalidFullTransactionId FullTransactionIdFromEpochAndXid(0, InvalidTransactionId)
#define FirstNormalFullTransactionId FullTransactionIdFromEpochAndXid(0, FirstNormalTransactionId)
#define FullTransactionIdIsNormal(x) FullTransactionIdFollowsOrEquals(x, FirstNormalFullTransactionId)
@@ -61,12 +52,127 @@
* A 64 bit value that contains an epoch and a TransactionId. This is
* wrapped in a struct to prevent implicit conversion to/from TransactionId.
* Not all values represent valid normal XIDs.
+ *
+ * 64bit xids that would map to a non-normal 32bit xid are not allowed, for
+ * obvious reasons.
+
+ * In addition, no xids with all the upper 32bit sets are allowed to exist,
+ * this is to make it easier to catch conversion errors from 32bit xids (which
+ * can point to "before" xid 0).
*/
typedef struct FullTransactionId
{
uint64 value;
} FullTransactionId;
+#define MAX_FULL_TRANSACTION_ID ((((uint64)~(uint32)0)<<32) - 1)
+
+/*
+ * This is separate from FullTransactionIdValidRange() so that input routines
+ * can check for invalid values without triggering an assert.
+ */
+static inline bool
+InFullTransactionIdRange(uint64 val)
+{
+ /* 64bit xid above the upper bound */
+ if (val > MAX_FULL_TRANSACTION_ID)
+ return false;
+
+ /*
+ * normal 64bit xids that'd map to special 32 xids aren't allowed.
+ */
+ if (val >= (uint64) FirstNormalTransactionId &&
+ (uint32) val < FirstNormalTransactionId)
+ return false;
+ return true;
+}
+
+/*
+ * Validation routine, typically used in asserts.
+ */
+static inline bool
+FullTransactionIdValidRange(FullTransactionId x)
+{
+ return InFullTransactionIdRange(x.value);
+}
+
+static inline uint64
+U64FromFullTransactionId(FullTransactionId x)
+{
+ Assert(FullTransactionIdValidRange(x));
+
+ return x.value;
+}
+
+static inline TransactionId
+XidFromFullTransactionId(FullTransactionId x)
+{
+ Assert(FullTransactionIdValidRange(x));
+
+ return (TransactionId) x.value;
+}
+
+static inline uint32
+EpochFromFullTransactionId(FullTransactionId x)
+{
+ Assert(FullTransactionIdValidRange(x));
+
+ return (uint32) (x.value >> 32);
+}
+
+static inline bool
+FullTransactionIdEquals(FullTransactionId a, FullTransactionId b)
+{
+ Assert(FullTransactionIdValidRange(a));
+ Assert(FullTransactionIdValidRange(b));
+
+ return a.value == b.value;
+}
+
+static inline bool
+FullTransactionIdPrecedes(FullTransactionId a, FullTransactionId b)
+{
+ Assert(FullTransactionIdValidRange(a));
+ Assert(FullTransactionIdValidRange(b));
+
+ return a.value < b.value;
+}
+
+static inline bool
+FullTransactionIdPrecedesOrEquals(FullTransactionId a, FullTransactionId b)
+{
+ Assert(FullTransactionIdValidRange(a));
+ Assert(FullTransactionIdValidRange(b));
+
+ return a.value <= b.value;
+}
+
+static inline bool
+FullTransactionIdFollows(FullTransactionId a, FullTransactionId b)
+{
+ Assert(FullTransactionIdValidRange(a));
+ Assert(FullTransactionIdValidRange(b));
+
+ return a.value > b.value;
+}
+
+static inline bool
+FullTransactionIdFollowsOrEquals(FullTransactionId a, FullTransactionId b)
+{
+ Assert(FullTransactionIdValidRange(a));
+ Assert(FullTransactionIdValidRange(b));
+
+ return a.value >= b.value;
+}
+
+static inline bool
+FullTransactionIdIsValid(FullTransactionId x)
+{
+ Assert(FullTransactionIdValidRange(x));
+
+ return x.value != (uint64) InvalidTransactionId;
+}
+
static inline FullTransactionId
FullTransactionIdFromEpochAndXid(uint32 epoch, TransactionId xid)
{
@@ -74,6 +180,8 @@ FullTransactionIdFromEpochAndXid(uint32 epoch, TransactionId xid)
result.value = ((uint64) epoch) << 32 | xid;
+ Assert(FullTransactionIdValidRange(result));
+
return result;
}
@@ -84,6 +192,8 @@ FullTransactionIdFromU64(uint64 value)
result.value = value;
+ Assert(FullTransactionIdValidRange(result));
+
return result;
}
@@ -102,6 +212,8 @@ FullTransactionIdFromU64(uint64 value)
static inline void
FullTransactionIdRetreat(FullTransactionId *dest)
{
+ Assert(FullTransactionIdValidRange(*dest));
+
dest->value--;
/*
@@ -118,6 +230,8 @@ FullTransactionIdRetreat(FullTransactionId *dest)
*/
while (XidFromFullTransactionId(*dest) < FirstNormalTransactionId)
dest->value--;
+
+ Assert(FullTransactionIdValidRange(*dest));
}
/*
@@ -127,6 +241,8 @@ FullTransactionIdRetreat(FullTransactionId *dest)
static inline void
FullTransactionIdAdvance(FullTransactionId *dest)
{
+ Assert(FullTransactionIdValidRange(*dest));
+
dest->value++;
/* see FullTransactionIdAdvance() */
@@ -135,6 +251,8 @@ FullTransactionIdAdvance(FullTransactionId *dest)
while (XidFromFullTransactionId(*dest) < FirstNormalTransactionId)
dest->value++;
+
+ Assert(FullTransactionIdValidRange(*dest));
}
/* back up a transaction ID variable, handling wraparound correctly */
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
index 8ac1679c381..21c0f4c67df 100644
--- a/src/backend/utils/adt/xid.c
+++ b/src/backend/utils/adt/xid.c
@@ -188,6 +188,13 @@ xid8in(PG_FUNCTION_ARGS)
uint64 result;
result = uint64in_subr(str, NULL, "xid8", fcinfo->context);
+
+ if (!InFullTransactionIdRange(result))
+ ereturn(fcinfo->context, 0,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value \"%s\" is out of range for type %s",
+ str, "xid8")));
+
PG_RETURN_FULLTRANSACTIONID(FullTransactionIdFromU64(result));
}
diff --git a/src/backend/utils/adt/xid8funcs.c b/src/backend/utils/adt/xid8funcs.c
index e616303a292..8b33d145a36 100644
--- a/src/backend/utils/adt/xid8funcs.c
+++ b/src/backend/utils/adt/xid8funcs.c
@@ -302,15 +302,18 @@ parse_snapshot(const char *str, Node *escontext)
const char *str_start = str;
char *endp;
StringInfo buf;
+ uint64 raw_fxid;
- xmin = FullTransactionIdFromU64(strtou64(str, &endp, 10));
- if (*endp != ':')
+ raw_fxid = strtou64(str, &endp, 10);
+ if (*endp != ':' || !InFullTransactionIdRange(raw_fxid))
goto bad_format;
+ xmin = FullTransactionIdFromU64(raw_fxid);
str = endp + 1;
- xmax = FullTransactionIdFromU64(strtou64(str, &endp, 10));
- if (*endp != ':')
+ raw_fxid = strtou64(str, &endp, 10);
+ if (*endp != ':' || !InFullTransactionIdRange(raw_fxid))
goto bad_format;
+ xmax = FullTransactionIdFromU64(raw_fxid);
str = endp + 1;
/* it should look sane */
@@ -326,7 +329,11 @@ parse_snapshot(const char *str, Node *escontext)
while (*str != '\0')
{
/* read next value */
- val = FullTransactionIdFromU64(strtou64(str, &endp, 10));
+ raw_fxid = strtou64(str, &endp, 10);
+
+ val = FullTransactionIdFromU64(raw_fxid);
+ if (!InFullTransactionIdRange(raw_fxid))
+ goto bad_format;
str = endp;
/* require the input to be in order */
diff --git a/src/test/regress/expected/xid.out b/src/test/regress/expected/xid.out
index e62f7019434..4348eb66bd5 100644
--- a/src/test/regress/expected/xid.out
+++ b/src/test/regress/expected/xid.out
@@ -6,11 +6,11 @@ select '010'::xid,
'-1'::xid,
'010'::xid8,
'42'::xid8,
- '0xffffffffffffffff'::xid8,
- '-1'::xid8;
- xid | xid | xid | xid | xid8 | xid8 | xid8 | xid8
------+-----+------------+------------+------+------+----------------------+----------------------
- 8 | 42 | 4294967295 | 4294967295 | 8 | 42 | 18446744073709551615 | 18446744073709551615
+ '0xefffffffffffffff'::xid8,
+ '0'::xid8;
+ xid | xid | xid | xid | xid8 | xid8 | xid8 | xid8
+-----+-----+------------+------------+------+------+----------------------+------
+ 8 | 42 | 4294967295 | 4294967295 | 8 | 42 | 17293822569102704639 | 0
(1 row)
-- garbage values
@@ -30,6 +30,18 @@ select 'asdf'::xid8;
ERROR: invalid input syntax for type xid8: "asdf"
LINE 1: select 'asdf'::xid8;
^
+select '-1'::xid8;
+ERROR: value "-1" is out of range for type xid8
+LINE 1: select '-1'::xid8;
+ ^
+select '0xffffffffffffffff'::xid8;
+ERROR: value "0xffffffffffffffff" is out of range for type xid8
+LINE 1: select '0xffffffffffffffff'::xid8;
+ ^
+select '0x0000300000000001'::xid8;
+ERROR: value "0x0000300000000001" is out of range for type xid8
+LINE 1: select '0x0000300000000001'::xid8;
+ ^
-- Also try it with non-error-throwing API
SELECT pg_input_is_valid('42', 'xid');
pg_input_is_valid
@@ -67,6 +79,24 @@ SELECT pg_input_error_message('0xffffffffffffffffffff', 'xid8');
value "0xffffffffffffffffffff" is out of range for type xid8
(1 row)
+SELECT pg_input_is_valid('-1', 'xid8');
+ pg_input_is_valid
+-------------------
+ f
+(1 row)
+
+SELECT pg_input_is_valid('0xffffffffffffffff', 'xid8');
+ pg_input_is_valid
+-------------------
+ f
+(1 row)
+
+SELECT pg_input_is_valid('0x0000300000000001', 'xid8');
+ pg_input_is_valid
+-------------------
+ f
+(1 row)
+
-- equality
select '1'::xid = '1'::xid;
?column?
@@ -160,11 +190,11 @@ select xid8cmp('1', '2'), xid8cmp('2', '2'), xid8cmp('2', '1');
-- min() and max() for xid8
create table xid8_t1 (x xid8);
-insert into xid8_t1 values ('0'), ('010'), ('42'), ('0xffffffffffffffff'), ('-1');
+insert into xid8_t1 values ('0'), ('010'), ('42'), ('0xefffffffffffffff');
select min(x), max(x) from xid8_t1;
min | max
-----+----------------------
- 0 | 18446744073709551615
+ 0 | 17293822569102704639
(1 row)
-- xid8 has btree and hash opclasses
diff --git a/src/test/regress/sql/xid.sql b/src/test/regress/sql/xid.sql
index b6996588ef6..b2104e4ce20 100644
--- a/src/test/regress/sql/xid.sql
+++ b/src/test/regress/sql/xid.sql
@@ -7,14 +7,17 @@ select '010'::xid,
'-1'::xid,
'010'::xid8,
'42'::xid8,
- '0xffffffffffffffff'::xid8,
- '-1'::xid8;
+ '0xefffffffffffffff'::xid8,
+ '0'::xid8;
-- garbage values
select ''::xid;
select 'asdf'::xid;
select ''::xid8;
select 'asdf'::xid8;
+select '-1'::xid8;
+select '0xffffffffffffffff'::xid8;
+select '0x0000300000000001'::xid8;
-- Also try it with non-error-throwing API
SELECT pg_input_is_valid('42', 'xid');
@@ -23,6 +26,9 @@ SELECT pg_input_error_message('0xffffffffff', 'xid');
SELECT pg_input_is_valid('42', 'xid8');
SELECT pg_input_is_valid('asdf', 'xid8');
SELECT pg_input_error_message('0xffffffffffffffffffff', 'xid8');
+SELECT pg_input_is_valid('-1', 'xid8');
+SELECT pg_input_is_valid('0xffffffffffffffff', 'xid8');
+SELECT pg_input_is_valid('0x0000300000000001', 'xid8');
-- equality
select '1'::xid = '1'::xid;
@@ -51,7 +57,7 @@ select xid8cmp('1', '2'), xid8cmp('2', '2'), xid8cmp('2', '1');
-- min() and max() for xid8
create table xid8_t1 (x xid8);
-insert into xid8_t1 values ('0'), ('010'), ('42'), ('0xffffffffffffffff'), ('-1');
+insert into xid8_t1 values ('0'), ('010'), ('42'), ('0xefffffffffffffff');
select min(x), max(x) from xid8_t1;
-- xid8 has btree and hash opclasses
--
2.38.0
--lxqvgbpizsnpz3zs--
^ permalink raw reply [nested|flat] 43+ messages in thread
* [PATCH v3 1/1] pg_upgrade: Transfer pg_largeobject_metadata's files when possible.
@ 2025-08-14 15:14 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2025-08-14 15:14 UTC (permalink / raw)
Commit 161a3e8b68 taught pg_upgrade to use COPY for large object
metadata for upgrades from v12 and newer, which is much faster to
restore than the proper large object SQL commands. For upgrades
from v16 and newer, we can take this a step further and transfer
the large object metadata files as if they were user tables. We
can't transfer the files from older versions because the aclitem
data type (needed by pg_largeobject_metadata.lomacl) changed its
storage format in v16 (see commit 7b378237aa). Note that this
commit is essentially a revert of commit 12a53c732c, but there are
enough differences that it should be considered a fresh effort.
There are a couple of caveats. First, we still need to COPY the
corresponding pg_shdepend rows for large objects, since those
aren't transferred by anything else. Second, we need to COPY
anything in pg_largeobject_metadata with a comment or security
label, else restoring those will fail. This means that an upgrade
in which every large object has a comment or security label won't
gain anything from this commit, but it should at least avoid making
these unusual use-cases any worse.
pg_upgrade must also take care to transfer the relfilenode of
pg_largeobject_metadata and its index, à la commits d498e052b4 and
bbe08b8869.
---
src/backend/commands/tablecmds.c | 12 ++--
src/bin/pg_dump/pg_dump.c | 80 ++++++++++++++++++----
src/bin/pg_upgrade/Makefile | 3 +-
src/bin/pg_upgrade/info.c | 11 ++-
src/bin/pg_upgrade/pg_upgrade.c | 6 +-
src/bin/pg_upgrade/t/006_transfer_modes.pl | 67 ++++++++++++++++++
6 files changed, 154 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..3be2e051d32 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -42,6 +42,7 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_largeobject.h"
+#include "catalog/pg_largeobject_metadata.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_policy.h"
@@ -2389,12 +2390,15 @@ truncate_check_rel(Oid relid, Form_pg_class reltuple)
/*
* Most system catalogs can't be truncated at all, or at least not unless
* allow_system_table_mods=on. As an exception, however, we allow
- * pg_largeobject to be truncated as part of pg_upgrade, because we need
- * to change its relfilenode to match the old cluster, and allowing a
- * TRUNCATE command to be executed is the easiest way of doing that.
+ * pg_largeobject and pg_largeobject_metadata to be truncated as part of
+ * pg_upgrade, because we need to change its relfilenode to match the old
+ * cluster, and allowing a TRUNCATE command to be executed is the easiest
+ * way of doing that.
*/
if (!allowSystemTableMods && IsSystemClass(relid, reltuple)
- && (!IsBinaryUpgrade || relid != LargeObjectRelationId))
+ && (!IsBinaryUpgrade ||
+ (relid != LargeObjectRelationId &&
+ relid != LargeObjectMetadataRelationId)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied: \"%s\" is a system catalog",
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bea793456f9..b4c45ad803e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1131,6 +1131,23 @@ main(int argc, char **argv)
shdepend->dataObj->filtercond = "WHERE classid = 'pg_largeobject'::regclass "
"AND dbid = (SELECT oid FROM pg_database "
" WHERE datname = current_database())";
+
+ /*
+ * If upgrading from v16 or newer, only dump large objects with
+ * comments/seclabels. For these upgrades, pg_upgrade can copy/link
+ * pg_largeobject_metadata's files (which is usually faster) but we
+ * still need to dump LOs with comments/seclabels here so that the
+ * subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade
+ * can't copy/link the files from older versions because aclitem
+ * (needed by pg_largeobject_metadata.lomacl) changed its storage
+ * format in v16.
+ */
+ if (fout->remoteVersion >= 160000)
+ lo_metadata->dataObj->filtercond = "WHERE oid IN "
+ "(SELECT objoid FROM pg_description "
+ "WHERE classoid = " CppAsString2(LargeObjectRelationId) " "
+ "UNION SELECT objoid FROM pg_seclabel "
+ "WHERE classoid = " CppAsString2(LargeObjectRelationId) ")";
}
/*
@@ -3629,26 +3646,32 @@ dumpDatabase(Archive *fout)
/*
* pg_largeobject comes from the old system intact, so set its
* relfrozenxids, relminmxids and relfilenode.
+ *
+ * pg_largeobject_metadata also comes from the old system intact for
+ * upgrades from v16 and newer, so set its relfrozenxids, relminmxids, and
+ * relfilenode, too. pg_upgrade can't copy/link the files from older
+ * versions because aclitem (needed by pg_largeobject_metadata.lomacl)
+ * changed its storage format in v16.
*/
if (dopt->binary_upgrade)
{
PGresult *lo_res;
PQExpBuffer loFrozenQry = createPQExpBuffer();
PQExpBuffer loOutQry = createPQExpBuffer();
+ PQExpBuffer lomOutQry = createPQExpBuffer();
PQExpBuffer loHorizonQry = createPQExpBuffer();
+ PQExpBuffer lomHorizonQry = createPQExpBuffer();
int ii_relfrozenxid,
ii_relfilenode,
ii_oid,
ii_relminmxid;
- /*
- * pg_largeobject
- */
if (fout->remoteVersion >= 90300)
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
- "WHERE oid IN (%u, %u);\n",
- LargeObjectRelationId, LargeObjectLOidPNIndexId);
+ "WHERE oid IN (%u, %u, %u, %u);\n",
+ LargeObjectRelationId, LargeObjectLOidPNIndexId,
+ LargeObjectMetadataRelationId, LargeObjectMetadataOidIndexId);
else
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, 0 AS relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
@@ -3663,35 +3686,57 @@ dumpDatabase(Archive *fout)
ii_oid = PQfnumber(lo_res, "oid");
appendPQExpBufferStr(loHorizonQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid\n");
+ appendPQExpBufferStr(lomHorizonQry, "\n-- For binary upgrade, set pg_largeobject_metadata relfrozenxid and relminmxid\n");
appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, preserve pg_largeobject and index relfilenodes\n");
+ appendPQExpBufferStr(lomOutQry, "\n-- For binary upgrade, preserve pg_largeobject_metadata and index relfilenodes\n");
for (int i = 0; i < PQntuples(lo_res); ++i)
{
Oid oid;
RelFileNumber relfilenumber;
+ PQExpBuffer horizonQry;
+ PQExpBuffer outQry;
+
+ oid = atooid(PQgetvalue(lo_res, i, ii_oid));
+ relfilenumber = atooid(PQgetvalue(lo_res, i, ii_relfilenode));
- appendPQExpBuffer(loHorizonQry, "UPDATE pg_catalog.pg_class\n"
+ if (oid == LargeObjectRelationId ||
+ oid == LargeObjectLOidPNIndexId)
+ {
+ horizonQry = loHorizonQry;
+ outQry = loOutQry;
+ }
+ else
+ {
+ horizonQry = lomHorizonQry;
+ outQry = lomOutQry;
+ }
+
+ appendPQExpBuffer(horizonQry, "UPDATE pg_catalog.pg_class\n"
"SET relfrozenxid = '%u', relminmxid = '%u'\n"
"WHERE oid = %u;\n",
atooid(PQgetvalue(lo_res, i, ii_relfrozenxid)),
atooid(PQgetvalue(lo_res, i, ii_relminmxid)),
atooid(PQgetvalue(lo_res, i, ii_oid)));
- oid = atooid(PQgetvalue(lo_res, i, ii_oid));
- relfilenumber = atooid(PQgetvalue(lo_res, i, ii_relfilenode));
-
- if (oid == LargeObjectRelationId)
- appendPQExpBuffer(loOutQry,
+ if (oid == LargeObjectRelationId ||
+ oid == LargeObjectMetadataRelationId)
+ appendPQExpBuffer(outQry,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
relfilenumber);
- else if (oid == LargeObjectLOidPNIndexId)
- appendPQExpBuffer(loOutQry,
+ else if (oid == LargeObjectLOidPNIndexId ||
+ oid == LargeObjectMetadataOidIndexId)
+ appendPQExpBuffer(outQry,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
relfilenumber);
}
appendPQExpBufferStr(loOutQry,
"TRUNCATE pg_catalog.pg_largeobject;\n");
+ appendPQExpBufferStr(lomOutQry,
+ "TRUNCATE pg_catalog.pg_largeobject_metadata;\n");
+
appendPQExpBufferStr(loOutQry, loHorizonQry->data);
+ appendPQExpBufferStr(lomOutQry, lomHorizonQry->data);
ArchiveEntry(fout, nilCatalogId, createDumpId(),
ARCHIVE_OPTS(.tag = "pg_largeobject",
@@ -3699,11 +3744,20 @@ dumpDatabase(Archive *fout)
.section = SECTION_PRE_DATA,
.createStmt = loOutQry->data));
+ if (fout->remoteVersion >= 160000)
+ ArchiveEntry(fout, nilCatalogId, createDumpId(),
+ ARCHIVE_OPTS(.tag = "pg_largeobject_metadata",
+ .description = "pg_largeobject_metadata",
+ .section = SECTION_PRE_DATA,
+ .createStmt = lomOutQry->data));
+
PQclear(lo_res);
destroyPQExpBuffer(loFrozenQry);
destroyPQExpBuffer(loHorizonQry);
+ destroyPQExpBuffer(lomHorizonQry);
destroyPQExpBuffer(loOutQry);
+ destroyPQExpBuffer(lomOutQry);
}
PQclear(res);
diff --git a/src/bin/pg_upgrade/Makefile b/src/bin/pg_upgrade/Makefile
index f83d2b5d309..69fcf593cae 100644
--- a/src/bin/pg_upgrade/Makefile
+++ b/src/bin/pg_upgrade/Makefile
@@ -3,8 +3,7 @@
PGFILEDESC = "pg_upgrade - an in-place binary upgrade utility"
PGAPPICON = win32
-# required for 003_upgrade_logical_replication_slots.pl
-EXTRA_INSTALL=contrib/test_decoding
+EXTRA_INSTALL=contrib/test_decoding src/test/modules/dummy_seclabel
subdir = src/bin/pg_upgrade
top_builddir = ../../..
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index c39eb077c2f..7ce08270168 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -498,7 +498,10 @@ get_rel_infos_query(void)
*
* pg_largeobject contains user data that does not appear in pg_dump
* output, so we have to copy that system table. It's easiest to do that
- * by treating it as a user table.
+ * by treating it as a user table. We can do the same for
+ * pg_largeobject_metadata for upgrades from v16 and newer. pg_upgrade
+ * can't copy/link the files from older versions because aclitem (needed
+ * by pg_largeobject_metadata.lomacl) changed its storage format in v16.
*/
appendPQExpBuffer(&query,
"WITH regular_heap (reloid, indtable, toastheap) AS ( "
@@ -514,10 +517,12 @@ get_rel_infos_query(void)
" 'binary_upgrade', 'pg_toast') AND "
" c.oid >= %u::pg_catalog.oid) OR "
" (n.nspname = 'pg_catalog' AND "
- " relname IN ('pg_largeobject') ))), ",
+ " relname IN ('pg_largeobject'%s) ))), ",
(user_opts.transfer_mode == TRANSFER_MODE_SWAP) ?
", " CppAsString2(RELKIND_SEQUENCE) : "",
- FirstNormalObjectId);
+ FirstNormalObjectId,
+ (GET_MAJOR_VERSION(old_cluster.major_version) >= 1600) ?
+ ", 'pg_largeobject_metadata'" : "");
/*
* Add a CTE that collects OIDs of toast tables belonging to the tables
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index d5cd5bf0b3a..490e98fa26f 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -29,9 +29,9 @@
* We control all assignments of pg_enum.oid because these oids are stored
* in user tables as enum values.
*
- * We control all assignments of pg_authid.oid for historical reasons (the
- * oids used to be stored in pg_largeobject_metadata, which is now copied via
- * SQL commands), that might change at some point in the future.
+ * We control all assignments of pg_authid.oid because the oids are stored in
+ * pg_largeobject_metadata, which is copied via file transfer for upgrades
+ * from v16 and newer.
*
* We control all assignments of pg_database.oid because we want the directory
* names to match between the old and new cluster.
diff --git a/src/bin/pg_upgrade/t/006_transfer_modes.pl b/src/bin/pg_upgrade/t/006_transfer_modes.pl
index 348f4021462..2f68f0b56aa 100644
--- a/src/bin/pg_upgrade/t/006_transfer_modes.pl
+++ b/src/bin/pg_upgrade/t/006_transfer_modes.pl
@@ -45,6 +45,22 @@ sub test_mode
$old->append_conf('postgresql.conf', "allow_in_place_tablespaces = true");
}
+ # We can only test security labels if both the old and new installations
+ # have dummy_seclabel.
+ my $test_seclabel = 1;
+ $old->start;
+ if (!$old->check_extension('dummy_seclabel'))
+ {
+ $test_seclabel = 0;
+ }
+ $old->stop;
+ $new->start;
+ if (!$new->check_extension('dummy_seclabel'))
+ {
+ $test_seclabel = 0;
+ }
+ $new->stop;
+
# Create a small variety of simple test objects on the old cluster. We'll
# check that these reach the new version after upgrading.
$old->start;
@@ -83,6 +99,29 @@ sub test_mode
$old->safe_psql('testdb3',
"CREATE TABLE test6 AS SELECT generate_series(607, 711)");
}
+
+ # While we are here, test handling of large objects.
+ $old->safe_psql('postgres', q|
+ CREATE ROLE regress_lo_1;
+ CREATE ROLE regress_lo_2;
+
+ SELECT lo_from_bytea(4532, '\xffffff00');
+ COMMENT ON LARGE OBJECT 4532 IS 'test';
+
+ SELECT lo_from_bytea(4533, '\x0f0f0f0f');
+ ALTER LARGE OBJECT 4533 OWNER TO regress_lo_1;
+ GRANT SELECT ON LARGE OBJECT 4533 TO regress_lo_2;
+ |);
+
+ if ($test_seclabel)
+ {
+ $old->safe_psql('postgres', q|
+ CREATE EXTENSION dummy_seclabel;
+
+ SELECT lo_from_bytea(4534, '\x00ffffff');
+ SECURITY LABEL ON LARGE OBJECT 4534 IS 'classified';
+ |);
+ }
$old->stop;
my $result = command_ok_or_fails_like(
@@ -132,6 +171,34 @@ sub test_mode
$result = $new->safe_psql('testdb3', "SELECT COUNT(*) FROM test6");
is($result, '105', "test6 data after pg_upgrade $mode");
}
+
+ # Tests for large objects
+ $result = $new->safe_psql('postgres', "SELECT lo_get(4532)");
+ is($result, '\xffffff00', "LO contents after upgrade");
+ $result = $new->safe_psql('postgres',
+ "SELECT obj_description(4532, 'pg_largeobject')");
+ is($result, 'test', "comment on LO after pg_upgrade");
+
+ $result = $new->safe_psql('postgres', "SELECT lo_get(4533)");
+ is($result, '\x0f0f0f0f', "LO contents after upgrade");
+ $result = $new->safe_psql('postgres',
+ "SELECT lomowner::regrole FROM pg_largeobject_metadata WHERE oid = 4533");
+ is($result, 'regress_lo_1', "LO owner after upgrade");
+ $result = $new->safe_psql('postgres',
+ "SELECT lomacl FROM pg_largeobject_metadata WHERE oid = 4533");
+ is($result, '{regress_lo_1=rw/regress_lo_1,regress_lo_2=r/regress_lo_1}',
+ "LO ACL after upgrade");
+
+ if ($test_seclabel)
+ {
+ $result = $new->safe_psql('postgres', "SELECT lo_get(4534)");
+ is($result, '\x00ffffff', "LO contents after upgrade");
+ $result = $new->safe_psql('postgres', q|
+ SELECT label FROM pg_seclabel WHERE objoid = 4534
+ AND classoid = 'pg_largeobject'::regclass
+ |);
+ is($result, 'classified', "seclabel on LO after pg_upgrade");
+ }
$new->stop;
}
--
2.39.5 (Apple Git-154)
--cdTrRbSR2rvrK+dI--
^ permalink raw reply [nested|flat] 43+ messages in thread
* [PATCH v2 1/1] pg_upgrade: Transfer pg_largeobject_metadata's files when possible.
@ 2025-08-14 15:14 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2025-08-14 15:14 UTC (permalink / raw)
Commit 161a3e8b68 taught pg_upgrade to use COPY for large object
metadata for upgrades from v12 and newer, which is much faster to
restore than the proper large object SQL commands. For upgrades
from v16 and newer, we can take this a step further and transfer
the large object metadata files as if they were user tables. We
can't transfer the files from older versions because the aclitem
data type (needed by pg_largeobject_metadata.lomacl) changed its
storage format in v16 (see commit 7b378237aa). Note that this
commit is essentially a revert of commit 12a53c732c, but there are
enough differences that it should be considered a fresh effort.
There are a couple of caveats. First, we still need to COPY the
corresponding pg_shdepend rows for large objects, since those
aren't transferred by anything else. Second, we need to COPY
anything in pg_largeobject_metadata with a comment or security
label, else restoring those will fail. This means that an upgrade
in which every large object has a comment or security label won't
gain anything from this commit, but it should at least avoid making
these unusual use-cases any worse.
pg_upgrade must also take care to transfer the relfilenode of
pg_largeobject_metadata and its index, à la commits d498e052b4 and
bbe08b8869.
---
src/backend/commands/tablecmds.c | 12 ++--
src/bin/pg_dump/pg_dump.c | 80 ++++++++++++++++++----
src/bin/pg_upgrade/Makefile | 3 +-
src/bin/pg_upgrade/info.c | 11 ++-
src/bin/pg_upgrade/pg_upgrade.c | 6 +-
src/bin/pg_upgrade/t/006_transfer_modes.pl | 55 +++++++++++++++
6 files changed, 142 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..3be2e051d32 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -42,6 +42,7 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_largeobject.h"
+#include "catalog/pg_largeobject_metadata.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_policy.h"
@@ -2389,12 +2390,15 @@ truncate_check_rel(Oid relid, Form_pg_class reltuple)
/*
* Most system catalogs can't be truncated at all, or at least not unless
* allow_system_table_mods=on. As an exception, however, we allow
- * pg_largeobject to be truncated as part of pg_upgrade, because we need
- * to change its relfilenode to match the old cluster, and allowing a
- * TRUNCATE command to be executed is the easiest way of doing that.
+ * pg_largeobject and pg_largeobject_metadata to be truncated as part of
+ * pg_upgrade, because we need to change its relfilenode to match the old
+ * cluster, and allowing a TRUNCATE command to be executed is the easiest
+ * way of doing that.
*/
if (!allowSystemTableMods && IsSystemClass(relid, reltuple)
- && (!IsBinaryUpgrade || relid != LargeObjectRelationId))
+ && (!IsBinaryUpgrade ||
+ (relid != LargeObjectRelationId &&
+ relid != LargeObjectMetadataRelationId)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied: \"%s\" is a system catalog",
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bea793456f9..b4c45ad803e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1131,6 +1131,23 @@ main(int argc, char **argv)
shdepend->dataObj->filtercond = "WHERE classid = 'pg_largeobject'::regclass "
"AND dbid = (SELECT oid FROM pg_database "
" WHERE datname = current_database())";
+
+ /*
+ * If upgrading from v16 or newer, only dump large objects with
+ * comments/seclabels. For these upgrades, pg_upgrade can copy/link
+ * pg_largeobject_metadata's files (which is usually faster) but we
+ * still need to dump LOs with comments/seclabels here so that the
+ * subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade
+ * can't copy/link the files from older versions because aclitem
+ * (needed by pg_largeobject_metadata.lomacl) changed its storage
+ * format in v16.
+ */
+ if (fout->remoteVersion >= 160000)
+ lo_metadata->dataObj->filtercond = "WHERE oid IN "
+ "(SELECT objoid FROM pg_description "
+ "WHERE classoid = " CppAsString2(LargeObjectRelationId) " "
+ "UNION SELECT objoid FROM pg_seclabel "
+ "WHERE classoid = " CppAsString2(LargeObjectRelationId) ")";
}
/*
@@ -3629,26 +3646,32 @@ dumpDatabase(Archive *fout)
/*
* pg_largeobject comes from the old system intact, so set its
* relfrozenxids, relminmxids and relfilenode.
+ *
+ * pg_largeobject_metadata also comes from the old system intact for
+ * upgrades from v16 and newer, so set its relfrozenxids, relminmxids, and
+ * relfilenode, too. pg_upgrade can't copy/link the files from older
+ * versions because aclitem (needed by pg_largeobject_metadata.lomacl)
+ * changed its storage format in v16.
*/
if (dopt->binary_upgrade)
{
PGresult *lo_res;
PQExpBuffer loFrozenQry = createPQExpBuffer();
PQExpBuffer loOutQry = createPQExpBuffer();
+ PQExpBuffer lomOutQry = createPQExpBuffer();
PQExpBuffer loHorizonQry = createPQExpBuffer();
+ PQExpBuffer lomHorizonQry = createPQExpBuffer();
int ii_relfrozenxid,
ii_relfilenode,
ii_oid,
ii_relminmxid;
- /*
- * pg_largeobject
- */
if (fout->remoteVersion >= 90300)
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
- "WHERE oid IN (%u, %u);\n",
- LargeObjectRelationId, LargeObjectLOidPNIndexId);
+ "WHERE oid IN (%u, %u, %u, %u);\n",
+ LargeObjectRelationId, LargeObjectLOidPNIndexId,
+ LargeObjectMetadataRelationId, LargeObjectMetadataOidIndexId);
else
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, 0 AS relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
@@ -3663,35 +3686,57 @@ dumpDatabase(Archive *fout)
ii_oid = PQfnumber(lo_res, "oid");
appendPQExpBufferStr(loHorizonQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid\n");
+ appendPQExpBufferStr(lomHorizonQry, "\n-- For binary upgrade, set pg_largeobject_metadata relfrozenxid and relminmxid\n");
appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, preserve pg_largeobject and index relfilenodes\n");
+ appendPQExpBufferStr(lomOutQry, "\n-- For binary upgrade, preserve pg_largeobject_metadata and index relfilenodes\n");
for (int i = 0; i < PQntuples(lo_res); ++i)
{
Oid oid;
RelFileNumber relfilenumber;
+ PQExpBuffer horizonQry;
+ PQExpBuffer outQry;
+
+ oid = atooid(PQgetvalue(lo_res, i, ii_oid));
+ relfilenumber = atooid(PQgetvalue(lo_res, i, ii_relfilenode));
- appendPQExpBuffer(loHorizonQry, "UPDATE pg_catalog.pg_class\n"
+ if (oid == LargeObjectRelationId ||
+ oid == LargeObjectLOidPNIndexId)
+ {
+ horizonQry = loHorizonQry;
+ outQry = loOutQry;
+ }
+ else
+ {
+ horizonQry = lomHorizonQry;
+ outQry = lomOutQry;
+ }
+
+ appendPQExpBuffer(horizonQry, "UPDATE pg_catalog.pg_class\n"
"SET relfrozenxid = '%u', relminmxid = '%u'\n"
"WHERE oid = %u;\n",
atooid(PQgetvalue(lo_res, i, ii_relfrozenxid)),
atooid(PQgetvalue(lo_res, i, ii_relminmxid)),
atooid(PQgetvalue(lo_res, i, ii_oid)));
- oid = atooid(PQgetvalue(lo_res, i, ii_oid));
- relfilenumber = atooid(PQgetvalue(lo_res, i, ii_relfilenode));
-
- if (oid == LargeObjectRelationId)
- appendPQExpBuffer(loOutQry,
+ if (oid == LargeObjectRelationId ||
+ oid == LargeObjectMetadataRelationId)
+ appendPQExpBuffer(outQry,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
relfilenumber);
- else if (oid == LargeObjectLOidPNIndexId)
- appendPQExpBuffer(loOutQry,
+ else if (oid == LargeObjectLOidPNIndexId ||
+ oid == LargeObjectMetadataOidIndexId)
+ appendPQExpBuffer(outQry,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
relfilenumber);
}
appendPQExpBufferStr(loOutQry,
"TRUNCATE pg_catalog.pg_largeobject;\n");
+ appendPQExpBufferStr(lomOutQry,
+ "TRUNCATE pg_catalog.pg_largeobject_metadata;\n");
+
appendPQExpBufferStr(loOutQry, loHorizonQry->data);
+ appendPQExpBufferStr(lomOutQry, lomHorizonQry->data);
ArchiveEntry(fout, nilCatalogId, createDumpId(),
ARCHIVE_OPTS(.tag = "pg_largeobject",
@@ -3699,11 +3744,20 @@ dumpDatabase(Archive *fout)
.section = SECTION_PRE_DATA,
.createStmt = loOutQry->data));
+ if (fout->remoteVersion >= 160000)
+ ArchiveEntry(fout, nilCatalogId, createDumpId(),
+ ARCHIVE_OPTS(.tag = "pg_largeobject_metadata",
+ .description = "pg_largeobject_metadata",
+ .section = SECTION_PRE_DATA,
+ .createStmt = lomOutQry->data));
+
PQclear(lo_res);
destroyPQExpBuffer(loFrozenQry);
destroyPQExpBuffer(loHorizonQry);
+ destroyPQExpBuffer(lomHorizonQry);
destroyPQExpBuffer(loOutQry);
+ destroyPQExpBuffer(lomOutQry);
}
PQclear(res);
diff --git a/src/bin/pg_upgrade/Makefile b/src/bin/pg_upgrade/Makefile
index f83d2b5d309..69fcf593cae 100644
--- a/src/bin/pg_upgrade/Makefile
+++ b/src/bin/pg_upgrade/Makefile
@@ -3,8 +3,7 @@
PGFILEDESC = "pg_upgrade - an in-place binary upgrade utility"
PGAPPICON = win32
-# required for 003_upgrade_logical_replication_slots.pl
-EXTRA_INSTALL=contrib/test_decoding
+EXTRA_INSTALL=contrib/test_decoding src/test/modules/dummy_seclabel
subdir = src/bin/pg_upgrade
top_builddir = ../../..
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index c39eb077c2f..7ce08270168 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -498,7 +498,10 @@ get_rel_infos_query(void)
*
* pg_largeobject contains user data that does not appear in pg_dump
* output, so we have to copy that system table. It's easiest to do that
- * by treating it as a user table.
+ * by treating it as a user table. We can do the same for
+ * pg_largeobject_metadata for upgrades from v16 and newer. pg_upgrade
+ * can't copy/link the files from older versions because aclitem (needed
+ * by pg_largeobject_metadata.lomacl) changed its storage format in v16.
*/
appendPQExpBuffer(&query,
"WITH regular_heap (reloid, indtable, toastheap) AS ( "
@@ -514,10 +517,12 @@ get_rel_infos_query(void)
" 'binary_upgrade', 'pg_toast') AND "
" c.oid >= %u::pg_catalog.oid) OR "
" (n.nspname = 'pg_catalog' AND "
- " relname IN ('pg_largeobject') ))), ",
+ " relname IN ('pg_largeobject'%s) ))), ",
(user_opts.transfer_mode == TRANSFER_MODE_SWAP) ?
", " CppAsString2(RELKIND_SEQUENCE) : "",
- FirstNormalObjectId);
+ FirstNormalObjectId,
+ (GET_MAJOR_VERSION(old_cluster.major_version) >= 1600) ?
+ ", 'pg_largeobject_metadata'" : "");
/*
* Add a CTE that collects OIDs of toast tables belonging to the tables
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index d5cd5bf0b3a..490e98fa26f 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -29,9 +29,9 @@
* We control all assignments of pg_enum.oid because these oids are stored
* in user tables as enum values.
*
- * We control all assignments of pg_authid.oid for historical reasons (the
- * oids used to be stored in pg_largeobject_metadata, which is now copied via
- * SQL commands), that might change at some point in the future.
+ * We control all assignments of pg_authid.oid because the oids are stored in
+ * pg_largeobject_metadata, which is copied via file transfer for upgrades
+ * from v16 and newer.
*
* We control all assignments of pg_database.oid because we want the directory
* names to match between the old and new cluster.
diff --git a/src/bin/pg_upgrade/t/006_transfer_modes.pl b/src/bin/pg_upgrade/t/006_transfer_modes.pl
index 348f4021462..b355f6de5b9 100644
--- a/src/bin/pg_upgrade/t/006_transfer_modes.pl
+++ b/src/bin/pg_upgrade/t/006_transfer_modes.pl
@@ -83,6 +83,33 @@ sub test_mode
$old->safe_psql('testdb3',
"CREATE TABLE test6 AS SELECT generate_series(607, 711)");
}
+
+ # While we are here, test handling of large objects.
+ $old->safe_psql('postgres', q|
+ CREATE ROLE regress_lo_1;
+ CREATE ROLE regress_lo_2;
+
+ SELECT lo_from_bytea(4532, '\xffffff00');
+ COMMENT ON LARGE OBJECT 4532 IS 'test';
+
+ SELECT lo_from_bytea(4533, '\x0f0f0f0f');
+ ALTER LARGE OBJECT 4533 OWNER TO regress_lo_1;
+ GRANT SELECT ON LARGE OBJECT 4533 TO regress_lo_2;
+ |);
+
+ # For same-version upgrades, test large objects with security labels
+ #
+ # This requires installing dummy_seclabel, so we limit this test to
+ # same-version upgrades.
+ if (!defined($ENV{oldinstall}))
+ {
+ $old->safe_psql('postgres', q|
+ CREATE EXTENSION dummy_seclabel;
+
+ SELECT lo_from_bytea(4534, '\x00ffffff');
+ SECURITY LABEL ON LARGE OBJECT 4534 IS 'classified';
+ |);
+ }
$old->stop;
my $result = command_ok_or_fails_like(
@@ -132,6 +159,34 @@ sub test_mode
$result = $new->safe_psql('testdb3', "SELECT COUNT(*) FROM test6");
is($result, '105', "test6 data after pg_upgrade $mode");
}
+
+ # Tests for large objects
+ $result = $new->safe_psql('postgres', "SELECT lo_get(4532)");
+ is($result, '\xffffff00', "LO contents after upgrade");
+ $result = $new->safe_psql('postgres',
+ "SELECT obj_description(4532, 'pg_largeobject')");
+ is($result, 'test', "comment on LO after pg_upgrade");
+
+ $result = $new->safe_psql('postgres', "SELECT lo_get(4533)");
+ is($result, '\x0f0f0f0f', "LO contents after upgrade");
+ $result = $new->safe_psql('postgres',
+ "SELECT lomowner::regrole FROM pg_largeobject_metadata WHERE oid = 4533");
+ is($result, 'regress_lo_1', "LO owner after upgrade");
+ $result = $new->safe_psql('postgres',
+ "SELECT lomacl FROM pg_largeobject_metadata WHERE oid = 4533");
+ is($result, '{regress_lo_1=rw/regress_lo_1,regress_lo_2=r/regress_lo_1}',
+ "LO ACL after upgrade");
+
+ if (!defined($ENV{oldinstall}))
+ {
+ $result = $new->safe_psql('postgres', "SELECT lo_get(4534)");
+ is($result, '\x00ffffff', "LO contents after upgrade");
+ $result = $new->safe_psql('postgres', q|
+ SELECT label FROM pg_seclabel WHERE objoid = 4534
+ AND classoid = 'pg_largeobject'::regclass
+ |);
+ is($result, 'classified', "seclabel on LO after pg_upgrade");
+ }
$new->stop;
}
--
2.39.5 (Apple Git-154)
--FuG7Wvwo8mkbkV/T--
^ permalink raw reply [nested|flat] 43+ messages in thread
* [PATCH v1 1/1] pg_upgrade: Transfer pg_largeobject_metadata's files when possible.
@ 2025-08-14 15:14 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2025-08-14 15:14 UTC (permalink / raw)
Commit 161a3e8b68 taught pg_upgrade to use COPY for large object
metadata for upgrades from v12 and newer, which is much faster to
restore than the proper large object SQL commands. For upgrades
from v16 and newer, we can take this a step further and transfer
the large object metadata files as if they were user tables. We
can't transfer the files from older versions because the aclitem
data type (needed by pg_largeobject_metadata.lomacl) changed its
storage format in v16 (see commit 7b378237aa). Note that this
commit is essentially a revert of commit 12a53c732c, but there are
enough differences that it should be considered a fresh effort.
There are a couple of caveats. First, we still need to COPY the
corresponding pg_shdepend rows for large objects, since those
aren't transferred by anything else. Second, we need to COPY
anything in pg_largeobject_metadata with a comment or security
label, else restoring those will fail. This means that an upgrade
in which every large object has a comment or security label won't
gain anything from this commit, but it should at least avoid making
these unusual use-cases any worse.
pg_upgrade must also take care to transfer the relfilenode of
pg_largeobject_metadata and its index, à la commits d498e052b4 and
bbe08b8869.
---
src/backend/commands/tablecmds.c | 12 +++--
src/bin/pg_dump/pg_dump.c | 80 ++++++++++++++++++++++++++------
src/bin/pg_upgrade/info.c | 11 +++--
src/bin/pg_upgrade/pg_upgrade.c | 6 +--
4 files changed, 86 insertions(+), 23 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c6dd2e020da..4132b570513 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -42,6 +42,7 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_largeobject.h"
+#include "catalog/pg_largeobject_metadata.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_policy.h"
@@ -2389,12 +2390,15 @@ truncate_check_rel(Oid relid, Form_pg_class reltuple)
/*
* Most system catalogs can't be truncated at all, or at least not unless
* allow_system_table_mods=on. As an exception, however, we allow
- * pg_largeobject to be truncated as part of pg_upgrade, because we need
- * to change its relfilenode to match the old cluster, and allowing a
- * TRUNCATE command to be executed is the easiest way of doing that.
+ * pg_largeobject and pg_largeobject_metadata to be truncated as part of
+ * pg_upgrade, because we need to change its relfilenode to match the old
+ * cluster, and allowing a TRUNCATE command to be executed is the easiest
+ * way of doing that.
*/
if (!allowSystemTableMods && IsSystemClass(relid, reltuple)
- && (!IsBinaryUpgrade || relid != LargeObjectRelationId))
+ && (!IsBinaryUpgrade ||
+ (relid != LargeObjectRelationId &&
+ relid != LargeObjectMetadataRelationId)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied: \"%s\" is a system catalog",
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index fc7a6639163..48066fab744 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1131,6 +1131,23 @@ main(int argc, char **argv)
shdepend->dataObj->filtercond = "WHERE classid = 'pg_largeobject'::regclass "
"AND dbid = (SELECT oid FROM pg_database "
" WHERE datname = current_database())";
+
+ /*
+ * If upgrading from v16 or newer, only dump large objects with
+ * comments/seclabels. For these upgrades, pg_upgrade can copy/link
+ * pg_largeobject_metadata's files (which is usually faster) but we
+ * still need to dump LOs with comments/seclabels here so that the
+ * subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade
+ * can't copy/link the files from older versions because aclitem
+ * (needed by pg_largeobject_metadata.lomacl) changed its storage
+ * format in v16.
+ */
+ if (fout->remoteVersion >= 160000)
+ lo_metadata->dataObj->filtercond = "WHERE oid IN "
+ "(SELECT objoid FROM pg_description "
+ "WHERE classoid = " CppAsString2(LargeObjectRelationId) " "
+ "UNION SELECT objoid FROM pg_seclabel "
+ "WHERE classoid = " CppAsString2(LargeObjectRelationId) ")";
}
/*
@@ -3629,26 +3646,32 @@ dumpDatabase(Archive *fout)
/*
* pg_largeobject comes from the old system intact, so set its
* relfrozenxids, relminmxids and relfilenode.
+ *
+ * pg_largeobject_metadata also comes from the old system intact for
+ * upgrades from v16 and newer, so set its relfrozenxids, relminmxids, and
+ * relfilenode, too. pg_upgrade can't copy/link the files from older
+ * versions because aclitem (needed by pg_largeobject_metadata.lomacl)
+ * changed its storage format in v16.
*/
if (dopt->binary_upgrade)
{
PGresult *lo_res;
PQExpBuffer loFrozenQry = createPQExpBuffer();
PQExpBuffer loOutQry = createPQExpBuffer();
+ PQExpBuffer lomOutQry = createPQExpBuffer();
PQExpBuffer loHorizonQry = createPQExpBuffer();
+ PQExpBuffer lomHorizonQry = createPQExpBuffer();
int ii_relfrozenxid,
ii_relfilenode,
ii_oid,
ii_relminmxid;
- /*
- * pg_largeobject
- */
if (fout->remoteVersion >= 90300)
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
- "WHERE oid IN (%u, %u);\n",
- LargeObjectRelationId, LargeObjectLOidPNIndexId);
+ "WHERE oid IN (%u, %u, %u, %u);\n",
+ LargeObjectRelationId, LargeObjectLOidPNIndexId,
+ LargeObjectMetadataRelationId, LargeObjectMetadataOidIndexId);
else
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, 0 AS relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
@@ -3663,35 +3686,57 @@ dumpDatabase(Archive *fout)
ii_oid = PQfnumber(lo_res, "oid");
appendPQExpBufferStr(loHorizonQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid\n");
+ appendPQExpBufferStr(lomHorizonQry, "\n-- For binary upgrade, set pg_largeobject_metadata relfrozenxid and relminmxid\n");
appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, preserve pg_largeobject and index relfilenodes\n");
+ appendPQExpBufferStr(lomOutQry, "\n-- For binary upgrade, preserve pg_largeobject_metadata and index relfilenodes\n");
for (int i = 0; i < PQntuples(lo_res); ++i)
{
Oid oid;
RelFileNumber relfilenumber;
+ PQExpBuffer horizonQry;
+ PQExpBuffer outQry;
+
+ oid = atooid(PQgetvalue(lo_res, i, ii_oid));
+ relfilenumber = atooid(PQgetvalue(lo_res, i, ii_relfilenode));
- appendPQExpBuffer(loHorizonQry, "UPDATE pg_catalog.pg_class\n"
+ if (oid == LargeObjectRelationId ||
+ oid == LargeObjectLOidPNIndexId)
+ {
+ horizonQry = loHorizonQry;
+ outQry = loOutQry;
+ }
+ else
+ {
+ horizonQry = lomHorizonQry;
+ outQry = lomOutQry;
+ }
+
+ appendPQExpBuffer(horizonQry, "UPDATE pg_catalog.pg_class\n"
"SET relfrozenxid = '%u', relminmxid = '%u'\n"
"WHERE oid = %u;\n",
atooid(PQgetvalue(lo_res, i, ii_relfrozenxid)),
atooid(PQgetvalue(lo_res, i, ii_relminmxid)),
atooid(PQgetvalue(lo_res, i, ii_oid)));
- oid = atooid(PQgetvalue(lo_res, i, ii_oid));
- relfilenumber = atooid(PQgetvalue(lo_res, i, ii_relfilenode));
-
- if (oid == LargeObjectRelationId)
- appendPQExpBuffer(loOutQry,
+ if (oid == LargeObjectRelationId ||
+ oid == LargeObjectMetadataRelationId)
+ appendPQExpBuffer(outQry,
"SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
relfilenumber);
- else if (oid == LargeObjectLOidPNIndexId)
- appendPQExpBuffer(loOutQry,
+ else if (oid == LargeObjectLOidPNIndexId ||
+ oid == LargeObjectMetadataOidIndexId)
+ appendPQExpBuffer(outQry,
"SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
relfilenumber);
}
appendPQExpBufferStr(loOutQry,
"TRUNCATE pg_catalog.pg_largeobject;\n");
+ appendPQExpBufferStr(lomOutQry,
+ "TRUNCATE pg_catalog.pg_largeobject_metadata;\n");
+
appendPQExpBufferStr(loOutQry, loHorizonQry->data);
+ appendPQExpBufferStr(lomOutQry, lomHorizonQry->data);
ArchiveEntry(fout, nilCatalogId, createDumpId(),
ARCHIVE_OPTS(.tag = "pg_largeobject",
@@ -3699,11 +3744,20 @@ dumpDatabase(Archive *fout)
.section = SECTION_PRE_DATA,
.createStmt = loOutQry->data));
+ if (fout->remoteVersion >= 160000)
+ ArchiveEntry(fout, nilCatalogId, createDumpId(),
+ ARCHIVE_OPTS(.tag = "pg_largeobject_metadata",
+ .description = "pg_largeobject_metadata",
+ .section = SECTION_PRE_DATA,
+ .createStmt = lomOutQry->data));
+
PQclear(lo_res);
destroyPQExpBuffer(loFrozenQry);
destroyPQExpBuffer(loHorizonQry);
+ destroyPQExpBuffer(lomHorizonQry);
destroyPQExpBuffer(loOutQry);
+ destroyPQExpBuffer(lomOutQry);
}
PQclear(res);
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index c39eb077c2f..7ce08270168 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -498,7 +498,10 @@ get_rel_infos_query(void)
*
* pg_largeobject contains user data that does not appear in pg_dump
* output, so we have to copy that system table. It's easiest to do that
- * by treating it as a user table.
+ * by treating it as a user table. We can do the same for
+ * pg_largeobject_metadata for upgrades from v16 and newer. pg_upgrade
+ * can't copy/link the files from older versions because aclitem (needed
+ * by pg_largeobject_metadata.lomacl) changed its storage format in v16.
*/
appendPQExpBuffer(&query,
"WITH regular_heap (reloid, indtable, toastheap) AS ( "
@@ -514,10 +517,12 @@ get_rel_infos_query(void)
" 'binary_upgrade', 'pg_toast') AND "
" c.oid >= %u::pg_catalog.oid) OR "
" (n.nspname = 'pg_catalog' AND "
- " relname IN ('pg_largeobject') ))), ",
+ " relname IN ('pg_largeobject'%s) ))), ",
(user_opts.transfer_mode == TRANSFER_MODE_SWAP) ?
", " CppAsString2(RELKIND_SEQUENCE) : "",
- FirstNormalObjectId);
+ FirstNormalObjectId,
+ (GET_MAJOR_VERSION(old_cluster.major_version) >= 1600) ?
+ ", 'pg_largeobject_metadata'" : "");
/*
* Add a CTE that collects OIDs of toast tables belonging to the tables
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index d5cd5bf0b3a..490e98fa26f 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -29,9 +29,9 @@
* We control all assignments of pg_enum.oid because these oids are stored
* in user tables as enum values.
*
- * We control all assignments of pg_authid.oid for historical reasons (the
- * oids used to be stored in pg_largeobject_metadata, which is now copied via
- * SQL commands), that might change at some point in the future.
+ * We control all assignments of pg_authid.oid because the oids are stored in
+ * pg_largeobject_metadata, which is copied via file transfer for upgrades
+ * from v16 and newer.
*
* We control all assignments of pg_database.oid because we want the directory
* names to match between the old and new cluster.
--
2.39.5 (Apple Git-154)
--DGpiUg7aQzWVm0yn--
^ permalink raw reply [nested|flat] 43+ messages in thread
* pg_upgrade: transfer pg_largeobject_metadata's files when possible
@ 2025-08-14 15:22 Nathan Bossart <[email protected]>
2025-08-19 07:49 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Hannu Krosing <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Nathan Bossart @ 2025-08-14 15:22 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
(new thread)
On Fri, Jul 18, 2025 at 11:05:04AM -0500, Nathan Bossart wrote:
> I'm cautiously optimistic that we can find some better gains for upgrades
> from v16 and newer. That would involve dumping lo_create() commands for
> all LOs with comments/seclabels, dumping the relevant pg_shdepend rows, and
> then copying/linking the pg_largeobject_metadata files like we did prior to
> v12.
Here is a patch. For background, the reason this is limited to upgrades
from v16 and newer is because the aclitem data type (needed by
pg_largeobject_metadata.lomacl) changed its storage format in v16 (see
commit 7b378237aa). Note that the patch is essentially a revert of commit
12a53c732c, but there are enough differences that it should be considered a
fresh effort.
Something I hadn't anticipated is that we need to take special care to
transfer the relfilenode of pg_largeobject_metadata and its index, as was
done for pg_largeobject in commits d498e052b4 and bbe08b8869. In fact, the
majority of the patch is dedicated to that.
My testing showed some decent, but not earth-shattering performance
improvements from this patch. For upgrades with many large objects with
NULL lomacl/lomowner columns, pg_upgrade was 50% faster. With non-NULL
lomacl/lomowner, that dropped to 25%. When each large object had a
comment, there was no change. I'm assuming that its rare to have lots of
large objects with comments or security labels, so I don't see any need to
expend energy trying to optimize that use-case.
I am a bit concerned that we'll forget to add checks for new types of
dependencies similar to comments and security labels. If we do, pg_upgrade
should just fail to restore the schema, and fixing the code should be easy
enough. Also, we'll need to remember to revisit this code if there's
another storage format change for one of pg_largeobject_metadata's columns,
but that seems unlikely to happen anytime soon. On the whole, I'm not too
worried about either of these points.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2025-08-19 07:49 ` Hannu Krosing <[email protected]>
2025-08-19 19:18 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Hannu Krosing @ 2025-08-19 07:49 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; PostgreSQL Hackers <[email protected]>
Have you considered re-creating pg_shdepend from
pg_largeobject_metadata directly instead of having special cases for
dumping it ?
It would also be useful in cases of old (pg_upgraded since before pg
12) databases which might be missing it anyway.
On Thu, Aug 14, 2025 at 5:22 PM Nathan Bossart <[email protected]> wrote:
>
> (new thread)
>
> On Fri, Jul 18, 2025 at 11:05:04AM -0500, Nathan Bossart wrote:
> > I'm cautiously optimistic that we can find some better gains for upgrades
> > from v16 and newer. That would involve dumping lo_create() commands for
> > all LOs with comments/seclabels, dumping the relevant pg_shdepend rows, and
> > then copying/linking the pg_largeobject_metadata files like we did prior to
> > v12.
>
> Here is a patch. For background, the reason this is limited to upgrades
> from v16 and newer is because the aclitem data type (needed by
> pg_largeobject_metadata.lomacl) changed its storage format in v16 (see
> commit 7b378237aa). Note that the patch is essentially a revert of commit
> 12a53c732c, but there are enough differences that it should be considered a
> fresh effort.
>
> Something I hadn't anticipated is that we need to take special care to
> transfer the relfilenode of pg_largeobject_metadata and its index, as was
> done for pg_largeobject in commits d498e052b4 and bbe08b8869. In fact, the
> majority of the patch is dedicated to that.
>
> My testing showed some decent, but not earth-shattering performance
> improvements from this patch. For upgrades with many large objects with
> NULL lomacl/lomowner columns, pg_upgrade was 50% faster. With non-NULL
> lomacl/lomowner, that dropped to 25%. When each large object had a
> comment, there was no change. I'm assuming that its rare to have lots of
> large objects with comments or security labels, so I don't see any need to
> expend energy trying to optimize that use-case.
>
> I am a bit concerned that we'll forget to add checks for new types of
> dependencies similar to comments and security labels. If we do, pg_upgrade
> should just fail to restore the schema, and fixing the code should be easy
> enough. Also, we'll need to remember to revisit this code if there's
> another storage format change for one of pg_largeobject_metadata's columns,
> but that seems unlikely to happen anytime soon. On the whole, I'm not too
> worried about either of these points.
>
> --
> nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-08-19 07:49 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Hannu Krosing <[email protected]>
@ 2025-08-19 19:18 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2025-08-19 19:18 UTC (permalink / raw)
To: Hannu Krosing <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Aug 19, 2025 at 09:49:26AM +0200, Hannu Krosing wrote:
> Have you considered re-creating pg_shdepend from
> pg_largeobject_metadata directly instead of having special cases for
> dumping it ?
I considered it when you last brought up the idea [0], but my testing
indicated that it's measurably slower.
> It would also be useful in cases of old (pg_upgraded since before pg
> 12) databases which might be missing it anyway.
We only use COPY for upgrades from v12 and newer, and the patch at hand
only applies to v16 and newer. There should be no need to repair
pg_shdepend for any such upgrades because we haven't copied/linked the
files since before v12.
[0] https://postgr.es/m/CAMT0RQTXiqH7zdQEVSVd2L7_Cw4wQ1eHOD8hfZ%2B0vecMXJWc-w%40mail.gmail.com
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2025-09-01 06:19 ` Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Michael Paquier @ 2025-09-01 06:19 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Aug 14, 2025 at 10:22:02AM -0500, Nathan Bossart wrote:
> Here is a patch. For background, the reason this is limited to upgrades
> from v16 and newer is because the aclitem data type (needed by
> pg_largeobject_metadata.lomacl) changed its storage format in v16 (see
> commit 7b378237aa). Note that the patch is essentially a revert of commit
> 12a53c732c, but there are enough differences that it should be considered a
> fresh effort.
Noted.
> Something I hadn't anticipated is that we need to take special care to
> transfer the relfilenode of pg_largeobject_metadata and its index, as was
> done for pg_largeobject in commits d498e052b4 and bbe08b8869. In fact, the
> majority of the patch is dedicated to that.
>
> My testing showed some decent, but not earth-shattering performance
> improvements from this patch. For upgrades with many large objects with
> NULL lomacl/lomowner columns, pg_upgrade was 50% faster. With non-NULL
> lomacl/lomowner, that dropped to 25%. When each large object had a
> comment, there was no change. I'm assuming that its rare to have lots of
> large objects with comments or security labels, so I don't see any need to
> expend energy trying to optimize that use-case.
I highly doubt that there are a lot of comments assigned to LOs, so
these numbers are pretty cool IMO. Security labels are a pain to test
in the upgrade path, or test_dummy_label could be extended with a new
TAP test and a pg_upgrade command.. There is some coverage with
comments on LOs in src/bin/pg_dump's 002, so that would be enough for
the comment part, at least.
> I am a bit concerned that we'll forget to add checks for new types of
> dependencies similar to comments and security labels. If we do, pg_upgrade
> should just fail to restore the schema, and fixing the code should be easy
> enough. Also, we'll need to remember to revisit this code if there's
> another storage format change for one of pg_largeobject_metadata's columns,
> but that seems unlikely to happen anytime soon. On the whole, I'm not too
> worried about either of these points.
This part does not worry me much, TBH. This stuff would require
dump/restore support and the pg_dump test suite would catch that for
commands with --binary-upgrade. So it should be hard to miss.
- /*
- * pg_largeobject
- */
if (fout->remoteVersion >= 90300)
appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid, relfilenode, oid\n"
"FROM pg_catalog.pg_class\n"
- "WHERE oid IN (%u, %u);\n",
- LargeObjectRelationId, LargeObjectLOidPNIndexId);
+ "WHERE oid IN (%u, %u, %u, %u);\n",
+ LargeObjectRelationId, LargeObjectLOidPNIndexId,
+ LargeObjectMetadataRelationId, LargeObjectMetadataOidIndexId);
[...]
appendPQExpBufferStr(loHorizonQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid\n");
+ appendPQExpBufferStr(lomHorizonQry, "\n-- For binary upgrade, set pg_largeobject_metadata relfrozenxid and relminmxid\n");
appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, preserve pg_largeobject and index relfilenodes\n");
+ appendPQExpBufferStr(lomOutQry, "\n-- For binary upgrade, preserve pg_largeobject_metadata and index relfilenodes\n");
Is all that really required when upgrading from a cluster in the
9.3~15 range?
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
@ 2025-09-02 14:43 ` Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2025-09-02 14:43 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Sep 01, 2025 at 03:19:46PM +0900, Michael Paquier wrote:
> I highly doubt that there are a lot of comments assigned to LOs, so
> these numbers are pretty cool IMO. Security labels are a pain to test
> in the upgrade path, or test_dummy_label could be extended with a new
> TAP test and a pg_upgrade command.. There is some coverage with
> comments on LOs in src/bin/pg_dump's 002, so that would be enough for
> the comment part, at least.
Do you think a new pg_upgrade test for security labels is worth the
trouble? It seems doable, but it'd be an awfully expensive test for this.
On the other hand, I'm not sure there's any coverage for pg_upgrade with
security labels, so perhaps this is a good time to establish some tests.
> - /*
> - * pg_largeobject
> - */
> if (fout->remoteVersion >= 90300)
> appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid, relminmxid, relfilenode, oid\n"
> "FROM pg_catalog.pg_class\n"
> - "WHERE oid IN (%u, %u);\n",
> - LargeObjectRelationId, LargeObjectLOidPNIndexId);
> + "WHERE oid IN (%u, %u, %u, %u);\n",
> + LargeObjectRelationId, LargeObjectLOidPNIndexId,
> + LargeObjectMetadataRelationId, LargeObjectMetadataOidIndexId);
> [...]
> appendPQExpBufferStr(loHorizonQry, "\n-- For binary upgrade, set pg_largeobject relfrozenxid and relminmxid\n");
> + appendPQExpBufferStr(lomHorizonQry, "\n-- For binary upgrade, set pg_largeobject_metadata relfrozenxid and relminmxid\n");
> appendPQExpBufferStr(loOutQry, "\n-- For binary upgrade, preserve pg_largeobject and index relfilenodes\n");
> + appendPQExpBufferStr(lomOutQry, "\n-- For binary upgrade, preserve pg_largeobject_metadata and index relfilenodes\n");
>
> Is all that really required when upgrading from a cluster in the
> 9.3~15 range?
No, that stuff is discarded for upgrades from those versions. My intent
was to maintain readability by avoiding lots of version checks. FWIW I
originally put all of the pg_large_object_metadata stuff in a separate
block, but that resulted in a lot of copy/pasted code. I'm happy to adjust
it as you see fit.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2025-09-04 04:59 ` Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Michael Paquier @ 2025-09-04 04:59 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Sep 02, 2025 at 09:43:40AM -0500, Nathan Bossart wrote:
> Do you think a new pg_upgrade test for security labels is worth the
> trouble? It seems doable, but it'd be an awfully expensive test for this.
> On the other hand, I'm not sure there's any coverage for pg_upgrade with
> security labels, so perhaps this is a good time to establish some tests.
I would argue in favor of these additions. Security labels are not
the most popular thing ever, AFAIK, but your patch makes the need more
relevant to have. The cheapest approach would be to add a LO creation
pattern in src/bin/pg_dump/t/002_pg_dump.pl, with an EXTRA_INSTALL
pointing at src/test/modules/dummy_seclabel/ to be able to create the
security label (we already do that in pg_upgrade and pg_basebackup so
the trick works). That should be enough to make sure that the binary
upgrade dumps have the seclabel data included. It's a bit funky, I
agree. So if you think that this is not worth the test cycles, I
won't push hard on this point, either.
> No, that stuff is discarded for upgrades from those versions. My intent
> was to maintain readability by avoiding lots of version checks. FWIW I
> originally put all of the pg_large_object_metadata stuff in a separate
> block, but that resulted in a lot of copy/pasted code. I'm happy to adjust
> it as you see fit.
+ if (fout->remoteVersion >= 160000)
+ ArchiveEntry(fout, nilCatalogId, createDumpId(),
+ ARCHIVE_OPTS(.tag = "pg_largeobject_metadata",
+ .description = "pg_largeobject_metadata",
+ .section = SECTION_PRE_DATA,
+ .createStmt = lomOutQry->data));
So it's filtered out depending on the old cluster version here. I
have managed to miss this part. Some tests with LOs and across
multiple backend versions (pg_dump -C --binary-upgrade) are showing me
that I am wrong and that you are right, with diffs showing up properly
in the binary upgrade dumps for pg_largeobject_metadata. This logic
looks OK to me, even if it's a waste to fill lomOutQry when upgrading
from an instance in the 9.3~15 range, discarding ArchiveEntry() at the
end. It's not a big deal.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
@ 2025-09-05 01:23 ` Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2025-09-05 01:23 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Sep 04, 2025 at 01:59:36PM +0900, Michael Paquier wrote:
> On Tue, Sep 02, 2025 at 09:43:40AM -0500, Nathan Bossart wrote:
>> Do you think a new pg_upgrade test for security labels is worth the
>> trouble? It seems doable, but it'd be an awfully expensive test for this.
>> On the other hand, I'm not sure there's any coverage for pg_upgrade with
>> security labels, so perhaps this is a good time to establish some tests.
>
> I would argue in favor of these additions. Security labels are not
> the most popular thing ever, AFAIK, but your patch makes the need more
> relevant to have. The cheapest approach would be to add a LO creation
> pattern in src/bin/pg_dump/t/002_pg_dump.pl, with an EXTRA_INSTALL
> pointing at src/test/modules/dummy_seclabel/ to be able to create the
> security label (we already do that in pg_upgrade and pg_basebackup so
> the trick works). That should be enough to make sure that the binary
> upgrade dumps have the seclabel data included. It's a bit funky, I
> agree. So if you think that this is not worth the test cycles, I
> won't push hard on this point, either.
Ah, I'd forgotten about EXTRA_INSTALL. That simplifies things. There's
enough special handling for large objects in pg_upgrade that I think we
ought to test it end-to-end, so I sneaked it into 006_tranfer_modes.pl.
WDYT?
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2025-09-05 06:35 ` Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Michael Paquier @ 2025-09-05 06:35 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Sep 04, 2025 at 08:23:58PM -0500, Nathan Bossart wrote:
> Ah, I'd forgotten about EXTRA_INSTALL. That simplifies things. There's
> enough special handling for large objects in pg_upgrade that I think we
> ought to test it end-to-end, so I sneaked it into 006_tranfer_modes.pl.
> WDYT?
Neat. That works for me.
+ $old->safe_psql('postgres', q|
+ CREATE EXTENSION dummy_seclabel;
Still I think that this bit is going to fail with installcheck,
because src/test/modules/ is not installed by default :)
You can make that conditional with check_extension(), like the tricks
for injection_points in other TAP tests. The security label creation
and test also need to be made conditional. The work is already done
with the check on oldinstall, it just needs an extra tweak, so I would
store the condition in a separate variable at the top of test_mode()
in 006_transfer_modes.pl, or something equivalent.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
@ 2025-09-05 18:12 ` Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2025-09-05 18:12 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, Sep 05, 2025 at 03:35:21PM +0900, Michael Paquier wrote:
> + $old->safe_psql('postgres', q|
> + CREATE EXTENSION dummy_seclabel;
>
> Still I think that this bit is going to fail with installcheck,
> because src/test/modules/ is not installed by default :)
>
> You can make that conditional with check_extension(), like the tricks
> for injection_points in other TAP tests. The security label creation
> and test also need to be made conditional. The work is already done
> with the check on oldinstall, it just needs an extra tweak, so I would
> store the condition in a separate variable at the top of test_mode()
> in 006_transfer_modes.pl, or something equivalent.
How does this look?
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2025-09-06 01:12 ` Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Michael Paquier @ 2025-09-06 01:12 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, Sep 05, 2025 at 01:12:49PM -0500, Nathan Bossart wrote:
> How does this look?
+ # We can only test security labels if both the old and new installations
+ # have dummy_seclabel.
+ my $test_seclabel = 1;
+ $old->start;
+ if (!$old->check_extension('dummy_seclabel'))
+ {
+ $test_seclabel = 0;
+ }
+ $old->stop;
+ $new->start;
+ if (!$new->check_extension('dummy_seclabel'))
+ {
+ $test_seclabel = 0;
+ }
+ $new->stop;
Yep. This plan is safe to rely on.
A tiny comment I may have is that the LO numbers are hardcoded and
duplicated. I would have used a variable to store these numbers.
Please feel free to ignore my picky-ism.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
@ 2025-09-08 19:20 ` Nathan Bossart <[email protected]>
2025-09-08 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Nathan Bossart @ 2025-09-08 19:20 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Sat, Sep 06, 2025 at 10:12:11AM +0900, Michael Paquier wrote:
> Yep. This plan is safe to rely on.
Committed, thanks for reviewing!
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2025-09-08 23:46 ` Michael Paquier <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Michael Paquier @ 2025-09-08 23:46 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Sep 08, 2025 at 02:20:33PM -0500, Nathan Bossart wrote:
> Committed, thanks for reviewing!
Thanks!
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-03 23:16 ` Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-03 23:16 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2025-09-08 14:20:33 -0500, Nathan Bossart wrote:
> On Sat, Sep 06, 2025 at 10:12:11AM +0900, Michael Paquier wrote:
> > Yep. This plan is safe to rely on.
>
> Committed, thanks for reviewing!
I just had occasion to look at this code and I found the way this works quite
confusing:
When upgrading from a new enough server, we migrate
pg_largeobject_metadata. So far so good. But we *also* do a COPY FROM
COPY "pg_catalog"."pg_largeobject_metadata" ("oid", "lomowner", "lomacl") FROM stdin;
...
for all the large objects that have a description or a security label.
For a while I was somewhat baffled, because that sure looks like it ought to
lead to uniqueness violations. But it doesn't.
The reason, I think, is that the COPY is happening into a relfilenode that
will be overwritten later, it doesn't yet contain the contents of the old
cluster.
Presumably we do this because we need the temporary pg_largeobject_metadata to
make COMMENT ON and security label commands not fail.
If this is the reasoning / how it works, shouldn't there be a comment in the
code or the commit message explaining that? Because it sure seems non-obvious
to me.
It's also not entirely obvious to me that this is safe - after all
(bbe08b8869bd, revised in 0e758ae89) appeared to have taken some pains to
ensure that the file gets unlinked immediately during the "binary upgrade
mode" TRUNCATE. But now we are actually filling that file again, after the
relation had been truncated?
Separately, if we don't handle large objects that don't have comments or
labels via pg_dump, why do we do dependency tracking for all LOs in getLOs(),
rather than just the ones that have a comment / label? Given how much memory
both the query results and the dependency tracking take...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-03 23:46 ` Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-03 23:46 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-03 18:16:17 -0500, Andres Freund wrote:
> On 2025-09-08 14:20:33 -0500, Nathan Bossart wrote:
> > On Sat, Sep 06, 2025 at 10:12:11AM +0900, Michael Paquier wrote:
> > > Yep. This plan is safe to rely on.
> >
> > Committed, thanks for reviewing!
>
> I just had occasion to look at this code and I found the way this works quite
> confusing:
>
> When upgrading from a new enough server, we migrate
> pg_largeobject_metadata. So far so good. But we *also* do a COPY FROM
> COPY "pg_catalog"."pg_largeobject_metadata" ("oid", "lomowner", "lomacl") FROM stdin;
> ...
>
> for all the large objects that have a description or a security label.
>
>
> For a while I was somewhat baffled, because that sure looks like it ought to
> lead to uniqueness violations. But it doesn't.
>
> The reason, I think, is that the COPY is happening into a relfilenode that
> will be overwritten later, it doesn't yet contain the contents of the old
> cluster.
>
> Presumably we do this because we need the temporary pg_largeobject_metadata to
> make COMMENT ON and security label commands not fail.
>
>
> If this is the reasoning / how it works, shouldn't there be a comment in the
> code or the commit message explaining that? Because it sure seems non-obvious
> to me.
>
> It's also not entirely obvious to me that this is safe - after all
> (bbe08b8869bd, revised in 0e758ae89) appeared to have taken some pains to
> ensure that the file gets unlinked immediately during the "binary upgrade
> mode" TRUNCATE. But now we are actually filling that file again, after the
> relation had been truncated?
An example of what could go wrong:
Imagine that we add support for freezing pages on-access (as we are
discussing). If pg_largeobject_metadata was *not* frozen / vacuumed on the old
server, there might not be a _vm on the old server, but due to the new
on-access freezing, there might be one created for the data in the ephemeral
pg_largeobject_metadata during the accesses from COMMENT ON.
Because there's no VM on the old server, transfer_relfile() would just return
false:
/* Is it an extent, fsm, or vm file? */
if (type_suffix[0] != '\0' || segno != 0)
{
/* Did file open fail? */
if (stat(old_file, &statbuf) != 0)
{
/* File does not exist? That's OK, just return */
if (errno == ENOENT)
return;
therefore not reaching the:
unlink(new_file);
Leaving a completely wrong visibilitymap in place.
There are other scary possibilities. Imagine that the ephemeral
pg_largeobject_metadata ends up bigger than on the old server, e.g. because we
are a bit more aggressive about bulk relation extension in the new version
(which we should be!). If that size difference ends up with a different
segment count between the old server and the ephemeral relation in the new
server, we're in trouble: transfer_relfile() wouldn't unlink the additional
segments on the target system.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-04 16:06 ` Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-04 16:06 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Feb 03, 2026 at 06:46:25PM -0500, Andres Freund wrote:
>> The reason, I think, is that the COPY is happening into a relfilenode that
>> will be overwritten later, it doesn't yet contain the contents of the old
>> cluster.
>>
>> Presumably we do this because we need the temporary pg_largeobject_metadata to
>> make COMMENT ON and security label commands not fail.
>>
>> If this is the reasoning / how it works, shouldn't there be a comment in the
>> code or the commit message explaining that? Because it sure seems non-obvious
>> to me.
Right, the COPY for LOs with comments and security labels is solely meant
to avoid failure when restoring the comments and security labels, since we
won't have transferred the relation files yet. This was the case before
commit 12a53c732c, where we had this comment in getBlobs():
* We *do* dump out the definition of the blob because we need that to
* make the restoration of the comments, and anything else, work since
* pg_upgrade copies the files behind pg_largeobject and
* pg_largeobject_metadata after the dump is restored.
Commit 3bcfcd815e (mine) added this one to pg_dump.c:
* If upgrading from v16 or newer, only dump large objects with
* comments/seclabels. For these upgrades, pg_upgrade can copy/link
* pg_largeobject_metadata's files (which is usually faster) but we
* still need to dump LOs with comments/seclabels here so that the
* subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade
* can't copy/link the files from older versions because aclitem
* (needed by pg_largeobject_metadata.lomacl) changed its storage
* format in v16.
IIUC your critique is that this doesn't explain the overwriting behavior
like the older comment does. I'll work on adding that.
>> It's also not entirely obvious to me that this is safe - after all
>> (bbe08b8869bd, revised in 0e758ae89) appeared to have taken some pains to
>> ensure that the file gets unlinked immediately during the "binary upgrade
>> mode" TRUNCATE. But now we are actually filling that file again, after the
>> relation had been truncated?
>
> An example of what could go wrong:
>
> [... examples of what could go wrong ...]
I'm considering a couple of options here, but it seems like the easiest
thing to do is to move the TRUNCATE commands to the end of the dump file.
At least, that seems to be sufficient for our existing tests. If that
seems okay to you, I can work on putting together a patch.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-04 20:08 ` Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-04 20:08 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Feb 04, 2026 at 10:06:29AM -0600, Nathan Bossart wrote:
> IIUC your critique is that this doesn't explain the overwriting behavior
> like the older comment does. I'll work on adding that.
>
> [...]
>
> I'm considering a couple of options here, but it seems like the easiest
> thing to do is to move the TRUNCATE commands to the end of the dump file.
> At least, that seems to be sufficient for our existing tests. If that
> seems okay to you, I can work on putting together a patch.
Here is a rough first draft of a patch that does this.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-05 16:19 ` Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-05 16:19 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-04 10:06:29 -0600, Nathan Bossart wrote:
> IIUC your critique is that this doesn't explain the overwriting behavior
> like the older comment does. I'll work on adding that.
I think even the old comment was woefully under-documenting that the commands
are all just make work that's going to be thrown out almost immediately after.
Thanks for addressing that!
On 2026-02-04 14:08:47 -0600, Nathan Bossart wrote:
> On Wed, Feb 04, 2026 at 10:06:29AM -0600, Nathan Bossart wrote:
> > IIUC your critique is that this doesn't explain the overwriting behavior
> > like the older comment does. I'll work on adding that.
> >
> > [...]
> >
> > I'm considering a couple of options here, but it seems like the easiest
> > thing to do is to move the TRUNCATE commands to the end of the dump file.
> > At least, that seems to be sufficient for our existing tests. If that
> > seems okay to you, I can work on putting together a patch.
>
> Here is a rough first draft of a patch that does this.
It certainly seems better than what we do now. Still feels pretty grotty and
error prone to me that we fill the catalog table and then throw the contents
out.
> @@ -1157,7 +1158,10 @@ main(int argc, char **argv)
> * subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade
> * can't copy/link the files from older versions because aclitem
> * (needed by pg_largeobject_metadata.lomacl) changed its storage
> - * format in v16.
> + * format in v16. At the end of the dump, we'll generate a TRUNCATE
> + * command for pg_largeobject_metadata so that it's contents are
> + * cleared in preparation for the subsequent file transfer by
> + * pg_upgrade.
> */
I'd move that comment to earlier in the paragraph, it sounds a bit like it
applies to the v16 specific bits.
> if (fout->remoteVersion >= 160000)
> lo_metadata->dataObj->filtercond = "WHERE oid IN "
> @@ -1243,6 +1247,13 @@ main(int argc, char **argv)
> for (i = 0; i < numObjs; i++)
> dumpDumpableObject(fout, dobjs[i]);
>
> + /*
> + * For binary upgrades, set relfrozenxids, relminmxids, and relfilenodes
> + * of pg_largeobject and maybe pg_largeobject_metadata, and remove all
> + * their files. We will transfer them from the old cluster as needed.
> + */
> + dumpLOTruncation(fout);
> +
> /*
> * Set up options info to ensure we dump what we want.
> */
Seems good to move this to a dedicated function, regardless of anything else.
Do I see correctly that we just rely on the ordering in the file, rather than
dependencies? That's not a complaint, I just don't know that code very well.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-05 17:36 ` Nathan Bossart <[email protected]>
2026-02-05 17:40 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-05 17:36 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 05, 2026 at 11:19:46AM -0500, Andres Freund wrote:
> It certainly seems better than what we do now. Still feels pretty grotty and
> error prone to me that we fill the catalog table and then throw the contents
> out.
Before I go any further with this approach, I thought of something else we
could do that I believe is worth considering...
As of commit 3bcfcd815e, the only reason we are dumping any of
pg_largeobject_metadata at all is to avoid an ERROR during COMMENT ON or
SECURITY LABEL ON because the call to LargeObjectExists() in
get_object_address() returns false. If we bypass that check in
binary-upgrade mode, we can skip dumping pg_largeobject_metadata entirely.
The attached patch passes our existing tests, and it seems to create the
expected binary-upgrade-mode dump files, too. I haven't updated any of the
comments yet.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-05 17:40 ` Nathan Bossart <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-05 17:40 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 05, 2026 at 11:36:00AM -0600, Nathan Bossart wrote:
> @@ -1046,7 +1046,7 @@ get_object_address(ObjectType objtype, Node *object,
> address.classId = LargeObjectRelationId;
> address.objectId = oidparse(object);
> address.objectSubId = 0;
> - if (!LargeObjectExists(address.objectId))
> + if (!LargeObjectExists(address.objectId) && !IsBinaryUpgrade)
> {
> if (!missing_ok)
> ereport(ERROR,
(Probably better to set missing_ok for only the specific commands we want
to bypass this check, but you get the idea...)
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-05 18:02 ` Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-05 18:02 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-05 11:36:00 -0600, Nathan Bossart wrote:
> On Thu, Feb 05, 2026 at 11:19:46AM -0500, Andres Freund wrote:
> > It certainly seems better than what we do now. Still feels pretty grotty and
> > error prone to me that we fill the catalog table and then throw the contents
> > out.
>
> Before I go any further with this approach, I thought of something else we
> could do that I believe is worth considering...
>
> As of commit 3bcfcd815e, the only reason we are dumping any of
> pg_largeobject_metadata at all is to avoid an ERROR during COMMENT ON or
> SECURITY LABEL ON because the call to LargeObjectExists() in
> get_object_address() returns false. If we bypass that check in
> binary-upgrade mode, we can skip dumping pg_largeobject_metadata entirely.
Yea, I think that's worth considering. As you say downthread, the check for
binary upgrade should probably be moved, but that's details.
Upthread I also wondering why we do all the work in getLOs() if we don't
actually need most of it (only if there are comments or labels). Right now
that's a very slow and very memory intensive part of doing an upgrade of a
system with a lot of binary upgrades. Do we need *any* of that if we go the
path you suggest?
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-05 19:31 ` Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-05 19:31 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 05, 2026 at 01:02:17PM -0500, Andres Freund wrote:
> Upthread I also wondering why we do all the work in getLOs() if we don't
> actually need most of it (only if there are comments or labels). Right now
> that's a very slow and very memory intensive part of doing an upgrade of a
> system with a lot of binary upgrades. Do we need *any* of that if we go the
> path you suggest?
AFAICT we only need it for the comments and security labels later on.
Commit a45c78e3 did batch 1000 large objects into each ArchiveEntry, but of
course there can still be a ton of entries. In theory, we could update the
pg_largeobject_metadata query to only retrieve LOs with comments and
security labels. I'm not sure it's worth trying to optimize further than
that; we've long operated under the assumption that comments/seclabels on
LOs are pretty rare.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-05 20:23 ` Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:34 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Andres Freund @ 2026-02-05 20:23 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-05 13:31:23 -0600, Nathan Bossart wrote:
> On Thu, Feb 05, 2026 at 01:02:17PM -0500, Andres Freund wrote:
> > Upthread I also wondering why we do all the work in getLOs() if we don't
> > actually need most of it (only if there are comments or labels). Right now
> > that's a very slow and very memory intensive part of doing an upgrade of a
> > system with a lot of binary upgrades. Do we need *any* of that if we go the
> > path you suggest?
>
> AFAICT we only need it for the comments and security labels later on.
And in binary upgrade mode not even for that, if we do the thing we talked
about re not checking references in binary upgrade mode? Right?
> Commit a45c78e3 did batch 1000 large objects into each ArchiveEntry, but of
> course there can still be a ton of entries.
Entries and then also the PGresult (almost as large as all the entries). The
peak memory usage is pretty bad. We could address the PGresult memory usage
with PQsetChunkedRowsMode() or explicit cursor use, but it doesn't seem
entirely trivial to combine with the batching.
> In theory, we could update the pg_largeobject_metadata query to only
> retrieve LOs with comments and security labels. I'm not sure it's worth
> trying to optimize further than that; we've long operated under the
> assumption that comments/seclabels on LOs are pretty rare.
I think that'd be a huge improvement. Right now it's not hard to get into a
situation where you have too many LOs to not have enough memory to do a
pg_upgrade.
Memory usage aside, it's also slow and expensive from the query execution and
data transfer side. Because of the ORDER BY that the batching requires, the
server needs to sort all of pg_largeobject_metadata before any rows can be
returned.
For 5M LOs:
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=715978.34..728478.39 rows=5000020 width=72) (actual time=10292.252..10652.950 rows=5000020.00 loops=1) │
│ Sort Key: pg_largeobject_metadata.lomowner, ((pg_largeobject_metadata.lomacl)::text), pg_largeobject_metadata.oid │
│ Sort Method: quicksort Memory: 509110kB │
│ -> Seq Scan on pg_largeobject_metadata (cost=0.00..159638.55 rows=5000020 width=72) (actual time=0.034..2284.442 rows=5000020.00 loops=1) │
│ SubPlan expr_1 │
│ -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1.00 loops=5000020) │
│ Planning Time: 0.117 ms │
│ Serialization: time=3961.343 ms output=218686kB format=text │
│ Execution Time: 14930.747 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
/usr/bin/time -v pg_dump --binary-upgrade --no-data --quote-all-identifiers --no-statistics --format=custom -f /tmp/dump lo_5m
Command being timed: "pg_dump --binary-upgrade --no-data --quote-all-identifiers --no-statistics --format=custom -f /tmp/dump lo_5m"
User time (seconds): 1.85
System time (seconds): 0.54
Percent of CPU this job got: 16%
Elapsed (wall clock) time (h:mm:ss or m:ss): 0:14.55
Average shared text size (kbytes): 0
Average unshared data size (kbytes): 0
Average stack size (kbytes): 0
Average total size (kbytes): 0
Maximum resident set size (kbytes): 935084
Average resident set size (kbytes): 0
Major (requiring I/O) page faults: 0
Minor (reclaiming a frame) page faults: 133379
Voluntary context switches: 30486
Involuntary context switches: 6
Swaps: 0
File system inputs: 0
File system outputs: 16
Socket messages sent: 0
Socket messages received: 0
Signals delivered: 0
Page size (bytes): 4096
Exit status: 0
Peak memory usage ~1GB.
And unfortunately 5M LOs is not a whole lot.
Filtering the LOs in the query to only return ones that have a comment / label
would typically make the query much faster and pg_dump consume a lot less
memory.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-05 20:30 ` Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-05 20:30 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 05, 2026 at 03:23:38PM -0500, Andres Freund wrote:
> On 2026-02-05 13:31:23 -0600, Nathan Bossart wrote:
>> On Thu, Feb 05, 2026 at 01:02:17PM -0500, Andres Freund wrote:
>> > Upthread I also wondering why we do all the work in getLOs() if we don't
>> > actually need most of it (only if there are comments or labels). Right now
>> > that's a very slow and very memory intensive part of doing an upgrade of a
>> > system with a lot of binary upgrades. Do we need *any* of that if we go the
>> > path you suggest?
>>
>> AFAICT we only need it for the comments and security labels later on.
>
> And in binary upgrade mode not even for that, if we do the thing we talked
> about re not checking references in binary upgrade mode? Right?
Well, we need _something_ to do what dumpLO() does today, i.e., call
dumpComment() and dumpSecLabel() for each large object that has one or the
other. I'm sure we could decouple that from the getLOs() machinery if we
tried hard enough, but I'm not seeing any great advantage in doing so if we
teach getLOs() to only get what it needs.
>> In theory, we could update the pg_largeobject_metadata query to only
>> retrieve LOs with comments and security labels. I'm not sure it's worth
>> trying to optimize further than that; we've long operated under the
>> assumption that comments/seclabels on LOs are pretty rare.
>
> I think that'd be a huge improvement. Right now it's not hard to get into a
> situation where you have too many LOs to not have enough memory to do a
> pg_upgrade.
It seems to work. I'm working on a nicer patch for all this stuff.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-05 21:29 ` Nathan Bossart <[email protected]>
2026-02-06 19:40 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-05 21:29 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 05, 2026 at 02:30:28PM -0600, Nathan Bossart wrote:
> On Thu, Feb 05, 2026 at 03:23:38PM -0500, Andres Freund wrote:
>> On 2026-02-05 13:31:23 -0600, Nathan Bossart wrote:
>>> In theory, we could update the pg_largeobject_metadata query to only
>>> retrieve LOs with comments and security labels. I'm not sure it's worth
>>> trying to optimize further than that; we've long operated under the
>>> assumption that comments/seclabels on LOs are pretty rare.
>>
>> I think that'd be a huge improvement. Right now it's not hard to get into a
>> situation where you have too many LOs to not have enough memory to do a
>> pg_upgrade.
>
> It seems to work. I'm working on a nicer patch for all this stuff.
Here is what I have so far.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-06 19:40 ` Nathan Bossart <[email protected]>
2026-02-07 12:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Hannu Krosing <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-06 19:40 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 05, 2026 at 03:29:55PM -0600, Nathan Bossart wrote:
> Here is what I have so far.
BTW I ran 006_transfer_modes.pl (which tests LOs with comments and security
labels) for upgrades from v10, v13, v16, v17, and v18, and all tests
succeeded.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-06 19:40 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-07 12:12 ` Hannu Krosing <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Hannu Krosing @ 2026-02-07 12:12 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Andres Freund <[email protected]>; Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; PostgreSQL Hackers <[email protected]>
Parts of this discussion are very much related to
https://commitfest.postgresql.org/patch/5774/ - "Extending skipping FK
checks on replicas to include ADD FK and TRUNCATE"
I have seen cases where adding a foreign key after copying data takes
several days for no benefit at all.
We should be allowing more of "skip unuseful work" options for all the
cases we can be pretty sure the dat ais ok - upgrades, replica setups
and restoring backup.
We probably should also have an option to tolerate slight corruption,
like duplicate PK entries if the source is an existing database.
99 times out of 100 the user would rather have a slight corruption
than no database at all, for example in case they had to do an
emergency restore and if fails after running 3 hours because ADD
PRIMARY KEY hits a duplicate row.
On Fri, Feb 6, 2026 at 8:40 PM Nathan Bossart <[email protected]> wrote:
>
> On Thu, Feb 05, 2026 at 03:29:55PM -0600, Nathan Bossart wrote:
> > Here is what I have so far.
>
> BTW I ran 006_transfer_modes.pl (which tests LOs with comments and security
> labels) for upgrades from v10, v13, v16, v17, and v18, and all tests
> succeeded.
>
> --
> nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-08 21:26 ` Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-08 21:26 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-05 15:29:55 -0600, Nathan Bossart wrote:
> Here is what I have so far.
Thanks!
> --- a/src/bin/pg_dump/pg_dump.c
> +++ b/src/bin/pg_dump/pg_dump.c
> @@ -214,12 +214,6 @@ static int nbinaryUpgradeClassOids = 0;
> static SequenceItem *sequences = NULL;
> static int nsequences = 0;
>
> -/*
> - * For binary upgrade, the dump ID of pg_largeobject_metadata is saved for use
> - * as a dependency for pg_shdepend and any large object comments/seclabels.
> - */
> -static DumpId lo_metadata_dumpId;
> -
> /* Maximum number of relations to fetch in a fetchAttributeStats() call. */
> #define MAX_ATTR_STATS_RELS 64
>
> @@ -1121,27 +1115,20 @@ main(int argc, char **argv)
> getTableData(&dopt, tblinfo, numTables, RELKIND_SEQUENCE);
>
> /*
> - * For binary upgrade mode, dump pg_largeobject_metadata and the
> - * associated pg_shdepend rows. This is faster to restore than the
> - * equivalent set of large object commands. We can only do this for
> - * upgrades from v12 and newer; in older versions, pg_largeobject_metadata
> - * was created WITH OIDS, so the OID column is hidden and won't be dumped.
> + * For binary upgrade mode, dump the pg_shdepend rows for large objects
> + * and maybe even pg_largeobject_metadata (see comment below for details).
> + * This is faster to restore than the equivalent set of large object
> + * commands. We can only do this for upgrades from v12 and newer; in
> + * older versions, pg_largeobject_metadata was created WITH OIDS, so the
> + * OID column is hidden and won't be dumped.
> */
It's not really related to this change, but what is that WITH OIDS bit about?
Sure, they aren't shown by default, but all it takes to change that is to
explicitly add the output column? I'm not saying we have to do that, I just
don't understand the reasoning as written here.
> @@ -3979,7 +3964,25 @@ getLOs(Archive *fout)
> appendPQExpBufferStr(loQry,
> "SELECT oid, lomowner, lomacl, "
> "acldefault('L', lomowner) AS acldefault "
> - "FROM pg_largeobject_metadata "
> + "FROM pg_largeobject_metadata ");
> +
> + /*
> + * For upgrades from v12 or newer, we transfer pg_largeobject_metadata via
*binary upgrades.
The improvements in pg_upgrade time and memory in a cluster with 50M LOs [1]
are really quite impressive, even if upgrading from < 16. It's rare to improve
memory usage by several orders of magnitude.
Greetings,
Andres Freund
[1] c=20;pgbench -n -c$c -j$c -P1 -t $((50000000 / $c)) -f <(echo -e "SELECT lo_create(0) AS loid\n") 50m_los
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-08 22:00 ` Nathan Bossart <[email protected]>
2026-02-09 17:05 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 2 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-08 22:00 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Sun, Feb 08, 2026 at 04:26:41PM -0500, Andres Freund wrote:
> On 2026-02-05 15:29:55 -0600, Nathan Bossart wrote:
>> + * commands. We can only do this for upgrades from v12 and newer; in
>> + * older versions, pg_largeobject_metadata was created WITH OIDS, so the
>> + * OID column is hidden and won't be dumped.
>> */
>
> It's not really related to this change, but what is that WITH OIDS bit about?
> Sure, they aren't shown by default, but all it takes to change that is to
> explicitly add the output column? I'm not saying we have to do that, I just
> don't understand the reasoning as written here.
IIRC the issue is that getTableAttrs() won't pick up the OID column on
older versions. It might be easy to fix that by adjusting its query for
binary upgrades from <v12. That could be worth doing, if for no other
reason than to simplify some of the pg_dump code. I'll make a note of it.
> The improvements in pg_upgrade time and memory in a cluster with 50M LOs [1]
> are really quite impressive, even if upgrading from < 16. It's rare to improve
> memory usage by several orders of magnitude.
Nice!
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-09 17:05 ` Nathan Bossart <[email protected]>
2026-02-09 20:58 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-09 17:05 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Here is what I have staged for commit, which I'm planning to do this
afternoon.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-09 17:05 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-09 20:58 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-09 20:58 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Feb 09, 2026 at 11:05:50AM -0600, Nathan Bossart wrote:
> Here is what I have staged for commit, which I'm planning to do this
> afternoon.
Committed.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-11 21:00 ` Nathan Bossart <[email protected]>
2026-02-12 20:05 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
1 sibling, 2 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-11 21:00 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Sun, Feb 08, 2026 at 04:00:40PM -0600, Nathan Bossart wrote:
> On Sun, Feb 08, 2026 at 04:26:41PM -0500, Andres Freund wrote:
>> On 2026-02-05 15:29:55 -0600, Nathan Bossart wrote:
>>> + * commands. We can only do this for upgrades from v12 and newer; in
>>> + * older versions, pg_largeobject_metadata was created WITH OIDS, so the
>>> + * OID column is hidden and won't be dumped.
>>> */
>>
>> It's not really related to this change, but what is that WITH OIDS bit about?
>> Sure, they aren't shown by default, but all it takes to change that is to
>> explicitly add the output column? I'm not saying we have to do that, I just
>> don't understand the reasoning as written here.
>
> IIRC the issue is that getTableAttrs() won't pick up the OID column on
> older versions. It might be easy to fix that by adjusting its query for
> binary upgrades from <v12. That could be worth doing, if for no other
> reason than to simplify some of the pg_dump code. I'll make a note of it.
This was a little more painful than I expected, but this seems to be what
is required to allow COPY-ing pg_largeobject_metadata during binary
upgrades from < v12.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-12 20:05 ` Nathan Bossart <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-12 20:05 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Feb 11, 2026 at 03:00:51PM -0600, Nathan Bossart wrote:
> This was a little more painful than I expected, but this seems to be what
> is required to allow COPY-ing pg_largeobject_metadata during binary
> upgrades from < v12.
I don't think there's anything terribly controversial here, so I'd like to
proceed with committing this in the next couple of days if there are no
objections.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-12 21:46 ` Andres Freund <[email protected]>
2026-02-12 21:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
1 sibling, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-12 21:46 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-11 15:00:51 -0600, Nathan Bossart wrote:
> On Sun, Feb 08, 2026 at 04:00:40PM -0600, Nathan Bossart wrote:
> > IIRC the issue is that getTableAttrs() won't pick up the OID column on
> > older versions. It might be easy to fix that by adjusting its query for
> > binary upgrades from <v12. That could be worth doing, if for no other
> > reason than to simplify some of the pg_dump code. I'll make a note of it.
>
> This was a little more painful than I expected, but this seems to be what
> is required to allow COPY-ing pg_largeobject_metadata during binary
> upgrades from < v12.
Nice!
> @@ -2406,11 +2404,14 @@ dumpTableData_copy(Archive *fout, const void *dcontext)
> column_list = fmtCopyColumnList(tbinfo, clistBuf);
>
> /*
> - * Use COPY (SELECT ...) TO when dumping a foreign table's data, and when
> - * a filter condition was specified. For other cases a simple COPY
> - * suffices.
> + * Use COPY (SELECT ...) TO when dumping a foreign table's data, when a
> + * filter condition was specified, and when in binary upgrade mode and
> + * dumping an old pg_largeobject_metadata defined WITH OIDS. For other
> + * cases a simple COPY suffices.
> */
> - if (tdinfo->filtercond || tbinfo->relkind == RELKIND_FOREIGN_TABLE)
> + if (tdinfo->filtercond || tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
> + (fout->dopt->binary_upgrade && fout->remoteVersion < 120000 &&
> + tbinfo->dobj.catId.oid == LargeObjectMetadataRelationId))
> {
> /* Temporary allows to access to foreign tables to dump data */
> if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
Not really the fault of this patch, but it seems somewhat grotty that this has
binary upgrade specific code in this place. I was certainly confused when
first trying to use pg_dump in binary upgrade mode with large objects, because
no data was dumped when using plain text mode, which is what I had been using
for simplicity...
> @@ -2406,11 +2404,14 @@ dumpTableData_copy(Archive *fout, const void *dcontext)
> column_list = fmtCopyColumnList(tbinfo, clistBuf);
>
> /*
> - * Use COPY (SELECT ...) TO when dumping a foreign table's data, and when
> - * a filter condition was specified. For other cases a simple COPY
> - * suffices.
> + * Use COPY (SELECT ...) TO when dumping a foreign table's data, when a
> + * filter condition was specified, and when in binary upgrade mode and
> + * dumping an old pg_largeobject_metadata defined WITH OIDS. For other
> + * cases a simple COPY suffices.
> */
> - if (tdinfo->filtercond || tbinfo->relkind == RELKIND_FOREIGN_TABLE)
> + if (tdinfo->filtercond || tbinfo->relkind == RELKIND_FOREIGN_TABLE ||
> + (fout->dopt->binary_upgrade && fout->remoteVersion < 120000 &&
> + tbinfo->dobj.catId.oid == LargeObjectMetadataRelationId))
> {
> /* Temporary allows to access to foreign tables to dump data */
> if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
I guess you could instead generate a COPY using WITH OIDS. But it's probably
not worth having that path, given we already need to support COPY (SELECT ..).
OTOH, I think it'd perhaps avoid needing to deal with this:
> @@ -9442,7 +9428,18 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
> "(pt.classoid = co.tableoid AND pt.objoid = co.oid)\n");
>
> appendPQExpBufferStr(q,
> - "WHERE a.attnum > 0::pg_catalog.int2\n"
> + "WHERE a.attnum > 0::pg_catalog.int2\n");
> +
> + /*
> + * For binary upgrades from <v12, be sure to pick up
> + * pg_largeobject_metadata's oid column.
> + */
> + if (fout->dopt->binary_upgrade && fout->remoteVersion < 120000)
> + appendPQExpBufferStr(q,
> + "OR (a.attnum = -2::pg_catalog.int2 AND src.tbloid = "
> + CppAsString2(LargeObjectMetadataRelationId) ")\n");
> +
as we'd just include the oid column without needing to somehow include it in
the attribute list.
> @@ -9544,7 +9541,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
>
> for (int j = 0; j < numatts; j++, r++)
> {
> - if (j + 1 != atoi(PQgetvalue(res, r, i_attnum)))
> + if (j + 1 != atoi(PQgetvalue(res, r, i_attnum)) &&
> + !(fout->dopt->binary_upgrade && fout->remoteVersion < 120000 &&
> + tbinfo->dobj.catId.oid == LargeObjectMetadataRelationId))
> pg_fatal("invalid column numbering in table \"%s\"",
> tbinfo->dobj.name);
> tbinfo->attnames[j] = pg_strdup(PQgetvalue(res, r, i_attname));
> --
> 2.50.1 (Apple Git-155)
I guess WITH OIDs also would avoid the need for this.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-12 21:59 ` Nathan Bossart <[email protected]>
2026-02-12 22:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-12 21:59 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 12, 2026 at 04:46:30PM -0500, Andres Freund wrote:
> On 2026-02-11 15:00:51 -0600, Nathan Bossart wrote:
>> This was a little more painful than I expected, but this seems to be what
>> is required to allow COPY-ing pg_largeobject_metadata during binary
>> upgrades from < v12.
>
> Nice!
Thanks for looking.
> Not really the fault of this patch, but it seems somewhat grotty that this has
> binary upgrade specific code in this place. I was certainly confused when
> first trying to use pg_dump in binary upgrade mode with large objects, because
> no data was dumped when using plain text mode, which is what I had been using
> for simplicity...
Yeah, this has been an annoyance while hacking in this area. I haven't
looked into what it'd take to fix it, though.
> I guess you could instead generate a COPY using WITH OIDS. But it's probably
> not worth having that path, given we already need to support COPY (SELECT ..).
Ah, I forgot all about that COPY option...
> as we'd just include the oid column without needing to somehow include it in
> the attribute list.
>
> [...]
>
> I guess WITH OIDs also would avoid the need for this.
Will give it a try.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-12 21:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-12 22:26 ` Nathan Bossart <[email protected]>
2026-02-16 21:15 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-12 22:26 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Feb 12, 2026 at 03:59:24PM -0600, Nathan Bossart wrote:
> On Thu, Feb 12, 2026 at 04:46:30PM -0500, Andres Freund wrote:
>> I guess you could instead generate a COPY using WITH OIDS. But it's probably
>> not worth having that path, given we already need to support COPY (SELECT ..).
>
> Ah, I forgot all about that COPY option...
>
>> as we'd just include the oid column without needing to somehow include it in
>> the attribute list.
>>
>> [...]
>>
>> I guess WITH OIDs also would avoid the need for this.
>
> Will give it a try.
So, in addition to hacking in the OIDS option to the COPY command executed
on the old server, we still need to hack the OID column into the column
list for the dumped COPY command (or omit the column list entirely for
pg_largeobject_metadata). That seems to result in roughly the same level
of hackery as before.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-12 21:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 22:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-16 21:15 ` Nathan Bossart <[email protected]>
2026-02-16 21:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Bossart @ 2026-02-16 21:15 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Committed.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-12 21:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 22:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-16 21:15 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
@ 2026-02-16 21:19 ` Andres Freund <[email protected]>
2026-02-16 21:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
0 siblings, 1 reply; 43+ messages in thread
From: Andres Freund @ 2026-02-16 21:19 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On 2026-02-16 15:15:42 -0600, Nathan Bossart wrote:
> Committed.
Thanks for working on this! I think the situation now is a lot better than it
was in 18...
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-12 21:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 22:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-16 21:15 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-16 21:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-16 21:31 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 43+ messages in thread
From: Nathan Bossart @ 2026-02-16 21:31 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Feb 16, 2026 at 04:19:10PM -0500, Andres Freund wrote:
> Thanks for working on this! I think the situation now is a lot better than it
> was in 18...
I hope so... I appreciate the pointers/reviews.
--
nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
@ 2026-02-05 20:34 ` Andres Freund <[email protected]>
1 sibling, 0 replies; 43+ messages in thread
From: Andres Freund @ 2026-02-05 20:34 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Michael Paquier <[email protected]>; Tom Lane <[email protected]>; Nitin Motiani <[email protected]>; Hannu Krosing <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On 2026-02-05 15:23:38 -0500, Andres Freund wrote:
> Memory usage aside, it's also slow and expensive from the query execution and
> data transfer side. Because of the ORDER BY that the batching requires, the
> server needs to sort all of pg_largeobject_metadata before any rows can be
> returned.
>
> For 5M LOs:
> ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
> │ Sort (cost=715978.34..728478.39 rows=5000020 width=72) (actual time=10292.252..10652.950 rows=5000020.00 loops=1) │
> │ Sort Key: pg_largeobject_metadata.lomowner, ((pg_largeobject_metadata.lomacl)::text), pg_largeobject_metadata.oid │
> │ Sort Method: quicksort Memory: 509110kB │
> │ -> Seq Scan on pg_largeobject_metadata (cost=0.00..159638.55 rows=5000020 width=72) (actual time=0.034..2284.442 rows=5000020.00 loops=1) │
> │ SubPlan expr_1 │
> │ -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1.00 loops=5000020) │
> │ Planning Time: 0.117 ms │
> │ Serialization: time=3961.343 ms output=218686kB format=text │
> │ Execution Time: 14930.747 ms │
> └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
> (9 rows)
This isn't quite the right query, sorry for that. I just tried moving the
acldefault() in a subselect, because I was wondering whether that'd prevent it
from being computed below the sort. Unfortunately no. It's a bit faster
without that, but not much:
EXPLAIN (ANALYZE, SERIALIZE, BUFFERS OFF, VERBOSE) SELECT oid, lomowner, lomacl::pg_catalog.text, acldefault('L', lomowner) AS acldefault FROM pg_largeobject_metadata ORDER BY lomowner, lomacl::pg_catalog.text, oid;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=665978.14..678478.19 rows=5000020 width=72) (actual time=8887.007..9243.088 rows=5000020.00 loops=1) │
│ Output: oid, lomowner, ((lomacl)::text), (acldefault('L'::"char", lomowner)) │
│ Sort Key: pg_largeobject_metadata.lomowner, ((pg_largeobject_metadata.lomacl)::text), pg_largeobject_metadata.oid │
│ Sort Method: quicksort Memory: 509110kB │
│ -> Seq Scan on pg_catalog.pg_largeobject_metadata (cost=0.00..109638.35 rows=5000020 width=72) (actual time=0.029..823.895 rows=5000020.00 loops=1) │
│ Output: oid, lomowner, (lomacl)::text, acldefault('L'::"char", lomowner) │
│ Planning Time: 0.087 ms │
│ Serialization: time=3965.649 ms output=218686kB format=text │
│ Execution Time: 13516.925 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
I might start a separate thread about this misoptimization...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 43+ messages in thread
end of thread, other threads:[~2026-02-16 21:31 UTC | newest]
Thread overview: 43+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-09 19:25 [PATCH v3 4/6] wip: stricter validation for 64bit xids Andres Freund <[email protected]>
2025-08-14 15:14 [PATCH v3 1/1] pg_upgrade: Transfer pg_largeobject_metadata's files when possible. Nathan Bossart <[email protected]>
2025-08-14 15:14 [PATCH v2 1/1] pg_upgrade: Transfer pg_largeobject_metadata's files when possible. Nathan Bossart <[email protected]>
2025-08-14 15:14 [PATCH v1 1/1] pg_upgrade: Transfer pg_largeobject_metadata's files when possible. Nathan Bossart <[email protected]>
2025-08-14 15:22 pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-08-19 07:49 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Hannu Krosing <[email protected]>
2025-08-19 19:18 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-01 06:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-02 14:43 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-04 04:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 01:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-05 06:35 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-05 18:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-06 01:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2025-09-08 19:20 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2025-09-08 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Michael Paquier <[email protected]>
2026-02-03 23:16 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-03 23:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-04 16:06 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-04 20:08 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 16:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 17:36 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 17:40 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 18:02 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 19:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:23 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-05 20:30 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 21:29 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-06 19:40 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-07 12:12 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Hannu Krosing <[email protected]>
2026-02-08 21:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-08 22:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-09 17:05 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-09 20:58 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-11 21:00 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 20:05 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 21:46 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-12 21:59 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-12 22:26 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-16 21:15 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-16 21:19 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
2026-02-16 21:31 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Nathan Bossart <[email protected]>
2026-02-05 20:34 ` Re: pg_upgrade: transfer pg_largeobject_metadata's files when possible Andres Freund <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox