public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v24 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED 2+ messages / 2 participants [nested] [flat]
* [PATCH v24 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED @ 2022-07-19 04:32 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Kyotaro Horiguchi @ 2022-07-19 04:32 UTC (permalink / raw) To ease invoking ALTER TABLE SET LOGGED/UNLOGGED, this command changes relation persistence of all tables in the specified tablespace. --- doc/src/sgml/ref/alter_table.sgml | 15 +++ src/backend/catalog/storage.c | 4 +- src/backend/commands/tablecmds.c | 140 +++++++++++++++++++++++ src/backend/parser/gram.y | 42 +++++++ src/backend/storage/buffer/bufmgr.c | 12 +- src/backend/storage/file/reinit.c | 4 +- src/backend/tcop/utility.c | 11 ++ src/include/catalog/storage_xlog.h | 2 +- src/include/commands/tablecmds.h | 2 + src/include/nodes/parsenodes.h | 10 ++ src/test/regress/expected/tablespace.out | 76 ++++++++++++ src/test/regress/sql/tablespace.sql | 41 +++++++ 12 files changed, 349 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index f0f912a56c..fd3d8290b8 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -33,6 +33,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable> ALTER TABLE ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ] SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ] +ALTER TABLE ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ] + SET { LOGGED | UNLOGGED } [ NOWAIT ] ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> @@ -759,6 +761,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM (for identity or serial columns). However, it is also possible to change the persistence of such sequences separately. </para> + <para> + All tables in the current database in a tablespace can be changed by + using the <literal>ALL IN TABLESPACE</literal> form, which will first + lock all tables to be changed and then change each one. This form also + supports + <literal>OWNED BY</literal>, which will only change tables owned by the + specified roles. If the <literal>NOWAIT</literal> option is specified, + then the command will fail if it is unable to immediately acquire all of + the locks required. The <literal>information_schema</literal> relations + are not considered part of the system catalogs and will be changed. See + also + <link linkend="sql-createtablespace"><command>CREATE TABLESPACE</command></link>. + </para> </listitem> </varlistentry> diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 2c6472cfd5..b913f7d993 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -509,14 +509,14 @@ log_smgrunlinkmark(const RelFileLocator *rlocator, ForkNumber forkNum, * Perform XLogInsert of an XLOG_SMGR_BUFPERSISTENCE record to WAL. */ void -log_smgrbufpersistence(const RelFileLocator *rlocator, bool persistence) +log_smgrbufpersistence(const RelFileLocator rlocator, bool persistence) { xl_smgr_bufpersistence xlrec; /* * Make an XLOG entry reporting the change of buffer persistence. */ - xlrec.rlocator = *rlocator; + xlrec.rlocator = rlocator; xlrec.persistence = persistence; XLogBeginInsert(); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1779959410..cbf88f2878 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -14827,6 +14827,146 @@ AlterTableMoveAll(AlterTableMoveAllStmt *stmt) return new_tablespaceoid; } +/* + * Alter Table ALL ... SET LOGGED/UNLOGGED + * + * Allows a user to change persistence of all objects in a given tablespace in + * the current database. Objects can be chosen based on the owner of the + * object also, to allow users to change persistence only their objects. The + * main permissions handling is done by the lower-level change persistence + * function. + * + * All to-be-modified objects are locked first. If NOWAIT is specified and the + * lock can't be acquired then we ereport(ERROR). + */ +void +AlterTableSetLoggedAll(AlterTableSetLoggedAllStmt * stmt) +{ + List *relations = NIL; + ListCell *l; + ScanKeyData key[1]; + Relation rel; + TableScanDesc scan; + HeapTuple tuple; + Oid tablespaceoid; + List *role_oids = roleSpecsToIds(stmt->roles); + + /* Ensure we were not asked to change something we can't */ + if (stmt->objtype != OBJECT_TABLE) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("only tables can be specified")); + + /* Get the tablespace OID */ + tablespaceoid = get_tablespace_oid(stmt->tablespacename, false); + + /* + * Now that the checks are done, check if we should set either to + * InvalidOid because it is our database's default tablespace. + */ + if (tablespaceoid == MyDatabaseTableSpace) + tablespaceoid = InvalidOid; + + /* + * Walk the list of objects in the tablespace to pick up them. This will + * only find objects in our database, of course. + */ + ScanKeyInit(&key[0], + Anum_pg_class_reltablespace, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(tablespaceoid)); + + rel = table_open(RelationRelationId, AccessShareLock); + scan = table_beginscan_catalog(rel, 1, key); + while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + Form_pg_class relForm = (Form_pg_class) GETSTRUCT(tuple); + Oid relOid = relForm->oid; + + /* + * Do not pick-up objects in pg_catalog as part of this, if an admin + * really wishes to do so, they can issue the individual ALTER + * commands directly. + * + * Also, explicitly avoid any shared tables, temp tables, or TOAST + * (TOAST will be changed with the main table). + */ + if (IsCatalogNamespace(relForm->relnamespace) || + relForm->relisshared || + isAnyTempNamespace(relForm->relnamespace) || + IsToastNamespace(relForm->relnamespace)) + continue; + + /* Only pick up the object type requested */ + if (relForm->relkind != RELKIND_RELATION) + continue; + + /* Check if we are only picking-up objects owned by certain roles */ + if (role_oids != NIL && !list_member_oid(role_oids, relForm->relowner)) + continue; + + /* + * Handle permissions-checking here since we are locking the tables + * and also to avoid doing a bunch of work only to fail part-way. Note + * that permissions will also be checked by AlterTableInternal(). + * + * Caller must be considered an owner on the table of which we're + * going to change persistence. + */ + if (!pg_class_ownercheck(relOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(get_rel_relkind(relOid)), + NameStr(relForm->relname)); + + if (stmt->nowait && + !ConditionalLockRelationOid(relOid, AccessExclusiveLock)) + ereport(ERROR, + errcode(ERRCODE_OBJECT_IN_USE), + errmsg("aborting because lock on relation \"%s.%s\" is not available", + get_namespace_name(relForm->relnamespace), + NameStr(relForm->relname))); + else + LockRelationOid(relOid, AccessExclusiveLock); + + /* + * Add to our list of objects of which we're going to change + * persistence. + */ + relations = lappend_oid(relations, relOid); + } + + table_endscan(scan); + table_close(rel, AccessShareLock); + + if (relations == NIL) + ereport(NOTICE, + errcode(ERRCODE_NO_DATA_FOUND), + errmsg("no matching relations in tablespace \"%s\" found", + tablespaceoid == InvalidOid ? "(database default)" : + get_tablespace_name(tablespaceoid))); + + /* + * Everything is locked, loop through and change persistence of all of the + * relations. + */ + foreach(l, relations) + { + List *cmds = NIL; + AlterTableCmd *cmd = makeNode(AlterTableCmd); + + if (stmt->logged) + cmd->subtype = AT_SetLogged; + else + cmd->subtype = AT_SetUnLogged; + + cmds = lappend(cmds, cmd); + + EventTriggerAlterTableStart((Node *) stmt); + /* OID is set by AlterTableInternal */ + AlterTableInternal(lfirst_oid(l), cmds, false); + EventTriggerAlterTableEnd(); + } +} + static void index_copy_data(Relation rel, RelFileLocator newrlocator) { diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0d8d292850..b56fbbd4b2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2086,6 +2086,48 @@ AlterTableStmt: n->nowait = $13; $$ = (Node *) n; } + | ALTER TABLE ALL IN_P TABLESPACE name SET LOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->logged = true; + n->nowait = $9; + $$ = (Node *)n; + } + | ALTER TABLE ALL IN_P TABLESPACE name OWNED BY role_list SET LOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->roles = $9; + n->logged = true; + n->nowait = $12; + $$ = (Node *)n; + } + | ALTER TABLE ALL IN_P TABLESPACE name SET UNLOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->logged = false; + n->nowait = $9; + $$ = (Node *)n; + } + | ALTER TABLE ALL IN_P TABLESPACE name OWNED BY role_list SET UNLOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->roles = $9; + n->logged = false; + n->nowait = $12; + $$ = (Node *)n; + } | ALTER INDEX qualified_name alter_table_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 796cb139c3..454f3bcf0a 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -3201,7 +3201,7 @@ SetRelationBuffersPersistence(SMgrRelation srel, bool permanent, bool isRedo) Assert(!RelFileLocatorBackendIsTemp(rlocator)); if (!isRedo) - log_smgrbufpersistence(&srel->smgr_rlocator.locator, permanent); + log_smgrbufpersistence(srel->smgr_rlocator.locator, permanent); ResourceOwnerEnlargeBuffers(CurrentResourceOwner); @@ -3210,14 +3210,16 @@ SetRelationBuffersPersistence(SMgrRelation srel, bool permanent, bool isRedo) BufferDesc *bufHdr = GetBufferDescriptor(i); uint32 buf_state; - if (!RelFileLocatorEquals(bufHdr->tag.rlocator, rlocator.locator)) + if (!RelFileLocatorEquals(BufTagGetRelFileLocator(&bufHdr->tag), + rlocator.locator)) continue; ReservePrivateRefCountEntry(); buf_state = LockBufHdr(bufHdr); - if (!RelFileLocatorEquals(bufHdr->tag.rlocator, rlocator.locator)) + if (!RelFileLocatorEquals(BufTagGetRelFileLocator(&bufHdr->tag), + rlocator.locator)) { UnlockBufHdr(bufHdr, buf_state); continue; @@ -3226,7 +3228,7 @@ SetRelationBuffersPersistence(SMgrRelation srel, bool permanent, bool isRedo) if (permanent) { /* Init fork is being dropped, drop buffers for it. */ - if (bufHdr->tag.forkNum == INIT_FORKNUM) + if (BufTagGetForkNum(&bufHdr->tag) == INIT_FORKNUM) { InvalidateBuffer(bufHdr); continue; @@ -3251,7 +3253,7 @@ SetRelationBuffersPersistence(SMgrRelation srel, bool permanent, bool isRedo) else { /* There shouldn't be an init fork */ - Assert(bufHdr->tag.forkNum != INIT_FORKNUM); + Assert(BufTagGetForkNum(&bufHdr->tag) != INIT_FORKNUM); UnlockBufHdr(bufHdr, buf_state); } } diff --git a/src/backend/storage/file/reinit.c b/src/backend/storage/file/reinit.c index ea5d6bbba1..aae8561f9d 100644 --- a/src/backend/storage/file/reinit.c +++ b/src/backend/storage/file/reinit.c @@ -237,7 +237,7 @@ ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, if (forkNum == INIT_FORKNUM || mark == SMGR_MARK_UNCOMMITTED) { - RelFileNumbers key; + RelFileNumber key; relfile_entry *ent; bool found; @@ -318,7 +318,7 @@ ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, rel.backend = InvalidBackendId; rel.locator.spcOid = tspid; rel.locator.dbOid = dbid; - rel.locator.relNumber = ent->reloid; + rel.locator.relNumber = ent->relnumber; srels[nrels++] = smgropen(rel.locator, InvalidBackendId); } diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index aa00815787..3307937276 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -164,6 +164,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) case T_AlterTSConfigurationStmt: case T_AlterTSDictionaryStmt: case T_AlterTableMoveAllStmt: + case T_AlterTableSetLoggedAllStmt: case T_AlterTableSpaceOptionsStmt: case T_AlterTableStmt: case T_AlterTypeStmt: @@ -1760,6 +1761,12 @@ ProcessUtilitySlow(ParseState *pstate, commandCollected = true; break; + case T_AlterTableSetLoggedAllStmt: + AlterTableSetLoggedAll((AlterTableSetLoggedAllStmt *) parsetree); + /* commands are stashed in AlterTableSetLoggedAll */ + commandCollected = true; + break; + case T_DropStmt: ExecDropStmt((DropStmt *) parsetree, isTopLevel); /* no commands stashed for DROP */ @@ -2688,6 +2695,10 @@ CreateCommandTag(Node *parsetree) tag = AlterObjectTypeCommandTag(((AlterTableMoveAllStmt *) parsetree)->objtype); break; + case T_AlterTableSetLoggedAllStmt: + tag = AlterObjectTypeCommandTag(((AlterTableSetLoggedAllStmt *) parsetree)->objtype); + break; + case T_AlterTableStmt: tag = AlterObjectTypeCommandTag(((AlterTableStmt *) parsetree)->objtype); break; diff --git a/src/include/catalog/storage_xlog.h b/src/include/catalog/storage_xlog.h index 9f48fb5e6f..0a3726f3db 100644 --- a/src/include/catalog/storage_xlog.h +++ b/src/include/catalog/storage_xlog.h @@ -88,7 +88,7 @@ extern void log_smgrcreatemark(const RelFileLocator *rlocator, ForkNumber forkNum, StorageMarks mark); extern void log_smgrunlinkmark(const RelFileLocator *rlocator, ForkNumber forkNum, StorageMarks mark); -extern void log_smgrbufpersistence(const RelFileLocator *rlocator, +extern void log_smgrbufpersistence(const RelFileLocator rlocator, bool persistence); extern void smgr_redo(XLogReaderState *record); diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 03f14d6be1..27286fff33 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -42,6 +42,8 @@ extern void AlterTableInternal(Oid relid, List *cmds, bool recurse); extern Oid AlterTableMoveAll(AlterTableMoveAllStmt *stmt); +extern void AlterTableSetLoggedAll(AlterTableSetLoggedAllStmt * stmt); + extern ObjectAddress AlterTableNamespace(AlterObjectSchemaStmt *stmt, Oid *oldschema); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 633e7671b3..789e9438d1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2426,6 +2426,16 @@ typedef struct AlterTableMoveAllStmt bool nowait; } AlterTableMoveAllStmt; +typedef struct AlterTableSetLoggedAllStmt +{ + NodeTag type; + char *tablespacename; + ObjectType objtype; /* Object type to move */ + List *roles; /* List of roles to change objects of */ + bool logged; + bool nowait; +} AlterTableSetLoggedAllStmt; + /* ---------------------- * Create/Alter Extension Statements * ---------------------- diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out index c52cf1cfcf..a679d58553 100644 --- a/src/test/regress/expected/tablespace.out +++ b/src/test/regress/expected/tablespace.out @@ -966,5 +966,81 @@ drop cascades to table testschema.part drop cascades to table testschema.atable drop cascades to materialized view testschema.amv drop cascades to table testschema.tablespace_acl +-- +-- Check persistence change in a tablespace +CREATE SCHEMA testschema; +GRANT CREATE ON SCHEMA testschema TO regress_tablespace_user1; +CREATE TABLESPACE regress_tablespace LOCATION ''; +GRANT CREATE ON TABLESPACE regress_tablespace TO regress_tablespace_user1; +CREATE TABLE testschema.lsu(a int) TABLESPACE regress_tablespace; +CREATE UNLOGGED TABLE testschema.usu(a int) TABLESPACE regress_tablespace; +CREATE TABLE testschema._lsu(a int) TABLESPACE pg_default; +CREATE UNLOGGED TABLE testschema._usu(a int) TABLESPACE pg_default; +SET ROLE regress_tablespace_user1; +CREATE TABLE testschema.lu1(a int) TABLESPACE regress_tablespace; +CREATE UNLOGGED TABLE testschema.uu1(a int) TABLESPACE regress_tablespace; +CREATE TABLE testschema._lu1(a int) TABLESPACE pg_default; +CREATE UNLOGGED TABLE testschema._uu1(a int) TABLESPACE pg_default; +SELECT relname, t.spcname, relpersistence + FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) + WHERE relnamespace = 'testschema'::regnamespace ORDER BY spcname, c.oid; + relname | spcname | relpersistence +---------+--------------------+---------------- + lsu | regress_tablespace | p + usu | regress_tablespace | u + lu1 | regress_tablespace | p + uu1 | regress_tablespace | u + _lsu | | p + _usu | | u + _lu1 | | p + _uu1 | | u +(8 rows) + +ALTER TABLE ALL IN TABLESPACE regress_tablespace + OWNED BY regress_tablespace_user1 SET LOGGED; +SELECT relname, t.spcname, relpersistence + FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) + WHERE relnamespace = 'testschema'::regnamespace ORDER BY spcname, c.oid; + relname | spcname | relpersistence +---------+--------------------+---------------- + lsu | regress_tablespace | p + usu | regress_tablespace | u + lu1 | regress_tablespace | p + uu1 | regress_tablespace | p + _lsu | | p + _usu | | u + _lu1 | | p + _uu1 | | u +(8 rows) + +RESET ROLE; +ALTER TABLE ALL IN TABLESPACE regress_tablespace SET UNLOGGED; +SELECT relname, t.spcname, relpersistence + FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) + WHERE relnamespace = 'testschema'::regnamespace ORDER BY spcname, c.oid; + relname | spcname | relpersistence +---------+--------------------+---------------- + lsu | regress_tablespace | u + usu | regress_tablespace | u + lu1 | regress_tablespace | u + uu1 | regress_tablespace | u + _lsu | | p + _usu | | u + _lu1 | | p + _uu1 | | u +(8 rows) + +-- Should succeed +DROP SCHEMA testschema CASCADE; +NOTICE: drop cascades to 8 other objects +DETAIL: drop cascades to table testschema.lsu +drop cascades to table testschema.usu +drop cascades to table testschema._lsu +drop cascades to table testschema._usu +drop cascades to table testschema.lu1 +drop cascades to table testschema.uu1 +drop cascades to table testschema._lu1 +drop cascades to table testschema._uu1 +DROP TABLESPACE regress_tablespace; DROP ROLE regress_tablespace_user1; DROP ROLE regress_tablespace_user2; diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql index 21db433f2a..a4b664f4e0 100644 --- a/src/test/regress/sql/tablespace.sql +++ b/src/test/regress/sql/tablespace.sql @@ -431,5 +431,46 @@ DROP TABLESPACE regress_tblspace_renamed; DROP SCHEMA testschema CASCADE; + +-- +-- Check persistence change in a tablespace +CREATE SCHEMA testschema; +GRANT CREATE ON SCHEMA testschema TO regress_tablespace_user1; +CREATE TABLESPACE regress_tablespace LOCATION ''; +GRANT CREATE ON TABLESPACE regress_tablespace TO regress_tablespace_user1; + +CREATE TABLE testschema.lsu(a int) TABLESPACE regress_tablespace; +CREATE UNLOGGED TABLE testschema.usu(a int) TABLESPACE regress_tablespace; +CREATE TABLE testschema._lsu(a int) TABLESPACE pg_default; +CREATE UNLOGGED TABLE testschema._usu(a int) TABLESPACE pg_default; +SET ROLE regress_tablespace_user1; +CREATE TABLE testschema.lu1(a int) TABLESPACE regress_tablespace; +CREATE UNLOGGED TABLE testschema.uu1(a int) TABLESPACE regress_tablespace; +CREATE TABLE testschema._lu1(a int) TABLESPACE pg_default; +CREATE UNLOGGED TABLE testschema._uu1(a int) TABLESPACE pg_default; + +SELECT relname, t.spcname, relpersistence + FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) + WHERE relnamespace = 'testschema'::regnamespace ORDER BY spcname, c.oid; + +ALTER TABLE ALL IN TABLESPACE regress_tablespace + OWNED BY regress_tablespace_user1 SET LOGGED; + +SELECT relname, t.spcname, relpersistence + FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) + WHERE relnamespace = 'testschema'::regnamespace ORDER BY spcname, c.oid; + +RESET ROLE; + +ALTER TABLE ALL IN TABLESPACE regress_tablespace SET UNLOGGED; + +SELECT relname, t.spcname, relpersistence + FROM pg_class c LEFT JOIN pg_tablespace t ON (c.reltablespace = t.oid) + WHERE relnamespace = 'testschema'::regnamespace ORDER BY spcname, c.oid; + +-- Should succeed +DROP SCHEMA testschema CASCADE; +DROP TABLESPACE regress_tablespace; + DROP ROLE regress_tablespace_user1; DROP ROLE regress_tablespace_user2; -- 2.31.1 ----Next_Part(Wed_Sep_28_17_21_19_2022_527)---- ^ permalink raw reply [nested|flat] 2+ messages in thread
* Request for Insights on ID Column Migration Approach @ 2024-09-30 17:05 Aditya Singh <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Aditya Singh @ 2024-09-30 17:05 UTC (permalink / raw) To: [email protected] I am just contacting you to talk about a current issue with our database. We have run out of a positive sequence in one of our tables and are now operating with negative sequences. To address this, we plan to migrate from the int4 ID column to an int8 ID column. The plan involves renaming the int8 column to the id column and setting it as the primary key. However, this process will require downtime, which may be substantial in a production environment. Fortunately, we have noted that other tables do not use the id column as a foreign key, which may help mitigate some concerns. Our Approach: 1. *Copy Data: *Copy all the data to the new *id_copy* column. 2. *C**reate a Unique Index*: We will then create a unique index on the new ID column before renaming it ,and alter it to non-nullable. This step will necessitate scanning the entire table to verify uniqueness and non-nullability. 3. *A**dd Primary Key*: After ensuring the uniqueness, we will add the ID column as the primary key. By doing this, we hope to bypass the additional scanning for uniqueness and nullability, as the column will already be set as not nullable and will have the uniqueness constraint from the unique index. We want to confirm if this approach will work as expected. If we should be aware of any potential pitfalls or considerations, could you please provide insights or point us toward relevant documentation? Thank you so much for your help, and I look forward to your guidance. Best regards, Aditya Narayan Singh Loyalty Juggernaut Inc. -- *Confidentiality Warning:* This message and any attachments are intended only for the use of the intended recipient(s), are confidential, and may be privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or other use of this message and any attachments is strictly prohibited. If received in error, please notify the sender immediately and permanently delete it. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-30 17:05 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-07-19 04:32 [PATCH v24 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED Kyotaro Horiguchi <[email protected]> 2024-09-30 17:05 Request for Insights on ID Column Migration Approach Aditya Singh <[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