Thread: Should REINDEX be listed under DDL?

Should REINDEX be listed under DDL?

From
Michael Paquier
Date:
Hi all,

On a recent thread about adding support for event triggers with
REINDEX, a change has been proposed to make REINDEX queries reflect in
the logs under the DDL category:
https://www.postgresql.org/message-id/ZW0ltJXJ2Aigvizl%40paquier.xyz

REINDEX being classified as LOGSTMT_ALL comes from 893632be4e17 back
in 2006, and the code does not know what to do about it.  Doing the
change would be as simple as that:
        case T_ReindexStmt:
-           lev = LOGSTMT_ALL;  /* should this be DDL? */
+           lev = LOGSTMT_DDL;

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?

Any thoughts?
--
Michael

Attachment

Re: Should REINDEX be listed under DDL?

From
Laurenz Albe
Date:
On Mon, 2023-12-04 at 14:26 +0900, Michael Paquier wrote:
> On a recent thread about adding support for event triggers with
> REINDEX, a change has been proposed to make REINDEX queries reflect in
> the logs under the DDL category:
> https://www.postgresql.org/message-id/ZW0ltJXJ2Aigvizl%40paquier.xyz
>
> REINDEX being classified as LOGSTMT_ALL comes from 893632be4e17 back
> in 2006, and the code does not know what to do about it.  Doing the
> change would be as simple as that:
>         case T_ReindexStmt:
> -           lev = LOGSTMT_ALL;  /* should this be DDL? */
> +           lev = LOGSTMT_DDL;
>
> 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.

Yours,
Laurenz Albe



Re: Should REINDEX be listed under DDL?

From
Isaac Morland
Date:
On Mon, 4 Dec 2023 at 02:54, Laurenz Albe <laurenz.albe@cybertec.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.