public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v5 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED 3+ messages / 3 participants [nested] [flat]
* [PATCH v5 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED @ 2020-11-11 14:21 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Kyotaro Horiguchi @ 2020-11-11 14:21 UTC (permalink / raw) To ease invoking ALTER TABLE SET LOGGED/UNLOGGED, this command changes relation persistence of all tables in the specified tablespace. --- src/backend/commands/tablecmds.c | 140 +++++++++++++++++++++++++++++++ src/backend/nodes/copyfuncs.c | 16 ++++ src/backend/nodes/equalfuncs.c | 15 ++++ src/backend/parser/gram.y | 20 +++++ src/backend/tcop/utility.c | 11 +++ src/include/commands/tablecmds.h | 2 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 9 ++ 8 files changed, 214 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 37a15d31ee..2f65abb19b 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -13696,6 +13696,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 persistene 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(NIL); + + /* 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, RelFileNode newrnode) { diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index ba3ccc712c..127da5151d 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -4138,6 +4138,19 @@ _copyAlterTableMoveAllStmt(const AlterTableMoveAllStmt *from) return newnode; } +static AlterTableSetLoggedAllStmt * +_copyAlterTableSetLoggedAllStmt(const AlterTableSetLoggedAllStmt *from) +{ + AlterTableSetLoggedAllStmt *newnode = makeNode(AlterTableSetLoggedAllStmt); + + COPY_STRING_FIELD(tablespacename); + COPY_SCALAR_FIELD(objtype); + COPY_SCALAR_FIELD(logged); + COPY_SCALAR_FIELD(nowait); + + return newnode; +} + static CreateExtensionStmt * _copyCreateExtensionStmt(const CreateExtensionStmt *from) { @@ -5441,6 +5454,9 @@ copyObjectImpl(const void *from) case T_AlterTableMoveAllStmt: retval = _copyAlterTableMoveAllStmt(from); break; + case T_AlterTableSetLoggedAllStmt: + retval = _copyAlterTableSetLoggedAllStmt(from); + break; case T_CreateExtensionStmt: retval = _copyCreateExtensionStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index a2ef853dc2..4f13a1762b 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1872,6 +1872,18 @@ _equalAlterTableMoveAllStmt(const AlterTableMoveAllStmt *a, return true; } +static bool +_equalAlterTableSetLoggedAllStmt(const AlterTableSetLoggedAllStmt *a, + const AlterTableSetLoggedAllStmt *b) +{ + COMPARE_STRING_FIELD(tablespacename); + COMPARE_SCALAR_FIELD(objtype); + COMPARE_SCALAR_FIELD(logged); + COMPARE_SCALAR_FIELD(nowait); + + return true; +} + static bool _equalCreateExtensionStmt(const CreateExtensionStmt *a, const CreateExtensionStmt *b) { @@ -3494,6 +3506,9 @@ equal(const void *a, const void *b) case T_AlterTableMoveAllStmt: retval = _equalAlterTableMoveAllStmt(a, b); break; + case T_AlterTableSetLoggedAllStmt: + retval = _equalAlterTableSetLoggedAllStmt(a, b); + break; case T_CreateExtensionStmt: retval = _equalCreateExtensionStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 31c95443a5..2222fd8fe3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -1934,6 +1934,26 @@ 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 SET UNLOGGED opt_nowait + { + AlterTableSetLoggedAllStmt *n = + makeNode(AlterTableSetLoggedAllStmt); + n->tablespacename = $6; + n->objtype = OBJECT_TABLE; + n->logged = false; + n->nowait = $9; + $$ = (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 53a511f1da..16606448bf 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -161,6 +161,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: @@ -1732,6 +1733,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 */ @@ -2619,6 +2626,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 08c463d3c4..646928466d 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/nodes.h b/src/include/nodes/nodes.h index caed683ba9..16d91d3e1d 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -424,6 +424,7 @@ typedef enum NodeTag T_AlterCollationStmt, T_CallStmt, T_AlterStatsStmt, + T_AlterTableSetLoggedAllStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index dc2bb40926..c3eab6f1ab 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2253,6 +2253,15 @@ typedef struct AlterTableMoveAllStmt bool nowait; } AlterTableMoveAllStmt; +typedef struct AlterTableSetLoggedAllStmt +{ + NodeTag type; + char *tablespacename; + ObjectType objtype; /* Object type to move */ + bool logged; + bool nowait; +} AlterTableSetLoggedAllStmt; + /* ---------------------- * Create/Alter Extension Statements * ---------------------- -- 2.27.0 ----Next_Part(Thu_Jan_14_17_32_17_2021_289)---- ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Missing docs for new enable_group_by_reordering GUC @ 2024-06-18 06:13 Andrei Lepikhov <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Andrei Lepikhov @ 2024-06-18 06:13 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; pgsql-hackers; +Cc: Alexander Korotkov <[email protected]> On 6/18/24 09:32, Bruce Momjian wrote: > This commit added enable_group_by_reordering: > > commit 0452b461bc4 > Author: Alexander Korotkov <[email protected]> > Date: Sun Jan 21 22:21:36 2024 +0200 > It mentions it was added as a GUC to postgresql.conf, but I see no SGML > docs for this new GUC value. Would someone please add docs for this? > Thanks. It is my mistake, sorry for that. See the patch in attachment. -- regards, Andrei Lepikhov Attachments: [text/x-patch] 0001-Add-doc-entry-for-the-new-paramenter-enable_group_by.patch (1.3K, ../../[email protected]/2-0001-Add-doc-entry-for-the-new-paramenter-enable_group_by.patch) download | inline diff: From b2d5aca0547bf912032ad60a30a55ef86a28708e Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" <[email protected]> Date: Tue, 18 Jun 2024 13:11:52 +0700 Subject: [PATCH] Add doc entry for the new paramenter enable_group_by_reordering --- doc/src/sgml/config.sgml | 14 ++++++++++++++ 1 file changed, 14 insertions(+) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 698169afdb..61093a033d 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5327,6 +5327,20 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" </listitem> </varlistentry> + <varlistentry id="guc-enable-groupby-reordering" xreflabel="enable_group_by_reordering"> + <term><varname>enable_group_by_reordering</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>enable_group_by_reordering</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Enables or disables reordering of keys in a <literal>GROUP BY</literal> + clause. The default is <literal>on</literal>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-enable-gathermerge" xreflabel="enable_gathermerge"> <term><varname>enable_gathermerge</varname> (<type>boolean</type>) <indexterm> -- 2.39.2 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Missing docs for new enable_group_by_reordering GUC @ 2024-06-18 12:13 Alexander Korotkov <[email protected]> parent: Andrei Lepikhov <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Alexander Korotkov @ 2024-06-18 12:13 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Bruce Momjian <[email protected]>; pgsql-hackers On Tue, Jun 18, 2024 at 9:14 AM Andrei Lepikhov <[email protected]> wrote: > On 6/18/24 09:32, Bruce Momjian wrote: > > This commit added enable_group_by_reordering: > > > > commit 0452b461bc4 > > Author: Alexander Korotkov <[email protected]> > > Date: Sun Jan 21 22:21:36 2024 +0200 > > It mentions it was added as a GUC to postgresql.conf, but I see no SGML > > docs for this new GUC value. Would someone please add docs for this? > > Thanks. > It is my mistake, sorry for that. See the patch in attachment. Bruce, thank for noticing. Andrei, thank you for providing a fix. Please, check the revised patch. ------ Regards, Alexander Korotkov Supabase Attachments: [application/octet-stream] v2-0001-Add-doc-entry-for-the-new-GUC-paramenter-enable_g.patch (2.0K, ../../CAPpHfdtmVCrsEYWhugW9VRNGBCcmOVNa5-MtdvfJgwpNrMRAsQ@mail.gmail.com/2-v2-0001-Add-doc-entry-for-the-new-GUC-paramenter-enable_g.patch) download | inline diff: From f44436b9ebd3f63cdd4d1d3e1b46a8f660e5edac Mon Sep 17 00:00:00 2001 From: Alexander Korotkov <[email protected]> Date: Tue, 18 Jun 2024 15:08:17 +0300 Subject: [PATCH v2] Add doc entry for the new GUC paramenter enable_group_by_reordering 0452b461bc4 adds alternative orderings of group-by keys during the query optimization. This new feature is controlled by the new GUC parameter enable_group_by_reordering, which accidentally came without the documentation. This commit adds the missing documentation for that GUC. Reported-by: Bruce Momjian Discussion: https://postgr.es/m/ZnDx2FYlba_OafQd%40momjian.us Author: Andrei Lepikhov --- doc/src/sgml/config.sgml | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 698169afdb6..d45cb02ad15 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5327,6 +5327,24 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" </listitem> </varlistentry> + <varlistentry id="guc-enable-groupby-reordering" xreflabel="enable_group_by_reordering"> + <term><varname>enable_group_by_reordering</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>enable_group_by_reordering</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Enables or disables the reordering of keys in a + <literal>GROUP BY</literal> clause to match the ordering keys of a + child node of the plan, such as an index scan. When turned off, keys + in a <literal>GROUP BY</literal> clause are only reordered to match + the <literal>ORDER BY</literal> clause, if any. The default is + <literal>on</literal>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-enable-gathermerge" xreflabel="enable_gathermerge"> <term><varname>enable_gathermerge</varname> (<type>boolean</type>) <indexterm> -- 2.39.3 (Apple Git-145) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-18 12:13 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-11 14:21 [PATCH v5 2/2] New command ALTER TABLE ALL IN TABLESPACE SET LOGGED/UNLOGGED Kyotaro Horiguchi <[email protected]> 2024-06-18 06:13 Re: Missing docs for new enable_group_by_reordering GUC Andrei Lepikhov <[email protected]> 2024-06-18 12:13 ` Re: Missing docs for new enable_group_by_reordering GUC Alexander Korotkov <[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