Skip to content

Fix the issue where upgrading from PostgreSQL to IvorySQL fails. #1157

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
gaoxueyu merged 1 commit into from
Jan 29, 2026
Merged

Fix the issue where upgrading from PostgreSQL to IvorySQL fails. #1157

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
83 changes: 71 additions & 12 deletions src/bin/pg_dump/pg_dump.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6958,6 +6958,28 @@ getPackages(Archive *fout, int *numPkgs)
int i_rolname;
int i_pkgacl;
int i_acldefault;


/*
* Check if pg_package table exists. This is needed for pg_upgrade from
* PostgreSQL to IvorySQL, where the source database is PostgreSQL which
* doesn't have pg_package table.
*/
res = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_class c "
"JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid "
"WHERE n.nspname = 'pg_catalog' AND c.relname = 'pg_package'",
PGRES_TUPLES_OK);
if (PQntuples(res) == 0)
{
/* pg_package table doesn't exist, return empty result */
PQclear(res);
destroyPQExpBuffer(query);
*numPkgs = 0;
return (PkgInfo *) pg_malloc0(sizeof(PkgInfo));
}
PQclear(res);

appendPQExpBuffer(query,
"SELECT p.tableoid, p.pkgname, p.oid, p.pkgnamespace,"
"p.pkgowner,"
Expand Down Expand Up @@ -7195,12 +7217,31 @@ getTables(Archive *fout, int *numTables)
"d.refobjsubid AS owning_col, "
"tsp.spcname AS reltablespace, ");

if (fout->remoteVersion < 180000)
appendPQExpBufferStr(query,
"false AS relhasrowid, ");
else
appendPQExpBufferStr(query,
"c.relhasrowid, ");
/*
* Check if relhasrowid column exists. This column is IvorySQL-specific
* for Oracle ROWID compatibility, and doesn't exist in native PostgreSQL.
* We need to check at runtime rather than relying on remoteVersion because
* when upgrading from PostgreSQL to IvorySQL, the source database is
* PostgreSQL which may have the same version number but lacks this column.
*/
{
PGresult *res_rowid;
bool relhasrowid_exists = false;

res_rowid = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_attribute "
"WHERE attrelid = 'pg_class'::regclass "
"AND attname = 'relhasrowid' "
"AND attnum > 0 LIMIT 1",
PGRES_TUPLES_OK);
relhasrowid_exists = (PQntuples(res_rowid) > 0);
PQclear(res_rowid);

if (relhasrowid_exists)
appendPQExpBufferStr(query, "c.relhasrowid, ");
else
appendPQExpBufferStr(query, "false AS relhasrowid, ");
}
Comment on lines +7220 to +7244
Copy link
Copy Markdown
Contributor

@coderabbitai coderabbitai bot Jan 22, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

⚠️ Potential issue | 🟡 Minor

Avoid false positives if the catalog column was dropped.
If relhasrowid is later removed (left as attisdropped), this probe still returns a row and the subsequent SELECT c.relhasrowid will fail. Filter out dropped attributes.

🩹 Proposed fix 
-							  "AND attnum > 0 LIMIT 1",
+							  "AND attnum > 0 AND NOT attisdropped LIMIT 1",
📝 Committable suggestion

‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.

