pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #16818: progress reporting ALTER TABLE ADD UNIQUE 3+ messages / 3 participants [nested] [flat]
* BUG #16818: progress reporting ALTER TABLE ADD UNIQUE @ 2021-01-11 19:31 PG Bug reporting form <noreply@postgresql.org> 2021-01-11 21:13 ` Re: BUG #16818: progress reporting ALTER TABLE ADD UNIQUE Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 1 reply; 3+ messages in thread From: PG Bug reporting form @ 2021-01-11 19:31 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: boekewurm+postgres@gmail.com The following bug has been logged on the website: Bug reference: 16818 Logged by: Matthias van de Meent Email address: boekewurm+postgres@gmail.com PostgreSQL version: 12.5 Operating system: Debian Stretch (9.13) Description: This may be considered a nitpick, but: 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. The only discussion regarding `ALTER TABLE` in index progress reporting seems to have been in the original thread[0], but that was about potentially thrashing callers' progress reporting status/values, and less about the command name of this backend state. [0] https://www.postgresql.org/message-id/flat/20190329150828.s2bu4zckuxnceo6u%40alap3.anarazel.de ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16818: progress reporting ALTER TABLE ADD UNIQUE 2021-01-11 19:31 BUG #16818: progress reporting ALTER TABLE ADD UNIQUE PG Bug reporting form <noreply@postgresql.org> @ 2021-01-11 21:13 ` Alvaro Herrera <alvherre@alvh.no-ip.org> 2021-01-11 22:12 ` Re: BUG #16818: progress reporting ALTER TABLE ADD UNIQUE Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Alvaro Herrera @ 2021-01-11 21:13 UTC (permalink / raw) To: boekewurm+postgres@gmail.com; pgsql-bugs@lists.postgresql.org 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 Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadalter-table-add-progress.patch (text/x-diff, 2.0K) download
commit adce17b33253a42c7e60f17c50698facfa7bd729 Author: Alvaro Herrera <alvherre@alvh.no-ip.org> 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
* Re: BUG #16818: progress reporting ALTER TABLE ADD UNIQUE 2021-01-11 19:31 BUG #16818: progress reporting ALTER TABLE ADD UNIQUE PG Bug reporting form <noreply@postgresql.org> 2021-01-11 21:13 ` Re: BUG #16818: progress reporting ALTER TABLE ADD UNIQUE Alvaro Herrera <alvherre@alvh.no-ip.org> @ 2021-01-11 22:12 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2021-01-11 22:12 UTC (permalink / raw) To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: boekewurm+postgres@gmail.com; pgsql-bugs@lists.postgresql.org Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > 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 ...) TBH, I think that reporting it as "CREATE INDEX" is good, and what the OP is asking for is less good. Creating an index is what is actually the time-consuming step here --- making the catalog entries for the constraint is negligible. Also, just how picky would we be about replicating the command spelling -- e.g., consider "ALTER TABLE t ADD PRIMARY KEY(p)" vs "ALTER TABLE t ADD CONSTRAINT c PRIMARY KEY(p)" vs "ALTER TABLE t ADD COLUMN c int PRIMARY KEY" vs all the same options for UNIQUE vs all the same options for EXCLUSION vs yadda yadda. That is not going to be helpful to anybody, IMO, especially not automated tools that might be watching the progress view. It's reasonable for the view to distinguish REINDEX and CONCURRENTLY options, as those are relevant to performance, but I don't think we should add purely-cosmetic variations. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-01-11 22:12 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-01-11 19:31 BUG #16818: progress reporting ALTER TABLE ADD UNIQUE PG Bug reporting form <noreply@postgresql.org> 2021-01-11 21:13 ` Alvaro Herrera <alvherre@alvh.no-ip.org> 2021-01-11 22:12 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox