Home • Explore • Blog • CI
☰



postgresql
19th chunk of `doc/src/sgml/ref/alter_table.sgml`
053165b947e1eaa891d9d3b83379327ca7222bd20ee058ae0000000100000fa1
 satisfying the desired partition constraint before running this
      command. The <literal>CHECK</literal> constraint will be used to
      determine that the table need not be scanned to validate the partition
      constraint. This does not work, however, if any of the partition keys
      is an expression and the partition does not accept
      <literal>NULL</literal> values. If attaching a list partition that will
      not accept <literal>NULL</literal> values, also add a
      <literal>NOT NULL</literal> constraint to the partition key column,
      unless it's an expression.
     </para>

     <para>
      If the new partition is a foreign table, nothing is done to verify
      that all the rows in the foreign table obey the partition constraint.
      (See the discussion in <xref linkend="sql-createforeigntable"/> about
      constraints on the foreign table.)
     </para>

     <para>
      When a table has a default partition, defining a new partition changes
      the partition constraint for the default partition. The default
      partition can't contain any rows that would need to be moved to the new
      partition, and will be scanned to verify that none are present. This
      scan, like the scan of the new partition, can be avoided if an
      appropriate <literal>CHECK</literal> constraint is present. Also like
      the scan of the new partition, it is always skipped when the default
      partition is a foreign table.
     </para>

     <para>
      Attaching a partition acquires a
      <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the parent table,
      in addition to the <literal>ACCESS EXCLUSIVE</literal> locks on the table
      being attached and on the default partition (if any).
     </para>

     <para>
      Further locks must also be held on all sub-partitions if the table being
      attached is itself a partitioned table.  Likewise if the default
      partition is itself a partitioned table.  The locking of the
      sub-partitions can be avoided by adding a <literal>CHECK</literal>
      constraint as described in
      <xref linkend="ddl-partitioning-declarative-maintenance"/>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-detach-partition">
    <term><literal>DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]</literal></term>

    <listitem>
     <para>
      This form detaches the specified partition of the target table.  The detached
      partition continues to exist as a standalone table, but no longer has any
      ties to the table from which it was detached.  Any indexes that were
      attached to the target table's indexes are detached.  Any triggers that
      were created as clones of those in the target table are removed.
      <literal>SHARE</literal> lock is obtained on any tables that reference
      this partitioned table in foreign key constraints.
     </para>
     <para>
      If <literal>CONCURRENTLY</literal> is specified, it runs using a reduced
      lock level to avoid blocking other sessions that might be accessing the
      partitioned table.  In this mode, two transactions are used internally.
      During the first transaction, a <literal>SHARE UPDATE EXCLUSIVE</literal>
      lock is taken on both parent table and partition, and the partition is
      marked as undergoing detach; at that point, the transaction is committed
      and all other transactions using the partitioned table are waited for.
      Once all those transactions have completed, the second transaction
      acquires <literal>SHARE UPDATE EXCLUSIVE</literal> on the partitioned
      table and <literal>ACCESS EXCLUSIVE</literal> on the partition,
      and the detach process completes.  A <literal>CHECK</literal> constraint
      that duplicates the partition constraint is added to the partition.
      <literal>CONCURRENTLY</literal> cannot be run in a transaction block and
      is not allowed if
Title: ALTER TABLE: Attaching and Detaching Partitions (Continued)
Summary
This section continues discussing the process of attaching partitions, including considerations for default partitions and how defining a new partition can change the constraints of the default partition. It also discusses the locks acquired during the attach operation. The section then discusses detaching partitions and how the detached partition becomes a standalone table, the removal of indexes and triggers, and the locks obtained. It details the use of the CONCURRENTLY option for detaching partitions with a reduced lock level, including its two-transaction process and restrictions. This involves marking the partition as detaching, waiting for other transactions, and then completing the detach, and adding a CHECK constraint.