Suggested change
/*
* Check if relhasrowid column exists. This column is IvorySQL-specific
* for Oracle ROWID compatibility, and doesn't exist in native PostgreSQL.
* We need to check at runtime rather than relying on remoteVersion because
* when upgrading from PostgreSQL to IvorySQL, the source database is
* PostgreSQL which may have the same version number but lacks this column.
*/
{
PGresult *res_rowid;
bool relhasrowid_exists = false;
res_rowid = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_attribute "
"WHERE attrelid = 'pg_class'::regclass "
"AND attname = 'relhasrowid' "
"AND attnum > 0 LIMIT 1",
PGRES_TUPLES_OK);
relhasrowid_exists = (PQntuples(res_rowid) > 0);
PQclear(res_rowid);
if (relhasrowid_exists)
appendPQExpBufferStr(query, "c.relhasrowid, ");
else
appendPQExpBufferStr(query, "false AS relhasrowid, ");
}
/*
* Check if relhasrowid column exists. This column is IvorySQL-specific
* for Oracle ROWID compatibility, and doesn't exist in native PostgreSQL.
* We need to check at runtime rather than relying on remoteVersion because
* when upgrading from PostgreSQL to IvorySQL, the source database is
* PostgreSQL which may have the same version number but lacks this column.
*/
{
PGresult *res_rowid;
bool relhasrowid_exists = false;
res_rowid = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_attribute "
"WHERE attrelid = 'pg_class'::regclass "
"AND attname = 'relhasrowid' "
"AND attnum > 0 AND NOT attisdropped LIMIT 1",
PGRES_TUPLES_OK);
relhasrowid_exists = (PQntuples(res_rowid) > 0);
PQclear(res_rowid);
if (relhasrowid_exists)
appendPQExpBufferStr(query, "c.relhasrowid, ");
else
appendPQExpBufferStr(query, "false AS relhasrowid, ");
}
🤖 Prompt for AI Agents 
In `@src/bin/pg_dump/pg_dump.c` around lines 7220 - 7244, The runtime probe for
the relhasrowid column can return a false positive if the attribute exists but
is marked dropped; update the pg_attribute query used in ExecuteSqlQuery
(res_rowid) to exclude dropped attributes by adding a condition like "AND NOT
attisdropped" (or "AND attisdropped = false") so relhasrowid_exists only becomes
true for live columns, leaving the rest of the logic using
query/appendPQExpBufferStr and res_rowid/PQclear unchanged.


if (fout->remoteVersion >= 120000)
appendPQExpBufferStr(query,
Expand Down Expand Up @@ -9309,13 +9350,31 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
appendPQExpBufferStr(q,
"'' AS attcompression,\n");

if (fout->remoteVersion >= 170000)
appendPQExpBuffer(q,
"a.attisinvisible,\n");
else
appendPQExpBuffer(q,
"'f' AS attisinvisible,\n");
/*
* Check if attisinvisible column exists. This column is IvorySQL-specific
* for Oracle compatibility, and doesn't exist in native PostgreSQL.
* We need to check at runtime rather than relying on remoteVersion because
* when upgrading from PostgreSQL to IvorySQL, the source database is
* PostgreSQL which may have the same version number but lacks this column.
*/
{
PGresult *res_invisible;
bool attisinvisible_exists = false;

res_invisible = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_attribute "
"WHERE attrelid = 'pg_attribute'::regclass "
"AND attname = 'attisinvisible' "
"AND attnum > 0 LIMIT 1",
PGRES_TUPLES_OK);
attisinvisible_exists = (PQntuples(res_invisible) > 0);
PQclear(res_invisible);

if (attisinvisible_exists)
appendPQExpBufferStr(q, "a.attisinvisible,\n");
else
appendPQExpBufferStr(q, "'f' AS attisinvisible,\n");
}
Comment on lines +9353 to +9377
Copy link
Copy Markdown
Contributor

@coderabbitai coderabbitai bot Jan 22, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

⚠️ Potential issue | 🟡 Minor

Avoid false positives if the catalog column was dropped.
Same risk as above for attisinvisible; add NOT attisdropped to the probe.

🩹 Proposed fix 
-							  "AND attnum > 0 LIMIT 1",
+							  "AND attnum > 0 AND NOT attisdropped LIMIT 1",
📝 Committable suggestion

‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.

