Some more doc patches for partitioned tables. In particular replace the caveat
that INCLUDING CONSTRAINTS doesn't exist and replace it with documentation of,
well, INCLUDING CONSTRAINTS.
Also, there was an instance of "LIKE WITH DEFAULTS" which is actually spelled
"LIKE INCLUDING DEFAULTS".
*** ddl.sgml 05 Sep 2006 22:08:33 +0100 1.61
--- ddl.sgml 19 Sep 2006 10:41:18 +0100
***************
*** 2083,2093 ****
<para>
One convenient way to create a compatible table to be a new child is using
the <command>LIKE</command> option of <command>CREATE TABLE</command>. This
! creates a table with the same columns with the same type (however note the
! caveat below regarding constraints). Alternatively a compatible table can
! be created by first creating a new child using <command>CREATE
! TABLE</command> then removing the inheritance link with <command>ALTER
! TABLE</command>.
</para>
<para>
--- 2083,2096 ----
<para>
One convenient way to create a compatible table to be a new child is using
the <command>LIKE</command> option of <command>CREATE TABLE</command>. This
! creates a table with the same columns with the same type. If there are any
! <command>CHECK</command> constraints defined on the parent table
! the <command>INCLUDING CONSTRAINTS</command> option
! to <command>LIKE</command> may be useful as the new child must have
! constraints matching the parentto be considered compatible. Alternatively a
! compatible table can be created by first creating a new child
! using <command>CREATE TABLE</command> then removing the inheritance link
! with <command>ALTER TABLE</command>.
</para>
<para>
***************
*** 2161,2179 ****
<listitem>
<para>
- There is no convenient way to define a table compatible with a specific
- parent including columns and constraints. The <command>LIKE</command>
- option for <command>CREATE TABLE</command> does not copy constraints
- which makes the tables it creates ineligible for being added using
- <command>ALTER TABLE</command>. Matching check constraints must be added
- manually or the table must be created as a child immediately, then if
- needed removed from the inheritance structure temporarily to be added
- again later.
- </para>
- </listitem>
-
- <listitem>
- <para>
If a table is ever removed from the inheritance structure using
<command>ALTER TABLE</command> then all its columns will be marked as
being locally defined. This means <command>DROP COLUMN</command> on the
--- 2164,2169 ----
***************
*** 2577,2632 ****
constraint for its partition.
</para>
</listitem>
! <listitem>
! <para>
! When the time comes to archive and remove the old data we first remove
! it from the production table using:
<programlisting>
ALTER TABLE measurement_y2003mm02 NO INHERIT measurement
</programlisting>
! Then we can perform any sort of data modification necessary prior to
! archiving without impacting the data viewed by the production system.
! This could include, for example, deleting or compressing out redundant
! data.
! </para>
! </listitem>
! <listitem>
! <para>
! Similarly we can a new partition to handle new data. We can either
! create an empty partition as the original partitions were created
! above, or for some applications it's necessary to bulk load and clean
! data for the new partition. If that operation involves multiple steps
! by different processes it can be helpful to work with it in a fresh
! table outside of the master partitioned table until it's ready to be
! loaded:
<programlisting>
! CREATE TABLE measurement_y2006m02 (LIKE measurement WITH DEFAULTS);
\COPY measurement_y2006m02 FROM 'measurement_y2006m02'
UPDATE ...
ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE
'2006-03-01');
ALTER TABLE measurement_y2006m02 INHERIT MEASUREMENT;
</programlisting>
- </para>
- </listitem>
-
- </orderedlist>
</para>
- <para>
- As we can see, a complex partitioning scheme could require a
- substantial amount of DDL. In the above example we would be
- creating a new partition each month, so it may be wise to write a
- script that generates the required DDL automatically.
- </para>
<para>
! The following caveats apply:
<itemizedlist>
<listitem>
<para>
--- 2567,2654 ----
constraint for its partition.
</para>
</listitem>
+ </orderedlist>
+ </para>
! <para>
! As we can see, a complex partitioning scheme could require a
! substantial amount of DDL. In the above example we would be
! creating a new partition each month, so it may be wise to write a
! script that generates the required DDL automatically.
! </para>
!
! <sect2 id="ddl-partitioning-managing-partitions">
! <title>Managing Partitions</title>
!
! <para>
! Normally the set of partitions established when initally defining the
! table are not intended to remain static. It's common to want to remove
! old partitions of data from the partitioned tables and add new partitions
! for new data periodically. One of the most important advantages of
! partitioning is precisely that it allows this otherwise painful task to
! be executed nearly instantaneously by manipulating the partition
! structure rather than moving the large amounts of data around physically.
! </para>
!
! <para>
! The simplest option for removing old data is to simply DROP the partition
! that is no longer necessary.
!
! <programlisting>
! DROP TABLE measurement_y2003mm02
! </programlisting>
!
! This can very quickly delete millions of records because it doesn't have
! to individually delete every record. (Note however that some filesystems
! may still take some time to delete large files.)
! </para>
!
! <para>
! Another option that is often preferable is to remove the partition from
! the partitioned table but retain access to it as a table in its own
! right.
<programlisting>
ALTER TABLE measurement_y2003mm02 NO INHERIT measurement
</programlisting>
! This allows further operations on the data before actually dropping it.
! Often this is a useful time to back up the data using COPY, pg_dump, or
! other tools, for example. It can also be a useful time to aggregate data
! into smaller formats, perform other data manipulations, or run reports.
! </para>
!
! <para>
! Similarly we can add a new partition to handle new data. We can create an
! empty partition in the partitioned table just as the original partitions
! were created above.
!
! <programlisting>
! CREATE TABLE measurement_y2006m02 (
! CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
! ) INHERITS (measurement);
! </programlisting>
! Alternatively sometimes it's easier to create the table outside the
! partition structure and move it in later. This allows the data to be
! loaded and checked prior to it appearing in the partitioned table. It
! also allows any data manipulation necessary to be performed without the
! intermediate data appearing in queries against the partitioned tale.
<programlisting>
! CREATE TABLE measurement_y2006m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
\COPY measurement_y2006m02 FROM 'measurement_y2006m02'
UPDATE ...
ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE
'2006-03-01');
ALTER TABLE measurement_y2006m02 INHERIT MEASUREMENT;
</programlisting>
</para>
+ <sect2 id="ddl-partitioning-caveats">
<para>
! The following caveats currently apply to partitioned tables:
<itemizedlist>
<listitem>
<para>
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
