From 429251a8d0db5cef2a21226a969ea6cc482b811d Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 25 Apr 2023 16:12:23 +0900 Subject: [PATCH v29 3/3] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED Simplifies ALTER TABLE SET LOGGED/UNLOGGED invocation by allowing users to specify relations based on tablespace or owner. --- doc/src/sgml/ref/alter_table.sgml | 15 +++ src/backend/commands/tablecmds.c | 140 +++++++++++++++++++++++ src/backend/parser/gram.y | 42 +++++++ src/backend/tcop/utility.c | 11 ++ 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 +++++++ src/tools/pgindent/typedefs.list | 1 + 9 files changed, 338 insertions(+) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index d4d93eeb7c..7ee09ca9cf 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 ] name SET SCHEMA new_schema ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ] +ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] + SET { LOGGED | UNLOGGED } [ NOWAIT ] ALTER TABLE [ IF EXISTS ] name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } ALTER TABLE [ IF EXISTS ] name @@ -769,6 +771,19 @@ WITH ( MODULUS numeric_literal, REM (for identity or serial columns). However, it is also possible to change the persistence of such sequences separately. + + All tables in the current database in a tablespace can be changed by + using the ALL IN TABLESPACE form, which will first + lock all tables to be changed and then change each one. This form also + supports + OWNED BY, which will only change tables owned by the + specified roles. If the NOWAIT option is specified, + then the command will fail if it is unable to immediately acquire all of + the locks required. The information_schema relations + are not considered part of the system catalogs and will be changed. See + also + CREATE TABLESPACE. + diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3192a97b0e..24c6dd2aeb 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -14966,6 +14966,146 @@ AlterTableMoveAll(AlterTableMoveAllStmt *stmt) return new_tablespaceoid; } +/* + * Alter Table ALL ... SET LOGGED/UNLOGGED + * + * Allows a user to modify the persistence of all objects in a specific + * tablespace in the current database. Objects can be filtered by owner, + * enabling users to update the persistence of only their objects. The primary + * permission handling is managed by the lower-level change persistence + * function. + * + * All objects to be modified are locked first. If NOWAIT is specified and the + * lock can't be acquired, an ERROR is thrown. + */ +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 (!object_ownercheck(RelationRelationId, 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 b3bdf947b6..5b2ae92608 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2110,6 +2110,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/tcop/utility.c b/src/backend/tcop/utility.c index e3ccf6c7f7..fcf550c839 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: @@ -1771,6 +1772,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 */ @@ -2699,6 +2706,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/commands/tablecmds.h b/src/include/commands/tablecmds.h index 16b6126669..28ef0dc8c0 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 2565348303..373c8c5650 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2681,6 +2681,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 9aabb85349..35b150b297 100644 --- a/src/test/regress/expected/tablespace.out +++ b/src/test/regress/expected/tablespace.out @@ -964,5 +964,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 d274d9615e..eb8e247a1d 100644 --- a/src/test/regress/sql/tablespace.sql +++ b/src/test/regress/sql/tablespace.sql @@ -429,5 +429,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; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index f72c5c8a9e..8a6c101bdf 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -97,6 +97,7 @@ AlterTSConfigurationStmt AlterTSDictionaryStmt AlterTableCmd AlterTableMoveAllStmt +AlterTableSetLoggedAllStmt AlterTableSpaceOptionsStmt AlterTableStmt AlterTableType -- 2.25.1