Suggested change
/*
* Check if attisinvisible column exists. This column is IvorySQL-specific
* for Oracle compatibility, and doesn't exist in native PostgreSQL.
* We need to check at runtime rather than relying on remoteVersion because
* when upgrading from PostgreSQL to IvorySQL, the source database is
* PostgreSQL which may have the same version number but lacks this column.
*/
{
PGresult *res_invisible;
bool attisinvisible_exists = false;
res_invisible = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_attribute "
"WHERE attrelid = 'pg_attribute'::regclass "
"AND attname = 'attisinvisible' "
"AND attnum > 0 LIMIT 1",
PGRES_TUPLES_OK);
attisinvisible_exists = (PQntuples(res_invisible) > 0);
PQclear(res_invisible);
if (attisinvisible_exists)
appendPQExpBufferStr(q, "a.attisinvisible,\n");
else
appendPQExpBufferStr(q, "'f' AS attisinvisible,\n");
}
/*
* Check if attisinvisible column exists. This column is IvorySQL-specific
* for Oracle compatibility, and doesn't exist in native PostgreSQL.
* We need to check at runtime rather than relying on remoteVersion because
* when upgrading from PostgreSQL to IvorySQL, the source database is
* PostgreSQL which may have the same version number but lacks this column.
*/
{
PGresult *res_invisible;
bool attisinvisible_exists = false;
res_invisible = ExecuteSqlQuery(fout,
"SELECT 1 FROM pg_catalog.pg_attribute "
"WHERE attrelid = 'pg_attribute'::regclass "
"AND attname = 'attisinvisible' "
"AND attnum > 0 AND NOT attisdropped LIMIT 1",
PGRES_TUPLES_OK);
attisinvisible_exists = (PQntuples(res_invisible) > 0);
PQclear(res_invisible);
if (attisinvisible_exists)
appendPQExpBufferStr(q, "a.attisinvisible,\n");
else
appendPQExpBufferStr(q, "'f' AS attisinvisible,\n");
}
🤖 Prompt for AI Agents 
In `@src/bin/pg_dump/pg_dump.c` around lines 9353 - 9377, The runtime probe for
attisinvisible can return a false positive if the pg_attribute row is marked
dropped; update the ExecuteSqlQuery used to detect attisinvisible
(res_invisible) to include "AND NOT attisdropped" in its WHERE clause so the
check only considers non-dropped attributes, leaving the surrounding logic that
sets attisinvisible_exists and appends to q (appendPQExpBufferStr) unchanged.


if (fout->remoteVersion >= 100000)
appendPQExpBufferStr(q,
Expand Down
6 changes: 3 additions & 3 deletions src/bin/pg_upgrade/controldata.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -624,7 +624,7 @@ get_control_data(ClusterInfo *cluster)
(!got_large_object &&
cluster->controldata.ctrl_ver >= LARGE_OBJECT_SIZE_PG_CONTROL_VER) ||
!got_date_is_int || !got_data_checksum_version ||
!got_database_mode_is_oracle ||
//!got_database_mode_is_oracle ||
(!got_default_char_signedness &&
cluster->controldata.cat_ver >= DEFAULT_CHAR_SIGNEDNESS_CAT_VER))
{
Expand Down Expand Up @@ -767,8 +767,8 @@ check_control_data(ControlData *oldctrl,
else if (oldctrl->data_checksum_version != newctrl->data_checksum_version)
pg_fatal("old and new cluster pg_controldata checksum versions do not match");

if (oldctrl->database_mode_is_oracle != newctrl->database_mode_is_oracle)
pg_fatal("old and new pg_controldata database mode do not match\n");
// if (oldctrl->database_mode_is_oracle != newctrl->database_mode_is_oracle)
// pg_fatal("old and new pg_controldata database mode do not match\n");
}


Expand Down
4 changes: 2 additions & 2 deletions src/bin/pg_upgrade/option.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -115,8 +115,8 @@ parseCommandLine(int argc, char *argv[])
if (os_user_effective_id == 0)
pg_fatal("%s: cannot be run as root", os_info.progname);

while ((option = getopt_long(argc, argv, "b:B:cd:D:j:kNo:O:p:P:q:Q:rs:U:v",
long_options, &optindex)) != -1)
while ((option = getopt_long(argc, argv, "b:B:cd:D:gj:kNo:O:p:P:q:Q:rs:U:v",
long_options, &optindex)) != -1)
{
switch (option)
{
Expand Down