Thread

1. Should REINDEX be listed under DDL?

   Michael Paquier <michael@paquier.xyz> — 2023-12-04T05:26:48Z

   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
   

2. Re: Should REINDEX be listed under DDL?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-12-04T07:53:56Z

   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
   
   
   
   

3. Re: Should REINDEX be listed under DDL?

   Isaac Morland <isaac.morland@gmail.com> — 2023-12-04T12:50:10Z

   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.