Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kz4VI-0002zI-M4 for pgsql-bugs@arkaria.postgresql.org; Mon, 11 Jan 2021 21:13:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kz4VH-0003BC-BB for pgsql-bugs@arkaria.postgresql.org; Mon, 11 Jan 2021 21:13:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kz4VG-00039V-IY for pgsql-bugs@lists.postgresql.org; Mon, 11 Jan 2021 21:13:31 +0000 Received: from [66.111.4.29] (helo=out5-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kz4VD-0000qh-Pe for pgsql-bugs@lists.postgresql.org; Mon, 11 Jan 2021 21:13:29 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.42]) by mailout.nyi.internal (Postfix) with ESMTP id B18675C0198; Mon, 11 Jan 2021 16:13:16 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Mon, 11 Jan 2021 16:13:16 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:subject:to :x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; bh=cVu2UZ/J1cu1yA6ylv/D2Op+gpfXSp+3JtPGiII8uao=; b=AjRjkLfJ Nbbdc653doJ/AshR9nw3jvHkmaftt75mxd3rKP7D7a7KiKCWotZlPrpf6MwGgpmF Ra8evPmMjxZV0wplxCzQb/M8YmNGy4Th1vgAzzvfoKo1zff06J/5GoFYG+wHzpVj c73XdxzqBaLkr22UpG9VC/LemYPxxKqz6L1V50DKZlCPScxD4NEhYhsh1fIr7/Fc Q8lDnaPzURkUNBmz4fX9d0rDx4ykJGdne+y9LFkc/yLCyWofk7LPSPJde39rOC0/ OAD3ZXZdNAFP0FZXxeIroj6KEc+4T3liCzv6uYJrr9nktkpw5Ptt5Mb5tY7/kvcw ua6SgYGhOLudWw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedrvdehuddgudegjecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepfffhvffukfggtggugfgjfgesmh ekreertderudenucfhrhhomheptehlvhgrrhhoucfjvghrrhgvrhgruceorghlvhhhvghr rhgvsegrlhhvhhdrnhhoqdhiphdrohhrgheqnecuggftrfgrthhtvghrnhephfefgeelke fgjeeuhefgfeevleeftedvhefftdeutefghfekhfduheffkeduteeknecukfhppeduledt rdelhedrudelrddujeejnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomheprghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh X-ME-Proxy: Received: from perhan.alvh.no-ip.org (unknown [190.95.19.177]) by mail.messagingengine.com (Postfix) with ESMTPA id 40A5724005A; Mon, 11 Jan 2021 16:13:16 -0500 (EST) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id B0B992A0BE3; Mon, 11 Jan 2021 18:13:13 -0300 (-03) Date: Mon, 11 Jan 2021 18:13:13 -0300 From: Alvaro Herrera To: boekewurm+postgres@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16818: progress reporting ALTER TABLE ADD UNIQUE Message-ID: <20210111211313.GA25789@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="J2SCkAp4GZ/dPZZf" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <16818-4313f4b1612e09d7@postgresql.org> User-Agent: Mutt/1.10.1 (2018-07-13) X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.29 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --J2SCkAp4GZ/dPZZf Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit On 2021-Jan-11, PG Bug reporting form wrote: > The progress reprorting for `ALTER TABLE test ADD UNIQUE (col)` is in > `pg_stat_progress_create_index`. As it indeed creates an index, that is not > too unexpected, but the `command` column of that view reports `CREATE > INDEX`, and _that_ is somewhat unexpected. A reasonable expectation would be > `ALTER TABLE ADD CONSTRAINT` or comparable. Hmm, seems a reasonable complaint. Are there other command wordings that would need to be handled? I can't think of any (but I already overlooked this one, evidently ...) This seems fixed easily, in a way -- we'd need to set a distinct value to the PROGRESS_CREATEIDX_COMMAND param when ALTER TABLE ADD; currently possible values are in progress.h: /* Commands of PROGRESS_CREATEIDX */ #define PROGRESS_CREATEIDX_COMMAND_CREATE 1 #define PROGRESS_CREATEIDX_COMMAND_CREATE_CONCURRENTLY 2 #define PROGRESS_CREATEIDX_COMMAND_REINDEX 3 #define PROGRESS_CREATEIDX_COMMAND_REINDEX_CONCURRENTLY 4 The problem is that we'd need to change system_view.sql to recognize the new value, and we can't change that on existing systems. If we fail to adjust that view definition, the column will show NULL when the command is ALTER TABLE ADD. Something like the attached patch, but I haven't tried to compile it yet. Probably need docs adjustments also. (Also: I don't see we set PROGRESS_CREATEIDX_COMMAND_REINDEX_CONCURRENTLY anywhere ... an oversight?) -- Álvaro Herrera --J2SCkAp4GZ/dPZZf Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="alter-table-add-progress.patch" commit adce17b33253a42c7e60f17c50698facfa7bd729 Author: Alvaro Herrera AuthorDate: Mon Jan 11 18:11:04 2021 -0300 CommitDate: Mon Jan 11 18:11:04 2021 -0300 First attempt at handling ALTER TABLE ADD in create index progress reporting diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 5d89e77dbe..7892988570 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1075,6 +1075,7 @@ CREATE VIEW pg_stat_progress_create_index AS WHEN 2 THEN 'CREATE INDEX CONCURRENTLY' WHEN 3 THEN 'REINDEX' WHEN 4 THEN 'REINDEX CONCURRENTLY' + WHEN 5 THEN 'ALTER TABLE ADD CONSTRAINT' END AS command, CASE S.param10 WHEN 0 THEN 'initializing' WHEN 1 THEN 'waiting for writers before build' diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 992f4813b4..0a72b10b0d 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -575,6 +575,8 @@ DefineIndex(Oid relationId, pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX, relationId); pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND, + is_alter_table ? + PROGRESS_CREATEIDX_COMMAND_ALTER_TABLE_ADD : concurrent ? PROGRESS_CREATEIDX_COMMAND_CREATE_CONCURRENTLY : PROGRESS_CREATEIDX_COMMAND_CREATE); diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h index 95ec5d02e9..54e68a2ba0 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -109,6 +109,7 @@ #define PROGRESS_CREATEIDX_COMMAND_CREATE_CONCURRENTLY 2 #define PROGRESS_CREATEIDX_COMMAND_REINDEX 3 #define PROGRESS_CREATEIDX_COMMAND_REINDEX_CONCURRENTLY 4 +#define PROGRESS_CREATEIDX_COMMAND_ALTER_TABLE_ADD 5 /* Lock holder wait counts */ #define PROGRESS_WAITFOR_TOTAL 3 --J2SCkAp4GZ/dPZZf--