Re: Should REINDEX be listed under DDL?

On Mon, 4 Dec 2023 at 02:54, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> REINDEX is philosophically a maintenance command and a Postgres
> > extension not in the SQL standard, so it does not really qualify as a
> > DDL because it does not do in object definitions, so we could just
> > delete this comment. Or could it be more useful to consider that as a
> > special case and report it as a DDL, impacting log_statements?
>
> It should be qualified just like CREATE INDEX.
> Both are not covered by the standard, which does not mention indexes,
> since they are an "implementation detail".
>
> I think that it is pretty clear that CREATE INDEX should be considered
> DDL, since it defines (creates) and object. The same should apply to
> REINDEX.
>

Isn't REINDEX more like REFRESH MATERIALIZED VIEW and CLUSTER (especially
without USING)?

CREATE INDEX (really, CREATE anything) is clearly DDL as it creates a new
object, and DROP and ALTER are the same. But REINDEX just reaches below the
abstraction and maintains the existing object without changing its
definition.

I don't think whether it's in the standard is the controlling fact. It's
not just DDL vs. not; there are naturally at least 3 categories: DDL,
maintenance, and data modification.

Getting back to the question at hand, I think REINDEX should be treated the
same as VACUUM and CLUSTER (without USING). So if and only if they are
considered DDL for this purpose then REINDEX should be too.