Re: Thinking about EXPLAIN ALTER TABLE

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Greg Stark <stark(at)mit(dot)edu>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Thinking about EXPLAIN ALTER TABLE
Date: 2018-12-10 16:13:51
Message-ID: 20181210161351.sbmseoqtdzwj7iao@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Greg

On 2018-Dec-07, Greg Stark wrote:

> I'm thinking I should try to move all these decisions to phase 1 as
> much as possible but I'm not sure how feasible it will be to get the
> results exactly correct. Of course the cases where it's hardest to
> predict are precisely where users would most like to know what's going
> to happen...

Maybe you can move some of these decisions to phase 1, but I'm not sure
it can be done for all of them. Another possible plan is to add a flag
"dry run" so that phases 2/3 do whatever analysis they need to report
for your EXPLAIN, but not actually carry out their tasks. (I see two
options to implement this, one is a global flag and the other is a new
argument to all those routines.)

> postgres***=# explain alter table t set unlogged;
> ┌─────────────────────────────────────┐
> │ QUERY PLAN │
> ├─────────────────────────────────────┤
> │ Lock Level: AccessExclusiveLock │
> │ ALTER TABLE: t │
> │ Relation: t │
> │ Rewrite: Due to ALTER PERSISTENCE │
> └─────────────────────────────────────┘

Note there's a relation scan that doesn't rewrite (to verify constraints
IIRC). That's certainly worth reporting in some form. Maybe instead of
"Rewrite:" use something like "Scan: read-only / rewrite due to ALTER
PERSISTENCE".

But ... not sure what you propose to print when a table rewrite is
caused by two subcommands, say change persistence at the same time as a
column datatype. And what if you add a new constraint together with
those two?

> postgres***=# explain alter table t alter column i set not null;
> ┌─────────────────────────────────┐
> │ QUERY PLAN │
> ├─────────────────────────────────┤
> │ Lock Level: AccessExclusiveLock │
> │ ALTER TABLE: t │
> │ Relation: t │
> │ Rewrite: none │
> │ Relation: t2 │
> │ Rewrite: none │
> └─────────────────────────────────┘

I think putting the "Rewrite:" at the same indentation level as the
relation that it qualifies is confusing. I'd do it this way:

┌─────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────┤
│ Lock Level: AccessExclusiveLock │
│ ALTER TABLE: t │
│ Relation: t │
│ Rewrite: none │
│ Relation: t2 │
│ Rewrite: none │
└─────────────────────────────────┘

Maybe make the output some legible form of YAML or JSON?

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2018-12-10 16:14:34 Re: Pluggable Storage - Andres's take
Previous Message Tom Lane 2018-12-10 15:44:52 Re: docs: outdated reference to recursive expression evaluation