Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator. None of these are
> required, but rather changes that would be nice to have:
>
> 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration. Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server. This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files. A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.
Attached is a patch that implements #1 above by preserving all
relfilenodes, with pg_dump support. It uses the same method I used for
preserving pg_type/pg_enum. I have tested this on the regression
database and it successfully preserved all relfilenodes.
This patch also removes the 'force' parameter in toast functions that
Tom added for 8.4 --- it is no longer needed.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/catalog/heap.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.364
diff -c -c -r1.364 heap.c
*** src/backend/catalog/heap.c 2 Jan 2010 16:57:36 -0000 1.364
--- src/backend/catalog/heap.c 5 Jan 2010 02:37:21 -0000
***************
*** 96,101 ****
--- 96,104 ----
char *relname);
static List *insert_ordered_unique_oid(List *list, Oid datum);
+ Oid binary_upgrade_next_heap_relfilenode = InvalidOid;
+ Oid binary_upgrade_next_toast_relfilenode = InvalidOid;
+
/* ----------------------------------------------------------------
* XXX UGLY HARD CODED BADNESS FOLLOWS XXX
***************
*** 942,956 ****
errmsg("only shared relations can be placed in pg_global tablespace")));
}
! /*
! * Allocate an OID for the relation, unless we were told what to use.
! *
! * The OID will be the relfilenode as well, so make sure it doesn't
! * collide with either pg_class OIDs or existing physical files.
! */
! if (!OidIsValid(relid))
relid = GetNewRelFileNode(reltablespace, shared_relation,
pg_class_desc);
/*
* Determine the relation's initial permissions.
--- 945,973 ----
errmsg("only shared relations can be placed in pg_global tablespace")));
}
! if ((relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE) &&
! OidIsValid(binary_upgrade_next_heap_relfilenode))
! {
! relid = binary_upgrade_next_heap_relfilenode;
! binary_upgrade_next_heap_relfilenode = InvalidOid;
! }
! else if (relkind == RELKIND_TOASTVALUE &&
! OidIsValid(binary_upgrade_next_toast_relfilenode))
! {
! relid = binary_upgrade_next_toast_relfilenode;
! binary_upgrade_next_toast_relfilenode = InvalidOid;
! }
! else if (!OidIsValid(relid))
! {
! /*
! * Allocate an OID for the relation, unless we were told what to use.
! *
! * The OID will be the relfilenode as well, so make sure it doesn't
! * collide with either pg_class OIDs or existing physical files.
! */
relid = GetNewRelFileNode(reltablespace, shared_relation,
pg_class_desc);
+ }
/*
* Determine the relation's initial permissions.
Index: src/backend/catalog/index.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.328
diff -c -c -r1.328 index.c
*** src/backend/catalog/index.c 2 Jan 2010 16:57:36 -0000 1.328
--- src/backend/catalog/index.c 5 Jan 2010 02:37:21 -0000
***************
*** 79,84 ****
--- 79,87 ----
tups_inserted;
} v_i_state;
+ /* For simple relation creation, this is the toast index relfilenode */
+ Oid binary_upgrade_next_index_relfilenode = InvalidOid;
+
/* non-export function prototypes */
static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
IndexInfo *indexInfo,
***************
*** 640,654 ****
accessMethodObjectId,
classObjectId);
! /*
! * Allocate an OID for the index, unless we were told what to use.
! *
! * The OID will be the relfilenode as well, so make sure it doesn't
! * collide with either pg_class OIDs or existing physical files.
! */
! if (!OidIsValid(indexRelationId))
indexRelationId = GetNewRelFileNode(tableSpaceId, shared_relation,
pg_class);
/*
* create the index relation's relcache entry and physical disk file. (If
--- 643,664 ----
accessMethodObjectId,
classObjectId);
! if (OidIsValid(binary_upgrade_next_index_relfilenode))
! {
! indexRelationId = binary_upgrade_next_index_relfilenode;
! binary_upgrade_next_index_relfilenode = InvalidOid;
! }
! else if (!OidIsValid(indexRelationId))
! {
! /*
! * Allocate an OID for the index, unless we were told what to use.
! *
! * The OID will be the relfilenode as well, so make sure it doesn't
! * collide with either pg_class OIDs or existing physical files.
! */
indexRelationId = GetNewRelFileNode(tableSpaceId, shared_relation,
pg_class);
+ }
/*
* create the index relation's relcache entry and physical disk file. (If
Index: src/backend/catalog/toasting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/toasting.c,v
retrieving revision 1.26
diff -c -c -r1.26 toasting.c
*** src/backend/catalog/toasting.c 2 Jan 2010 16:57:36 -0000 1.26
--- src/backend/catalog/toasting.c 5 Jan 2010 02:37:21 -0000
***************
*** 32,53 ****
#include "utils/syscache.h"
Oid binary_upgrade_next_pg_type_toast_oid = InvalidOid;
static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
! Datum reloptions, bool force);
static bool needs_toast_table(Relation rel);
/*
* AlterTableCreateToastTable
* If the table needs a toast table, and doesn't already have one,
! * then create a toast table for it. (With the force option, make
! * a toast table even if it appears unnecessary.)
! *
! * The caller can also specify the OID to be used for the toast table.
! * Usually, toastOid should be InvalidOid to allow a free OID to be assigned.
! * (This option, as well as the force option, is not used by core Postgres,
! * but is provided to support pg_migrator.)
*
* reloptions for the toast table can be passed, too. Pass (Datum) 0
* for default reloptions.
--- 32,48 ----
#include "utils/syscache.h"
Oid binary_upgrade_next_pg_type_toast_oid = InvalidOid;
+ extern Oid binary_upgrade_next_toast_relfilenode;
static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
! Datum reloptions);
static bool needs_toast_table(Relation rel);
/*
* AlterTableCreateToastTable
* If the table needs a toast table, and doesn't already have one,
! * then create a toast table for it.
*
* reloptions for the toast table can be passed, too. Pass (Datum) 0
* for default reloptions.
***************
*** 57,64 ****
* to end with CommandCounterIncrement if it makes any changes.
*/
void
! AlterTableCreateToastTable(Oid relOid, Oid toastOid,
! Datum reloptions, bool force)
{
Relation rel;
--- 52,58 ----
* to end with CommandCounterIncrement if it makes any changes.
*/
void
! AlterTableCreateToastTable(Oid relOid, Datum reloptions)
{
Relation rel;
***************
*** 70,76 ****
rel = heap_open(relOid, AccessExclusiveLock);
/* create_toast_table does all the work */
! (void) create_toast_table(rel, toastOid, InvalidOid, reloptions, force);
heap_close(rel, NoLock);
}
--- 64,70 ----
rel = heap_open(relOid, AccessExclusiveLock);
/* create_toast_table does all the work */
! (void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions);
heap_close(rel, NoLock);
}
***************
*** 96,102 ****
relName)));
/* create_toast_table does all the work */
! if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0, false))
elog(ERROR, "\"%s\" does not require a toast table",
relName);
--- 90,96 ----
relName)));
/* create_toast_table does all the work */
! if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0))
elog(ERROR, "\"%s\" does not require a toast table",
relName);
***************
*** 108,119 ****
* create_toast_table --- internal workhorse
*
* rel is already opened and exclusive-locked
! * toastOid and toastIndexOid are normally InvalidOid, but
! * either or both can be nonzero to specify caller-assigned OIDs
*/
static bool
! create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
! Datum reloptions, bool force)
{
Oid relOid = RelationGetRelid(rel);
HeapTuple reltup;
--- 102,112 ----
* create_toast_table --- internal workhorse
*
* rel is already opened and exclusive-locked
! * toastOid and toastIndexOid are normally InvalidOid, but during
! * bootstrap they can be nonzero to specify hand-assigned OIDs
*/
static bool
! create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptions)
{
Oid relOid = RelationGetRelid(rel);
HeapTuple reltup;
***************
*** 152,163 ****
/*
* Check to see whether the table actually needs a TOAST table.
! *
! * Caller can optionally override this check. (Note: at present no
! * callers in core Postgres do so, but this option is needed by
! * pg_migrator.)
*/
! if (!force && !needs_toast_table(rel))
return false;
/*
--- 145,154 ----
/*
* Check to see whether the table actually needs a TOAST table.
! * If the relfilenode is specified, force toast file creation.
*/
! if (!needs_toast_table(rel) &&
! !OidIsValid(binary_upgrade_next_toast_relfilenode))
return false;
/*
Index: src/backend/commands/cluster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/cluster.c,v
retrieving revision 1.189
diff -c -c -r1.189 cluster.c
*** src/backend/commands/cluster.c 2 Jan 2010 16:57:37 -0000 1.189
--- src/backend/commands/cluster.c 5 Jan 2010 02:37:21 -0000
***************
*** 743,749 ****
if (isNull)
reloptions = (Datum) 0;
}
! AlterTableCreateToastTable(OIDNewHeap, InvalidOid, reloptions, false);
if (OidIsValid(toastid))
ReleaseSysCache(tuple);
--- 743,749 ----
if (isNull)
reloptions = (Datum) 0;
}
! AlterTableCreateToastTable(OIDNewHeap, reloptions);
if (OidIsValid(toastid))
ReleaseSysCache(tuple);
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.313
diff -c -c -r1.313 tablecmds.c
*** src/backend/commands/tablecmds.c 2 Jan 2010 16:57:37 -0000 1.313
--- src/backend/commands/tablecmds.c 5 Jan 2010 02:37:21 -0000
***************
*** 2614,2621 ****
(tab->subcmds[AT_PASS_ADD_COL] ||
tab->subcmds[AT_PASS_ALTER_TYPE] ||
tab->subcmds[AT_PASS_COL_ATTRS]))
! AlterTableCreateToastTable(tab->relid, InvalidOid,
! (Datum) 0, false);
}
}
--- 2614,2620 ----
(tab->subcmds[AT_PASS_ADD_COL] ||
tab->subcmds[AT_PASS_ALTER_TYPE] ||
tab->subcmds[AT_PASS_COL_ATTRS]))
! AlterTableCreateToastTable(tab->relid, (Datum) 0);
}
}
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.339
diff -c -c -r1.339 execMain.c
*** src/backend/executor/execMain.c 2 Jan 2010 16:57:40 -0000 1.339
--- src/backend/executor/execMain.c 5 Jan 2010 02:37:22 -0000
***************
*** 2194,2200 ****
(void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);
! AlterTableCreateToastTable(intoRelationId, InvalidOid, reloptions, false);
/*
* And open the constructed table for writing.
--- 2194,2200 ----
(void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);
! AlterTableCreateToastTable(intoRelationId, reloptions);
/*
* And open the constructed table for writing.
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.326
diff -c -c -r1.326 utility.c
*** src/backend/tcop/utility.c 2 Jan 2010 16:57:53 -0000 1.326
--- src/backend/tcop/utility.c 5 Jan 2010 02:37:22 -0000
***************
*** 491,504 ****
"toast",
validnsps,
true, false);
! (void) heap_reloptions(RELKIND_TOASTVALUE,
! toast_options,
true);
! AlterTableCreateToastTable(relOid,
! InvalidOid,
! toast_options,
! false);
}
else
{
--- 491,500 ----
"toast",
validnsps,
true, false);
! (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options,
true);
! AlterTableCreateToastTable(relOid, toast_options);
}
else
{
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.564
diff -c -c -r1.564 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 2 Jan 2010 16:57:59 -0000 1.564
--- src/bin/pg_dump/pg_dump.c 5 Jan 2010 02:37:22 -0000
***************
*** 200,206 ****
PQExpBuffer upgrade_buffer, Oid pg_type_oid);
static bool binary_upgrade_set_type_oids_by_rel_oid(
PQExpBuffer upgrade_buffer, Oid pg_rel_oid);
! static void binary_upgrade_clear_pg_type_toast_oid(PQExpBuffer upgrade_buffer);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
static const char *fmtCopyColumnList(const TableInfo *ti);
static void do_sql_command(PGconn *conn, const char *query);
--- 200,207 ----
PQExpBuffer upgrade_buffer, Oid pg_type_oid);
static bool binary_upgrade_set_type_oids_by_rel_oid(
PQExpBuffer upgrade_buffer, Oid pg_rel_oid);
! static void binary_upgrade_set_relfilenodes(PQExpBuffer upgrade_buffer,
! Oid pg_class_oid, bool is_index);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
static const char *fmtCopyColumnList(const TableInfo *ti);
static void do_sql_command(PGconn *conn, const char *query);
***************
*** 2289,2309 ****
}
static void
! binary_upgrade_clear_pg_type_toast_oid(PQExpBuffer upgrade_buffer)
{
! /*
! * One complexity is that while the heap might now have a TOAST table,
! * the TOAST table might have been created long after creation when
! * the table was loaded with wide data. For that reason, we clear
! * binary_upgrade_set_next_pg_type_toast_oid so it is not reused
! * by a later table. Logically any later creation that needs a TOAST
! * table should have its own TOAST pg_type oid, but we are cautious.
! */
! appendPQExpBuffer(upgrade_buffer,
! "\n-- For binary upgrade, clear toast oid because it might not have been needed\n");
appendPQExpBuffer(upgrade_buffer,
! "SELECT binary_upgrade.set_next_pg_type_oid('%u'::pg_catalog.oid);\n\n",
! InvalidOid);
}
/*
--- 2290,2367 ----
}
static void
! binary_upgrade_set_relfilenodes(PQExpBuffer upgrade_buffer, Oid pg_class_oid,
! bool is_index)
{
! PQExpBuffer upgrade_query = createPQExpBuffer();
! int ntups;
! PGresult *upgrade_res;
! Oid pg_class_relfilenode;
! Oid pg_class_reltoastrelid;
! Oid pg_class_reltoastidxid;
!
! appendPQExpBuffer(upgrade_query,
! "SELECT c.relfilenode, c.reltoastrelid, t.reltoastidxid "
! "FROM pg_catalog.pg_class c LEFT JOIN "
! "pg_catalog.pg_class t ON (c.reltoastrelid = t.oid) "
! "WHERE c.oid = '%u'::pg_catalog.oid;",
! pg_class_oid);
!
! upgrade_res = PQexec(g_conn, upgrade_query->data);
! check_sql_result(upgrade_res, g_conn, upgrade_query->data, PGRES_TUPLES_OK);
!
! /* Expecting a single result only */
! ntups = PQntuples(upgrade_res);
! if (ntups != 1)
! {
! write_msg(NULL, ngettext("query returned %d row instead of one: %s\n",
! "query returned %d rows instead of one: %s\n",
! ntups),
! ntups, upgrade_query->data);
! exit_nicely();
! }
!
! pg_class_relfilenode = atooid(PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "relfilenode")));
! pg_class_reltoastrelid = atooid(PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "reltoastrelid")));
! pg_class_reltoastidxid = atooid(PQgetvalue(upgrade_res, 0, PQfnumber(upgrade_res, "reltoastidxid")));
!
appendPQExpBuffer(upgrade_buffer,
! "\n-- For binary upgrade, must preserve relfilenodes\n");
!
! if (!is_index)
! appendPQExpBuffer(upgrade_buffer,
! "SELECT binary_upgrade.set_next_heap_relfilenode('%u'::pg_catalog.oid);\n",
! pg_class_relfilenode);
! else
! appendPQExpBuffer(upgrade_buffer,
! "SELECT binary_upgrade.set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
! pg_class_relfilenode);
!
! if (OidIsValid(pg_class_reltoastrelid))
! {
! /*
! * One complexity is that the table definition might not require
! * the creation of a TOAST table, and the TOAST table might have
! * been created long after table creation, when the table was
! * loaded with wide data. By setting the TOAST relfilenode we
! * force creation of the TOAST heap and TOAST index by the
! * backend so we can cleanly migrate the files during binary
! * migration.
! */
!
! appendPQExpBuffer(upgrade_buffer,
! "SELECT binary_upgrade.set_next_toast_relfilenode('%u'::pg_catalog.oid);\n",
! pg_class_reltoastrelid);
!
! /* every toast table has an index */
! appendPQExpBuffer(upgrade_buffer,
! "SELECT binary_upgrade.set_next_index_relfilenode('%u'::pg_catalog.oid);\n",
! pg_class_reltoastidxid);
! }
! appendPQExpBuffer(upgrade_buffer, "\n");
!
! PQclear(upgrade_res);
! destroyPQExpBuffer(upgrade_query);
}
/*
***************
*** 10480,10485 ****
--- 10538,10546 ----
appendPQExpBuffer(delq, "%s;\n",
fmtId(tbinfo->dobj.name));
+ if (binary_upgrade)
+ binary_upgrade_set_relfilenodes(q, tbinfo->dobj.catId.oid, false);
+
appendPQExpBuffer(q, "CREATE TABLE %s (",
fmtId(tbinfo->dobj.name));
actual_atts = 0;
***************
*** 10781,10789 ****
}
}
- if (binary_upgrade && toast_set)
- binary_upgrade_clear_pg_type_toast_oid(q);
-
ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
tbinfo->dobj.name,
tbinfo->dobj.namespace->dobj.name,
--- 10842,10847 ----
***************
*** 10926,10931 ****
--- 10984,10992 ----
*/
if (indxinfo->indexconstraint == 0)
{
+ if (binary_upgrade)
+ binary_upgrade_set_relfilenodes(q, indxinfo->dobj.catId.oid, true);
+
/* Plain secondary index */
appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
***************
*** 11006,11011 ****
--- 11067,11075 ----
exit_nicely();
}
+ if (binary_upgrade && !coninfo->condef)
+ binary_upgrade_set_relfilenodes(q, indxinfo->dobj.catId.oid, true);
+
appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
fmtId(tbinfo->dobj.name));
appendPQExpBuffer(q, " ADD CONSTRAINT %s ",
***************
*** 11416,11422 ****
--- 11480,11489 ----
resetPQExpBuffer(query);
if (binary_upgrade)
+ {
+ binary_upgrade_set_relfilenodes(query, tbinfo->dobj.catId.oid, false);
binary_upgrade_set_type_oids_by_rel_oid(query, tbinfo->dobj.catId.oid);
+ }
appendPQExpBuffer(query,
"CREATE SEQUENCE %s\n",
Index: src/include/catalog/toasting.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/toasting.h,v
retrieving revision 1.11
diff -c -c -r1.11 toasting.h
*** src/include/catalog/toasting.h 2 Jan 2010 16:58:02 -0000 1.11
--- src/include/catalog/toasting.h 5 Jan 2010 02:37:22 -0000
***************
*** 17,24 ****
/*
* toasting.c prototypes
*/
! extern void AlterTableCreateToastTable(Oid relOid, Oid toastOid,
! Datum reloptions, bool force);
extern void BootstrapToastTable(char *relName,
Oid toastOid, Oid toastIndexOid);
--- 17,23 ----
/*
* toasting.c prototypes
*/
! extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions);
extern void BootstrapToastTable(char *relName,
Oid toastOid, Oid toastIndexOid);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
