Re: pgsql: Allow UNIQUE indexes on partitioned tables

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: pgsql: Allow UNIQUE indexes on partitioned tables
Дата
Msg-id CAKFQuwY4Ld7ecxL_KAmaxwt0FUu5VcPPN2L4dh+3BeYbrdBa5g@mail.gmail.com
обсуждение исходный текст
Ответы Re: pgsql: Allow UNIQUE indexes on partitioned tables
Re: pgsql: Allow UNIQUE indexes on partitioned tables
Список pgsql-hackers
Дерево обсуждения
I found the following change to be confusing.

/doc/src/sgml/ref/alter_table.sgml
+       <para>
+        Additional restrictions apply when unique indexes are applied to
+        partitioned tables; see <xref linkend="sql-createtable" />.
+       </para>

That paragraph appears in the section covering "ALTER TABLE name ADD table_constraint_using_index"

However, the code says:

/src/backend/commands/tablecmds.c
+   /*
+    * Doing this on partitioned tables is not a simple feature to implement,
+    * so let's punt for now.
+    */
+   if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+       ereport(ERROR,
+               (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                errmsg("ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables")));

I was expecting the doc for ADD CONSTRAINT USING INDEX to note the limitation explicitly - in lieu of the above paragraph.

Also, I cannot reason out what the following limitation means:

/doc/src/sgml/ref/create_table.sgml
+      If any partitions are in turn partitioned, all columns of each partition
+      key are considered at each level below the <literal>UNIQUE</literal>
+      constraint.


As an aside, adding a link to "Data Definiton/Table Partitioning" from at least CREATE TABLE ... PARTITION BY; and swapping "PARTITION BY" and "PARTITION OF" in the Parameters section of that page - one must partition by a table before one can partition it (and also the synopsis lists them in the BY before OF order), would be helpful.

David J.


On Mon, Feb 19, 2018 at 1:40 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Allow UNIQUE indexes on partitioned tables

If we restrict unique constraints on partitioned tables so that they
must always include the partition key, then our standard approach to
unique indexes already works --- each unique key is forced to exist
within a single partition, so enforcing the unique restriction in each
index individually is enough to have it enforced globally.  Therefore we
can implement unique indexes on partitions by simply removing a few
restrictions (and adding others.)

Discussion: https://postgr.es/m/20171222212921.hi6hg6pem2w2t36z@alvherre.pgsql
Discussion: https://postgr.es/m/20171229230607.3iib6b62fn3uaf47@alvherre.pgsql
Reviewed-by: Simon Riggs, Jesper Pedersen, Peter Eisentraut, Jaime
        Casanova, Amit Langote

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/eb7ed3f3063401496e4aa4bd68fa33f0be31a72f

Modified Files
--------------
doc/src/sgml/ddl.sgml                         |   9 +-
doc/src/sgml/ref/alter_table.sgml             |  15 +-
doc/src/sgml/ref/create_index.sgml            |   5 +
doc/src/sgml/ref/create_table.sgml            |  18 +-
src/backend/bootstrap/bootparse.y             |   2 +
src/backend/catalog/index.c                   |  50 ++++-
src/backend/catalog/pg_constraint.c           |  76 +++++++
src/backend/catalog/toasting.c                |   4 +-
src/backend/commands/indexcmds.c              | 125 +++++++++--
src/backend/commands/tablecmds.c              |  71 ++++++-
src/backend/parser/analyze.c                  |   7 +
src/backend/parser/parse_utilcmd.c            |  31 +--
src/backend/tcop/utility.c                    |   1 +
src/bin/pg_dump/t/002_pg_dump.pl              |  65 ++++++
src/include/catalog/index.h                   |   5 +-
src/include/catalog/pg_constraint_fn.h        |   4 +-
src/include/commands/defrem.h                 |   1 +
src/include/parser/parse_utilcmd.h            |   3 +-
src/test/regress/expected/alter_table.out     |   8 -
src/test/regress/expected/create_index.out    |   6 +
src/test/regress/expected/create_table.out    |  12 --
src/test/regress/expected/indexing.out        | 294 +++++++++++++++++++++++++-
src/test/regress/expected/insert_conflict.out |   2 +-
src/test/regress/sql/alter_table.sql          |   2 -
src/test/regress/sql/create_index.sql         |   6 +
src/test/regress/sql/create_table.sql         |   8 -
src/test/regress/sql/indexing.sql             | 172 ++++++++++++++-
27 files changed, 907 insertions(+), 95 deletions(-)


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Claudio Freire
Дата:
Сообщение: Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem
Следующее
От: Oleg Bartunov
Дата:
Сообщение: Re: [PROPOSAL] Nepali Snowball dictionary